Data Definition and Data Types
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 changes the schema, not individual row data. 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 has true, false and unknown. 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, often auto-committing).
- Forgetting that VARCHAR(n) limits characters, not bytes, in standard SQL.
- Adding a NOT NULL column to a populated table without a default.