- 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.
- Oracle Database – Tables, Constraints and Sequences
A CREATE TABLE worth copying, and the five constraint types. Why an unindexed foreign key locks the child table, why composite UNIQUE lets partly-null duplicates through, and the ON UPDATE CASCADE Oracle does not have. Identity columns versus sequences, CACHE and the gaps it creates, and how to drop a column on a large table.
- Oracle Database – Data Types
VARCHAR2 versus CHAR and why the BYTE/CHAR length qualifier matters in UTF-8. NUMBER precision and why money never goes in a binary float. The fact that DATE includes a time, and the range comparison that follows from it. Booleans before 23ai, the empty string that is NULL, and a full mapping table to Java types.
- Oracle Database – Users, Schemas and Privileges
In Oracle a user is a schema, so CREATE USER is how you create a namespace. Setting up a proper application account: why a tablespace quota is not optional, what CONNECT and RESOURCE really grant, the owner/role/service-account split, three ways to stop qualifying table names, and the password expiry that locks out service accounts.