SQU IT Exit: Database Systems and SQL

What this domain tests

Database Systems and SQL tests the relational model, keys, normalization, and whether you can write and debug queries. Expect joins, aggregation, NULL logic, constraints, and transactions.

Core rules and concepts

• A primary key is unique and not NULL; a foreign key references a candidate key and enforces referential integrity.
• 1NF removes repeating groups; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies.
• Logical clause order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. WHERE filters rows before grouping; HAVING filters groups after aggregation.
• INNER JOIN keeps matches; LEFT JOIN keeps all left rows and fills unmatched right columns with NULL.
• SQL uses three-valued logic: NULL = NULL is UNKNOWN, so test with IS NULL.
• ACID: atomicity, consistency, isolation, durability.

Worked example

Tables Student(id, name) and Enroll(student_id, course). To list students enrolled in more than one course: SELECT s.name FROM Student s JOIN Enroll e ON s.id=e.student_id GROUP BY s.id, s.name HAVING COUNT(*) > 1; The join pairs each enrollment with its student; GROUP BY collapses rows per student; COUNT() counts enrollment rows; HAVING removes groups of size one. Filtering with WHERE COUNT() would fail because WHERE runs before grouping.

Traps that cost marks

Using WHERE instead of HAVING. Assuming COUNT(column) counts NULLs; it does not, while COUNT(*) counts rows. Writing WHERE age = NULL. Omitting non-aggregated columns from GROUP BY. Forgetting that a missing join condition creates a Cartesian product.