Step 3 / Select topic SQL Medium

What this bank covers

Inequality skips NULL rows
= NULL never matches
NOT IN with a NULL in the list
NOT EXISTS is NULL-safe
COALESCE brings NULL rows back
count(col) ignores NULLs
UNKNOWN OR TRUE is TRUE
IS NULL after a LEFT JOIN
NULLIF creates extra NULLs
NOT IN over a column containing NULL
Inner join row count
Left join with duplicate matches
Chained joins multiply matches
Cross join cardinality
Right join with an aggregate
WHERE on the outer side of a left join
Filter in ON versus WHERE
Self-join to find a manager
count(*) over a left join
NULLs never satisfy an equality join
COUNT(*) vs COUNT(column)
HAVING filters groups
AVG ignores NULL
Ordering groups by an aggregate
GROUP BY collects NULLs into one group
COUNT DISTINCT vs plain COUNT
Aggregates over an empty result set
WHERE runs before grouping
Grouping by several columns
Aggregate without GROUP BY on an empty table
Scalar subquery in WHERE
Finding the row that holds an aggregate
NOT IN with a NULL in the subquery
Correlated NOT EXISTS
Subquery in FROM (derived table)
Scalar subquery over an empty table
Second-highest value with a scalar subquery
Correlated EXISTS counts matching customers, not matching orders
> ANY compares against the smallest subquery value
Comparing rows to an aggregate computed by a subquery
UNION removes duplicates
UNION ALL keeps duplicates
INTERSECT returns distinct common values
EXCEPT drops matching values
INTERSECT binds tighter than UNION
UNION treats NULLs as duplicates
EXCEPT is not symmetric
Aggregating an EXCEPT result
Summing a deduplicated UNION
Empty INTERSECT result
ORDER BY with two sort keys
ORDER BY by column position
ORDER BY a computed expression
OFFSET applied after sorting
Lexicographic ordering of numeric text
DESC applies only to its own sort key
Ordering by a derived-table column
Custom sort order with CASE
ORDER BY sorts the whole set operation
ROW_NUMBER with ORDER BY
RANK with tied values
SUM OVER PARTITION BY
LAG with an offset
FIRST_VALUE inside a partition
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
DENSE_RANK with tied values
LEAD offset and default value
Default window frame with peer rows
AVG over a partition containing NULL
Counting rows in a simple CTE
Referencing one CTE more than once
Chaining CTEs
Recursive CTE row generation
Where a recursive CTE stops
Walking a hierarchy with a recursive CTE
Joining a base table to a single-row CTE
Combining two CTEs with UNION
Carrying an accumulator through a recursive CTE
A CTE name hides a real table
UPDATE then re-filter the changed rows
INSERT ... SELECT with a filter
UPDATE with CASE to clamp values
UPDATE from a correlated COUNT
DELETE filtered by LIKE
Partial column list leaves a NULL
DELETE driven by a subquery
UPDATE with a NULL operand
NOT IN against a NULL-containing subquery
Two UPDATE statements in sequence
UNIQUE and NULL values
ON DELETE CASCADE
ON DELETE SET NULL
CHECK constraints and NULL
Composite primary keys
PRIMARY KEY implies NOT NULL
ON UPDATE CASCADE
Multi-column UNIQUE constraints
Nullable foreign keys
ON DELETE CASCADE and referencing rows