SQL Exercises and Practice Queries

These SQL exercises let you practise everything from this tutorial on the sample shop database. Try to write each query yourself first, then open the solution with "Try it Yourself" and compare.

Exercise 1: Revenue per Product

Task: show each product with its total revenue (Quantity × Price), highest first, including products with no sales as 0.

SELECT p.ProductName,
       COALESCE(SUM(o.Quantity * p.Price), 0) AS revenue
FROM Products p
LEFT JOIN Orders o ON o.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY revenue DESC;

ProductName    | revenue
---------------+--------
Coffee Beans   | 352
Green Tea      | 240
Wheat Flour    | 240
Basmati Rice   | 187.5
Dark Chocolate | 170
Almonds        | 120
Olive Oil      | 112.5
Honey          | 0
(8 rows)

Exercise 2: Revenue per Country

Task: total revenue per customer country.

SELECT c.Country, SUM(o.Quantity * p.Price) AS revenue
FROM Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID
JOIN Products p ON p.ProductID = o.ProductID
GROUP BY c.Country
ORDER BY revenue DESC;

Country  | revenue
---------+--------
Pakistan | 747.5
USA      | 354
UAE      | 170
UK       | 150.5
(4 rows)

Exercise 3: Products to Restock

Task: list products with fewer than 70 units in stock that have been ordered at least once.

SELECT ProductName, Stock
FROM Products
WHERE Stock < 70
  AND ProductID IN (SELECT ProductID FROM Orders);

ProductName | Stock
------------+------
Olive Oil   | 45
Almonds     | 60
(2 rows)

Exercise 4: Each Customer's Biggest Order

Task: for every customer who ordered, show their largest order by quantity.

WITH r AS (
  SELECT CustomerID, OrderID, Quantity,
         ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Quantity DESC) AS rn
  FROM Orders
)
SELECT c.CustomerName, r.OrderID, r.Quantity
FROM r JOIN Customers c ON c.CustomerID = r.CustomerID
WHERE r.rn = 1
ORDER BY r.Quantity DESC;

CustomerName   | OrderID | Quantity
---------------+---------+---------
Evergreen Co   | 105     | 40
Golden Grains  | 108     | 30
Blue Sky Foods | 102     | 25
Indigo Retail  | 109     | 15
Harbor Goods   | 106     | 12
Alpha Traders  | 101     | 10
Cedar Market   | 110     | 5
(7 rows)

More Ideas to Practise

• Find the month with the highest number of units ordered
• List customers who bought both Basmati Rice and Olive Oil
• Show the percentage of total revenue for each category
• Find customers whose name and city start with the same letter

Frequently asked questions

What is the best way to practise SQL?

Write queries against a small dataset you understand, check the results by hand, then move to larger real datasets. Explaining your query out loud helps too.

What SQL questions come up in interviews?

Common ones are joins, GROUP BY with HAVING, finding duplicates, second highest value, running totals and the difference between WHERE and HAVING or UNION and UNION ALL.

What should I learn after SQL basics?

Database design and normalisation, query performance with indexes and execution plans, and using SQL from a programming language such as Python, PHP or Node.js.