SQL Queries Practice (20 with Answers)

How to practise

The best way to learn SQL is to write queries yourself. Below are 20 common tasks with answers. For each, try writing your own query first, then compare. Run them on a small sample database and tweak the conditions to see what changes.

Basic SELECT queries

1. All students: SELECT * FROM students;

2. Only names: SELECT name FROM students;

3. Unique cities: SELECT DISTINCT city FROM students;

4. First 5 rows: SELECT * FROM students LIMIT 5;

Filtering and sorting

5. Marks above 40: SELECT * FROM students WHERE marks > 40;

6. Class 10 and passed: SELECT * FROM students WHERE class = 10 AND marks >= 33;

7. Names starting with A: SELECT * FROM students WHERE name LIKE 'A%';

8. Sorted by marks (high to low): SELECT * FROM students ORDER BY marks DESC;

9. Top 3 scorers: SELECT * FROM students ORDER BY marks DESC LIMIT 3;

Aggregate queries

10. Count students: SELECT COUNT(*) FROM students;

11. Average marks: SELECT AVG(marks) FROM students;

12. Highest and lowest: SELECT MAX(marks), MIN(marks) FROM students;

13. Count per class: SELECT class, COUNT(*) FROM students GROUP BY class;

14. Classes with over 30 students: SELECT class, COUNT(*) FROM students GROUP BY class HAVING COUNT(*) > 30;

Join queries

15. Students with their class name:
SELECT s.name, c.class_name FROM students s JOIN classes c ON s.class_id = c.id;

16. Orders with customer names:
SELECT o.id, cu.name FROM orders o JOIN customers cu ON o.customer_id = cu.id;

17. Customers with no orders:
SELECT cu.name FROM customers cu LEFT JOIN orders o ON o.customer_id = cu.id WHERE o.id IS NULL;

A few harder ones

18. Second highest marks:
SELECT DISTINCT marks FROM students ORDER BY marks DESC LIMIT 1 OFFSET 1;

19. Total sales per month:
SELECT DATE_FORMAT(order_date,'%Y-%m') AS m, SUM(amount) FROM orders GROUP BY m;

20. Duplicate emails:
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

Practice tips

• Write before you read. Attempt each query yourself first.
• Run everything. Use a sample database and see the real output.
• Change conditions. Tweak WHERE, ORDER BY and GROUP BY to learn their effect.
• Redo from memory. Come back a day later and rewrite the tricky ones.

Summary

• Practise by writing queries yourself, then checking.
• Master SELECT with WHERE, ORDER BY and LIMIT.
• Learn aggregates with GROUP BY and HAVING.
• Be comfortable with JOINs, including LEFT JOIN for "missing" rows.
• Practise classics like second highest and duplicate detection.

Next, dive deeper into GROUP BY and HAVING, or try more in 50 MySQL queries.