SQL Fundamentals
What the domain covers
The SQL Fundamentals domain of the EXIN SQL Foundation exam tests the vocabulary and mental model behind every query: 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 table is a set of rows; 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.