SQL Basics — Exercises

Consolidation exercises for SELECT, WHERE, ORDER BY, LIMIT, and DISTINCT. These use the same customers, products, and orders tables from 05-basic-query-practice.

---

Level 1 — Warm-Up (SELECT, FROM, basic WHERE)

Exercise 1.1 Retrieve all columns from the customers table. How many rows does it contain?

Exercise 1.2 Retrieve product_name, category, and unit_price from the products table.

Exercise 1.3 Find all orders with status = 'completed'. Show order_id, customer_id, order_date.

Exercise 1.4 Find all customers from country = 'India'.

Exercise 1.5 Find all products with unit_price > 100.

Show answers

-- 1.1
SELECT * FROM customers;

-- 1.2
SELECT product_name, category, unit_price FROM products;

-- 1.3
SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'completed';

-- 1.4
SELECT * FROM customers WHERE country = 'India';

-- 1.5
SELECT * FROM products WHERE unit_price > 100;

---

Level 2 — Core Skills (Multiple conditions, BETWEEN, IN, NULL, ORDER BY, LIMIT)

Exercise 2.1 Find all orders that are either 'pending' or 'cancelled'. Use IN.

Exercise 2.2 Find products priced between £30 and £100 (inclusive). Show product_name, category, unit_price sorted cheapest first.

Exercise 2.3 Find customers who signed up in 2023. Sort by signup_date most recent first.

Exercise 2.4 Find orders where no discount was applied (discount_pct IS NULL) and the order is completed. Show order_id, customer_id, unit_price.

Exercise 2.5 List the 5 most expensive products.

Exercise 2.6 How many unique countries are our customers from?

Exercise 2.7 Find orders where quantity >= 3 AND unit_price > 50. Sort by unit_price descending.

Show answers

-- 2.1
SELECT order_id, customer_id, status
FROM orders
WHERE status IN ('pending', 'cancelled');

-- 2.2
SELECT product_name, category, unit_price
FROM products
WHERE unit_price BETWEEN 30 AND 100
ORDER BY unit_price ASC;

-- 2.3
SELECT customer_id, first_name, last_name, signup_date
FROM customers
WHERE signup_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY signup_date DESC;

-- 2.4
SELECT order_id, customer_id, unit_price
FROM orders
WHERE discount_pct IS NULL
  AND status = 'completed';

-- 2.5
SELECT product_name, category, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 5;

-- 2.6
SELECT COUNT(DISTINCT country) AS unique_countries
FROM customers;

-- 2.7
SELECT order_id, customer_id, quantity, unit_price
FROM orders
WHERE quantity >= 3
  AND unit_price > 50
ORDER BY unit_price DESC;

---

Level 3 — Stretch (Complex conditions, calculated columns, combined clauses)

Exercise 3.1 Calculate the gross total and net total for each order. Gross total is quantity * unit_price. Net total applies the discount: quantity * unit_price * (1 - COALESCE(discount_pct, 0) / 100). Show orders sorted by net total descending.

Exercise 3.2 Find all orders placed in the first two weeks of January 2024 (Jan 1–14) that are either completed or pending, with a gross total above £100. Sort by order_date ascending.

Exercise 3.3 List all customers whose email contains the word 'gmail' AND who are from the UK or India. Sort alphabetically by last name.

Exercise 3.4 Find the top 3 most expensive products in the Electronics or Furniture category.

Exercise 3.5 — Analytical thinking Write a query that finds orders where: - The order status is NOT cancelled or refunded - AND the delivery date is still NULL (not yet delivered) - AND the order was placed more than 7 days ago (before 2024-01-11, assuming today is 2024-01-18)

These are potentially overdue orders. Sort by order_date ascending (oldest first).

Show answers

-- 3.1
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;

-- 3.2
SELECT
    order_id,
    customer_id,
    order_date,
    status,
    quantity * unit_price AS gross_total
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-14'
  AND status IN ('completed', 'pending')
  AND quantity * unit_price > 100
ORDER BY order_date ASC;

-- 3.3
SELECT customer_id, first_name, last_name, email, country
FROM customers
WHERE email LIKE '%gmail%'
  AND country IN ('UK', 'India')
ORDER BY last_name;

-- 3.4
SELECT product_name, category, unit_price
FROM products
WHERE category IN ('Electronics', 'Furniture')
ORDER BY unit_price DESC
LIMIT 3;

-- 3.5
SELECT
    order_id,
    customer_id,
    order_date,
    status
FROM orders
WHERE status NOT IN ('cancelled', 'refunded')
  AND delivery_date IS NULL
  AND order_date < '2024-01-11'
ORDER BY order_date ASC;

---

Reflection Questions

After completing the exercises, think through these:

1. In Exercise 3.2, why did you use quantity * unit_price > 100 in the WHERE clause instead of the gross_total alias? (Hint: execution order.)

2. In Exercise 3.5, what happens if you use status <> 'cancelled' AND status <> 'refunded' instead of NOT IN? Are they equivalent?

3. You need to find orders placed "this month." How would you write this in a way that works without hardcoding the date?

4. MySQL: WHERE MONTH(order_date) = MONTH(CURDATE()) AND YEAR(order_date) = YEAR(CURDATE())
5. PostgreSQL: WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE)

---

Mini Challenge — Put It All Together

Scenario: Your manager asks for a report of "high-priority orders to follow up on."

Define these as orders that: - Were placed in January 2024 - Are from VIP customers (customer segment = 'VIP', use a join or subquery) - Have not been delivered yet (delivery_date IS NULL) - Are not cancelled or refunded

Return: order_id, customer_id, order_date, status, gross_total (quantity × unit_price)

Sort by order_date ascending (oldest first), and limit to 20 results.

Show answer

SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    o.status,
    o.quantity * o.unit_price   AS gross_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
  AND c.segment = 'VIP'
  AND o.delivery_date IS NULL
  AND o.status NOT IN ('cancelled', 'refunded')
ORDER BY o.order_date ASC
LIMIT 20;

---

← Basic Query Practice · Next: Interview Questions →