100 MySQL Practice Queries

Advanced Practice (Set 2)

Joins & Subqueries

SELECT s.name, c.class_name FROM students s JOIN classes c ON s.class_id=c.class_id;
SELECT name FROM students WHERE marks > (SELECT AVG(marks) FROM students);
SELECT s.name, SUM(f.amount) FROM students s JOIN fees f ON s.roll_no=f.roll_no GROUP BY s.name;
SELECT name FROM students WHERE roll_no NOT IN (SELECT roll_no FROM fees);

Window & Ranking

SELECT name, marks, RANK() OVER (ORDER BY marks DESC) AS rnk FROM students;
SELECT name, class, RANK() OVER (PARTITION BY class ORDER BY marks DESC) AS class_rank FROM students;
SELECT name FROM (SELECT name, marks, ROW_NUMBER() OVER (ORDER BY marks DESC) rn FROM students) t WHERE rn=2;

Build the Rest

Extend to 100 by combining: per-class toppers, fee defaulters, monthly fee totals, students above their class average (correlated subquery), and CASE-based grade reports.

Summary

• Set 2 adds JOINs, subqueries, correlated subqueries and window functions.
• Combine techniques to reach 100 real-world queries.

Advanced Practice (Set 2)

Joins और Subqueries

SELECT s.name, c.class_name FROM students s JOIN classes c ON s.class_id=c.class_id;
SELECT name FROM students WHERE marks > (SELECT AVG(marks) FROM students);
SELECT s.name, SUM(f.amount) FROM students s JOIN fees f ON s.roll_no=f.roll_no GROUP BY s.name;
SELECT name FROM students WHERE roll_no NOT IN (SELECT roll_no FROM fees);

Window और Ranking

SELECT name, marks, RANK() OVER (ORDER BY marks DESC) AS rnk FROM students;
SELECT name, class, RANK() OVER (PARTITION BY class ORDER BY marks DESC) AS class_rank FROM students;
SELECT name FROM (SELECT name, marks, ROW_NUMBER() OVER (ORDER BY marks DESC) rn FROM students) t WHERE rn=2;

बाकी बनाएं

100 तक बढ़ाएं: per-class toppers, fee defaulters, monthly fee totals, अपनी class average से ऊपर students (correlated subquery), और CASE-based grade reports।

सारांश

• Set 2 JOINs, subqueries, correlated subqueries और window functions जोड़ता है।
• Techniques मिलाकर 100 real-world queries तक पहुँचें।