Structured Query Language and Relational Databases
What this domain tests
This domain tests relational concepts and SQL retrieval. Expect questions on keys, constraints, DDL versus DML, filtering, sorting, grouping, and simple joins.
Core rules
- A relation is a table; tuples are rows; attributes are columns. A primary key uniquely identifies each row and cannot be NULL. A foreign key references a primary key in another table.
- Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
- DDL defines structures: CREATE, ALTER, DROP. DML manipulates data: INSERT, UPDATE, DELETE. DQL retrieves data: SELECT.
- SELECT columns FROM table WHERE condition ORDER BY column [ASC|DESC].
- DISTINCT removes duplicate result rows. Aggregate functions are COUNT, SUM, AVG, MIN, MAX.
- GROUP BY forms groups; HAVING filters groups after aggregation. WHERE filters rows before grouping.
- NULL is unknown, not zero or blank. Test it with IS NULL or IS NOT NULL, never with = NULL.
- String literals use single quotes: 'Chennai'.
Worked example
List departments with more than two employees: SELECT dept, COUNT() FROM employee GROUP BY dept HAVING COUNT() > 2; First, rows are grouped by dept. COUNT() counts rows in each group. HAVING then removes groups whose count is not greater than 2. You cannot use WHERE COUNT() > 2 because WHERE runs before grouping, when the count does not yet exist.
Traps
- Using WHERE instead of HAVING for aggregate conditions.
- Writing = NULL instead of IS NULL.
- Confusing DELETE (removes rows) with DROP (removes the table structure).
- Using double quotes for string literals in standard SQL.
- Forgetting that COUNT(column) ignores NULLs while COUNT(*) counts rows.