Advanced SQL Queries – Class 12
Informatics Practices
This document provides advanced SQL queries covering joins, subqueries, set operations,
nested queries, and complex conditions. Practice these thoroughly to excel in exams.
Example Tables
Table: Students
+----+-------+----------+-------+-----------+
| ID | Name | Department | Marks | City |
+----+-------+----------+-------+-----------+
| 1 | Asha | CS | 85 | Mumbai |
| 2 | Rahul | IT | 75 | Pune |
| 3 | Meena | CS | 65 | Nashik |
| 4 | John | EC | 55 | Mumbai |
| 5 | Priya | IT | 45 | Pune |
+----+-------+----------+-------+-----------+
Table: Subjects
+----+-----------+-----------+
| ID | Student_ID | Subject |
+----+-----------+-----------+
|1 |1 | Math |
|2 |1 | Physics |
|3 |2 | Math |
|4 |3 | Chemistry |
|5 |4 | Physics |
|6 |5 | Chemistry |
+----+-----------+-----------+
Table: Scores
+----+-----------+-------+
| ID | Student_ID | Score |
+----+-----------+-------+
|1 |1 | 90 |
|2 |2 | 70 |
|3 |3 | 60 |
|4 |4 | 50 |
|5 |5 | 40 |
+----+-----------+-------+
Advanced SQL Queries
1. INNER JOIN with Multiple Tables
SELECT S.Name, S.Department, Sub.Subject, Sc.Score
FROM Students S
JOIN Subjects Sub ON S.ID = Sub.Student_ID
JOIN Scores Sc ON S.ID = Sc.Student_ID
WHERE S.Marks > 60;
2. LEFT JOIN Example
SELECT S.Name, Sub.Subject
FROM Students S
LEFT JOIN Subjects Sub ON S.ID = Sub.Student_ID
ORDER BY S.Name;
3. RIGHT JOIN Example
SELECT S.Name, Sc.Score
FROM Students S
RIGHT JOIN Scores Sc ON S.ID = Sc.Student_ID
WHERE Sc.Score > 50;
4. FULL OUTER JOIN Simulation
SELECT S.Name, Sc.Score
FROM Students S
LEFT JOIN Scores Sc ON S.ID = Sc.Student_ID
UNION
SELECT S.Name, Sc.Score
FROM Students S
RIGHT JOIN Scores Sc ON S.ID = Sc.Student_ID;
5. Subquery with EXISTS
SELECT S.Name
FROM Students S
WHERE EXISTS (SELECT * FROM Scores Sc WHERE S.ID = Sc.Student_ID AND Sc.Score > 80);
6. Correlated Subquery
SELECT S.Name, S.Marks
FROM Students S
WHERE S.Marks > (SELECT AVG(Sc.Score) FROM Scores Sc WHERE Sc.Student_ID = S.ID);
7. Nested Queries
SELECT Name
FROM Students
WHERE ID IN (SELECT Student_ID FROM Scores WHERE Score = (SELECT MAX(Score)
FROM Scores));
8. GROUP BY with HAVING
SELECT S.Department, COUNT(S.ID) AS TotalStudents
FROM Students S
GROUP BY S.Department
HAVING COUNT(S.ID) > 1;
9. Set Operations – UNION
SELECT Name FROM Students WHERE City = 'Mumbai'
UNION
SELECT Name FROM Students WHERE City = 'Pune';
10. Set Operations – INTERSECT Simulation
SELECT Name FROM Students WHERE ID IN
(SELECT Student_ID FROM Subjects WHERE Subject = 'Math')
AND ID IN
(SELECT Student_ID FROM Scores WHERE Score > 60);
11. CASE Statement for Grading
SELECT Name, Marks,
CASE
WHEN Marks >= 80 THEN 'A'
WHEN Marks >= 60 THEN 'B'
WHEN Marks >= 40 THEN 'C'
ELSE 'F'
END AS Grade
FROM Students;
12. Complex WHERE Clause
SELECT * FROM Students
WHERE (Department = 'CS' AND Marks > 70)
OR (City = 'Pune' AND Marks < 50);
13. Update with Subquery
UPDATE Students
SET Marks = Marks + 5
WHERE ID IN (SELECT Student_ID FROM Scores WHERE Score < 60);
14. Delete with Subquery
DELETE FROM Students
WHERE ID IN (SELECT Student_ID FROM Scores WHERE Score < 50);
15. Aggregate with JOIN
SELECT S.Department, AVG(Sc.Score) AS AvgScore
FROM Students S
JOIN Scores Sc ON S.ID = Sc.Student_ID
GROUP BY S.Department;
