Scribd
Upload
19 views4 pages

Advanced SQL Queries for Class 12

This document provides a comprehensive guide to advanced SQL queries, including various types of joins, subqueries, set operations, and complex conditions. It includes example tables and SQL statements for practical understanding and exam preparation. Students are encouraged to practice these queries thoroughly to enhance their skills.

Uploaded by

pratham2211ps
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
19 views4 pages

Advanced SQL Queries for Class 12

This document provides a comprehensive guide to advanced SQL queries, including various types of joins, subqueries, set operations, and complex conditions. It includes example tables and SQL statements for practical understanding and exam preparation. Students are encouraged to practice these queries thoroughly to enhance their skills.

Uploaded by

pratham2211ps
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

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 [Link], [Link], [Link], [Link]
FROM Students S
JOIN Subjects Sub ON [Link] = Sub.Student_ID
JOIN Scores Sc ON [Link] = Sc.Student_ID
WHERE [Link] > 60;

2. LEFT JOIN Example


SELECT [Link], [Link]
FROM Students S
LEFT JOIN Subjects Sub ON [Link] = Sub.Student_ID
ORDER BY [Link];

3. RIGHT JOIN Example


SELECT [Link], [Link]
FROM Students S
RIGHT JOIN Scores Sc ON [Link] = Sc.Student_ID
WHERE [Link] > 50;

4. FULL OUTER JOIN Simulation


SELECT [Link], [Link]
FROM Students S
LEFT JOIN Scores Sc ON [Link] = Sc.Student_ID
UNION
SELECT [Link], [Link]
FROM Students S
RIGHT JOIN Scores Sc ON [Link] = Sc.Student_ID;

5. Subquery with EXISTS


SELECT [Link]
FROM Students S
WHERE EXISTS (SELECT * FROM Scores Sc WHERE [Link] = Sc.Student_ID AND [Link] > 80);
6. Correlated Subquery
SELECT [Link], [Link]
FROM Students S
WHERE [Link] > (SELECT AVG([Link]) FROM Scores Sc WHERE Sc.Student_ID = [Link]);

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 [Link], COUNT([Link]) AS TotalStudents
FROM Students S
GROUP BY [Link]
HAVING COUNT([Link]) > 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 [Link], AVG([Link]) AS AvgScore
FROM Students S
JOIN Scores Sc ON [Link] = Sc.Student_ID
GROUP BY [Link];

Common questions

Powered by AI

You can use an UPDATE statement combined with a subquery to conditionally modify records. The query is: UPDATE Students SET Marks = Marks + 5 WHERE ID IN (SELECT Student_ID FROM Scores WHERE Score < 60); This increases the Marks by 5 for students whose scores are less than 60 from the Scores table, validly updating relevant records.

To simulate an INTERSECT operation without direct support, use two IN clauses within the WHERE condition: 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); This results in the names of students who both study Math and have a score greater than 60.

To combine and eliminate duplicates in results, a UNION operation can be employed: SELECT Name FROM Students WHERE City = 'Mumbai' UNION SELECT Name FROM Students WHERE City = 'Pune'; This SQL statement identifies distinct student names from cities Mumbai and Pune, ensuring no repetitions in result sets.

The DELETE operation can be utilized alongside a subquery to precisely remove entries. The SQL query: DELETE FROM Students WHERE ID IN (SELECT Student_ID FROM Scores WHERE Score < 50); determines which student records to delete based on their scores from the Scores table, targeting those with scores below 50.

The CASE statement allows you to conditionally assign grades based on numerical marks. It can be written as: 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; This approach categorizes grades 'A' for marks 80 and above, 'B' for 60 to 79, 'C' for 40 to 59, and 'F' for below 40.

A correlated subquery can compare each student's marks to the average score calculated within the subquery, uniquely tied to that student's ID. The query would look like: SELECT S.Name, S.Marks FROM Students S WHERE S.Marks > (SELECT AVG(Sc.Score) FROM Scores Sc WHERE Sc.Student_ID = S.ID); This clause calculates an average for each student's scores from the Scores table and filters those whose Marks are higher.

A complex WHERE clause allows simultaneous criteria consideration with conditional logic. Example query: SELECT * FROM Students WHERE (Department = 'CS' AND Marks > 70) OR (City = 'Pune' AND Marks < 50); Different conditions are checked such as students in CS with marks over 70 or students located in Pune with marks under 50, offering nuanced data retrieval from the Students table.

The GROUP BY clause can be used along with HAVING to filter aggregated data. The query is: SELECT S.Department, COUNT(S.ID) AS TotalStudents FROM Students S GROUP BY S.Department HAVING COUNT(S.ID) > 1; It groups students by Department, counts them, and then filters out departments with one or fewer students, highlighting categories exceeding this count.

To simulate a FULL OUTER JOIN, utilize a combination of LEFT JOIN and RIGHT JOIN, then use UNION to combine results. The example query: 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; captures the complete dataset where some entries might be absent corresponding scores.

You can use an INNER JOIN across three tables (Students, Subjects, and Scores) to achieve this. The SQL query would be: 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; This ensures only students with marks above 60 are included, joining based on the common Student_ID.

You might also like