Database Management Systems and SQL
Database Management Systems and SQL tests database concepts, the relational model, keys, normalization, integrity and SQL statements.
Core rules
A DBMS stores and manages data, controls redundancy and provides security and concurrency. A relation is a table with rows (tuples) and columns (attributes). A primary key uniquely identifies a row and cannot be NULL; a candidate key is any minimal unique key; a foreign key references another table's primary key and enforces referential integrity. Normalization reduces redundancy: 1NF removes repeating groups, 2NF removes partial dependencies, 3NF removes transitive dependencies. SQL is divided into DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DQL (SELECT) and DCL (GRANT, REVOKE). SELECT uses WHERE, ORDER BY, GROUP BY, HAVING and aggregate functions such as COUNT, SUM, AVG, MIN and MAX. Joins combine related tables.
Worked example
Table Student(roll, name, marks). Query: SELECT name FROM Student WHERE marks > 60 ORDER BY name; The WHERE clause filters rows before grouping; ORDER BY sorts the selected names alphabetically. If we wanted the number of such students, we would use SELECT COUNT(*) FROM Student WHERE marks > 60.
Traps
WHERE filters rows; HAVING filters groups after GROUP BY. NULL is not equal to zero and needs IS NULL. DELETE removes rows; DROP removes the table structure. A primary key cannot contain duplicate or NULL values. SELECT * returns all columns, which may be unnecessary.