Database Concepts and SQL: Keys, DDL and Query Logic
What this domain tests
Database concepts and SQL test the relational vocabulary, the difference between key types, DDL versus DML, and your ability to read or write a short query with WHERE, ORDER BY and aggregates.
Concepts and keys
A database is an organised collection of related data; a DBMS is the software that creates and manages it, such as MySQL. In the relational model a table is a relation, a row is a tuple or record, and a column is an attribute or field. A primary key uniquely identifies each row and cannot be NULL; there is one per table. A candidate key is any minimal set of columns that is unique; the ones not chosen become alternate keys. A foreign key references a primary key in another table and enforces referential integrity. A composite key spans two or more columns.
SQL
DDL changes structure: CREATE, ALTER, DROP, TRUNCATE. DML changes or reads data: INSERT, UPDATE, DELETE, SELECT. Constraints include PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT and FOREIGN KEY. Aggregates are COUNT, SUM, AVG, MIN and MAX, used with GROUP BY and filtered by HAVING.
Worked example
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(30) NOT NULL, marks INT); then INSERT INTO student VALUES (1, 'Aarav', 78); and SELECT name, marks FROM student WHERE marks > 60 ORDER BY marks DESC; The result is one row, Aarav and 78, because the filter keeps marks above 60 and the sort places the highest mark first. String values take single quotes; numbers do not.
Traps
- Writing marks = NULL instead of marks IS NULL.
- Confusing DELETE (rows only) with DROP (the table structure too) and TRUNCATE (all rows, structure kept).
- Using WHERE to filter groups; grouped results need HAVING.
- Assuming COUNT(column) equals COUNT(*); COUNT(column) skips NULLs.
- Forgetting that a foreign key value must already exist in the referenced table.