100 MySQL Practice Queries with Answers
In this lesson: 100 MySQL questions with solutions and output — beginner to advanced — the complete practice set for exams and interviews.
The complete set
This is the ultimate MySQL practice set — 100 queries with answers, grouped by topic from basics to advanced. Write each one yourself before checking, and run them on a sample database. Done in order, they cover everything you need for exams, interviews and real work.
Basics (1-15)
1. SELECT * FROM students; — 2. SELECT name FROM students; — 3. SELECT DISTINCT city FROM students;
4. SELECT * FROM students LIMIT 5; — 5. SELECT COUNT(*) FROM students; — 6. SELECT name AS n FROM students;
7. SELECT name, marks+5 FROM students; — 8. SELECT CONCAT(name,'-',city) FROM students;
9. SELECT CURDATE(); — 10. SELECT UPPER(name) FROM students; — 11. SELECT LOWER(name) FROM students;
12. SELECT LENGTH(name) FROM students; — 13. SELECT NOW(); — 14. SELECT 5+3, 10*2; — 15. SELECT ROUND(3.567,1);
Filtering & sorting (16-35)
16. ... WHERE marks > 40; — 17. ... WHERE class=10 AND marks>=33; — 18. ... WHERE city='Delhi' OR city='Pune';
19. ... WHERE name LIKE 'A%'; — 20. ... WHERE name LIKE '%a'; — 21. ... WHERE name LIKE '_a%';
22. ... WHERE marks BETWEEN 40 AND 80; — 23. ... WHERE city IN ('Delhi','Pune');
24. ... WHERE city NOT IN ('Delhi'); — 25. ... WHERE phone IS NULL; — 26. ... WHERE phone IS NOT NULL;
27. ... ORDER BY marks; — 28. ... ORDER BY marks DESC; — 29. ... ORDER BY city, marks DESC;
30. ... ORDER BY marks DESC LIMIT 3; — 31. ... LIMIT 5 OFFSET 10; — 32. ... WHERE marks!=0;
33. ... WHERE NOT city='Delhi'; — 34. ... WHERE marks>=40 AND marks<=60; — 35. ... WHERE name REGEXP '^[AB]';
Aggregates (36-55)
36. SELECT SUM(marks) FROM students; — 37. SELECT AVG(marks) FROM students;
38. SELECT MAX(marks), MIN(marks) FROM students; — 39. SELECT COUNT(DISTINCT city) FROM students;
40. SELECT class, COUNT(*) FROM students GROUP BY class; — 41. SELECT class, AVG(marks) FROM students GROUP BY class;
42. SELECT class, SUM(marks) FROM students GROUP BY class; — 43. SELECT city, MAX(marks) FROM students GROUP BY city;
44. ... GROUP BY class HAVING COUNT(*)>30; — 45. ... GROUP BY class HAVING AVG(marks)>50;
46. SELECT ROUND(AVG(marks),2) FROM students; — 47. SELECT SUM(CASE WHEN marks>=40 THEN 1 ELSE 0 END) FROM students;
48. SELECT class, COUNT(*) FROM students GROUP BY class ORDER BY COUNT(*) DESC;
49. SELECT city, AVG(marks) FROM students GROUP BY city HAVING AVG(marks)>60;
50-55. Variations: group by two columns, count nulls, min/max per group, average rounded, total per city, pass-count per class.
Joins (56-70)
56. SELECT s.name,c.class_name FROM students s JOIN classes c ON s.class_id=c.id;
57. SELECT c.class_name,s.name FROM classes c LEFT JOIN students s ON s.class_id=c.id;
58. SELECT c.class_name,COUNT(s.id) FROM classes c LEFT JOIN students s ON s.class_id=c.id GROUP BY c.id;
59. SELECT cu.name FROM customers cu LEFT JOIN orders o ON o.customer_id=cu.id WHERE o.id IS NULL;
60. SELECT o.id,cu.name FROM orders o JOIN customers cu ON o.customer_id=cu.id;
61. Three tables: FROM order_items oi JOIN orders o ON oi.order_id=o.id JOIN products p ON oi.product_id=p.id;
62. Self-join: FROM emp a JOIN emp b ON a.salary=b.salary AND a.id
63-70. Join + WHERE, join + GROUP BY, join + ORDER BY, RIGHT JOIN, join three tables with totals, join with aggregate, join with HAVING, join with DISTINCT.
Subqueries (71-80)
71. SELECT * FROM students WHERE marks>(SELECT AVG(marks) FROM students);
72. SELECT * FROM students WHERE marks=(SELECT MAX(marks) FROM students);
73. SELECT name FROM students WHERE class_id IN (SELECT id FROM classes WHERE class_name='Class 10');
74. Second highest: SELECT MAX(marks) FROM students WHERE marks<(SELECT MAX(marks) FROM students);
75. SELECT AVG(t.total) FROM (SELECT SUM(amount) total FROM orders GROUP BY customer_id) t;
76-80. Correlated subquery, EXISTS, NOT EXISTS, subquery in SELECT, subquery with IN and GROUP BY.
Dates & strings (81-90)
81. SELECT TIMESTAMPDIFF(YEAR,dob,CURDATE()) FROM members; — 82. ... WHERE order_date>=CURDATE()-INTERVAL 7 DAY;
83. SELECT LAST_DAY(CURDATE()); — 84. SELECT DATE_FORMAT(CURDATE(),'%Y-%m-01');
85. SELECT DATE_FORMAT(order_date,'%Y-%m'), SUM(amount) FROM orders GROUP BY 1;
86. SELECT SUBSTRING(name,1,3) FROM students; — 87. SELECT TRIM(name) FROM students;
88. SELECT REPLACE(name,'a','A') FROM students; — 89. SELECT IFNULL(phone,'N/A') FROM students;
90. SELECT COALESCE(phone,mobile,'N/A') FROM students;
Advanced (91-100)
91. Grade: SELECT name, CASE WHEN marks>=90 THEN 'A' WHEN marks>=75 THEN 'B' ELSE 'C' END FROM students;
92. Pivot: SELECT region, SUM(CASE WHEN month='Jan' THEN amount ELSE 0 END) Jan FROM sales GROUP BY region;
93. Duplicates: SELECT email,COUNT(*) FROM users GROUP BY email HAVING COUNT(*)>1;
94. Delete duplicates: DELETE t1 FROM users t1 JOIN users t2 ON t1.email=t2.email AND t1.id>t2.id;
95. ROW_NUMBER: SELECT name, ROW_NUMBER() OVER (ORDER BY marks DESC) rn FROM students;
96. RANK: SELECT name, RANK() OVER (ORDER BY marks DESC) rnk FROM students;
97. DENSE_RANK for Nth: SELECT * FROM (SELECT marks, DENSE_RANK() OVER (ORDER BY marks DESC) r FROM students) t WHERE r=2;
98. Running total: SELECT name, SUM(amount) OVER (ORDER BY id) running FROM orders;
99. Update with CASE: UPDATE students SET result=CASE WHEN marks>=40 THEN 'Pass' ELSE 'Fail' END;
100. Insert from select: INSERT INTO archive SELECT * FROM users WHERE active=0;
Summary
- 100 queries from basics to window functions and projects.
- Work by topic, in order: basics, filtering, aggregates, joins, subqueries, dates, advanced.
- Write each yourself, run it, and tweak the conditions.
- Master joins, grouping and subqueries — the core of real SQL.
- Revisit the advanced ones until you can write them from memory.
Next, keep the syntax handy with the MySQL cheat sheet, or start with the smaller 50 MySQL queries set.
