Database Management Systems and SQL
Database Management Systems and SQL tests whether you can design a small relational schema and write correct queries against it. Typical questions cover keys, integrity constraints, normalization up to 3NF, SQL DDL and DML statements, joins and aggregate queries.
Core rules
- A relation is a table; rows are tuples, columns are attributes, and each column draws values from a domain.
- Primary key: unique and never NULL, one per table. A candidate key is any minimal unique column set; alternate keys are candidates not chosen. A foreign key references another table's primary key and enforces referential integrity.
- DDL defines structure (CREATE, ALTER, DROP); DML changes data (INSERT, UPDATE, DELETE); DQL retrieves it (SELECT).
- Logical order of a SELECT: 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) ignore NULLs, while COUNT(*) counts rows including those with NULLs.
- 1NF: atomic values. 2NF: 1NF plus no partial dependency on part of a composite key. 3NF: 2NF plus no transitive dependency.
Worked example
Table Student(roll, name, class, marks). Query: SELECT class, AVG(marks) FROM Student WHERE marks > 40 GROUP BY class HAVING COUNT(*) >= 2;
- WHERE removes students scoring 40 or less.
- Remaining rows are grouped by class.
- HAVING keeps only classes with at least two remaining students.
- AVG(marks) is computed per surviving group.
Traps
- Writing marks = NULL; SQL needs marks IS NULL.
- Using HAVING as a row filter before grouping.
- Confusing DELETE (removes rows), DROP (removes the table) and TRUNCATE (clears all rows, no WHERE).
- Selecting a non-aggregated column that is absent from GROUP BY.