Level 3 - Question 3: UNION and INTERSECT Operations

Scenario

A university maintains separate tables for current students and alumni. They want to find students who are in both lists (perhaps due to data duplication).

Task

Write a query using INTERSECT to find student IDs that appear in both the current_students and alumni tables.

Sample Schema: `current_students`

student_id	name
1001	Alice Brown
1002	Bob Green
1003	Carol White

Sample Schema: `alumni`

student_id	name
1002	Bob Green
1004	David Black
1005	Emma Davis

Solution:

SELECT student_id
FROM current_students
INTERSECT
SELECT student_id
FROM alumni;

The INTERSECT operator returns only the student IDs that exist in both tables. In this case, student_id 1002 (Bob Green) appears in both tables.

Bonus: You could also use UNION to combine all unique students from both tables, or MINUS to find students only in one table.

Previous Question Next Question