Databases and Data Handling
Databases and Data Handling tests the relational model, keys and constraints, and simple SQL. Typical questions ask you to identify a primary key, explain referential integrity, or write a query using WHERE, ORDER BY and an aggregate function.
Relational vocabulary
A relation is a table, a tuple is a row, an attribute is a column, and the domain is the set of allowed values for a column. A candidate key uniquely identifies a row; one candidate key is chosen as the primary key, which can be neither NULL nor duplicated. A foreign key is an attribute referring to a primary key in another table and it enforces referential integrity.
SQL statement groups
DDL defines structure: CREATE, ALTER, DROP, TRUNCATE. DML changes data: INSERT, UPDATE, DELETE. Retrieval uses SELECT. DCL controls privileges: GRANT and REVOKE. Aggregate functions are COUNT, SUM, AVG, MIN and MAX.
Worked example. Table Student(roll, name, marks). Query: SELECT name FROM Student WHERE marks > 60 ORDER BY marks DESC; Logical order is FROM, then WHERE, then SELECT, then ORDER BY. Rows scoring 60 or less are discarded first, only the name column is projected, and rows are sorted by marks, highest first, so marks appears in the sort but not in the output.
Traps. Compare NULL with IS NULL, never with an equals sign. WHERE filters rows before grouping, HAVING filters groups after. DROP removes the table structure while DELETE removes rows only. COUNT(*) counts all rows; COUNT(column) skips NULLs.