100 MySQL Practice Queries
पूरा set
यह अंतिम MySQL practice set है — 100 queries answers के साथ, basics से advanced तक topic से समूहबद्ध। हर एक जाँचने से पहले खुद लिखिए, और sample database पर चलाइए। क्रम में की जाएँ, ये exams, interviews और असली काम को ज़रूरी सब कुछ कवर करती हैं।
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: दो columns से group, nulls गिनना, प्रति group min/max, rounded average, प्रति city total, प्रति class pass-count।
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. तीन 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, तीन tables totals के साथ, aggregate वाला join, HAVING वाला join, DISTINCT वाला join।
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. दूसरी सबसे ऊँची: 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, SELECT में subquery, IN और GROUP BY वाला subquery।
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. Nth को DENSE_RANK: 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. CASE से update: UPDATE students SET result=CASE WHEN marks>=40 THEN 'Pass' ELSE 'Fail' END;
100. Select से insert: INSERT INTO archive SELECT * FROM users WHERE active=0;
सारांश
- 100 queries basics से window functions और projects तक।
- Topic से, क्रम में कीजिए: basics, filtering, aggregates, joins, subqueries, dates, advanced।
- हर एक खुद लिखिए, चलाइए, और conditions tweak कीजिए।
- Joins, grouping और subqueries में महारत पाइए — असली SQL का दिल।
- Advanced वाले तब तक दोबारा देखिए जब तक याद से लिख सकें।
आगे, syntax पास रखिए MySQL cheat sheet से, या छोटे 50 MySQL queries set से शुरू कीजिए।
