SQL models, types and constraints
What the domain covers
The following shared SQL foundations cover the vocabulary and mental model behind queries: relational tables, rows and columns, candidate/primary/foreign keys, entity and referential integrity, and the SQL statement families. Exam items ask you to classify statements, predict NULL behavior, and explain why a result set has a particular shape or order.
Core rules
- A relation is mathematically a set of tuples, while an SQL table can allow duplicate rows unless constrained; without ORDER BY, the DBMS may return rows in any order.
- DDL: CREATE, ALTER, DROP, TRUNCATE. DML: SELECT, INSERT, UPDATE, DELETE, MERGE. DCL: GRANT, REVOKE. TCL: COMMIT, ROLLBACK, SAVEPOINT.
- Primary key: unique and not null; only one per table, possibly composite. Foreign key: references a candidate key and enforces referential integrity. A foreign key column may be NULL unless separately constrained.
- A candidate key is a minimal unique set of columns; the primary key is the chosen candidate key. A table can have several UNIQUE constraints but only one PRIMARY KEY.
- NULL is unknown, not zero or blank. Comparisons with NULL produce UNKNOWN; test with IS NULL / IS NOT NULL.
- Logical SELECT order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
Worked example
SELECT employee_id, last_name, COALESCE(commission_pct, 0) AS commission FROM employees WHERE department_id = 50 ORDER BY last_name;
Reasoning: FROM identifies employees; WHERE keeps department 50 rows; SELECT evaluates COALESCE, replacing unknown commission with 0; ORDER BY sorts by last_name. Without ORDER BY the row order is unspecified. If department_id were NULL, the WHERE predicate would be UNKNOWN and the row would not be returned.
Traps that cost marks
- Using = NULL instead of IS NULL.
- Expecting alphabetical order without ORDER BY.
- Assuming COALESCE changes stored data: it only changes the result expression.
- Quoting identifiers with single quotes: string literals use single quotes; delimited identifiers use double quotes in standard SQL (backticks in MySQL).
- Treating SELECT * as harmless in production code; it couples queries to column order and adds columns.
- Assuming a primary key is always a single column; composite keys are common.
What the domain covers
This domain tests DDL and the SQL type system: creating and changing tables, choosing character, numeric, temporal, boolean and binary types, and enforcing rules with constraints. Expect items on CREATE TABLE syntax, ALTER TABLE operations, DROP versus TRUNCATE, and why a constraint rejects or accepts a row.
Core rules
- DDL defines or changes schema objects and can also remove or transform data as a consequence. CREATE TABLE defines columns, data types and constraints. ALTER TABLE adds, drops or renames columns and constraints. DROP TABLE removes the table and its data; TRUNCATE removes all rows but keeps the table and is not filterable.
- Character types: CHAR fixed length, VARCHAR variable with a maximum, CLOB for large text. Numeric: exact DECIMAL/NUMERIC(p,s) for money; approximate FLOAT/REAL/DOUBLE for scientific values. Temporal: DATE, TIME, TIMESTAMP, INTERVAL. Boolean expressions use TRUE, FALSE and UNKNOWN in SQL’s three-valued logic; NULL remains the marker underlying unknown comparisons. BLOB stores binary large objects.
- Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT. A PRIMARY KEY implies NOT NULL and UNIQUE. Foreign key actions include NO ACTION, CASCADE, SET NULL, SET DEFAULT and RESTRICT.
- Choose the narrowest type that fits the domain; exact numerics avoid rounding surprises.
Worked example
CREATE TABLE enrollment ( student_id INTEGER NOT NULL, course_id INTEGER NOT NULL, enrolled_on DATE DEFAULT CURRENT_DATE NOT NULL, grade DECIMAL(3,1) CHECK (grade BETWEEN 0 AND 10), PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(student_id) );
Reasoning: the composite primary key allows one enrollment per student/course pair. enrolled_on cannot be NULL and defaults to today. grade is exact one-decimal numeric; the CHECK limits it to 0–10, and NULL passes CHECK because the predicate is UNKNOWN. The foreign key prevents enrollment for a nonexistent student; ON DELETE CASCADE would remove enrollments if a student is deleted.
Traps that cost marks
- Using FLOAT/DOUBLE for currency; use DECIMAL.
- Assuming CHECK rejects NULL; it does not, unless NOT NULL is also present.
- Confusing DELETE (DML, filterable, transactional) with TRUNCATE (all rows, with transactional and identity behavior dependent on the DBMS).
- Forgetting that VARCHAR(n) limits characters, not bytes, in standard SQL.
- Adding a NOT NULL column to a populated table without a default.