<PWC/>
SQL course
sql programming language icon

SQL Exercises: 25 Practice Problems with Solutions

SQL is a language you learn by querying. Every problem below runs against the same small customers-and-orders schema, which is created at the top of each snippet, so you can run them in any order.

Run the whole snippet in the editor below — it creates the sample tables, inserts the rows and then runs the query, so each problem is self-contained.

Run your answer here

Type your solution, press Run, and use the Input box when a problem asks you to read from standard input.

SQLite Compiler
Output
Code runs on the Play with Coding execution engine — your code is saved locally for next time.
How to practise so it actually sticks
Attempt the problem before opening the solution, even if your first version is clumsy. A working ugly answer teaches more than a beautiful one you read. When you get stuck for more than five minutes, read the hint — not the solution — and try again.
After you pass, open the solution and ask what is different about it. Shorter? Fewer variables? A built-in you did not know? That comparison is where most of the growth happens. Then change the problem slightly: sort the other direction, handle an empty input, read a value instead of hard-coding it.
Aim for three to five problems a day rather than thirty in one sitting. Spacing practice over days is what moves syntax from "I can look it up" to "my fingers know it".
The exercises
Showing 25 of 25 exercises.
  1. BeginnerSELECT basics
    1. Select every column
    Return every row and column from customers.
    Hint: SELECT * FROM table;
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM customers;
  2. BeginnerSELECT basics
    2. Pick specific columns
    Return only the name and city of every customer, with the column headed 'town' instead of 'city'.
    Hint: Use AS to alias a column.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT name, city AS town FROM customers;
  3. BeginnerSELECT basics
    3. Unique values
    List each city once.
    Hint: DISTINCT removes duplicate rows from the result.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT DISTINCT city FROM customers;
  4. BeginnerFiltering
    4. Filter rows
    Return every customer from Delhi.
    Hint: String comparisons use single quotes.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM customers
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM customers WHERE city = 'Delhi';
  5. BeginnerFiltering
    5. Combine conditions
    Return orders over 1000 that are not monitors.
    Hint: AND with <> or NOT.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM orders
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM orders WHERE amount > 1000 AND product <> 'Monitor';
  6. BeginnerFiltering
    6. IN and BETWEEN
    Return orders whose amount is between 500 and 3000, for the products Keyboard or Mouse.
    Hint: BETWEEN is inclusive on both ends.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM orders
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM orders
WHERE amount BETWEEN 500 AND 3000
  AND product IN ('Keyboard', 'Mouse');
  7. IntermediateFiltering
    7. Pattern matching
    Return customers whose name starts with a letter before 'C' in the alphabet, and products containing 'o'.
    Hint: LIKE '%o%' matches anywhere; % is the wildcard.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT name FROM customers WHERE name < 'C';
SELECT DISTINCT product FROM orders WHERE product LIKE '%o%';
  8. IntermediateFiltering
    8. Handle NULL correctly
    Return customers whose city is missing, and show a placeholder for a missing city in a second query.
    Hint: Use IS NULL, never = NULL; COALESCE supplies a default.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM customers WHERE city IS NULL;
SELECT name, COALESCE(city, 'unknown') AS city FROM customers;
  9. BeginnerSorting
    9. Sort and limit
    Return the three largest orders, biggest first.
    Hint: ORDER BY amount DESC LIMIT 3.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM orders
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM orders ORDER BY amount DESC LIMIT 3;
  10. IntermediateSorting
    10. Sort by two keys
    Sort customers by city ascending, then by join date descending.
    Hint: Comma-separate the sort keys.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM customers
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM customers ORDER BY city ASC, joined DESC;
  11. BeginnerAggregation
    11. Aggregate the whole table
    Return the number of orders, the total amount, the average amount and the largest amount.
    Hint: COUNT(*), SUM, AVG, MAX.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT COUNT(*) AS orders, SUM(amount) AS total, AVG(amount) AS average, MAX(amount) AS biggest
FROM orders;
  12. IntermediateAggregation
    12. Group and count
    Return the number of orders and total spend per customer_id.
    Hint: Every non-aggregated column must appear in GROUP BY.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY total DESC;
  13. IntermediateAggregation
    13. Filter groups with HAVING
    Return only customers whose total spend exceeds 3000.
    Hint: WHERE filters rows, HAVING filters groups.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 3000;
  14. IntermediateJoins
    14. Inner join two tables
    List each order with the customer's name.
    Hint: Join orders to customers on customer_id = customers.id.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT c.name, o.product, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY c.name;
  15. IntermediateJoins
    15. Keep unmatched rows
    List every customer with their order count, including customers who have never ordered.
    Hint: LEFT JOIN plus COUNT of the joined key, not COUNT(*).
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY orders DESC;
  16. AdvancedJoins
    16. Join then aggregate
    Return total spend per city, highest first.
    Hint: Group by the joined table's column.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT c.city, SUM(o.amount) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.city
