- Oracle Database – PIVOT and UNPIVOT
Rows into columns and back again. The implicit GROUP BY over every column you did not mention — the one fact behind almost every surprising PIVOT result — plus why values missing from the IN list vanish silently, why an empty cell is NULL from sum but 0 from count, the three errors you get for a non-constant IN list and what to do instead, and UNPIVOT's habit of dropping NULLs by default.
- Oracle Database – Analytic Functions
Window functions are the feature that most changes how you write SQL. ROW_NUMBER, RANK and DENSE_RANK compared on ties, top-N per group, deduplication by ROWID, LAG and LEAD, running totals — including the ROWS versus RANGE default that quietly gives wrong numbers — the LAST_VALUE frame trap, KEEP DENSE_RANK, and gaps and islands.
- Oracle Database – CROSS APPLY and OUTER APPLY
An inline view that can see the row it is joined to, evaluated once per outer row. The four spellings and why CROSS APPLY silently drops rows where OUTER APPLY keeps them. Top-N per group and exactly when it beats ROW_NUMBER, several aggregates in one pass, per-row table functions, and how to read Starts and WINDOW NOSORT STOPKEY in the plan.
- Oracle Database – Joins
ANSI joins, the legacy (+) operator you will meet in every pre-2005 codebase and why missing one silently turns an outer join into an inner one, semi-joins with EXISTS, and the NOT IN trap that returns zero rows without an error when the subquery contains a NULL. Plus the correlated join a plain JOIN cannot do, the three join methods Oracle picks between, and how to recognise a bad nested loop in a plan.
- Oracle Database – SELECT Essentials
The querying idioms that are specific to Oracle: DUAL, FETCH FIRST versus the ROWNUM pagination trap, NVL against COALESCE, NULLS FIRST, date format masks where MM and MI are not the same thing, LISTAGG and its 4000-byte overflow, ROLLUP, MERGE as the upsert, and INSERT ALL because Oracle rejects a multi-row VALUES list.