Database Objects, Queries, and SQL
This domain tests the vocabulary of database objects and your ability to read and predict the result of SQL statements, especially filtering, joining, and aggregation.
Objects and statement families
- Tables store rows; views are stored queries that present a virtual table without holding the data; indexes speed lookups at the cost of write time and storage; stored procedures and triggers hold server-side logic.
- DDL defines structure (CREATE, ALTER, DROP). DML changes data (SELECT, INSERT, UPDATE, DELETE). DCL controls permissions (GRANT, REVOKE).
Clause order and evaluation
The written order is SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY. The logical evaluation order matters more: rows are filtered by WHERE before grouping, groups are filtered by HAVING after aggregation, and ORDER BY runs last. That is why WHERE cannot test an aggregate while HAVING can.
Joins and NULL logic
INNER JOIN returns only matching rows; LEFT JOIN keeps all rows from the left table and fills unmatched right columns with NULL; RIGHT JOIN mirrors that; FULL OUTER JOIN keeps unmatched rows from both sides. A missing join condition produces a Cartesian product, multiplying row counts. Because NULL is unknown rather than empty, comparisons with = NULL never match; use IS NULL and IS NOT NULL.
Worked example
SELECT Department, COUNT() FROM Employees WHERE Status = 'Active' GROUP BY Department HAVING COUNT() > 5 ORDER BY Department; Reading it: WHERE first removes inactive staff, GROUP BY collapses the remainder per department, COUNT(*) counts rows in each group, HAVING then drops groups of five or fewer, and ORDER BY sorts what survives.
Traps that cost marks
- Putting an aggregate condition in WHERE instead of HAVING.
- Selecting a column that is neither aggregated nor grouped.
- Using COUNT(column) when NULLs should be included, where COUNT(*) is correct.
- Forgetting that a LEFT JOIN with a WHERE filter on the right table behaves like an INNER JOIN.