Database Concepts and SQL
What this domain tests
Database Concepts and SQL tests the relational model, keys, integrity, normalisation and query writing. Expect SELECT queries with filtering, grouping, ordering and joins.
Core rules
- A relation (table) has rows (tuples) and columns (attributes). A primary key uniquely identifies a row and cannot be NULL; a foreign key references a primary key in another table.
- Candidate key: minimal unique identifier; alternate keys are candidates not chosen as primary.
- SQL categories: DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DQL (SELECT), DCL (GRANT, REVOKE).
- Query logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters rows before grouping; HAVING filters groups after aggregation.
- Aggregate functions: COUNT, SUM, AVG, MIN, MAX. NULL is not equal to anything; use IS NULL.
Worked example
SELECT class, COUNT(*) AS n
FROM Student
WHERE marks > 50
GROUP BY class
HAVING COUNT(*) > 2
ORDER BY n DESC;
This keeps students with marks above 50, groups them by class, keeps only classes with more than two such students, and sorts by count descending. Reasoning: WHERE removes individual rows; GROUP BY forms groups; HAVING tests each group; ORDER BY sorts the result.
Traps
Do not put aggregate conditions in WHERE. COUNT(*) counts rows; COUNT(column) ignores NULLs. String literals need single quotes. A foreign key may allow NULL unless declared NOT NULL. Do not assume SELECT lists columns in table order.