Step 3 / Select topic SQL Hard

What this bank covers

NOT IN against a list containing NULL
COUNT(DISTINCT col) and NULLs
Inequality filters drop NULL rows
Anti-join via IS NULL on the preserved side
GROUP BY treats all NULLs as one group
IS DISTINCT FROM as NULL-safe inequality
AVG ignores NULL inputs
IN lists containing NULL
NOT applied to an UNKNOWN comparison
UNION duplicate removal treats NULLs as equal
Inner join row multiplication
Predicate in the ON clause of a LEFT JOIN
Self-join producing unordered pairs
Non-equi (range) join
Anti-join with NOT EXISTS
Fan-out inflating an aggregate
Chained inner joins across three tables
FULL OUTER JOIN row count with duplicate keys
Filter in ON versus WHERE for an outer join
SUM over an empty row set
Counting groups when NULLs are present
HAVING filters groups after aggregation
COUNT(*) versus COUNT(column)
DISTINCT inside an aggregate
Aggregating the results of a GROUP BY
AVG ignores NULL values
HAVING without GROUP BY
MIN when every value in the group is NULL
SUM of values that cancel out
Scalar subquery inside an expression
Correlated subquery per group
EXISTS as a semi-join
Subquery in FROM (derived table)
Two scalar subqueries in one expression
Correlated subquery returning NULL
ALL with a NULL in the subquery
ANY over an empty subquery
NOT IN with a NULL in the list
IN behaves like a semi-join
UNION removes duplicate rows
UNION ALL keeps every row
INTERSECT returns distinct common rows
EXCEPT returns distinct unmatched rows
EXCEPT ALL subtracts multiplicities
Set operations treat NULLs as equal
INTERSECT binds tighter than UNION
INTERSECT ALL keeps the minimum multiplicity
NULL matches NULL in INTERSECT
UNION matches columns by position
Multi-key ORDER BY with OFFSET
ORDER BY ordinal position
NULLS FIRST in ORDER BY
Text ordering is not numeric ordering
Ordering groups with a tie-break key
ORDER BY an expression not in the select list
ORDER BY applies to the whole set operation
Ordering by a CASE expression
OFFSET with a multi-column ORDER BY
RANK leaves gaps after ties
The default window frame includes all peers
WHERE runs before window functions
LAG shifts values down and loses the last one
NTILE puts the extra rows in the earliest buckets
Picking one row per partition with ROW_NUMBER
LAST_VALUE with the default frame returns the current row
DENSE_RANK produces no gaps
A centred sliding ROWS frame
Ranking aggregated groups
Filtering a CTE of group totals
One CTE reading another
Referencing a CTE twice
Recursive CTE generating a series
Recursive descent through a parent/child table
UNION in a recursive CTE removes duplicates
Using a CTE to strip NULLs before NOT IN
Aggregating, filtering, then aggregating again
A CTE sharing a name with a base table
Accumulating a total with a recursive CTE
UPDATE with a correlated scalar subquery
DELETE driven by an anti-join
UPDATE with a WHERE v = NULL predicate
INSERT ... SELECT with filters and DISTINCT
UPDATE with a searched CASE expression
Two sequential UPDATE statements
DELETE using NOT IN over a NULL-bearing subquery
Simultaneous assignment in one UPDATE
UPDATE from an unmatched scalar subquery
DELETE compared against the table's own average
UNIQUE constraints and NULL
CHECK constraints evaluate to UNKNOWN for NULL
Referential action ON DELETE CASCADE
Referential action ON DELETE SET NULL
Referential action ON UPDATE CASCADE
PRIMARY KEY implies NOT NULL
Cascading deletes down a chain of foreign keys
DEFAULT satisfies NOT NULL
Composite UNIQUE keys with NULL parts
Multi-column CHECK constraints and NULL
LEFT JOIN with a disjunctive ON predicate
ORDER BY resolves output column names first