50 Important MySQL Queries
इस set को कैसे इस्तेमाल करें
यहाँ 50 MySQL queries answers के साथ हैं, बुनियादी से advanced तक समूहबद्ध। हर एक को, पहले अपनी query लिखिए, फिर जाँचिए। ये क्रम में बनते हैं, तो ऊपर से नीचे काम करना असली कौशल बढ़ाने का सबसे अच्छा तरीका है।
Basics (1-10)
1. सारी rows: SELECT * FROM students;
2. सिर्फ़ names: SELECT name FROM students;
3. Unique cities: SELECT DISTINCT city FROM students;
4. पहली 5: SELECT * FROM students LIMIT 5;
5. Rows गिनो: SELECT COUNT(*) FROM students;
6. Column rename: SELECT name AS student_name FROM students;
7. Computed column: SELECT name, marks + 5 FROM students;
8. Concatenate: SELECT CONCAT(name, ' (', city, ')') FROM students;
9. आज की date: SELECT CURDATE();
10. Uppercase: SELECT UPPER(name) FROM students;
Filtering और sorting (11-20)
11. Marks > 40: ... WHERE marks > 40;
12. दो conditions: ... WHERE class = 10 AND marks >= 33;
13. कोई एक condition: ... WHERE city = 'Delhi' OR city = 'Mumbai';
14. A से शुरू: ... WHERE name LIKE 'A%';
15. Range में: ... WHERE marks BETWEEN 40 AND 80;
16. List में: ... WHERE city IN ('Delhi','Pune');
17. Not null: ... WHERE phone IS NOT NULL;
18. Ascending sort: ... ORDER BY marks;
19. Descending sort: ... 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. प्रति class गिनती: SELECT class, COUNT(*) FROM students GROUP BY class;
25. प्रति class औसत: SELECT class, AVG(marks) FROM students GROUP BY class;
26. 30 से ऊपर classes: ... GROUP BY class HAVING COUNT(*) > 30;
27. प्रति city सबसे ऊँचा: SELECT city, MAX(marks) FROM students GROUP BY city;
28. Distinct गिनो: SELECT COUNT(DISTINCT city) FROM students;
29. Round average: SELECT ROUND(AVG(marks), 2) FROM students;
30. Condition से sum: 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. Join पर count: 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 customers के साथ: SELECT o.id, cu.name FROM orders o JOIN customers cu ON o.customer_id = cu.id;
35. बिना orders customers: ... LEFT JOIN orders o ON o.customer_id = cu.id WHERE o.id IS NULL;
36. तीन-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 (समान 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. Joined column से order: ... ORDER BY c.class_name;
Advanced (41-50)
41. दूसरा सबसे ऊँचा: SELECT DISTINCT marks FROM students ORDER BY marks DESC LIMIT 1 OFFSET 1;
42. औसत से ऊपर: 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: SELECT DATE_FORMAT(order_date,'%Y-%m') m, SUM(amount) FROM orders GROUP BY m;
45. पिछले 7 दिन: ... WHERE order_date >= CURDATE() - INTERVAL 7 DAY;
46. DOB से age: SELECT name, TIMESTAMPDIFF(YEAR, dob, CURDATE()) FROM members;
47. CASE से grade: SELECT name, CASE WHEN marks>=90 THEN 'A' ELSE 'B' END FROM students;
48. FROM में subquery: SELECT AVG(t.total) FROM (SELECT SUM(amount) total FROM orders GROUP BY customer_id) t;
49. CASE से pivot: SELECT region, SUM(CASE WHEN month='Jan' THEN amount ELSE 0 END) Jan FROM sales GROUP BY region;
50. CASE से update: UPDATE students SET grade = CASE WHEN marks>=40 THEN 'Pass' ELSE 'Fail' END;
अभ्यास tips
- पहले लिखिए, फिर देखिए। Answer से पहले हर query आज़माइए।
- सब चलाइए। Sample database पर असली output देखिए।
- Conditions tweak कीजिए। उनका effect सीखने को WHERE और GROUP BY बदलिए।
- कठिन वाले दोबारा देखिए। 41-50 एक दिन बाद याद से करिए।
सारांश
- 50 queries बुनियादी SELECT से joins, subqueries और pivots तक।
- इन्हें क्रम में कीजिए; जाँचने से पहले लिखिए।
- Filtering, sorting, grouping और joining में महारत पाइए।
- दूसरी सबसे ऊँची और pivots जैसे advanced patterns अभ्यास कीजिए।
- असली data पर सक्रिय अभ्यास सच्चा query कौशल बनाता है।
आगे, syntax पास रखिए MySQL cheat sheet से, या आगे बढ़िए 100 practice queries से।