ORDER BY total DESC;
  17. AdvancedJoins
    17. Self join
    Find pairs of customers who live in the same city, without pairing anyone with themselves or repeating pairs.
    Hint: Join the table to itself and require a.id < b.id.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT a.name AS one, b.name AS two, a.city
FROM customers a
JOIN customers b ON a.city = b.city AND a.id < b.id;
  18. AdvancedSubqueries
    18. Subquery in WHERE
    Return every order larger than the average order amount.
    Hint: Put the aggregate in a scalar subquery.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM orders
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
  19. AdvancedSubqueries
    19. EXISTS and NOT EXISTS
    Return customers who have at least one order, then customers with none.
    Hint: A correlated EXISTS references the outer row.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
  20. AdvancedSubqueries
    20. Use a CTE
    Use a WITH clause to compute per-customer totals, then return the top spender.
    Hint: A CTE names a temporary result you can select from.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

WITH
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

WITH totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, t.total
FROM totals t
JOIN customers c ON c.id = t.customer_id
ORDER BY t.total DESC
LIMIT 1;
  21. IntermediateExpressions
    21. Conditional column with CASE
    Label each order 'small', 'medium' or 'large' by amount.
    Hint: CASE WHEN ... THEN ... ELSE ... END.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT product, amount,
  CASE
    WHEN amount < 1000 THEN 'small'
    WHEN amount < 5000 THEN 'medium'
    ELSE 'large'
  END AS size
FROM orders;
  22. AdvancedWindow functions
    22. Rank with a window function
    Rank orders by amount within each customer, showing the rank alongside the row.
    Hint: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...).
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT customer_id, product, amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank_in_customer
FROM orders;
  23. AdvancedWindow functions
    23. Running total
    Show a running total of order amounts ordered by date.
    Hint: SUM(amount) OVER (ORDER BY ordered_on).
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

SELECT ordered_on, amount,
  SUM(amount) OVER (ORDER BY ordered_on) AS running_total
FROM orders
ORDER BY ordered_on;
  24. IntermediateChanging data
    24. Insert, update, delete
    Add a new customer, raise every keyboard price by 10 percent and delete orders below 500.
    Hint: Always write the WHERE clause before running an UPDATE or DELETE.
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

INSERT
    Show solution
    -- Sample schema used by these exercises
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  product TEXT,
  amount NUMERIC,
  ordered_on DATE
);
INSERT INTO customers VALUES
  (1, 'Ada',  'Delhi',  '2025-01-10'),
  (2, 'Ben',  'Mumbai', '2025-02-14'),
  (3, 'Cy',   'Delhi',  '2025-03-02'),
  (4, 'Dina', 'Pune',   '2025-04-21');
INSERT INTO orders VALUES
  (1, 1, 'Keyboard', 2400, '2025-05-01'),
  (2, 1, 'Mouse',     900, '2025-05-04'),
  (3, 2, 'Monitor', 12000, '2025-05-06'),
  (4, 3, 'Keyboard', 2400, '2025-06-11'),
  (5, 3, 'Cable',     300, '2025-06-12'),
  (6, 3, 'Monitor', 11500, '2025-07-02');

INSERT INTO customers (id, name, city, joined) VALUES (5, 'Eve', 'Delhi', '2025-08-01');

UPDATE orders SET amount = amount * 1.1 WHERE product = 'Keyboard';

DELETE FROM orders WHERE amount < 500;

SELECT * FROM orders;
  25. AdvancedChanging data
    25. Create a table with constraints
    Create a reviews table with a primary key, a NOT NULL body, a rating checked between 1 and 5 and a foreign key to customers.
    Hint: CHECK enforces the rating range at the database level.
    CREATE TABLE reviews (
);
    Show solution
    CREATE TABLE reviews (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  body TEXT NOT NULL,
  rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
What to do next
If a whole topic feels shaky, go back to that chapter in the 17-chapter SQL course and re-read it, then return here. When the advanced problems feel routine, take the final SQL quiz and claim your certificate, or open the SQL online compiler and build something of your own.