Matura Computer Science: Databases and SQL

Databases and SQL asks you to model data relationally - keys, integrity, normal forms - and to write queries that return exactly the required rows, including grouping and joins.

Core rules

• A relation is a table; the primary key uniquely identifies each row and cannot be NULL; a foreign key references a primary key and enforces referential integrity.
• Normalisation: 1NF - atomic values, no repeating groups; 2NF - no partial dependency on part of a composite key; 3NF - no transitive dependency on a non-key attribute.
• Logical evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
• INNER JOIN keeps only matching pairs; LEFT JOIN keeps every left row and fills unmatched right columns with NULL.
• NULL means unknown: NULL = NULL is not TRUE, so use IS NULL. Aggregates ignore NULL, except COUNT(*).

Worked example

Tables Student(id, name, class_id) and Mark(id, student_id, grade). Average grade per class, only classes above 3.5:

SELECT class_id, AVG(grade) AS avg_grade
FROM Student JOIN Mark ON Student.id = Mark.student_id
GROUP BY class_id
HAVING AVG(grade) > 3.5;

Reasoning: join first to attach marks to classes, then group. WHERE runs before grouping, so it cannot filter an aggregate - that is what HAVING is for. Selecting name here would be invalid because it is neither grouped nor aggregated.

Traps

• Putting AVG(grade) > 3.5 in WHERE.
• Omitting the join condition and getting a Cartesian product.
• Confusing DELETE (rows, optional WHERE), TRUNCATE (all rows, no WHERE) and DROP (removes the table).
• COUNT(column) skips NULLs while COUNT(*) counts rows.
• Writing WHERE grade = NULL instead of IS NULL.