Step 3 / Select topic SQL Easy

What this bank covers

NULL = NULL is unknown
Counting NULLs with IS NULL
IS NOT NULL excludes NULL rows
COALESCE makes NULLs comparable
count(column) skips NULLs
NOT IN with a NULL in the list
Comparing to NULL never matches
Adding IS NULL to an inequality
IS NULL after a LEFT JOIN
NOT IN with a NULL in the subquery
Inner join keeps only matching pairs
LEFT JOIN preserves every left row
CROSS JOIN multiplies row counts
FULL OUTER JOIN keeps unmatched rows from both sides
Self join on a manager column
count on the right-hand column of a LEFT JOIN
Aggregating over a LEFT JOIN
RIGHT JOIN keeps unmatched right rows
NULL columns from an unmatched LEFT JOIN row
Joining on a non-equality condition
SUM inside a group
count(DISTINCT col)
HAVING filters groups
SUM ignores NULL
MIN per group
SUM over no rows
count(*) per group
GROUP BY and NULL keys
AVG skips NULL rows
HAVING on an aggregate
Scalar subquery in WHERE
IN with a subquery list
Correlated EXISTS subquery
Subquery in FROM (derived table)
Comparison with ALL
NOT EXISTS for unmatched rows
Scalar subquery in the SELECT list
Comparison with ANY
Correlated subquery per group
UNION removes duplicates
UNION ALL keeps every row
INTERSECT returns distinct common values
EXCEPT removes values found in the second query
EXCEPT is not symmetric
UNION treats NULLs as duplicates
INTERSECT ALL keeps the smaller multiplicity
EXCEPT ALL subtracts row counts
Summing a UNION result
INTERSECT binds tighter than UNION
ORDER BY defaults to ascending
ORDER BY DESC picks the largest first
Sorting text values ascending
ORDER BY with a column position
Multiple ORDER BY keys break ties
ORDER BY can reference a select alias
OFFSET skips rows after ordering
Ordering grouped rows by an aggregate
Descending text ordering
Ordering by an expression
ROW_NUMBER picks a position
RANK leaves gaps after ties
An empty OVER () clause spans every row
ORDER BY inside OVER makes a running total
LAG looks one row back
PARTITION BY limits the window
DENSE_RANK with ties
LEAD past the last row
FIRST_VALUE within a partition
Default window frame with ORDER BY
A CTE as a named subquery
Naming CTE columns
Chaining CTEs
Joining a table to a CTE
Recursive CTE generating a series
Filtering an aggregate through a CTE
Counting rows kept by a CTE
Referencing one CTE more than once
Rows produced by a recursive CTE
A CTE only applies where it is used
UPDATE with a WHERE filter
DELETE and NULL comparisons
Omitted columns, defaults and count()
Setting a column to NULL
DELETE with no WHERE clause
UPDATE that matches no rows
INSERT ... SELECT with a filter
UPDATE changing a text column
DELETE driven by a subquery
Two UPDATE statements in sequence
DEFAULT fills an omitted NOT NULL column
Composite primary keys constrain the column pair
A foreign key column may hold NULL
Self-referencing foreign keys need a NULL root
ON UPDATE CASCADE rewrites child keys
CHECK constraints accept UNKNOWN results
UNIQUE permits repeated NULLs
NOT NULL rejects NULL, not 0
ON DELETE CASCADE removes matching children
ON DELETE SET NULL keeps the child rows