SQL Practice Problems with Solutions: 15 Graded Exercises
Solve 15 graded SQL practice problems with worked solutions on one retail dataset: joins, GROUP BY, HAVING, window functions and data-quality checks.
Before board exams, most students in Indore solve previous years' papers. Rereading the textbook feels productive, but only the papers show what you cannot do yet. You attempt a question, check the answer key, and rarely repeat the mistake.
SQL is the same. The skill appears only when you turn a business question into a query, run it, and compare your result with a known answer.
This page is that question paper. The questions are business questions from the Malwa Mart sample shop used throughout this series, the answer key is a worked solution with its expected output, and the exam hall is the Run button under each level.
A SQL practice problem is a business question with a known correct result, which you solve by writing a query, running it and checking its output against the expected answer.
This page follows query optimization and closes the series. Each answer names the tutorial to revise, so you can go back to exactly what you miss.
The Malwa Mart practice database
Six small tables describe four grocery stores in Indore, small enough to check every answer by hand.
| Table | Rows | Key columns |
|---|---|---|
stores |
4 | store_id, store_name, area (Vijay Nagar, Palasia, Rajwada, Bhawarkuan) |
customers |
7 | customer_id, name, city |
products |
8 | product_id, product_name, category, price |
orders |
11 (ids 101 to 111) | order_id, customer_id, store_id, order_date, amount, payment_mode |
order_items |
16 | order_id, product_id, quantity |
staff |
6 | staff_id, name, role, store_id, manager_id |
Two rows are deliberately awkward. Order 109 has no payment mode, and order 111 has no amount because it has not been billed yet. Neha D'Souza (customer 7) has never ordered. Good solutions handle all three.
How to solve any SQL problem
Strong candidates follow a short routine before typing:
The grain question, "what does one row of my answer stand for?", decides whether you need GROUP BY, and on which columns.
- Read the question twice. Note the output columns and every condition.
- Decide the grain. One row per order, customer, store or day?
- Pick tables and joins. Use LEFT JOIN when unmatched rows must stay.
- Filter, group, compute. WHERE before grouping, HAVING after, window functions for ranks and running values.
- Check by hand. Verify one result row against the raw data.
Quick check
You need customers with at least three orders. Where does COUNT(*) >= 3 belong?
WHERE filters rows before grouping, so it cannot see COUNT. HAVING filters the groups GROUP BY creates.
Easy SQL practice problems (1 to 5)
Problem 1. List UPI orders worth more than 300 rupees, newest first.
SELECT order_id, order_date, amount FROM orders
WHERE payment_mode = 'UPI' AND amount > 300
ORDER BY order_date DESC;Answer: orders 105 (680) and 101 (1,040). Order 111's NULL amount fails amount > 300. Revise: SELECT and WHERE.
Problem 2. How many customers live in each city? Show the biggest city first.
SELECT city, COUNT(*) AS customers FROM customers
GROUP BY city ORDER BY customers DESC, city;Answer: Indore 5, Dewas 1, Ujjain 1. The second sort key breaks the tie, so the order is stable.
Problem 3. Show the number of orders and total revenue for each payment mode, labelling a missing mode as "Not recorded".
SELECT COALESCE(payment_mode, 'Not recorded') AS mode, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders GROUP BY mode ORDER BY revenue DESC;Answer: UPI 5 orders and 2,215 rupees, Card 1,740, Cash 915, Not recorded 340. UPI counts order 111, but SUM skips its NULL amount. Revise: handling NULL.
Problem 4. Which customers have never placed an order?
SELECT c.name FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;Answer: Neha D'Souza. This anti-join keeps unmatched customers, then filters for the NULLs the join created. NOT EXISTS also works. Revise: SQL joins.
Problem 5. Find orders with incomplete data: a missing amount or a missing payment mode.
SELECT order_id, amount, payment_mode FROM orders
WHERE amount IS NULL OR payment_mode IS NULL;Answer: 109 (no mode) and 111 (no amount). Writing amount = NULL returns nothing, because a comparison with NULL is never true.
Run all five easy solutions:
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INT, store_id INT, order_date TEXT, amount INT, payment_mode TEXT);
INSERT INTO customers VALUES (1,'Asha Verma','Indore'), (2,'Ravi Patel','Indore'), (3,'Meera Joshi','Dewas'),
(4,'Pooja Rathore','Ujjain'), (5,'Sana Qureshi','Indore'), (6,'Vikram Chouhan','Indore'), (7,'Neha D''Souza','Indore');
INSERT INTO orders VALUES
(101,1,1,'2026-03-01',1040,'UPI'), (102,2,2,'2026-03-01',330,'Cash'), (103,1,1,'2026-03-03',275,'UPI'),
(104,3,3,'2026-03-04',180,'Card'), (105,4,2,'2026-03-05',680,'UPI'), (106,2,4,'2026-03-07',120,'Cash'),
(107,5,3,'2026-03-08',1560,'Card'), (108,6,1,'2026-03-08',220,'UPI'), (109,4,4,'2026-03-10',340,NULL),
(110,1,2,'2026-03-12',465,'Cash'), (111,6,3,'2026-03-14',NULL,'UPI');
""")
solutions = {
1: "SELECT order_id, order_date, amount FROM orders WHERE payment_mode = 'UPI' AND amount > 300 ORDER BY order_date DESC",
2: "SELECT city, COUNT(*) AS customers FROM customers GROUP BY city ORDER BY customers DESC, city",
3: """SELECT COALESCE(payment_mode, 'Not recorded') AS mode, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders GROUP BY mode ORDER BY revenue DESC""",
4: "SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL",
5: "SELECT order_id, amount, payment_mode FROM orders WHERE amount IS NULL OR payment_mode IS NULL",
}
for number, sql in solutions.items():
print(f"Problem {number}:", con.execute(sql).fetchall())Medium SQL practice problems (6 to 10)
Problem 6. Build a store scoreboard: area, number of orders, revenue and average bill, best store first.
SELECT s.area, COUNT(o.order_id) AS orders, SUM(o.amount) AS revenue, ROUND(AVG(o.amount), 0) AS avg_bill
FROM stores AS s
LEFT JOIN orders AS o ON o.store_id = s.store_id
GROUP BY s.store_id, s.area
ORDER BY revenue DESC;Answer: Rajwada leads with 1,740 rupees from 3 orders, but its average bill is 870, not 580. AVG ignores order 111's NULL amount and divides by 2. Revise: aggregate functions.
Problem 7. What is the second-highest order amount?
SELECT MAX(amount) AS second_highest FROM orders
WHERE amount < (SELECT MAX(amount) FROM orders);
-- or: SELECT DISTINCT amount FROM orders ORDER BY amount DESC LIMIT 1 OFFSET 1;Answer: 1,040 rupees (order 101), below order 107's 1,560. With no second value, the subquery version returns NULL rather than no row. Revise: subqueries.
Problem 8. List repeat customers (two or more orders) with their order count and total spend.
SELECT c.name, COUNT(*) AS orders, SUM(o.amount) AS spent
FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING COUNT(*) >= 2
ORDER BY spent DESC;Answer: Asha 3 orders and 1,780 rupees, Pooja 1,020, Ravi 450, Vikram 220. A condition on a group belongs in HAVING. Revise: GROUP BY and HAVING.
Problem 9. Show units sold and revenue by product category, using bill lines rather than order totals.
SELECT p.category, SUM(i.quantity) AS units, SUM(i.quantity * p.price) AS revenue
FROM order_items AS i JOIN products AS p ON p.product_id = i.product_id
GROUP BY p.category ORDER BY revenue DESC;Answer: Grocery 14 units and 2,995 rupees, Household 1,360, Snacks 580, Dairy 455. The total, 5,390, is 180 more than the orders table, because bill lines exist for unbilled order 111.
Problem 10. List every staff member with their manager's name, including the person who has no manager.
SELECT e.name, e.role, COALESCE(m.name, '(no manager)') AS manager
FROM staff AS e LEFT JOIN staff AS m ON m.staff_id = e.manager_id
ORDER BY e.staff_id;Answer: Kavita and Arjun report to Rajesh Sharma, who has no manager; Priya and Farhan report to Kavita, and Deepak to Arjun. An INNER JOIN would silently drop Rajesh. Revise: self join.
Run the medium solutions:
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE stores (store_id INTEGER PRIMARY KEY, store_name TEXT, area TEXT);
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INT, store_id INT, order_date TEXT, amount INT, payment_mode TEXT);
INSERT INTO stores VALUES (1,'Malwa Mart Vijay Nagar','Vijay Nagar'), (2,'Malwa Mart Palasia','Palasia'),
(3,'Malwa Mart Rajwada','Rajwada'), (4,'Malwa Mart Bhawarkuan','Bhawarkuan');
INSERT INTO customers VALUES (1,'Asha Verma','Indore'), (2,'Ravi Patel','Indore'), (3,'Meera Joshi','Dewas'),
(4,'Pooja Rathore','Ujjain'), (5,'Sana Qureshi','Indore'), (6,'Vikram Chouhan','Indore'), (7,'Neha D''Souza','Indore');
INSERT INTO orders VALUES
(101,1,1,'2026-03-01',1040,'UPI'), (102,2,2,'2026-03-01',330,'Cash'), (103,1,1,'2026-03-03',275,'UPI'),
(104,3,3,'2026-03-04',180,'Card'), (105,4,2,'2026-03-05',680,'UPI'), (106,2,4,'2026-03-07',120,'Cash'),
(107,5,3,'2026-03-08',1560,'Card'), (108,6,1,'2026-03-08',220,'UPI'), (109,4,4,'2026-03-10',340,NULL),
(110,1,2,'2026-03-12',465,'Cash'), (111,6,3,'2026-03-14',NULL,'UPI');
""")
solutions = {
6: """SELECT s.area, COUNT(o.order_id), SUM(o.amount), ROUND(AVG(o.amount), 0) FROM stores s
LEFT JOIN orders o ON o.store_id = s.store_id GROUP BY s.store_id, s.area ORDER BY 3 DESC""",
7: "SELECT MAX(amount) FROM orders WHERE amount < (SELECT MAX(amount) FROM orders)",
8: """SELECT c.name, COUNT(*), SUM(o.amount) FROM orders o JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name HAVING COUNT(*) >= 2 ORDER BY 3 DESC""",
}
for number, sql in solutions.items():
print(f"Problem {number}:", con.execute(sql).fetchall())Problems 9 and 10 use the products, bill-line and staff tables:
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE products (product_id INTEGER PRIMARY KEY, product_name TEXT, category TEXT, price INT);
CREATE TABLE order_items (order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id));
CREATE TABLE staff (staff_id INTEGER PRIMARY KEY, name TEXT, role TEXT, store_id INT, manager_id INT);
INSERT INTO products VALUES (1,'Basmati Rice 5kg','Grocery',520), (2,'Toor Dal 1kg','Grocery',165), (3,'Poha 1kg','Grocery',60),
(4,'Butter 500g','Dairy',275), (5,'Paneer 200g','Dairy',90), (6,'Namkeen Mix 400g','Snacks',110),
(7,'Detergent 2kg','Household',340), (8,'Sev 500g','Snacks',120);
INSERT INTO order_items VALUES (101,1,1), (101,7,1), (101,8,1), (101,3,1), (102,2,2), (103,4,1), (104,3,3), (105,7,2),
(106,8,1), (107,1,3), (108,6,2), (109,7,1), (110,2,1), (110,8,1), (110,3,3), (111,5,2);
INSERT INTO staff VALUES (1,'Rajesh Sharma','Operations Head',NULL,NULL), (2,'Kavita Jain','Store Manager',1,1),
(3,'Arjun Malviya','Store Manager',3,1), (4,'Priya Soni','Cashier',1,2),
(5,'Deepak Yadav','Cashier',3,3), (6,'Farhan Ali','Stock Clerk',1,2);
""")
print("Problem 9:", con.execute("""SELECT p.category, SUM(i.quantity), SUM(i.quantity * p.price)
FROM order_items i JOIN products p ON p.product_id = i.product_id
GROUP BY p.category ORDER BY 3 DESC""").fetchall())
for row in con.execute("""SELECT e.name, e.role, COALESCE(m.name, '(no manager)')
FROM staff e LEFT JOIN staff m ON m.staff_id = e.manager_id ORDER BY e.staff_id"""):
print("Problem 10:", row)Hard SQL practice problems (11 to 15)
Problem 11. Show each customer's first order: name, order id and date.
WITH ranked AS (
SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
FROM orders AS o
)
SELECT c.name, r.order_id, r.order_date
FROM ranked AS r JOIN customers AS c ON c.customer_id = r.customer_id
WHERE r.rn = 1 ORDER BY r.order_date;Answer: six customers, starting with Asha (101) and Ravi (102) on 1 March. Adding order_id to the window's ORDER BY makes the pick deterministic if a customer ordered twice on one day. Revise: window functions.
Problem 12. For each day, show revenue, a running total and the change from the previous trading day.
WITH daily AS (SELECT order_date, SUM(amount) AS revenue FROM orders GROUP BY order_date)
SELECT order_date, revenue,
SUM(revenue) OVER (ORDER BY order_date) AS running_total,
revenue - LAG(revenue) OVER (ORDER BY order_date) AS change
FROM daily;Answer: 1 March starts at 1,370, and the running total reaches 5,210 on 12 March. On 14 March revenue is NULL (order 111), so its change is NULL while the running total stays at 5,210. Revise: CTEs.
Problem 13. Find the best-selling product by units in each category. If two products tie, show both.
WITH units AS (
SELECT p.category, p.product_name, SUM(i.quantity) AS units,
RANK() OVER (PARTITION BY p.category ORDER BY SUM(i.quantity) DESC) AS rnk
FROM order_items AS i JOIN products AS p ON p.product_id = i.product_id
GROUP BY p.category, p.product_name
)
SELECT category, product_name, units FROM units WHERE rnk = 1;Answer: Poha (7 units), Paneer (2), Detergent (4) and Sev (3). RANK, not ROW_NUMBER, is what keeps ties; a window can sort by an aggregate because windows run after GROUP BY.
Problem 14. Which customers have bought products from both the Snacks and Household categories?
SELECT c.name
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
JOIN products AS p ON p.product_id = i.product_id
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE p.category IN ('Snacks', 'Household')
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT p.category) = 2;Answer: only Asha, whose order 101 had detergent and sev. Pooja bought detergent twice but no snacks. The COUNT(DISTINCT ...) = 2 pattern generalises to "bought from all N categories". INTERSECT is another approach, covered in UNION, INTERSECT and EXCEPT.
Problem 15. Data quality: find orders whose stored amount does not match the total of their bill lines.
WITH line_totals AS (
SELECT i.order_id, SUM(i.quantity * p.price) AS items_total
FROM order_items AS i JOIN products AS p ON p.product_id = i.product_id
GROUP BY i.order_id
)
SELECT o.order_id, o.amount, t.items_total
FROM orders AS o JOIN line_totals AS t ON t.order_id = o.order_id
WHERE o.amount IS NOT t.items_total; -- PostgreSQL: IS DISTINCT FROM; MySQL: NOT (o.amount <=> t.items_total)Answer: only order 111, stored as NULL but worth 180 rupees. A plain <> would miss it, because NULL <> 180 is unknown, not true.
Run problems 11 and 12, which need only the orders table:
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INT, store_id INT, order_date TEXT, amount INT, payment_mode TEXT);
INSERT INTO orders VALUES
(101,1,1,'2026-03-01',1040,'UPI'), (102,2,2,'2026-03-01',330,'Cash'), (103,1,1,'2026-03-03',275,'UPI'),
(104,3,3,'2026-03-04',180,'Card'), (105,4,2,'2026-03-05',680,'UPI'), (106,2,4,'2026-03-07',120,'Cash'),
(107,5,3,'2026-03-08',1560,'Card'), (108,6,1,'2026-03-08',220,'UPI'), (109,4,4,'2026-03-10',340,NULL),
(110,1,2,'2026-03-12',465,'Cash'), (111,6,3,'2026-03-14',NULL,'UPI');
""")
first = """SELECT customer_id, order_id, order_date FROM (SELECT *, ROW_NUMBER() OVER
(PARTITION BY customer_id ORDER BY order_date, order_id) AS rn FROM orders) WHERE rn = 1"""
print("Problem 11:", con.execute(first).fetchall())
daily = """WITH daily AS (SELECT order_date, SUM(amount) AS revenue FROM orders GROUP BY order_date)
SELECT order_date, revenue, SUM(revenue) OVER (ORDER BY order_date),
revenue - LAG(revenue) OVER (ORDER BY order_date) FROM daily"""
for row in con.execute(daily):
print("Problem 12:", row)Run problems 13 to 15, which work on the bill lines:
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE products (product_id INTEGER PRIMARY KEY, product_name TEXT, category TEXT, price INT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INT, amount INT);
CREATE TABLE order_items (order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id));
INSERT INTO products VALUES (1,'Basmati Rice 5kg','Grocery',520), (2,'Toor Dal 1kg','Grocery',165), (3,'Poha 1kg','Grocery',60),
(4,'Butter 500g','Dairy',275), (5,'Paneer 200g','Dairy',90), (6,'Namkeen Mix 400g','Snacks',110),
(7,'Detergent 2kg','Household',340), (8,'Sev 500g','Snacks',120);
INSERT INTO orders VALUES (101,1,1040), (102,2,330), (103,1,275), (104,3,180), (105,4,680), (106,2,120),
(107,5,1560), (108,6,220), (109,4,340), (110,1,465), (111,6,NULL);
INSERT INTO order_items VALUES (101,1,1), (101,7,1), (101,8,1), (101,3,1), (102,2,2), (103,4,1), (104,3,3), (105,7,2),
(106,8,1), (107,1,3), (108,6,2), (109,7,1), (110,2,1), (110,8,1), (110,3,3), (111,5,2);
""")
solutions = {
13: """SELECT category, product_name, units FROM (SELECT p.category, p.product_name, SUM(i.quantity) AS units,
RANK() OVER (PARTITION BY p.category ORDER BY SUM(i.quantity) DESC) AS rnk FROM order_items i
JOIN products p ON p.product_id = i.product_id GROUP BY p.category, p.product_name) WHERE rnk = 1""",
14: """SELECT o.customer_id FROM orders o JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id WHERE p.category IN ('Snacks', 'Household')
GROUP BY o.customer_id HAVING COUNT(DISTINCT p.category) = 2""",
15: """WITH t AS (SELECT i.order_id, SUM(i.quantity * p.price) AS total FROM order_items i
JOIN products p ON p.product_id = i.product_id GROUP BY i.order_id)
SELECT o.order_id, o.amount, t.total FROM orders o JOIN t ON t.order_id = o.order_id WHERE o.amount IS NOT t.total""",
}
for number, sql in solutions.items():
print(f"Problem {number}:", con.execute(sql).fetchall())To keep these blocks short, problems 11 and 14 return customer ids; join customers as in the readable solutions to get names.
Quick check
Which ranking function keeps two products that tie for first place?
RANK and DENSE_RANK give tied rows the same rank, so both pass a rnk = 1 filter. ROW_NUMBER always numbers rows uniquely.
Common mistakes in SQL practice and tests
- Losing rows with INNER JOIN. Problems 4, 6 and 10 need LEFT JOIN because some rows have no match.
- Comparing with NULL using = or <>. Use IS NULL, IS NOT, or COALESCE, as problems 5 and 15 show.
- Filtering groups in WHERE. Conditions on COUNT or SUM go in HAVING.
- Non-deterministic picks. "First order" needs a tie-breaker in ORDER BY, or the answer can change between runs.
When these feel easy, try timed SQL practice tests on Debugshala Test, and the SQL interview questions page explains the theory interviewers ask alongside these problems. For mentor feedback on your solutions, Debugshala's Data Analytics programme in Indore reviews queries like these in class.
Key takeaways
- Practise on a dataset small enough to check by hand, with deliberate NULLs and unmatched rows.
- Decide the grain of the answer before writing GROUP BY.
- LEFT JOIN plus IS NULL finds missing matches; HAVING filters groups.
- Window functions answer "first", "top per group", "running total" and "change from previous" questions.
- Null-safe comparisons (IS NOT, IS DISTINCT FROM) are essential for data-quality checks.
FAQ
Where can I practise SQL queries online for free?
You can run every problem on this page in your browser with the Run buttons, with nothing to install. For more variety, write your own questions on the same tables, then build speed with timed quizzes on Debugshala Test before interviews.
How many SQL problems should I solve before an interview?
Quality matters more than a number. Around 50 to 100 varied problems covering joins, GROUP BY with HAVING, subqueries, CTEs and window functions is a sensible target, if you can explain each solution aloud. Interviewers weigh your reasoning about NULLs and ties as much as the final query.
What are the most common SQL practice questions for data analysts?
Typical questions ask for top N per group, second-highest values, customers with no orders, repeat customers, running totals, month-over-month change and duplicates. Nearly all reduce to joins, GROUP BY with HAVING, and window functions such as ROW_NUMBER, RANK and LAG.
Do these solutions work in MySQL and PostgreSQL?
Yes, with small changes. MySQL 8 and PostgreSQL support the same CTEs and window functions. Problem 15's null-safe comparison is written IS DISTINCT FROM in PostgreSQL and with the <=> operator in MySQL. Grouping by the output alias in problem 3 works in all three databases.
Is there a SQL training institute in Indore with practice sessions?
Yes. Debugshala, at Old Palasia in Indore, runs a Data Analytics course in which learners solve SQL problems on practical datasets with mentor feedback. Classes are offline in English and Hindi, fresh graduates and non-IT candidates are welcome, placement assistance is offered, and you can start with a free demo class.
From the team behind this site
Go further with the Data Analytics Course at Debugshala
These tutorials are written by Debugshala, a training and placement institute at Old Palasia, Indore. The classroom course adds live mentoring, real projects and interview preparation.
- Offline classes at Old Palasia, Indore
- Free demo class before you pay
- Non-IT, fresher and year-gap friendly
- Placement assistance
700+ students placed · 200+ hiring partners · 4.9★ Google rating
