Appendices

Appendix A. SQL Syntax Reference

The book's SQL grammar, consolidated: each statement with its clause order, its required and optional parts, and a canonical university example. UPPERCASE marks keywords; lower_snake_case marks identifiers; square brackets mark optional parts. Platform differences are marked (PG) and (MySQL) and tabulated in Appendix I.

Statement categories

Database and schema

CREATE DATABASE university [CHARACTER SET utf8mb4] [COLLATE ...];  -- charset: MySQL
DROP DATABASE [IF EXISTS] university_dev;
CREATE SCHEMA archive;                 -- PG: a namespace; MySQL: synonym for database
SET search_path = archive, public;     -- (PG)

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] course_section (
    section_id    INTEGER     PRIMARY KEY,
    course_id     VARCHAR(8)  NOT NULL REFERENCES course(course_id),
    semester      VARCHAR(6)  [NOT] NULL,
    capacity      SMALLINT    DEFAULT 40,
    instructor_id INTEGER     REFERENCES instructor(instructor_id)
        [ON DELETE CASCADE | RESTRICT | SET NULL | SET DEFAULT],
    grade         CHAR(2),
    CONSTRAINT name CHECK (condition [OR column IS NULL]),
    CONSTRAINT name UNIQUE (col, ...),
    CONSTRAINT name PRIMARY KEY (col, ...),
    CONSTRAINT name FOREIGN KEY (col) REFERENCES table(col) [actions]
);

Generated keys (Chapter 9): PG GENERATED ALWAYS | BY DEFAULT AS IDENTITY or legacy SERIAL; MySQL AUTO_INCREMENT.

ALTER TABLE (one operation per statement)

ALTER TABLE student ADD COLUMN email VARCHAR(120) [UNIQUE];
ALTER TABLE student ALTER COLUMN full_name TYPE VARCHAR(80);   -- (PG)
ALTER TABLE student MODIFY COLUMN full_name VARCHAR(80);       -- (MySQL)
ALTER TABLE student ADD CONSTRAINT name UNIQUE (email);
ALTER TABLE student DROP CONSTRAINT name;                      -- (MySQL: DROP INDEX name for keys)
ALTER TABLE student DROP COLUMN email;
ALTER TABLE student RENAME COLUMN a TO b;

INSERT / UPDATE / DELETE

INSERT INTO table (col, ...) VALUES (val, ...), (val, ...) ...;
INSERT INTO table (col, ...) SELECT ...;
INSERT INTO table (col, ...) VALUES (val, ...)
    ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col;          -- (PG)
    -- (MySQL: ON DUPLICATE KEY UPDATE col = VALUES(col) / new syntax)
INSERT INTO t ... RETURNING col, ...;                           -- (PG)

UPDATE table SET col = expr [, col = expr] [WHERE condition]
    [RETURNING col, ...];                                        -- (PG)

DELETE FROM table [WHERE condition] [RETURNING col, ...];        -- (PG)
TRUNCATE TABLE table;                                            -- no WHERE, FK-guarded

SELECT — clause order (fixed)

WITH [RECURSIVE] name AS (subquery) [, name2 AS (...)]
SELECT [DISTINCT] expr [AS alias], ...
FROM   table [AS alias]
       [JOIN table2 [AS alias] ON condition | USING (col)
          | LEFT | RIGHT | FULL [OUTER] JOIN ... | CROSS JOIN ... | NATURAL JOIN ...]
WHERE  condition
GROUP BY expr, ...
HAVING aggregate_condition
WINDOW name AS (window_spec)
ORDER BY expr [ASC | DESC] [NULLS FIRST | LAST]   -- NULLS: (PG); (MySQL: none)
LIMIT n [OFFSET m]                                  -- (MySQL also: LIMIT m, n)
       [FETCH FIRST n ROWS ONLY]                    -- standard form (PG supports)
FOR UPDATE [OF table] [SKIP LOCKED | NOWAIT];

Conditions: comparisons (= <> < <= > >=), BETWEEN a AND b (inclusive), IN (list | subquery), LIKE 'pat' (% any run, _ one char; ILIKE case-insensitive — PG), IS [NOT] NULL, EXISTS (subquery), col op ANY|ALL (set), logical AND / OR / NOT (AND binds tighter).

Subqueries and CTEs

Scalar (one value, in an expression); derived (in FROM, alias required); correlated (references the outer row); WITH name AS (...) chains; WITH RECURSIVE name AS (seed UNION ALL step [WHERE stop]).

Window functions

func() OVER ([PARTITION BY cols] [ORDER BY cols]
             [ROWS | RANGE BETWEEN frame_start AND frame_end])
-- funcs: ROW_NUMBER, RANK, DENSE_RANK, NTILE(n), LAG(x[, o[, d]]), LEAD(...),
--        aggregates (COUNT, SUM, AVG, MIN, MAX), FIRST_VALUE, LAST_VALUE

Set operations

query UNION [ALL] query;      -- dedup / keep-dup
query INTERSECT [ALL] query;  -- MySQL 8.0.31+
query EXCEPT [ALL] query;     -- MySQL 8.0.31+; Oracle/old: MINUS

Views and materialized views

CREATE [OR REPLACE] VIEW name AS SELECT ... [WITH CHECK OPTION];
CREATE MATERIALIZED VIEW name AS SELECT ...;                 -- (PG)
REFRESH MATERIALIZED VIEW [CONCURRENTLY] name;                -- (PG; needs unique index)
DROP VIEW name [CASCADE];

Indexes

CREATE [UNIQUE] INDEX name ON table (col [ASC | DESC], ...)
    [INCLUDE (col, ...)]                          -- covering (PG)
    [WHERE condition];                            -- partial (PG)
CREATE INDEX name ON table ((expression));        -- functional (MySQL 8.0.13+) / expression (PG)
CREATE INDEX ... USING gin | gist | brin | hash; -- (PG) access methods
DROP INDEX name;

Transactions and locks

BEGIN;                                            -- MySQL: START TRANSACTION [READ ONLY | WRITE]
SAVEPOINT name;  ROLLBACK [TO SAVEPOINT name];  COMMIT;
SET TRANSACTION ISOLATION LEVEL
    READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE;
SELECT ... FOR UPDATE [SKIP LOCKED | NOWAIT];     -- also FOR SHARE
LOCK TABLE name IN mode MODE;                     -- (PG)

Privileges

GRANT  priv [, priv] ON object TO grantee [, PUBLIC] [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] priv ON object FROM grantee [CASCADE | RESTRICT];
-- objects: TABLE t | DATABASE db | ALL TABLES IN SCHEMA s (PG) | db.* (MySQL)
-- privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, CREATE,
--   CONNECT, EXECUTE, TEMPORARY/USAGE, ALL [PRIVILEGES]

Stored programs (shells only — Chapter 22)

CREATE FUNCTION name(args) RETURNS type ... LANGUAGE plpgsql;      -- (PG) body in $$
CREATE PROCEDURE name(IN | OUT | INOUT arg type, ...) ...;         -- (MySQL) DELIMITER-wrapped
CALL name(args);
CREATE TRIGGER name BEFORE | AFTER INSERT | UPDATE | DELETE ON table
    FOR EACH ROW [WHEN (cond)] EXECUTE FUNCTION fn();              -- (PG)
CREATE TRIGGER name BEFORE | AFTER ... ON table FOR EACH ROW BEGIN ... END; -- (MySQL)

Common expressions and idioms