SQL Projects — Before Getting Started
Practice is the fastest way to learn SQL. Below are 5 college-level projects with simplified 2–3 table schemas and 15–20 questions per project progressing from easy to medium. Create the tables, insert sample data, and try solving every question.
How to use: For each project, first create the database and tables using the schema provided, then run the sample INSERT queries below to populate the tables with data. Solve each question in order — easy questions test single-table basics, medium questions require JOINs and GROUP BY. The questions are framed based on the inserted sample data, so you can verify your answers against the known data.
PROJECT 1 Student Marks & Address System
Problem Statement
A college needs a database to store student details, their addresses, and marks obtained in various subjects. The system should allow listing students by city, finding toppers, identifying students who failed, and generating subject-wise reports. Each student has one address. Marks are recorded per subject per student.
Schema
CREATE DATABASE student_management;
USE student_management;
CREATE TABLE students (
roll_no INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
dob DATE,
gender CHAR(1),
phone VARCHAR(15)
);
CREATE TABLE addresses (
id INT AUTO_INCREMENT PRIMARY KEY,
roll_no INT,
city VARCHAR(50),
state VARCHAR(50),
pincode VARCHAR(10),
FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);
CREATE TABLE marks (
id INT AUTO_INCREMENT PRIMARY KEY,
roll_no INT,
subject VARCHAR(50),
marks_obtained INT,
FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);Sample Data (INSERT Queries)
Sample Data
-- Insert students
INSERT INTO students VALUES
(1, 'Aarav Mehta', '2002-03-15', 'M', '9876543210'),
(2, 'Priya Sharma', '2003-07-22', 'F', '9876543211'),
(3, 'Rohan Gupta', '2002-11-10', 'M', '9876543212'),
(4, 'Ananya Patel', '2003-01-05', 'F', '9876543213'),
(5, 'Vikram Singh', '2002-09-18', 'M', '9876543214'),
(6, 'Neha Reddy', '2003-05-25', 'F', '9876543215'),
(7, 'Aditya Nair', '2002-12-30', 'M', '9876543216'),
(8, 'Kavya Iyer', '2003-04-12', 'F', '9876543217'),
(9, 'Siddharth Das', '2002-08-08', 'M', '9876543218'),
(10, 'Pooja Joshi', '2003-06-14', 'F', '9876543219');
-- Insert addresses
INSERT INTO addresses (roll_no, city, state, pincode) VALUES
(1, 'Mumbai', 'Maharashtra', '400001'),
(2, 'Delhi', 'Delhi', '110001'),
(3, 'Pune', 'Maharashtra', '411001'),
(4, 'Mumbai', 'Maharashtra', '400002'),
(5, 'Bangalore', 'Karnataka', '560001'),
(6, 'Chennai', 'Tamil Nadu', '600001'),
(7, 'Delhi', 'Delhi', '110002'),
(8, 'Hyderabad', 'Telangana', '500001'),
(9, 'Mumbai', 'Maharashtra', '400003'),
(10, 'Pune', 'Maharashtra', '411002');
-- Insert marks
INSERT INTO marks (roll_no, subject, marks_obtained) VALUES
(1, 'Mathematics', 85),
(1, 'Physics', 72),
(1, 'Chemistry', 68),
(2, 'Mathematics', 92),
(2, 'Physics', 88),
(2, 'Chemistry', 79),
(3, 'Mathematics', 55),
(3, 'Physics', 38),
(3, 'Chemistry', 42),
(4, 'Mathematics', 78),
(4, 'Physics', 65),
(4, 'Chemistry', 71),
(5, 'Mathematics', 40),
(5, 'Physics', 35),
(5, 'Chemistry', 30),
(6, 'Mathematics', 88),
(6, 'Physics', 82),
(6, 'Chemistry', 75),
(7, 'Mathematics', 62),
(7, 'Physics', 45),
(7, 'Chemistry', 50),
(8, 'Mathematics', 95),
(8, 'Physics', 90),
(8, 'Chemistry', 87),
(9, 'Mathematics', 33),
(9, 'Physics', 28),
(9, 'Chemistry', 35),
(10, 'Mathematics', 70),
(10, 'Physics', 58),
(10, 'Chemistry', 63);Practice Questions (18)
Easy (1–10) — Single table, basic WHERE, ORDER BY
- List all students with their name and phone number.
- List all female students ordered by name alphabetically. (Expected: Ananya, Kavya, Neha, Priya, Pooja)
- Find students whose name starts with the letter 'A'.
- Find students born after 2003-01-01. (Expected: Priya, Ananya, Neha, Kavya, Pooja)
- Insert a new student (roll_no: 11, name: 'Ravi Kumar', dob: '2003-05-15', gender: 'M', phone: '9876543220').
- Update the phone number of student 'Pooja Joshi' to '9999999999'.
- Delete the student record with roll_no 11.
- Find all students from the city 'Mumbai' (requires JOIN with addresses). (Expected: Aarav, Ananya, Siddharth)
- List all unique cities from the addresses table. (Expected: Mumbai, Delhi, Pune, Bangalore, Chennai, Hyderabad)
- List all students sorted by name alphabetically.
Medium (11–18) — JOINs, GROUP BY, HAVING, subqueries, aggregates
- List each student’s name alongside their city using JOIN. (Expected: 10 rows)
- Find the total marks obtained by each student (GROUP BY roll_no, ORDER BY total DESC). (Hint: Kavya Iyer should be highest with 272)
- Find the student with the highest total marks using a subquery.
- Count how many students are in each city. (Expected: Mumbai=3, Delhi=2, Pune=2)
- Find subjects where the average marks are below 50 (HAVING AVG < 50).
- List students who scored above 80 in any subject. (Expected: Aarav, Priya, Neha, Kavya)
- Find the city with the most students.
- Display each student’s name, subject, marks, and a grade column using CASE (≥80: A, ≥60: B, ≥40: C, <40: F).
PROJECT 2 Swiggy Food Order Management
Problem Statement
Build a database for a food delivery platform. The system tracks customers, restaurants, and their orders. Each customer can place multiple orders from different restaurants. Each order contains one or more food items with quantity and price. The system should support finding top restaurants, calculating revenue, and analyzing customer behaviour.
Schema
CREATE DATABASE swiggy_db;
USE swiggy_db;
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(50)
);
CREATE TABLE restaurants (
restaurant_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
cuisine VARCHAR(50),
city VARCHAR(50),
rating DECIMAL(2,1)
);
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
restaurant_id INT,
order_date DATE,
item_name VARCHAR(100),
quantity INT,
price DECIMAL(8,2),
status ENUM('Delivered','Cancelled','Pending'),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
);Sample Data (INSERT Queries)
Sample Data
-- Insert customers
INSERT INTO customers (name, city) VALUES
('Rahul Verma', 'Bangalore'),
('Sneha Patel', 'Mumbai'),
('Amit Singh', 'Delhi'),
('Priya Nair', 'Bangalore'),
('Vikash Kumar', 'Delhi'),
('Meera Reddy', 'Hyderabad'),
('Arjun Mehta', 'Mumbai'),
('Deepa Iyer', 'Chennai');
-- Insert restaurants
INSERT INTO restaurants (name, cuisine, city, rating) VALUES
('Meghana Foods', 'Biryani', 'Bangalore', 4.5),
('Anand Bhavan', 'South Indian','Bangalore', 4.2),
('Pizza Palace', 'Italian', 'Mumbai', 4.0),
('Wok In The Cloud','Chinese', 'Delhi', 3.8),
('Spice Garden', 'North Indian','Delhi', 4.3),
('Biryani House', 'Biryani', 'Hyderabad', 4.7),
('Chai Point', 'Snacks', 'Bangalore', 4.1);
-- Insert orders
INSERT INTO orders (customer_id, restaurant_id, order_date, item_name, quantity, price, status) VALUES
(1, 1, '2026-01-10', 'Chicken Biryani', 2, 350.00, 'Delivered'),
(1, 2, '2026-01-15', 'Masala Dosa', 1, 120.00, 'Delivered'),
(1, 7, '2026-02-01', 'Chai + Samosa', 3, 150.00, 'Delivered'),
(2, 3, '2026-01-20', 'Margherita Pizza', 1, 250.00, 'Delivered'),
(2, 3, '2026-03-05', 'Pasta', 2, 300.00, 'Cancelled'),
(3, 5, '2026-02-10', 'Butter Chicken', 1, 280.00, 'Delivered'),
(3, 4, '2026-02-14', 'Hakka Noodles', 2, 200.00, 'Delivered'),
(3, 5, '2026-03-20', 'Paneer Tikka', 1, 220.00, 'Pending'),
(4, 1, '2026-03-01', 'Mutton Biryani', 1, 450.00, 'Delivered'),
(4, 7, '2026-03-10', 'Vada Pav', 2, 80.00, 'Delivered'),
(5, 5, '2026-01-25', 'Dal Makhani', 1, 180.00, 'Delivered'),
(5, 4, '2026-02-28', 'Manchurian', 2, 240.00, 'Delivered'),
(5, 5, '2026-03-15', 'Tandoori Roti', 3, 150.00, 'Cancelled'),
(6, 6, '2026-02-05', 'Hyderabadi Biryani',1, 380.00, 'Delivered'),
(6, 6, '2026-03-12', 'Double Ka Meetha',2, 200.00, 'Delivered'),
(7, 3, '2026-03-08', 'Garlic Bread', 1, 150.00, 'Delivered'),
(7, 3, '2026-03-22', 'Tiramisu', 1, 180.00, 'Pending'),
(8, 3, '2026-04-01', 'Peppy Paneer', 1, 220.00, 'Delivered');Practice Questions (18)
Easy (1–10) — Basic queries, filtering, sorting
- List all customers with their name and city.
- List all restaurants in 'Bangalore' sorted by rating descending. (Expected: Meghana Foods 4.5, Anand Bhavan 4.2, Chai Point 4.1)
- Find all orders placed by customer 'Rahul Verma' (requires JOIN). (Expected: 3 orders)
- Find all orders with status 'Cancelled'. (Expected: 2 orders)
- Find all orders where price is greater than 300. (Expected: Chicken Biryani, Butter Chicken, Mutton Biryani, Hyderabadi Biryani)
- Find all items ordered from 'Pizza Palace'.
- List distinct cities from the customers table.
- Find orders placed between '2026-01-01' and '2026-03-31'.
- List all restaurants with rating above 4.0 sorted by rating.
- Find all 'Delivered' orders from customer 'Priya Nair'.
Medium (11–18) — JOINs, GROUP BY, HAVING, aggregates, multi-table
- List each order with customer name and restaurant name using JOIN. (Expected: 18 rows)
- Find the total number of orders per restaurant (GROUP BY restaurant).
- Find the most ordered item (item with highest total quantity).
- Calculate total revenue per restaurant from delivered orders only.
- Find customers who have placed more than 2 orders (HAVING COUNT > 2). (Expected: Rahul, Vikash, Priya)
- Find the average order value per city (JOIN orders with customers).
- Count cancelled vs delivered orders per restaurant using CASE.
- Find the top 3 highest-spending customers by total amount.
PROJECT 3 Hotel Booking System
Problem Statement
A hotel chain needs a database to manage hotels, room bookings, and guest information. Each hotel offers different room types (Single, Double, Suite) at different prices. Guests book rooms for specific dates and the system tracks booking status (Confirmed, Checked-In, Checked-Out, Cancelled). The system should support finding available rooms, calculating revenue, and generating guest booking history.
Schema
CREATE DATABASE hotel_booking;
USE hotel_booking;
CREATE TABLE hotels (
hotel_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(50),
star_rating INT CHECK (star_rating >= 1 AND star_rating <= 5)
);
CREATE TABLE guests (
guest_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
phone VARCHAR(15),
city VARCHAR(50)
);
CREATE TABLE bookings (
booking_id INT AUTO_INCREMENT PRIMARY KEY,
hotel_id INT,
guest_id INT,
room_type ENUM('Single','Double','Suite'),
check_in DATE,
check_out DATE,
total_amount DECIMAL(10,2),
status ENUM('Confirmed','Checked-In','Checked-Out','Cancelled'),
FOREIGN KEY (hotel_id) REFERENCES hotels(hotel_id),
FOREIGN KEY (guest_id) REFERENCES guests(guest_id)
);Sample Data (INSERT Queries)
Sample Data
-- Insert hotels
INSERT INTO hotels (name, city, star_rating) VALUES
('Taj Palace', 'Delhi', 5),
('ITC Maurya', 'Delhi', 5),
('The Leela', 'Bangalore', 5),
('OYO Townhouse', 'Mumbai', 3),
('Goa Marriott', 'Goa', 4),
('Park Hyatt', 'Hyderabad', 5),
('Budget Inn', 'Delhi', 2);
-- Insert guests
INSERT INTO guests (name, phone, city) VALUES
('Amit Sharma', '9876543001', 'Delhi'),
('Neha Gupta', '9876543002', 'Mumbai'),
('Rahul Joshi', '9876543003', 'Bangalore'),
('Priya Das', '9876543004', 'Delhi'),
('Vikram Rao', '9876543005', 'Hyderabad'),
('Sneha Menon', '9876543006', 'Chennai'),
('Arjun Verma', '9876543007', 'Delhi');
-- Insert bookings
INSERT INTO bookings (hotel_id, guest_id, room_type, check_in, check_out, total_amount, status) VALUES
(1, 1, 'Suite', '2026-01-10', '2026-01-13', 15000.00, 'Checked-Out'),
(1, 4, 'Double', '2026-02-14', '2026-02-16', 8000.00, 'Checked-Out'),
(2, 3, 'Single', '2026-01-20', '2026-01-22', 6000.00, 'Checked-Out'),
(2, 7, 'Suite', '2026-03-05', '2026-03-08', 18000.00, 'Confirmed'),
(3, 2, 'Double', '2026-02-01', '2026-02-04', 12000.00, 'Checked-Out'),
(3, 5, 'Suite', '2026-03-10', '2026-03-12', 16000.00, 'Checked-In'),
(4, 6, 'Single', '2026-03-15', '2026-03-17', 3000.00, 'Confirmed'),
(5, 1, 'Double', '2026-04-01', '2026-04-05', 10000.00, 'Confirmed'),
(5, 3, 'Single', '2026-04-10', '2026-04-12', 5000.00, 'Cancelled'),
(6, 5, 'Double', '2026-01-25', '2026-01-28', 14000.00, 'Checked-Out'),
(6, 2, 'Suite', '2026-03-20', '2026-03-22', 20000.00, 'Cancelled'),
(7, 4, 'Single', '2026-02-20', '2026-02-22', 2000.00, 'Checked-Out'),
(7, 7, 'Double', '2026-04-05', '2026-04-07', 4000.00, 'Confirmed');Practice Questions (17)
Easy (1–9) — Single table operations
- List all hotels with name, city, and star rating.
- List all 5-star hotels sorted by name. (Expected: ITC Maurya, Park Hyatt, Taj Palace, The Leela)
- Find all guests from the city 'Delhi'. (Expected: Amit, Priya, Arjun)
- List all bookings with status 'Confirmed'.
- Find all Suite-type bookings. (Expected: 4 Suite bookings)
- Find bookings where total_amount is greater than 10000.
- List all distinct room types available across all hotels.
- Find all bookings with status 'Cancelled'. (Expected: 2 cancelled bookings)
- List all hotels in 'Delhi' sorted by star_rating descending.
Medium (10–17) — JOINs, GROUP BY, HAVING, aggregates
- List each booking with guest name and hotel name using JOIN. (Expected: 13 rows)
- Find total revenue per hotel (SUM of total_amount, GROUP BY hotel).
- Find the most popular room type by booking count. (Expected: Double with 6 bookings)
- Find hotels where average booking amount exceeds 8000 (HAVING AVG > 8000).
- Count bookings per status per hotel (GROUP BY hotel, status).
- Find guests who have stayed in more than 1 different hotel. (Expected: Rahul, Vikash, Priya)
- Calculate total nights stayed per guest (DATEDIFF check_out, check_in) with JOIN.
- Find the hotel with the most cancelled bookings.
PROJECT 4 Library Book Borrowing System
Problem Statement
A university library manages its book inventory and tracks which member borrowed which book and when. Each book has a title, author, and a fixed number of copies. Members borrow books, and must return them by a due date. Late returns incur a fine of Rs 10 per day. The librarian needs to find overdue books, most popular books, and members with pending fines.
Schema
CREATE DATABASE library_db;
USE library_db;
CREATE TABLE books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author VARCHAR(100),
total_copies INT DEFAULT 1,
available_copies INT DEFAULT 1
);
CREATE TABLE members (
member_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
type ENUM('Student','Faculty')
);
CREATE TABLE borrowings (
borrow_id INT AUTO_INCREMENT PRIMARY KEY,
book_id INT,
member_id INT,
borrow_date DATE,
due_date DATE,
return_date DATE,
fine DECIMAL(6,2) DEFAULT 0,
FOREIGN KEY (book_id) REFERENCES books(book_id),
FOREIGN KEY (member_id) REFERENCES members(member_id)
);Sample Data (INSERT Queries)
Sample Data
-- Insert books
INSERT INTO books (title, author, total_copies, available_copies) VALUES
('Data Structures', 'Narasimha Karumanchi', 5, 3),
('Operating Systems', 'Galvin', 4, 2),
('Database Management', 'Korth', 3, 0),
('Computer Networks', 'Tanenbaum', 4, 4),
('Introduction to Algorithms','Thomas Cormen', 6, 2),
('Clean Code', 'Robert Martin', 3, 1),
('Artificial Intelligence', 'Stuart Russell', 4, 3),
('Machine Learning', 'Tom Mitchell', 3, 1),
('Web Development', 'Jon Duckett', 5, 5),
('Python Programming', 'John Zelle', 4, 2);
-- Insert members
INSERT INTO members (name, type) VALUES
('Rohan Singh', 'Student'),
('Priya Patel', 'Student'),
('Dr. Meera Nair', 'Faculty'),
('Amit Kumar', 'Student'),
('Neha Sharma', 'Faculty'),
('Vikram Singh', 'Student');
-- Insert borrowings
INSERT INTO borrowings (book_id, member_id, borrow_date, due_date, return_date, fine) VALUES
(1, 1, '2026-01-05', '2026-01-19', '2026-01-18', 0.00),
(2, 1, '2026-01-10', '2026-01-24', '2026-01-28', 40.00),
(3, 2, '2026-01-15', '2026-01-29', '2026-01-29', 0.00),
(5, 2, '2026-02-01', '2026-02-15', '2026-02-20', 50.00),
(5, 3, '2026-02-05', '2026-02-19', '2026-02-18', 0.00),
(6, 3, '2026-02-10', '2026-02-24', '2026-02-22', 0.00),
(1, 4, '2026-02-15', '2026-03-01', '2026-03-03', 20.00),
(7, 4, '2026-03-01', '2026-03-15', NULL, 0.00),
(3, 5, '2026-03-05', '2026-03-19', '2026-03-17', 0.00),
(5, 5, '2026-03-10', '2026-03-24', NULL, 0.00),
(8, 6, '2026-03-15', '2026-03-29', '2026-04-05', 70.00),
(2, 1, '2026-03-20', '2026-04-03', NULL, 0.00),
(10,2, '2026-03-25', '2026-04-08', NULL, 0.00),
(5, 6, '2026-04-01', '2026-04-15', NULL, 0.00),
(9, 4, '2026-04-05', '2026-04-19', NULL, 0.00);Practice Questions (17)
Easy (1–9) — CRUD, filtering, sorting
- List all books with title and author sorted by title.
- Find all books where available_copies is 0 (fully issued out). (Expected: Database Management)
- List all Faculty members. (Expected: Dr. Meera Nair, Neha Sharma)
- Find all borrowings that have not been returned (return_date IS NULL). (Expected: 6 unreturned)
- Find borrowings where fine is greater than 0. (Expected: 4 borrowings with fines)
- Find all members whose name contains 'Singh'.
- List all books sorted by available_copies descending (most available first).
- Find all borrowings due in March 2026 (due_date between '2026-03-01' and '2026-03-31').
- List all borrowings sorted by borrow_date descending (most recent first).
Medium (10–17) — JOINs, GROUP BY, HAVING, aggregates, subqueries
- List each borrowing with book title and member name using JOIN. (Expected: 15 rows)
- Find the most borrowed book (book with highest borrow count). (Expected: Introduction to Algorithms with 4 borrows)
- Count how many books each member has currently borrowed (return_date IS NULL).
- Calculate total fine collected per member. (Total fines: 180.00)
- Find members who have borrowed more than 2 books total (HAVING COUNT > 2). (Expected: Rohan, Priya, Amit, Vikram)
- List books that are currently overdue (return_date IS NULL AND due_date < CURDATE()).
- Calculate total fines for each member type (Student vs Faculty) using GROUP BY type.
- Find the author whose books have been borrowed the most times. (Expected: Thomas Cormen)
PROJECT 5 E-Commerce Product & Orders
Problem Statement
An online marketplace sells products from multiple sellers. The database stores products with name, price, and stock; customers who place orders; and the order details including which product, quantity, and total amount. The system should support finding best-selling products, low-stock alerts, customer spending patterns, and seller performance.
Schema
CREATE DATABASE ecommerce_db;
USE ecommerce_db;
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2),
stock INT DEFAULT 0,
category VARCHAR(50)
);
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(50),
joined_date DATE
);
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
product_id INT,
quantity INT,
order_date DATE,
total_amount DECIMAL(10,2),
status ENUM('Placed','Shipped','Delivered','Returned'),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);Sample Data (INSERT Queries)
Sample Data
-- Insert products
INSERT INTO products (name, price, stock, category) VALUES
('iPhone 15', 79999.00, 25, 'Electronics'),
('Samsung Galaxy S24', 69999.00, 30, 'Electronics'),
('MacBook Air M3', 114999.00, 15, 'Electronics'),
('Nike Air Max', 12999.00, 50, 'Footwear'),
('Adidas Ultraboost', 14999.00, 35, 'Footwear'),
('Java Programming', 599.00, 100, 'Books'),
('Python Crash Course',799.00, 80, 'Books'),
('Prestige Cooker', 2499.00, 40, 'Home'),
('Boat Airdopes', 1999.00, 60, 'Electronics'),
('Levi's Jeans', 3499.00, 45, 'Fashion'),
('Wooden Study Table', 8999.00, 8, 'Home'),
('Yoga Mat', 999.00, 70, 'Sports');
-- Insert customers
INSERT INTO customers (name, city, joined_date) VALUES
('Rahul Verma', 'Mumbai', '2025-01-10'),
('Sneha Patel', 'Delhi', '2025-02-15'),
('Amit Kumar', 'Bangalore', '2025-03-20'),
('Priya Sharma', 'Mumbai', '2025-05-01'),
('Vikash Singh', 'Delhi', '2025-06-12'),
('Neha Reddy', 'Hyderabad', '2025-08-20');
-- Insert orders
INSERT INTO orders (customer_id, product_id, quantity, order_date, total_amount, status) VALUES
(1, 1, 1, '2026-01-05', 79999.00, 'Delivered'),
(1, 6, 2, '2026-01-10', 1198.00, 'Delivered'),
(1, 4, 1, '2026-02-15', 12999.00, 'Delivered'),
(2, 3, 1, '2026-01-20', 114999.00, 'Delivered'),
(2, 7, 3, '2026-02-28', 2397.00, 'Delivered'),
(2, 9, 2, '2026-03-15', 3998.00, 'Shipped'),
(3, 2, 1, '2026-02-10', 69999.00, 'Delivered'),
(3, 10, 1, '2026-03-01', 3499.00, 'Delivered'),
(3, 11, 1, '2026-03-10', 8999.00, 'Returned'),
(4, 5, 2, '2026-03-05', 29998.00, 'Delivered'),
(4, 12, 3, '2026-03-20', 2997.00, 'Shipped'),
(4, 8, 1, '2026-04-01', 2499.00, 'Placed'),
(5, 1, 1, '2026-03-10', 79999.00, 'Delivered'),
(5, 9, 1, '2026-03-25', 1999.00, 'Delivered'),
(5, 6, 5, '2026-04-05', 2995.00, 'Placed'),
(6, 4, 2, '2026-04-01', 25998.00, 'Placed'),
(6, 7, 1, '2026-04-05', 799.00, 'Placed'),
(6, 12, 2, '2026-04-08', 1998.00, 'Placed');Practice Questions (18)
Easy (1–10) — Single table basics
- List all products with name, price, and stock sorted by price descending.
- Find all products in the 'Electronics' category. (Expected: 4 products)
- Find products where stock is less than 10 (low stock alert). (Expected: Wooden Study Table with 8)
- List all customers from 'Mumbai'. (Expected: Rahul, Priya)
- Find all orders with status 'Delivered'. (Expected: 10 orders)
- Find products priced above 50000 sorted by price. (Expected: MacBook, iPhone, Samsung)
- Find all orders placed in March 2026.
- List all distinct categories from the products table.
- Find orders where quantity is greater than 2. (Expected: 5 orders)
- List all customers joined before 2026-01-01 sorted by name.
Medium (11–18) — JOINs, GROUP BY, HAVING, aggregates
- List each order with customer name and product name using JOIN. (Expected: 18 rows)
- Find total quantity sold per product (GROUP BY product, ORDER BY total DESC).
- Find the customer who has spent the most total amount. (Expected: Rahul with 94196)
- Count number of orders per customer.
- Find total revenue per category. (Expected: Electronics highest)
- Find customers who have placed more than 3 orders (HAVING COUNT > 3). (Expected: Rahul, Priya, Vikash, Neha)
- Find the most popular product in each category.
- Calculate average order value per city (JOIN orders + customers).
SQL Intro
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It is used to create, read, update, and delete data (CRUD operations).
Key Point: SQL works with relational databases like MySQL, PostgreSQL, SQL Server, Oracle, and SQLite.
Example 1
-- Create a simple table and query it
CREATE TABLE employees (
id INT,
name VARCHAR(100),
salary DECIMAL(10,2)
);
INSERT INTO employees VALUES
(1, 'Alice', 75000.00),
(2, 'Bob', 82000.00);
SELECT * FROM employees;
Example 2
-- Query with a condition
SELECT name, salary
FROM employees
WHERE salary > 70000;SQL Syntax
SQL follows a straightforward syntax. Statements end with a semicolon. SQL is case-insensitive, but keywords are conventionally written in UPPERCASE.
Example 1
-- Basic SELECT statement
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;
Example 2
-- Statement ordering: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 2
ORDER BY emp_count DESC;SQL Database
A database is a structured collection of data. SQL databases store data in tables with rows (records) and columns (fields). Use DDL commands to manage databases.
Example 1
-- List all databases (MySQL)
SHOW DATABASES;
Example 2
-- Use a specific database
USE company_db;
-- List all tables in the current database
SHOW TABLES;SQL Create DB
The CREATE DATABASE statement creates a new database. Use IF NOT EXISTS to avoid errors if the database already exists.
Example 1
-- Create a new database
CREATE DATABASE school_db;
Example 2
-- Create only if it does not already exist
CREATE DATABASE IF NOT EXISTS school_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;SQL Drop DB
The DROP DATABASE statement permanently deletes a database and all its tables and data. This action is irreversible.
Example 1
-- Delete a database
DROP DATABASE school_db;
Example 2
-- Drop only if it exists (safe drop)
DROP DATABASE IF EXISTS school_db;SQL Backup DB
Backing up a database ensures data safety. SQL provides command-line tools and logical backup methods.
Example 1 — MySQL Backup
-- MySQL: backup to SQL dump file (run from terminal)
mysqldump -u root -p school_db > school_backup.sql
Example 2 — Restore Backup
-- MySQL: restore from backup (run from terminal)
mysql -u root -p school_db < school_backup.sqlSQL Create Table
The CREATE TABLE statement defines a new table with columns, data types, and constraints.
Example 1
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
enrollment_date DATE,
gpa DECIMAL(3,2) DEFAULT 0.00
);
Example 2
-- Create table from existing table
CREATE TABLE student_backup AS
SELECT student_id, first_name, last_name
FROM students
WHERE gpa >= 3.50;SQL Drop Table
The DROP TABLE statement removes a table and all its data permanently.
Example 1
-- Delete a table
DROP TABLE student_backup;
Example 2
-- Safe drop: only if it exists
DROP TABLE IF EXISTS student_backup;SQL Alter Table
The ALTER TABLE statement modifies an existing table structure. It supports adding, dropping, renaming, and modifying columns, as well as managing constraints and renaming the table itself.
| Operation | Syntax |
|---|---|
ADD COLUMN | Adds a new column to a table |
DROP COLUMN | Deletes a column from a table |
RENAME COLUMN | Renames an existing column |
MODIFY COLUMN | Changes the data type, size, or constraints |
ADD CONSTRAINT | Adds a new constraint (UNIQUE, CHECK, FK, etc.) |
RENAME TO | Renames the table |
Add Column — Adds a new column to a table
Example 1
-- Add a single column
ALTER TABLE employees
ADD COLUMN phone VARCHAR(15);
Example 2 — Add multiple columns with defaults
-- Add multiple columns at once
ALTER TABLE employees
ADD COLUMN address VARCHAR(255),
ADD COLUMN city VARCHAR(50) DEFAULT 'Unknown',
ADD COLUMN join_date DATE DEFAULT (CURRENT_DATE);Drop Column — Deletes a column in a table
Example 1
-- Drop a single column
ALTER TABLE employees
DROP COLUMN phone;
Example 2 — Drop multiple columns
-- Drop multiple columns at once (MySQL)
ALTER TABLE employees
DROP COLUMN address,
DROP COLUMN city;Rename Column — Renames a column
Example 1 — MySQL 8.0+ / PostgreSQL
-- Rename column using RENAME COLUMN
ALTER TABLE employees
RENAME COLUMN phone TO phone_number;
Example 2 — SQL Server
-- SQL Server: use sp_rename
sp_rename 'employees.phone', 'phone_number', 'COLUMN';Modify Column — Changes the data type, size, or constraints of a column
Example 1 — Change data type and size
-- Expand email column and make it required
ALTER TABLE employees
MODIFY COLUMN email VARCHAR(150) NOT NULL;
Example 2 — SQL Server syntax
-- SQL Server: ALTER COLUMN
ALTER TABLE employees
ALTER COLUMN salary DECIMAL(12,2) NOT NULL;Add Constraint — Adds a new constraint
Example 1 — Add UNIQUE constraint
-- Ensure no two employees share the same email
ALTER TABLE employees
ADD CONSTRAINT uq_email UNIQUE (email);
Example 2 — Add CHECK and FOREIGN KEY
-- Add a CHECK constraint
ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18);
-- Add a FOREIGN KEY constraint
ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);Rename Table — Renames a table
Example 1 — MySQL / PostgreSQL
-- Rename table using RENAME TO
ALTER TABLE employees
RENAME TO staff;
Example 2 — SQL Server / MySQL shorthand
-- MySQL alternative syntax
RENAME TABLE employees TO staff;
-- SQL Server syntax
sp_rename 'employees', 'staff';SQL Constraints
Constraints enforce rules on data in tables. They ensure data accuracy, consistency, and integrity.
| Constraint | Description |
|---|---|
NOT NULL | Column cannot have NULL values |
UNIQUE | All values in the column must be different |
PRIMARY KEY | Uniquely identifies each row (NOT NULL + UNIQUE) |
FOREIGN KEY | Links two tables together |
CHECK | Ensures values satisfy a specific condition |
DEFAULT | Sets a default value when none is specified |
Example 1
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
product VARCHAR(100) NOT NULL,
quantity INT CHECK (quantity > 0),
order_date DATE DEFAULT (CURRENT_DATE)
);
Example 2 — Adding Constraints After Creation
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);SQL Not Null
The NOT NULL constraint ensures a column cannot contain NULL values. Every row must have a value.
Example 1
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
email VARCHAR(100) NOT NULL
);
Example 2
-- Adding NOT NULL constraint to an existing column
ALTER TABLE users
MODIFY COLUMN username VARCHAR(50) NOT NULL;SQL Unique
The UNIQUE constraint ensures all values in a column are different. Unlike PRIMARY KEY, it allows one NULL value.
Example 1
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
phone VARCHAR(15) UNIQUE
);
Example 2 — Named Unique Constraint
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
email VARCHAR(100),
CONSTRAINT uq_email UNIQUE (email)
);SQL Primary Key
A PRIMARY KEY uniquely identifies each row in a table. It is a combination of NOT NULL and UNIQUE. A table can have only one primary key.
Example 1
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
Example 2 — Composite Primary Key
-- Composite key: two columns together form the primary key
CREATE TABLE enrollments (
student_id INT,
course_id INT,
enroll_date DATE,
PRIMARY KEY (student_id, course_id)
);SQL Foreign Key
A FOREIGN KEY creates a link between two tables. It references the PRIMARY KEY of another table, enforcing referential integrity.
Example 1
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
total_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
Example 2 — ON DELETE CASCADE
-- Auto-delete orders when the customer is deleted
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
total_amount DECIMAL(10,2),
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE
);SQL Check
The CHECK constraint ensures that all values in a column satisfy a specific condition before data is inserted or updated.
Example 1
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) CHECK (price > 0),
stock INT CHECK (stock >= 0)
);
Example 2
-- Named CHECK constraint with complex condition
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
age INT,
salary DECIMAL(10,2),
CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65),
CONSTRAINT chk_salary CHECK (salary >= 15000)
);SQL Default
The DEFAULT constraint sets a default value for a column when no value is provided during INSERT.
Example 1
CREATE TABLE orders (
order_id INT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'pending',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
Example 2
-- Adding a default value to an existing column
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'processing';SQL Create Index
Indexes improve the speed of data retrieval operations. They work like a book's index — pointing to the location of data quickly.
Example 1 — Single Column Index
-- Create index on last_name for faster searches
CREATE INDEX idx_lastname
ON employees (last_name);
Example 2 — Unique Composite Index
-- Unique index prevents duplicate combinations
CREATE UNIQUE INDEX idx_email
ON users (email);SQL Auto Increment
The AUTO_INCREMENT attribute automatically generates a unique sequential number for a column (typically for primary keys).
Example 1
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
-- No need to provide id — it auto-generates
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com');
Example 2 — Custom Start Value
-- Set auto-increment to start from 1001
CREATE TABLE invoices (
invoice_no INT AUTO_INCREMENT PRIMARY KEY,
customer VARCHAR(100) NOT NULL,
amount DECIMAL(10,2) NOT NULL
) AUTO_INCREMENT = 1001;SQL Dates
SQL provides date and time data types (DATE, DATETIME, TIMESTAMP) and functions for manipulation.
Example 1
-- Get current date and time
SELECT NOW() AS current_datetime;
SELECT CURDATE() AS today;
Example 2
-- Date formatting and date arithmetic
SELECT
DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date,
DATEDIFF(CURDATE(), order_date) AS days_since_order
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);SQL Views
A VIEW is a stored query that acts as a virtual table. It doesn't store data physically — it returns results from the underlying query each time it's accessed.
Example 1 — Create View
CREATE VIEW active_customers AS
SELECT id, name, email, city
FROM customers
WHERE status = 'active';
-- Use the view like a regular table
SELECT * FROM active_customers WHERE city = 'Mumbai';
Example 2 — Modify & Drop View
-- Replace view with new definition
CREATE OR REPLACE VIEW active_customers AS
SELECT id, name, email, city, phone
FROM customers
WHERE status = 'active';
-- Delete a view
DROP VIEW active_customers;SQL Select
The SELECT statement retrieves data from one or more tables. Use * to select all columns.
Example 1
-- Select all columns
SELECT * FROM employees;
-- Select specific columns
SELECT name, department, salary
FROM employees;
Example 2
-- Select with expressions
SELECT
name,
salary,
salary * 12 AS annual_salary,
salary * 0.05 AS bonus
FROM employees;SQL Select Distinct
The DISTINCT keyword removes duplicate rows from the result set, returning only unique values.
Example 1
-- Get unique departments
SELECT DISTINCT department
FROM employees;
Example 2
-- Count unique cities
SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;SQL Where
The WHERE clause filters rows based on specified conditions. Only matching rows are returned.
Example 1
SELECT name, salary
FROM employees
WHERE department = 'Engineering'
AND salary > 80000;
Example 2
SELECT *
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-06-30'
AND status = 'completed';SQL Order By
The ORDER BY clause sorts the result set by one or more columns. Default is ascending (ASC).
Example 1
-- Sort by salary descending
SELECT name, salary
FROM employees
ORDER BY salary DESC;
Example 2
-- Multi-column sort
SELECT department, name, salary
FROM employees
ORDER BY department ASC, salary DESC;SQL And
The AND operator combines multiple conditions in a WHERE clause. All conditions must be true for a row to be included.
Example 1
SELECT name, salary, department
FROM employees
WHERE department = 'Sales'
AND salary >= 60000
AND status = 'active';
Example 2
SELECT *
FROM products
WHERE price > 100
AND category = 'Electronics'
AND stock > 0;SQL Or
The OR operator returns rows where at least one condition is true.
Example 1
SELECT name, department
FROM employees
WHERE department = 'Engineering'
OR department = 'Design';
Example 2
SELECT *
FROM customers
WHERE city = 'Mumbai'
OR city = 'Delhi'
OR city = 'Bangalore';SQL Not
The NOT operator negates a condition, returning rows where the condition is false.
Example 1
SELECT name, department
FROM employees
WHERE NOT department = 'HR';
Example 2
SELECT *
FROM orders
WHERE NOT status = 'cancelled'
AND NOT total_amount IS NULL;SQL Insert Into
The INSERT INTO statement adds new rows to a table.
Example 1
-- Insert single row
INSERT INTO employees (name, department, salary)
VALUES ('Ravi Kumar', 'Engineering', 95000);
Example 2 — Insert Multiple Rows
-- Insert multiple rows at once
INSERT INTO employees (name, department, salary) VALUES
('Priya Sharma', 'Design', 72000),
('Amit Patel', 'Marketing', 68000),
('Sneha Reddy', 'Finance', 88000);SQL Null Values
NULL represents missing or unknown data. Use IS NULL and IS NOT NULL to test for NULL values — = NULL does not work.
Example 1
-- Find employees without a phone number
SELECT name, phone
FROM employees
WHERE phone IS NULL;
Example 2
-- Filter out NULL salary rows
SELECT name, salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;SQL Update
The UPDATE statement modifies existing rows in a table. Always use a WHERE clause to avoid updating all rows.
Example 1
-- Update a single employee's salary
UPDATE employees
SET salary = 105000
WHERE id = 3;
Example 2
-- Give a 10% raise to all Marketing employees
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Marketing';SQL Delete
The DELETE statement removes rows from a table. Always use a WHERE clause to target specific rows.
Example 1
-- Delete a specific employee
DELETE FROM employees
WHERE id = 5;
Example 2
-- Delete old inactive records
DELETE FROM sessions
WHERE last_active < DATE_SUB(NOW(), INTERVAL 90 DAY);SQL Select Top
SELECT TOP (SQL Server) or LIMIT (MySQL/PostgreSQL) restricts the number of rows returned.
Example 1 — SQL Server
-- Top 5 highest-paid employees (SQL Server)
SELECT TOP 5 name, salary
FROM employees
ORDER BY salary DESC;
Example 2 — MySQL
-- Top 10 most recent orders (MySQL / PostgreSQL)
SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 10;SQL Aggregate Functions
Aggregate functions perform calculations on a set of rows and return a single value. Common aggregates: COUNT, SUM, AVG, MIN, MAX.
Example 1
-- Multiple aggregates in one query
SELECT
COUNT(*) AS total_employees,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees;
Example 2
-- Aggregate with GROUP BY
SELECT
department,
COUNT(*) AS headcount,
SUM(salary) AS total_salary
FROM employees
GROUP BY department;SQL Min()
The MIN() function returns the smallest value in a column.
Example 1
-- Find the lowest salary
SELECT MIN(salary) AS lowest_salary
FROM employees;
Example 2
-- Min salary per department
SELECT department, MIN(salary) AS dept_min
FROM employees
GROUP BY department;SQL Max()
The MAX() function returns the largest value in a column.
Example 1
-- Find the highest order amount
SELECT MAX(total_amount) AS max_order
FROM orders;
Example 2
-- Find the employee with the highest salary
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);SQL Count()
The COUNT() function returns the number of rows that match a condition.
Example 1
-- Count all employees
SELECT COUNT(*) AS total_employees
FROM employees;
-- Count with a condition
SELECT COUNT(*) AS senior_employees
FROM employees
WHERE salary > 100000;
Example 2
-- Count employees per department
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY headcount DESC;SQL Sum()
The SUM() function returns the total of all values in a numeric column.
Example 1
-- Total revenue from all orders
SELECT SUM(total_amount) AS total_revenue
FROM orders;
Example 2
-- Sum of salaries by department
SELECT department, SUM(salary) AS dept_payroll
FROM employees
GROUP BY department
HAVING SUM(salary) > 500000;SQL Avg()
The AVG() function returns the average value of a numeric column.
Example 1
-- Average salary company-wide
SELECT AVG(salary) AS avg_salary
FROM employees;
Example 2
-- Average order value per customer
SELECT
customer_id,
AVG(total_amount) AS avg_order_value,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3;SQL Like
The LIKE operator filters rows based on a pattern match in a column.
Example 1
-- Names starting with 'A'
SELECT name
FROM employees
WHERE name LIKE 'A%';
Example 2
-- Emails containing 'gmail'
SELECT name, email
FROM users
WHERE email LIKE '%@gmail.com';SQL Wildcards
Wildcards are used with LIKE for pattern matching: % matches zero or more characters; _ matches exactly one character.
Example 1
-- Names with exactly 5 characters
SELECT name
FROM employees
WHERE name LIKE '_____';
Example 2
-- Names starting with 'S' and ending with 'i'
SELECT name
FROM employees
WHERE name LIKE 'S%i';SQL In
The IN operator allows specifying multiple values in a WHERE clause, acting as a shorthand for multiple OR conditions.
Example 1
-- Employees in specific departments
SELECT name, department
FROM employees
WHERE department IN ('Engineering', 'Design', 'Product');
Example 2
-- NOT IN to exclude values
SELECT *
FROM products
WHERE category NOT IN ('Archived', 'Discontinued');SQL Between
The BETWEEN operator filters values within a given range (inclusive of both endpoints).
Example 1
-- Employees with salary between 50k and 100k
SELECT name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 100000;
Example 2
-- Orders placed in January 2026
SELECT *
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';SQL Aliases
Aliases give temporary names to tables or columns using AS. They improve readability and are required for calculated columns.
Example 1 — Column Alias
SELECT
name AS employee_name,
salary AS monthly_salary,
salary * 12 AS annual_salary
FROM employees;
Example 2 — Table Alias
SELECT e.name, d.dept_name
FROM employees AS e
INNER JOIN departments AS d
ON e.dept_id = d.dept_id;SQL Joins
Joins combine rows from two or more tables based on a related column. They are the foundation of relational queries.
| Join Type | Returns |
|---|---|
INNER JOIN | Matching rows from both tables |
LEFT JOIN | All from left + matching from right |
RIGHT JOIN | All from right + matching from left |
FULL JOIN | All rows from both tables |
SELF JOIN | Table joined with itself |
Example 1
-- Get orders with customer names
SELECT o.order_id, c.name, o.total_amount
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.id;
Example 2
-- Join three tables
SELECT c.name, p.product_name, oi.quantity
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
INNER JOIN order_items AS oi ON o.order_id = oi.order_id
INNER JOIN products AS p ON oi.product_id = p.id;SQL Inner Join
INNER JOIN returns only rows that have matching values in both tables. Non-matching rows are excluded.
Example 1
SELECT e.name, d.dept_name
FROM employees AS e
INNER JOIN departments AS d
ON e.dept_id = d.dept_id;
Example 2 — With WHERE filter
SELECT c.name, o.total_amount
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
WHERE o.total_amount > 1000;SQL Left Join
LEFT JOIN returns all rows from the left table, and matching rows from the right table. NULL is returned for non-matching right rows.
Example 1
-- All customers, including those with no orders
SELECT c.name, o.order_id, o.total_amount
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id;
Example 2 — Find unmatched rows
-- Customers who have never placed an order
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
WHERE o.order_id IS NULL;SQL Right Join
RIGHT JOIN returns all rows from the right table, and matching rows from the left table. NULL is returned for non-matching left rows.
Example 1
-- All departments, even those with no employees
SELECT e.name, d.dept_name
FROM employees AS e
RIGHT JOIN departments AS d
ON e.dept_id = d.dept_id;
Example 2
-- All products, even those never ordered
SELECT p.product_name, oi.quantity
FROM order_items AS oi
RIGHT JOIN products AS p
ON oi.product_id = p.id;SQL Full Join
FULL JOIN returns all rows from both tables. Where there is no match, NULL is filled in for the missing side. Note: MySQL does not support FULL JOIN natively — use UNION of LEFT and RIGHT JOIN.
Example 1 — SQL Server / PostgreSQL
SELECT c.name, o.order_id
FROM customers AS c
FULL JOIN orders AS o
ON c.id = o.customer_id;
Example 2 — MySQL workaround
-- MySQL: simulate FULL JOIN using UNION
SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
UNION
SELECT c.name, o.order_id
FROM customers AS c
RIGHT JOIN orders AS o ON c.id = o.customer_id;SQL Self Join
A SELF JOIN joins a table with itself. Use table aliases to distinguish between the two instances. Common for hierarchical data like employees and managers.
Example 1 — Employee and Manager
SELECT
e.name AS employee,
m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.id;
Example 2 — Pairs in same department
-- Find employees who share the same department
SELECT
a.name AS employee_1,
b.name AS employee_2,
a.department
FROM employees AS a
INNER JOIN employees AS b
ON a.department = b.department
AND a.id < b.id;SQL Union
UNION combines the result sets of two or more SELECT statements and removes duplicate rows.
Example 1
-- Combine customer names from two regions
SELECT name FROM customers_north
UNION
SELECT name FROM customers_south;
Example 2
-- Unique list of all cities from employees and customers
SELECT city FROM employees
UNION
SELECT city FROM customers;SQL Union All
UNION ALL combines result sets including duplicates. It is faster than UNION because it skips the dedup step.
Example 1
-- Combine all orders from two years (include duplicates)
SELECT order_id, total_amount FROM orders_2025
UNION ALL
SELECT order_id, total_amount FROM orders_2026;
Example 2
-- Combine staff and contractors into one list
SELECT name, 'Staff' AS type FROM employees
UNION ALL
SELECT name, 'Contractor' AS type FROM contractors;SQL Group By
GROUP BY groups rows sharing the same values so aggregate functions can be applied to each group.
Example 1
-- Count employees per department
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;
Example 2
-- Monthly order summary
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS total_revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;SQL Having
HAVING filters groups after GROUP BY has been applied. Unlike WHERE, HAVING works with aggregate functions.
Example 1
-- Departments with more than 5 employees
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
Example 2
-- Customers with total orders above 50000
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 50000;SQL Exists
EXISTS tests for the existence of rows in a subquery. Returns TRUE if the subquery returns at least one row.
Example 1
-- Customers who have placed at least one order
SELECT name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);
Example 2
-- Departments that have at least one employee earning above 100k
SELECT d.dept_name
FROM departments AS d
WHERE EXISTS (
SELECT 1
FROM employees AS e
WHERE e.dept_id = d.dept_id AND e.salary > 100000
);SQL Any
ANY (or SOME) compares a value to each value returned by a subquery. The condition is true if it matches at least one value.
Example 1
-- Employees earning more than any employee in Sales
SELECT name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Sales'
);
Example 2
-- Products priced less than ANY product in Electronics
SELECT product_name, price
FROM products
WHERE price < ANY (
SELECT price
FROM products
WHERE category = 'Electronics'
);SQL All
ALL compares a value to every value returned by a subquery. The condition is true only if it matches all values.
Example 1
-- Employees earning more than ALL employees in Marketing
SELECT name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department = 'Marketing'
);
Example 2
-- Departments where average salary is above ALL other department averages
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > ALL (
SELECT AVG(salary)
FROM employees
WHERE department != employees.department
GROUP BY department
);SQL Select Into
SELECT INTO copies data from one table into a new table. MySQL uses CREATE TABLE ... AS SELECT instead.
Example 1 — SQL Server
-- Create a backup of high-value orders
SELECT order_id, customer_id, total_amount
INTO high_value_orders
FROM orders
WHERE total_amount > 10000;
Example 2 — MySQL equivalent
-- MySQL: create table from select
CREATE TABLE high_value_orders AS
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 10000;SQL Insert Into Select
INSERT INTO ... SELECT copies rows from a SELECT query into an existing table. The column count and types must match.
Example 1
-- Copy active customers to a mailing list
INSERT INTO mailing_list (name, email)
SELECT name, email
FROM customers
WHERE status = 'active';
Example 2
-- Archive old orders into a separate table
INSERT INTO orders_archive (order_id, customer_id, total_amount, order_date)
SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_date < '2024-01-01';SQL Case
The CASE expression is SQL's if-else logic. It evaluates conditions and returns values based on which condition is met.
Example 1 — Simple CASE
SELECT
name,
salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 60000 THEN 'Mid-Level'
ELSE 'Junior'
END AS level
FROM employees;
Example 2
-- Categorize products by price range
SELECT
product_name,
price,
CASE
WHEN price < 500 THEN 'Budget'
WHEN price < 2000 THEN 'Mid-Range'
WHEN price < 5000 THEN 'Premium'
ELSE 'Luxury'
END AS price_tier
FROM products;SQL Null Functions
Functions like COALESCE, IFNULL, NULLIF, and NVL handle NULL values gracefully.
Example 1 — COALESCE
-- Return the first non-NULL value from a list
SELECT
name,
COALESCE(phone, email, 'No contact') AS primary_contact
FROM employees;
Example 2 — NULLIF & IFNULL
-- NULLIF: returns NULL if two values are equal (avoids div/0)
SELECT
name,
IFNULL(bonus, 0) AS bonus,
salary / NULLIF(months_worked, 0) AS monthly_salary
FROM employees;SQL Stored Procedures
A STORED PROCEDURE is a prepared SQL code that you can save and reuse. It can accept parameters and encapsulate complex business logic.
Example 1 — Create Procedure
DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept_name VARCHAR(50))
BEGIN
SELECT name, salary
FROM employees
WHERE department = dept_name
ORDER BY salary DESC;
END //
DELIMITER ;
Example 2 — Call Procedure
-- Call the stored procedure
CALL GetEmployeesByDept('Engineering');SQL Comments
Comments explain SQL code for readability. They are ignored by the database engine.
Example 1
-- This is a single-line comment
/*
This is a
multi-line comment
*/
SELECT * FROM employees; -- Inline comment
Example 2
-- ========================================
-- Report: Top 10 customers by revenue
-- Author: Database Team
-- Updated: 2026-06-01
-- ========================================
SELECT
c.name,
SUM(o.total_amount) AS total_spent
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC
LIMIT 10;SQL Operators
SQL operators are symbols or keywords used in conditions: comparison, logical, arithmetic, and special operators.
| Category | Operators |
|---|---|
| Comparison | =, <>, !=, >, <, >=, <= |
| Logical | AND, OR, NOT |
| Arithmetic | +, -, *, /, % (MOD) |
| Special | IN, BETWEEN, LIKE, IS NULL, EXISTS, ANY, ALL |
Example 1 — Arithmetic Operators
SELECT
name,
salary,
salary + COALESCE(bonus, 0) AS total_comp,
salary * 0.10 AS hike_amount,
salary % 5000 AS remainder
FROM employees;
Example 2 — IS NULL & IN Operators
SELECT *
FROM employees
WHERE bonus IS NULL
AND department IN ('Engineering', 'Product')
AND NOT (status = 'archived');