Subqueries

-- Average पेक्षा जास्त marks असलेले students
SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);
-- Inner query: AVG = 70.5 (example)
-- Outer query: marks > 70.5 असलेले students

-- सर्वात जास्त marks असलेला student
SELECT name, marks
FROM students
WHERE marks = (SELECT MAX(marks) FROM students);

-- Pune मधले students च्या average पेक्षा जास्त
SELECT name, marks, city
FROM students
WHERE marks > (
    SELECT AVG(marks)
    FROM students
    WHERE city = 'Pune'
);

-- Python course enrolled असलेले students
SELECT name, city
FROM students
WHERE id IN (
    SELECT student_id
    FROM enrollments
    WHERE course = 'Python'
);

-- कोणत्याही course मध्ये enrolled नसलेले
SELECT name
FROM students
WHERE id NOT IN (
    SELECT DISTINCT student_id
    FROM enrollments
    WHERE student_id IS NOT NULL
);

-- City averages मधून 75+ असलेल्या cities
SELECT city, avg_marks
FROM (
    SELECT city, ROUND(AVG(marks), 1) AS avg_marks
    FROM students
    GROUP BY city
) AS city_stats              -- derived table ला alias लागतो!
WHERE avg_marks > 75
ORDER BY avg_marks DESC;

-- Top scorer प्रत्येक city मधून
SELECT s.name, s.city, s.marks
FROM students s
INNER JOIN (
    SELECT city, MAX(marks) AS top_marks
    FROM students
    GROUP BY city
) AS tops ON s.city = tops.city AND s.marks = tops.top_marks;