Data Analyst SQL practice questions
10 free questions with answers and explanations. Try each one, then tap “Show answer”.
- Aggregation
1. Table sales: id | region | product | qty | price 1 | South | Rice | 8 | 15 2 | South | Rice | 9 | 10 3 | North | Tea | 11 | 15 4 | East | Rice | 3 | 12 5 | East | Rice | 9 | 8 6 | North | Soap | 1 | 20 7 | East | Soap | 1 | 5 What does this return? SELECT COUNT(DISTINCT product) FROM sales;
- A. 2
- B. 4
- C. 7
- D. 3
Show answer
Answer: D. 3
The distinct products are Rice, Tea, Soap.
- Aggregation
2. Table sales: id | region | product | qty | price 1 | East | Soap | 6 | 10 2 | South | Rice | 8 | 12 3 | North | Soap | 10 | 8 4 | North | Soap | 9 | 15 5 | North | Soap | 10 | 15 6 | South | Tea | 9 | 15 7 | East | Soap | 5 | 20 What does this return? SELECT AVG(price) FROM sales WHERE region = 'East';
- A. 2
- B. 13.57
- C. 30
- D. 15
Show answer
Answer: D. 15
Average price of the 2 'East' rows = 15.
- Aggregation
3. Table sales: id | region | product | qty | price 1 | South | Soap | 5 | 8 2 | South | Rice | 4 | 8 3 | South | Soap | 4 | 10 4 | North | Tea | 8 | 5 5 | South | Soap | 5 | 10 6 | East | Tea | 8 | 15 7 | South | Rice | 8 | 8 What does this return? SELECT COUNT(DISTINCT product) FROM sales;
- A. 7
- B. 4
- C. 3
- D. 2
Show answer
Answer: C. 3
The distinct products are Soap, Rice, Tea.
- Filtering
4. Table sales: id | region | product | qty | price 1 | North | Tea | 11 | 20 2 | North | Soap | 9 | 20 3 | South | Tea | 6 | 10 4 | North | Tea | 1 | 15 5 | North | Tea | 6 | 12 6 | South | Tea | 10 | 10 7 | East | Tea | 12 | 10 What does this return? SELECT COUNT(*) FROM sales WHERE region = 'North';
- A. 3
- B. 4
- C. 5
- D. 7
Show answer
Answer: B. 4
4 rows have region = 'North'.
- Joins
5. customers: id | name 1 | Grace 2 | Eve 3 | Cara 4 | Ada 5 | Hana orders: order_id | customer_id 100 | 6 101 | 4 102 | 1 103 | 6 104 | 5 105 | 4 How many rows does this return? SELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;
- A. 5
- B. 6
- C. 7
- D. 4
Show answer
Answer: B. 6
LEFT JOIN keeps every customer; customers with no orders appear once with NULLs: 6 rows.
- Grouping
6. Table sales: id | region | product | qty | price 1 | South | Tea | 8 | 10 2 | East | Rice | 3 | 15 3 | East | Tea | 4 | 15 4 | South | Tea | 5 | 20 5 | East | Soap | 5 | 5 6 | South | Soap | 10 | 12 7 | South | Rice | 4 | 10 How many rows does this return? SELECT region, SUM(qty) FROM sales GROUP BY region HAVING SUM(qty) > 15;
- A. 0
- B. 2
- C. 1
- D. 3
Show answer
Answer: C. 1
Totals by region: South 27, East 12. 1 exceeds 15.
- Filtering
7. Table sales: id | region | product | qty | price 1 | North | Soap | 1 | 5 2 | South | Soap | 1 | 8 3 | North | Soap | 7 | 20 4 | South | Soap | 5 | 5 5 | North | Tea | 3 | 12 6 | East | Tea | 3 | 10 7 | North | Rice | 10 | 15 What does this return? SELECT COUNT(*) FROM sales WHERE region = 'South';
- A. 7
- B. 2
- C. 3
- D. 1
Show answer
Answer: B. 2
2 rows have region = 'South'.
- Sorting
8. Table sales: id | region | product | qty | price 1 | North | Rice | 12 | 12 2 | South | Rice | 7 | 8 3 | South | Tea | 5 | 5 4 | North | Rice | 3 | 5 5 | North | Rice | 4 | 8 6 | South | Tea | 12 | 5 7 | South | Rice | 8 | 8 Which id does this return? SELECT id FROM sales ORDER BY price DESC LIMIT 1;
- A. 4
- B. 2
- C. 6
- D. 1
Show answer
Answer: D. 1
The highest price is 12, in row 1.
- Concepts
9. COUNT(column) differs from COUNT(*) because COUNT(column)
- A. only works on numbers
- B. ignores NULL values in that column
- C. counts NULLs twice
- D. always returns 1
Show answer
Answer: B. ignores NULL values in that column
COUNT(*) counts every row.
- Aggregation
10. Table sales: id | region | product | qty | price 1 | East | Tea | 10 | 12 2 | South | Soap | 8 | 15 3 | East | Rice | 2 | 20 4 | North | Soap | 11 | 10 5 | North | Soap | 7 | 5 6 | South | Rice | 8 | 10 7 | North | Rice | 1 | 5 What does this return? SELECT SUM(qty) FROM sales WHERE product = 'Rice';
- A. 13
- B. 11
- C. 47
- D. 3
Show answer
Answer: B. 11
Add qty for rows where product = 'Rice': 11.
Get fresh questions every day, focused on your weak topics, plus timed mock tests. Free to start.
Practise free in the web app Practise on TelegramMore Data Analyst practice
About the Data Analyst on Mock Night · Original practice questions; not affiliated with the exam board.