Data Manipulation and Queries
What the domain covers
This domain tests changing data and retrieving it from one table: INSERT, UPDATE, DELETE, SELECT with WHERE and ORDER BY, DISTINCT, and aggregate functions with GROUP BY and HAVING. Exam items ask you to predict row counts, handle NULLs, and decide whether a condition belongs in WHERE or HAVING.
Core rules
- INSERT adds rows; a column list is safer than positional values. UPDATE changes existing rows and should always have a WHERE clause unless all rows are intended. DELETE removes rows; TRUNCATE is not a substitute when you need filtering.
- WHERE filters rows before grouping. GROUP BY collapses rows into groups. HAVING filters groups after aggregation. SELECT projects columns and aggregates; ORDER BY sorts the final result and can use aliases.
- Aggregate functions: COUNT, SUM, AVG, MIN, MAX. COUNT(*) counts rows; COUNT(column) counts non-NULL values; SUM and AVG ignore NULLs. AVG(column) divides by the non-NULL count, not the total row count.
- DISTINCT applies to the whole selected row, not to one column in isolation.
- Transactions: COMMIT makes changes permanent; ROLLBACK undoes uncommitted work; SAVEPOINT allows partial rollback.
Worked example
SELECT department_id, COUNT() AS rows, AVG(salary) AS avg_salary FROM employees WHERE salary > 0 GROUP BY department_id HAVING COUNT() >= 5 ORDER BY avg_salary DESC;
Reasoning: WHERE removes non-positive salaries before grouping. GROUP BY forms one group per department_id, including a NULL group if present. COUNT(*) counts rows per group; AVG(salary) ignores NULL salaries. HAVING keeps only groups with at least five rows. ORDER BY sorts by the alias. If the intent were “average salary above 5000,” that condition would go in HAVING, not WHERE, because it depends on an aggregate.
Traps that cost marks
- UPDATE or DELETE without WHERE, changing every row.
- Putting aggregate conditions in WHERE instead of HAVING.
- Assuming COUNT(column) includes NULLs.
- Expecting DISTINCT to deduplicate one column while returning other columns.
- Using ORDER BY in a view or subquery where the DBMS does not preserve it.