50 MySQL Queries with Answers
In this lesson: 50 MySQL practice queries from easy to advanced with answers and output — joins, group by, subqueries.
How to use this set
Here are 50 MySQL queries with answers, grouped from basic to advanced. For each, write your own query first, then check. They build in order, so working through them top to bottom is the best way to grow real skill.
Basics (1-10)
1. All rows: SELECT * FROM students;
2. Only names: SELECT name FROM students;
3. Unique cities: SELECT DISTINCT city FROM students;
4. First 5: SELECT * FROM students LIMIT 5;
5. Count rows: SELECT COUNT(*) FROM students;
6. Rename column: SELECT name AS student_name FROM students;
7. Add a computed column: SELECT name, marks + 5 FROM students;
8. Concatenate: SELECT CONCAT(name, ' (', city, ')') FROM students;
9. Current date: SELECT CURDATE();
10. Uppercase: SELECT UPPER(name) FROM students;
Filtering and sorting (11-20)
11. Marks > 40: ... WHERE marks > 40;
12. Two conditions: ... WHERE class = 10 AND marks >= 33;
13. Either condition: ... WHERE city = 'Delhi' OR city = 'Mumbai';
14. Starts with A: ... WHERE name LIKE 'A%';
15. In a range: ... WHERE marks BETWEEN 40 AND 80;
16. In a list: ... WHERE city IN ('Delhi','Pune');
17. Not null: ... WHERE phone IS NOT NULL;
18. Sort ascending: ... ORDER BY marks;
19. Sort descending: ... ORDER BY marks DESC;
20. Top 3: ... ORDER BY marks DESC LIMIT 3;
Aggregates (21-30)
21. Total: SELECT SUM(marks) FROM students;
22. Average: SELECT AVG(marks) FROM students;
23. Max/min: SELECT MAX(marks), MIN(marks) FROM students;
24. Count per class: SELECT class, COUNT(*) FROM students GROUP BY class;
25. Average per class: SELECT class, AVG(marks) FROM students GROUP BY class;
26. Classes over 30: ... GROUP BY class HAVING COUNT(*) > 30;
27. Highest per city: SELECT city, MAX(marks) FROM students GROUP BY city;
28. Count distinct: SELECT COUNT(DISTINCT city) FROM students;
29. Round average: SELECT ROUND(AVG(marks), 2) FROM students;
30. Sum with condition: SELECT SUM(CASE WHEN marks >= 40 THEN 1 ELSE 0 END) AS passed FROM students;
Joins (31-40)
31. Inner join: SELECT s.name, c.class_name FROM students s JOIN classes c ON s.class_id = c.id;
32. Left join: SELECT c.class_name, s.name FROM classes c LEFT JOIN students s ON s.class_id = c.id;
33. Count per join: SELECT c.class_name, COUNT(s.id) FROM classes c LEFT JOIN students s ON s.class_id = c.id GROUP BY c.id;
34. Orders with customers: SELECT o.id, cu.name FROM orders o JOIN customers cu ON o.customer_id = cu.id;
35. Customers with no orders: ... LEFT JOIN orders o ON o.customer_id = cu.id WHERE o.id IS NULL;
36. Three-table join: SELECT ... FROM order_items oi JOIN orders o ON oi.order_id=o.id JOIN products p ON oi.product_id=p.id;
37. Self-join (same salary): SELECT a.name, b.name FROM emp a JOIN emp b ON a.salary=b.salary AND a.id
38. Join + filter: ... JOIN ... WHERE p.price > 100;
39. Join + group: SELECT cu.name, SUM(oi.quantity*oi.price) FROM customers cu JOIN orders o ON o.customer_id=cu.id JOIN order_items oi ON oi.order_id=o.id GROUP BY cu.id;
40. Order by joined column: ... ORDER BY c.class_name;
Advanced (41-50)
41. Second highest: SELECT DISTINCT marks FROM students ORDER BY marks DESC LIMIT 1 OFFSET 1;
42. Above average: SELECT * FROM students WHERE marks > (SELECT AVG(marks) FROM students);
43. Duplicate emails: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
44. Sales per month: SELECT DATE_FORMAT(order_date,'%Y-%m') m, SUM(amount) FROM orders GROUP BY m;
45. Last 7 days: ... WHERE order_date >= CURDATE() - INTERVAL 7 DAY;
46. Age from DOB: SELECT name, TIMESTAMPDIFF(YEAR, dob, CURDATE()) FROM members;
47. Grade with CASE: SELECT name, CASE WHEN marks>=90 THEN 'A' ELSE 'B' END FROM students;
48. Subquery in FROM: SELECT AVG(t.total) FROM (SELECT SUM(amount) total FROM orders GROUP BY customer_id) t;
49. Pivot with CASE: SELECT region, SUM(CASE WHEN month='Jan' THEN amount ELSE 0 END) Jan FROM sales GROUP BY region;
50. Update with CASE: UPDATE students SET grade = CASE WHEN marks>=40 THEN 'Pass' ELSE 'Fail' END;
Practice tips
- Write first, then peek. Attempt each query before the answer.
- Run them all. See real output on a sample database.
- Tweak conditions. Change WHERE and GROUP BY to learn their effect.
- Revisit the hard ones. Redo 41-50 from memory a day later.
Summary
- 50 queries from basic SELECT to joins, subqueries and pivots.
- Work through them in order; write before checking.
- Master filtering, sorting, grouping and joining.
- Practise advanced patterns like second highest and pivots.
- Active practice on real data builds true query skill.
Next, keep the syntax handy with the MySQL cheat sheet, or push further with 100 practice queries.
