SQL Exercises

Practise what you've learned. Type the missing piece into each highlighted gap and press Check. Further down are query challenges with live editors and model solutions — all using the demo shop database.

Fill in the blanks

Querying basics

Select every column from the products table.

SELECT  FROM products;

Name the calculated column "stock_value".

SELECT name, price * stock  stock_value FROM products;

Keep only customers in Portugal.

SELECT name FROM customers  country = 'Portugal';

Find products that are not in the Books category.

SELECT name FROM products WHERE category  'Books';

Sort products from most to least expensive.

SELECT name, price FROM products ORDER BY price ;

Return only the first three rows.

SELECT name FROM products ORDER BY price  3;

List each country only once.

SELECT  country FROM customers;

Filtering

Match any of three statuses.

SELECT id FROM orders WHERE status  ('pending', 'shipped', 'cancelled');

Find products priced from 20 to 40 inclusive.

SELECT name FROM products WHERE price  20 AND 40;

Find product names starting with "W".

SELECT name FROM products WHERE name LIKE '';

Find customers with no city.

SELECT name FROM customers WHERE city ;

Show 'Unknown' when the city is missing.

SELECT name, (city, 'Unknown') FROM customers;

Complete the CASE expression.

SELECT name, CASE WHEN stock = 0 THEN 'Sold out' ELSE 'In stock'  AS availability FROM products;

Functions

Convert names to capital letters.

SELECT (name) FROM products;

Round prices to one decimal place.

SELECT name, round(price, ) FROM products;

Extract the year from each order date (SQLite).

SELECT strftime('', order_date) FROM orders;

Convert the text '42' into a number.

SELECT ('42' AS INTEGER) + 1;

Grouping

Count all rows in the orders table.

SELECT (*) FROM orders;

Average price per category.

SELECT category, AVG(price) FROM products  category;

Keep only categories with more than two products.

SELECT category, COUNT(*) FROM products GROUP BY category  COUNT(*) > 2;

Joins and subqueries

Join each order to its customer.

SELECT o.id, c.name FROM orders AS o JOIN customers AS c  c.id = o.customer_id;

Keep customers even if they have no orders.

SELECT c.name, o.id FROM customers AS c  JOIN orders AS o ON o.customer_id = c.id;

Combine two lists, removing duplicates.

SELECT city FROM customers  SELECT location FROM departments;

Find products that have been ordered at least once.

SELECT name FROM products AS p WHERE  (SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.id);

Start a common table expression.

 cheap AS (SELECT * FROM products WHERE price < 20) SELECT name FROM cheap;

Number rows within each category by price.

SELECT name, ROW_NUMBER()  (PARTITION BY category ORDER BY price) AS pos FROM products;

Changing data and design

Add a new department.

 INTO departments (name, location) VALUES ('Legal', 'Lisbon');

Change the status of order 12.

UPDATE orders  status = 'shipped' WHERE id = 12;

Remove the items of order 5 (the cancelled order).

 FROM order_items WHERE order_id = 5;

Undo everything since BEGIN.

BEGIN; DELETE FROM order_items; ;

Make the column required.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT );

Link each note to a customer.

CREATE TABLE notes (id INTEGER PRIMARY KEY, customer_id INTEGER  customers(id));

Speed up lookups of orders by date.

CREATE  idx_orders_date ON orders (order_date);

Save a query as a reusable view.

CREATE  toys AS SELECT * FROM products WHERE category = 'Toys';

Query challenges

1. Out-of-towners

List customers whose city is not Lisbon — including customers with no city — sorted by name.

Try it yourself
Result
Show a model solution
SQL 
SELECT name, city
FROM customers
WHERE city <> 'Lisbon' OR city IS NULL
ORDER BY name;
Result 8 rows
namecity
Ben CarterManchester
Chloé MartinLyon
David MüllerBerlin
Elena GarcíaMadrid
Farah HaddadNULL
George BrownLondon
Hana NovakPrague
Isaac LeviPorto

2. Category report

For each category show the number of products, the cheapest and dearest price, and the total units in stock, most stock first.

Try it yourself
Result
Show a model solution
SQL 
SELECT category, COUNT(*) AS products, MIN(price) AS cheapest, MAX(price) AS dearest, SUM(stock) AS units
FROM products
GROUP BY category
ORDER BY units DESC;
Result 4 rows
categoryproductscheapestdearestunits
Electronics414.9989255
Home312.549245
Books324.94573
Toys21839.962

3. Order receipts

For order 4, list each product name, quantity, unit price and line total, followed by the order total.

Try it yourself
Result
Show a model solution
SQL 
SELECT p.name, oi.quantity, oi.unit_price, oi.quantity * oi.unit_price AS line_total
FROM order_items AS oi
JOIN products AS p ON p.id = oi.product_id
WHERE oi.order_id = 4
UNION ALL
SELECT 'TOTAL', SUM(quantity), NULL, SUM(quantity * unit_price)
FROM order_items
WHERE order_id = 4;
Result 3 rows
namequantityunit_priceline_total
Ceramic Mug412.550
Linen Throw14949
TOTAL5NULL99

4. Highest earner per department

Show the highest-paid employee in each department, with the department name.

Try it yourself
Result
Show a model solution
SQL 
WITH ranked AS (
  SELECT e.first_name, e.last_name, e.salary, d.name AS department,
         RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS r
  FROM employees AS e
  JOIN departments AS d ON d.id = e.department_id
)
SELECT department, first_name, last_name, salary
FROM ranked
WHERE r = 1
ORDER BY salary DESC;
Result 4 rows
departmentfirst_namelast_namesalary
EngineeringMeiTanaka98000
SalesRafaelCosta82000
MarketingDanielKim71000
SupportEthanWalsh60000

5. Customers who never ordered a book

List customers who have placed orders, but none containing a product in the Books category.

Try it yourself
Result
Show a model solution
SQL 
SELECT c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id)
  AND NOT EXISTS (
    SELECT 1
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.id
    JOIN products AS p ON p.id = oi.product_id
    WHERE o.customer_id = c.id AND p.category = 'Books'
  )
ORDER BY c.name;
Result 5 rows
name
Ben Carter
Chloé Martin
David Müller
George Brown
Hana Novak

6. A safe price change

Inside a transaction, raise all Home prices by 10%, check the result with a query, then roll back and show that prices are unchanged.

Try it yourself
Result
Show a model solution
SQL 
BEGIN;
UPDATE products SET price = round(price * 1.1, 2) WHERE category = 'Home';
SELECT name, price FROM products WHERE category = 'Home';
ROLLBACK;

SELECT name, price FROM products WHERE category = 'Home';
Result 3 rows
nameprice
Ceramic Mug13.75
Desk Lamp37.4
Linen Throw53.9
Result 3 rows
nameprice
Ceramic Mug12.5
Desk Lamp34
Linen Throw49
PreviousPlayground