Basic Query Practice — Guided Walkthrough¶

Now that you know SELECT, WHERE, ORDER BY, LIMIT, and DISTINCT, it's time to combine them on a realistic dataset. This session walks through increasingly complex business questions — each one answered with a query you can run right now.

Sample Dataset¶

The queries in this file use three tables. Create them in your SQL environment before starting.

-- Customers table
CREATE TABLE customers (
    customer_id     VARCHAR(10) PRIMARY KEY,
    first_name      VARCHAR(50),
    last_name       VARCHAR(50),
    email           VARCHAR(100),
    city            VARCHAR(50),
    country         VARCHAR(50),
    signup_date     DATE,
    segment         VARCHAR(20)   -- 'VIP', 'Regular', 'New'
);

-- Products table
CREATE TABLE products (
    product_id      VARCHAR(10) PRIMARY KEY,
    product_name    VARCHAR(100),
    category        VARCHAR(50),
    unit_price      DECIMAL(10,2),
    cost_price      DECIMAL(10,2),
    stock_qty       INT
);

-- Orders table
CREATE TABLE orders (
    order_id        VARCHAR(10) PRIMARY KEY,
    customer_id     VARCHAR(10),
    product_id      VARCHAR(10),
    order_date      DATE,
    quantity        INT,
    unit_price      DECIMAL(10,2),
    discount_pct    DECIMAL(5,2),   -- can be NULL
    status          VARCHAR(20),    -- 'completed', 'pending', 'cancelled', 'refunded'
    delivery_date   DATE            -- NULL if not yet delivered
);

Sample data (insert to follow along)¶

INSERT INTO customers VALUES
('C001','Priya','Sharma','priya@gmail.com','Mumbai','India','2022-03-15','VIP'),
('C002','Arjun','Mehta','arjun@company.com','Delhi','India','2023-01-08','Regular'),
('C003','Sarah','Johnson','sarah@gmail.com','London','UK','2022-11-20','VIP'),
('C004','Liu','Wei','liu.wei@corp.com','Shanghai','China','2023-06-01','New'),
('C005','James','O''Brien','james@outlook.com','Dublin','Ireland','2022-07-14','Regular');

INSERT INTO products VALUES
('P001','Wireless Headphones','Electronics',149.00,65.00,45),
('P002','Yoga Mat','Sports',35.99,12.00,200),
('P003','Data Analytics Book','Books',49.99,18.00,80),
('P004','Standing Desk','Furniture',389.00,180.00,12),
('P005','Coffee Maker','Kitchen',89.99,35.00,67);

INSERT INTO orders VALUES
('O001','C001','P001','2024-01-05',2,149.00,NULL,'completed','2024-01-08'),
('O002','C003','P004','2024-01-07',1,389.00,10.00,'completed','2024-01-12'),
('O003','C002','P003','2024-01-09',3,49.99,NULL,'pending',NULL),
('O004','C001','P002','2024-01-10',1,35.99,5.00,'completed','2024-01-13'),
('O005','C004','P001','2024-01-11',1,149.00,NULL,'cancelled',NULL),
('O006','C005','P005','2024-01-14',2,89.99,15.00,'completed','2024-01-18'),
('O007','C003','P003','2024-01-15',5,49.99,NULL,'pending',NULL),
('O008','C002','P004','2024-01-18',1,389.00,NULL,'pending',NULL);

Guided Business Questions¶

Q1 — What products do we sell?¶

SELECT
    product_id,
    product_name,
    category,
    unit_price
FROM products
ORDER BY category, unit_price;

Output:

product_id	product_name	category	unit_price
P003	Data Analytics Book	Books	49.99
P001	Wireless Headphones	Electronics	149.00
P004	Standing Desk	Furniture	389.00
P005	Coffee Maker	Kitchen	89.99
P002	Yoga Mat	Sports	35.99

Q2 — Which orders are still pending delivery?¶

SELECT
    order_id,
    customer_id,
    product_id,
    order_date,
    status
FROM orders
WHERE delivery_date IS NULL
  AND status <> 'cancelled'
ORDER BY order_date;

Output:

order_id	customer_id	product_id	order_date	status
O003	C002	P003	2024-01-09	pending
O007	C003	P003	2024-01-15	pending
O008	C002	P004	2024-01-18	pending

Q3 — What is the order total for each order?¶

Note: we need to account for the optional discount.

SELECT
    order_id,
    customer_id,
    quantity,
    unit_price,
    discount_pct,
    quantity * unit_price                                           AS gross_total,
    quantity * unit_price * (1 - COALESCE(discount_pct, 0) / 100)  AS net_total
FROM orders
ORDER BY net_total DESC;

Output:

order_id	customer_id	quantity	unit_price	discount_pct	gross_total	net_total
O002	C003	1	389.00	10.00	389.00	350.10
O008	C002	1	389.00	NULL	389.00	389.00
O001	C001	2	149.00	NULL	298.00	298.00

COALESCE explained

COALESCE(discount_pct, 0) returns discount_pct if it's not NULL, or 0 if it is. This means "treat no discount as 0% off."

Q4 — Which VIP customers placed orders this month?¶

SELECT DISTINCT
    o.customer_id,
    c.first_name,
    c.last_name,
    c.segment
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.segment = 'VIP'
  AND o.order_date >= '2024-01-01'
ORDER BY c.last_name;

Output:

customer_id	first_name	last_name	segment
C003	Sarah	Johnson	VIP
C001	Priya	Sharma	VIP

We're previewing JOINs here

We need data from two tables — orders (for dates) and customers (for segment). JOINs are covered fully in Week-01/Day-02-Part-2-SQL-Advanced/01-joins.

Q5 — What categories of products have we sold?¶

SELECT DISTINCT
    p.category
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE o.status = 'completed'
ORDER BY p.category;

Output:

category
Books
Electronics
Furniture
Kitchen
Sports

Q6 — Top 3 most ordered products¶

SELECT
    product_id,
    COUNT(*)        AS times_ordered,
    SUM(quantity)   AS total_units_sold
FROM orders
WHERE status = 'completed'
GROUP BY product_id
ORDER BY total_units_sold DESC
LIMIT 3;

Output:

product_id	times_ordered	total_units_sold
P003	2	8
P001	1	2
P002	1	1

GROUP BY preview

We're previewing GROUP BY and aggregate functions here — they're covered fully in Week-01/Day-02-Part-2-SQL-Advanced/02-group-by-and-having.

Common Query Patterns to Memorise¶

-- Preview a new table safely
SELECT * FROM table_name LIMIT 100;

-- Unique values in a column
SELECT DISTINCT column_name FROM table_name ORDER BY column_name;

-- Filter + sort + limit (Top N)
SELECT cols FROM table WHERE condition ORDER BY metric DESC LIMIT n;

-- Count unique values
SELECT COUNT(DISTINCT column_name) FROM table_name;

-- Null check
SELECT * FROM table WHERE column IS NULL;
SELECT * FROM table WHERE column IS NOT NULL;

Checklist Before Running Any Query¶

Before you hit run, ask yourself:

[ ] Did I specify only the columns I need (not SELECT *)?
[ ] Does my WHERE clause narrow down the rows correctly?
[ ] If filtering dates, did I use BETWEEN or >=/<= correctly?
[ ] Did I handle NULL values with IS NULL / IS NOT NULL?
[ ] Did I add LIMIT if this is an exploratory query on a large table?
[ ] Does the ORDER BY match what the question is asking for?

← LIMIT and DISTINCT · Next: SQL Exercises →