Module 6: Subqueries and set operations

Put a query inside a query, test for existence, and combine result sets with UNION, INTERSECT and EXCEPT.

1. Scalar and IN subqueries: A scalar subquery (SELECT AVG(…) …) is one value (NULL if empty, error if many); use IN (SELECT …) for a list.
2. Correlated subqueries, EXISTS and the NOT IN trap: A correlated subquery uses the outer row; use NOT EXISTS for "never", because NOT IN returns nothing if the list has a NULL.
3. Subqueries in FROM: derived tables: A subquery in FROM (with an alias) is a temporary table: summarise inside, then filter, join or aggregate outside.
4. UNION, INTERSECT and EXCEPT: UNION stacks and de-duplicates (UNION ALL keeps duplicates); INTERSECT = in both, EXCEPT = in first only; one ORDER BY at the end.

Ends with a 12-question checkpoint (80% passes) and a project: The merit scholarship shortlist.