EXIN SQL Foundation cheat sheet

SQL is a declarative, set-based language: you state the result you want, not the algorithm; the DBMS optimizer chooses the execution plan.

The relational model stores data in tables (relations) of rows (tuples) and columns (attributes); a primary key uniquely identifies each row and cannot contain NULL.

Logical processing order of a SELECT statement: FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then DISTINCT, then ORDER BY, then LIMIT/OFFSET.

Because of that order, column aliases defined in SELECT cannot be referenced in WHERE, but they can be used in ORDER BY (in most dialects).

SQL statement families: DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE, MERGE), DQL (SELECT), DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK, SAVEPOINT).

Three-valued logic: comparisons with NULL yield UNKNOWN, and WHERE keeps only rows where the condition is TRUE; UNKNOWN and FALSE rows are both discarded.

NULL is not equal to anything, including itself; test it with IS NULL or IS NOT NULL, never with = NULL.

A NULL in an aggregate is ignored by COUNT(col), SUM, AVG, MIN and MAX; COUNT(*) counts all rows regardless of NULLs.

A transaction is atomic and ends with COMMIT (made permanent) or ROLLBACK (undone); most DBMSs run in autocommit mode until an explicit transaction begins.

CREATE TABLE defines columns with a data type plus optional constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY ... REFERENCES, CHECK, DEFAULT.

A FOREIGN KEY must match a value existing in the referenced primary or unique key, or be NULL; it enforces referential integrity and blocks deleting a referenced parent row.

ALTER TABLE adds, drops or modifies columns and constraints (ADD COLUMN, DROP COLUMN, ADD CONSTRAINT, DROP CONSTRAINT); syntax details vary between products.

DROP TABLE removes the table and its data permanently; TRUNCATE TABLE removes all rows quickly but keeps the table structure; DELETE removes rows and can be rolled back.

Character types: CHAR(n) is fixed length and padded with spaces; VARCHAR(n) is variable length up to n; VARCHAR typically stores only the characters used.

Exact numeric types: INTEGER for whole numbers, DECIMAL(p,s)/NUMERIC(p,s) for exact fixed-point values; FLOAT and REAL are approximate and unsuitable for money.

DATE stores a calendar date, TIME stores a time of day, TIMESTAMP stores date plus time and may carry fractional seconds or time zone information.

A CHECK constraint restricts allowed column values and is violated only when the condition evaluates to FALSE, not when it is UNKNOWN due to NULL.

A view is a stored query used as a virtual table; CREATE VIEW defines it, CREATE OR REPLACE VIEW redefines it, and DROP VIEW removes the definition only.

INSERT INTO t (cols) VALUES (...); omitting a column inserts its DEFAULT or NULL, and INSERT INTO t SELECT ... copies rows from a query.

UPDATE t SET col = value WHERE condition; without WHERE every row is updated. DELETE FROM t WHERE condition; without WHERE every row is deleted.

WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY and may contain aggregate functions, which WHERE may not.

Aggregate functions: COUNT, SUM, AVG, MIN, MAX. With GROUP BY, every selected non-aggregated column must appear in the GROUP BY list.

JOIN types: INNER returns matching pairs only; LEFT returns all left rows with NULLs for unmatched right; RIGHT mirrors it; FULL returns unmatched rows from both sides.

A self join joins a table to itself using two aliases, e.g. to compare rows within the same table such as employee to manager.

ORDER BY sorts the final result and defaults to ascending (ASC); DESC reverses it; multiple keys are applied left to right.

DISTINCT removes duplicate rows from the result set and applies to the whole selected row, not to a single column.

LIKE uses % for any sequence of characters and _ for exactly one character; patterns are case-sensitive in some DBMSs and case-insensitive in others.

A subquery in WHERE can return a single value, a list for IN, or an existence test with EXISTS; a correlated subquery references columns of the outer query and runs per outer row.

IN is equivalent to = ANY; NOT IN returns no rows if the list contains NULL, so NOT EXISTS is the safe alternative.

A scalar subquery in SELECT or WHERE must return at most one row and one column, otherwise a runtime error occurs.

UNION combines result sets and removes duplicates; UNION ALL keeps duplicates and is faster; both require the same number of columns with compatible types.

INTERSECT returns rows present in both queries; EXCEPT (MINUS in Oracle) returns rows in the first query not in the second.

A CASE expression returns a value: CASE WHEN cond THEN result ... ELSE result END; it can be used in SELECT, WHERE, ORDER BY and inside aggregates.

A window function such as ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) computes per-row values without collapsing rows, unlike GROUP BY.

A view can hide complexity or restrict columns; an updatable view generally requires no DISTINCT, no aggregates and no GROUP BY, and a WITH CHECK OPTION blocks rows failing the view's WHERE.

An index speeds up lookups and joins on its columns but slows INSERT, UPDATE and DELETE; a unique index also enforces uniqueness.