This article demonstrates practical methods for querying data across multiple tables using join operations and subqueries. The examples utilize a sample database named XSCJ containing student, sc (student-course), and course tables.

1. Retrieve Student IDs, Names, Course Numbers, and Grades This query fetches all students along with their course enrollments and grades.

SELECT student.sno, sname, cno, grade
FROM student, sc
WHERE student.sno = sc.sno;

2. Find Students Enrolled in 'Database Principles and Applications' This query identifies students taking a specific course.

SELECT DISTINCT student.sno, sname
FROM student, sc, course
WHERE student.sno = sc.sno
  AND sc.cno = course.cno
  AND cname = 'Database Principles and Applications';

3. Using Table Aliases for Clarity Aliases simplify queries, especialy with self-joins or multiple tables.

SELECT s.sno, s.sname, e.cno, e.grade
FROM student AS s, sc AS e
WHERE s.sno = e.sno;

4. Self-Join to Compare Ages A self-join finds students older than a specific individual.

SELECT A.sname, A.ssex, A.sage
FROM student AS A, student AS B
WHERE B.sname = 'Liu Lei'
  AND A.sage > B.sage;

5. Standard INNER JOIN Syntax Modern JOIN syntax explicitly defines the relationship.

SELECT student.sno, sname, cno, grade
FROM student
INNER JOIN sc ON student.sno = sc.sno;

6. LEFT OUTER JOIN to Include All Students This returns all students, even those without course registrations.

SELECT student.sno, sname, cno, grade
FROM student
LEFT OUTER JOIN sc ON student.sno = sc.sno;

7. RIGHT OUTER JOIN for Enrollment-Centric View This focuses on enrollments, including those with missing student data.

SELECT student.sno, cno, grade, sname
FROM student
RIGHT OUTER JOIN sc ON student.sno = sc.sno;

8. RIGHT OUTER JOIN to List All Courses Shows all courses, including those with no enrolled students.

SELECT sc.sno, course.cno, cname, credit
FROM sc
RIGHT OUTER JOIN course ON sc.cno = course.cno;

9. FULL OUTER JOIN for Complete Data Set Combines all records from both tables.

SELECT *
FROM student
FULL OUTER JOIN sc ON student.sno = sc.sno;

10. Retrieve Unique Departments for Students Aged 19 or 20 Using UNION removes duplicate department names.

SELECT DISTINCT sdept FROM student WHERE sage = 19
UNION
SELECT DISTINCT sdept FROM student WHERE sage = 20;

11. Retreive Departments for Students Aged 19 or 20 (With Duplicates) Using UNION ALL preserves duplicate rows.

SELECT sdept FROM student WHERE sage = 19
UNION ALL
SELECT sdept FROM student WHERE sage = 20;
-- Alternative using IN
SELECT sdept FROM student WHERE sage IN (19, 20);

12. Subquery to Find Enrolled Students Identifies students present in the enrollment table.

SELECT sno, sname
FROM student
WHERE sno IN (SELECT sno FROM sc);

13. Equivalent Join Query for Enrolled Students The same result achieved with a JOIN.

SELECT DISTINCT student.sno, sname
FROM student
INNER JOIN sc ON student.sno = sc.sno;

14. Subquery with Aggregate Function Finds students older than the average age.

SELECT sno, sname, sage
FROM student
WHERE sage > (SELECT AVG(sage) FROM student);

15. Subquery with ANY Operator Finds students older than any student in the CS department.

SELECT sname, sage
FROM student
WHERE sage > ANY (SELECT sage FROM student WHERE sdept = 'CS')
  AND sdept != 'CS';

16. EXISTS to Find Courses with Enrollments Returns courses that have at least one student registered.

SELECT *
FROM course
WHERE EXISTS (SELECT * FROM sc WHERE course.cno = sc.cno);

17. NOT EXISTS to Find Courses Without Enrollments Returns courses that have no students registered.

SELECT *
FROM course
WHERE NOT EXISTS (SELECT * FROM sc WHERE course.cno = sc.cno);

18. Create a New Table from a Query Result Generates a temporary table containing CS department students.

SELECT * INTO temp
FROM student
WHERE sdept = 'CS';
SELECT * FROM temp;

19. Insert Data into a New Table via SELECT Populates a new table with a Cartesian product of student and course IDs.

INSERT INTO SC1(sno, cno)
SELECT sno, cno FROM student, course;

20. Update Grades Using a Subquery Increases grades by 5 points for students in a specific course.

UPDATE sc
SET grade = grade + 5
WHERE cno = (
    SELECT cno
    FROM course
    WHERE sc.cno = course.cno
      AND cname = 'Frontend Page Design'
);

21. Delete Records Using a Correlated Subquery Removes enrollment records for a specific course.

DELETE FROM sc
WHERE cno = (
    SELECT cno
    FROM course
    WHERE sc.cno = course.cno
      AND cname = 'Frontend Page Design'
);