Chapter 3.3 Lock-in

B · Merged rule card

3.3.1 SQL DDL: CREATE TABLE, data types, constraints, ON DELETE/ON UPDATE

• CREATE TABLE with types (INT, DECIMAL(p,s), CHAR(n), VARCHAR(n), DATE …).
• PRIMARY KEY = UNIQUE + NOT NULL, one per table; UNIQUE allows many NULLs.
• FOREIGN KEY … REFERENCES … ON DELETE/UPDATE CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION (default).
• CHECK rejects only FALSE; DEFAULT fills omitted columns.
• ALTER TABLE ADD/DROP column/constraint; DROP TABLE removes schema + rows.

3.3.2 SQL DML: INSERT, UPDATE, DELETE

• INSERT INTO T [(cols)] VALUES (…) | SELECT …; missing columns get DEFAULT/NULL.
• UPDATE T SET … [WHERE …]: right-hand sides use old values; all qualifying rows change together.
• DELETE FROM T [WHERE …]: rows only; FK actions fire.
• No WHERE = all rows. DROP removes schema; DELETE does not.

3.3.3 SELECT–FROM–WHERE, DISTINCT, aliases, LIKE, BETWEEN, ORDER BY; bags vs sets

• SELECT [DISTINCT] list FROM tables WHERE cond ORDER BY … ≈ π(σ(×)) with bags.
• Order: FROM → WHERE → SELECT → DISTINCT → ORDER BY.
• SQL keeps duplicates; DISTINCT removes them.
• BETWEEN inclusive; IN list; LIKE with % (any, incl. empty) and _ (one char).
• Aliases needed for self-joins; ORDER BY ASC default, applied last.

3.3.4 NULL in SQL: three-valued logic, IS NULL, comparisons

• AND: F dominates; OR: T dominates; else U. NOT U = U.
• WHERE/HAVING keep only TRUE; CHECK rejects only FALSE.
• Test NULL with IS NULL / IS NOT NULL; = NULL is always UNKNOWN.
• x NOT IN (…, NULL) is never TRUE.
• DISTINCT/GROUP BY treat NULLs as one value; ORDER BY NULL position is system-dependent.

3.3.5 Aggregates, GROUP BY, HAVING; COUNT(*) vs COUNT(col); NULL handling

• COUNT(*) counts rows; COUNT(col), SUM, AVG, MIN, MAX ignore NULL; AVG = SUM/COUNT(col).
• Empty input: COUNT 0, others NULL; no GROUP BY → exactly one row.
• GROUP BY: one row per group; SELECT only grouping columns + aggregates; NULLs form one group.
• WHERE filters rows (no aggregates); HAVING filters groups.
• Order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY.

3.3.6 Joins in SQL: INNER, NATURAL, LEFT/RIGHT/FULL OUTER

• JOIN … ON (inner), USING (cols), NATURAL JOIN (all common names), LEFT/RIGHT/FULL OUTER JOIN, CROSS JOIN.
• ON conditions decide matching (outer rows still kept); WHERE filters afterwards (drops padded rows).
• After LEFT JOIN, count with COUNT(right.col).
• "No match": LEFT JOIN … WHERE right.key IS NULL.

3.3.7 UNION / INTERSECT / EXCEPT and their ALL variants

• UNION / INTERSECT / EXCEPT: set semantics (duplicates removed); need union compatibility.
• ALL: UNION m + n; INTERSECT min(m, n); EXCEPT max(0, m − n).
• Set operators treat NULL rows as equal to each other.
• Oracle's MINUS = EXCEPT.

3.3.8 Nested subqueries: IN, ANY/SOME, ALL, EXISTS, scalar subqueries

• IN: equals some value; NOT IN: differs from all, and no NULL present.
• op ANY: true for some value (empty → FALSE); op ALL: true for every value (empty → TRUE).
• > ANY ≈ > MIN; > ALL ≈ > MAX; = ANY ≡ IN; <> ALL ≡ NOT IN.
• NULL in the subquery: NOT IN and ALL can never be TRUE.
• EXISTS: at least one row; never UNKNOWN.
• Scalar subquery: one value; many rows → error; none → NULL.

3.3.9 Correlated subqueries; for-all in SQL; tracing a query on a table instance

• Correlated subquery references an outer column; evaluated per outer row.
• Some: EXISTS; none: NOT EXISTS; all: NOT EXISTS (… AND NOT EXISTS (…)).
• COUNT alternative: HAVING COUNT(DISTINCT x) = (SELECT COUNT(*) …); differs when the "all" set is empty.
• Trace with a table: outer row | inner result | condition | kept.
• NULL rows: comparisons inside COUNT give 0 matches.

3.3.10 Views and WITH

• View = stored query (virtual table); always current; used for security, simplicity, logical independence.
• Materialised view = stored rows, needs refresh.
• Updatable views: one table, no aggregates/GROUP BY/DISTINCT, key + required columns present.
• WITH CHECK OPTION: changes must keep rows visible.
• WITH name AS (…): temporary named result for one statement.