Database Management Systems and SQL
What this domain tests
Relational database concepts and SQL: tables, rows (tuples), columns (attributes), keys, integrity constraints, and the DDL/DML commands used to define and manipulate data. Expect small queries written against a given table schema.
Core rules
- Primary key: unique and NOT NULL, one per table. Candidate keys are all minimal unique identifiers; alternate keys are candidates not chosen. A foreign key references a primary key in another table and enforces referential integrity.
- DDL: CREATE, ALTER, DROP, TRUNCATE. DML: INSERT, UPDATE, DELETE, SELECT. TCL: COMMIT, ROLLBACK, SAVEPOINT. DCL: GRANT, REVOKE.
- Syntax: SELECT columns FROM table WHERE condition ORDER BY column [ASC|DESC];
- Aggregate functions: COUNT, SUM, AVG, MIN, MAX. WHERE filters rows before grouping; HAVING filters groups after GROUP BY.
- DISTINCT removes duplicate rows. NULL is not equal to anything, so use IS NULL and IS NOT NULL.
Worked example
Table student(roll, name, marks, dept). Query: SELECT dept, AVG(marks) FROM student WHERE marks >= 40 GROUP BY dept HAVING COUNT(*) > 2; Rows below 40 are removed first; remaining rows are grouped by dept; groups with more than two students are kept; the average is shown per department.
Traps that cost marks
Using = NULL instead of IS NULL; putting an aggregate in WHERE instead of HAVING; forgetting quotes around text values; selecting a non-grouped column; confusing DELETE (removes rows, can be rolled back) with DROP (removes the table structure); assuming COUNT(column) counts NULLs - it does not.