SQL Statements
What the domain tests
This domain tests fluent use of MySQL 8.0 DDL and DML: creating and altering objects, inserting and modifying rows, joining tables, grouping, using subqueries and common table expressions, and applying window functions. Expect questions about affected-row counts, duplicate-key handling, and SQL mode effects.
Core rules
- INSERT, UPDATE, DELETE, and SELECT are DML. CREATE, ALTER, and DROP are DDL and cause an implicit commit on transactional storage engines.
- INSERT ... ON DUPLICATE KEY UPDATE inserts a row or, on a duplicate unique or primary key, updates it. Its affected-row count is 1 for an insert, 2 for an update, and 0 when the update sets the same values unless CLIENT_FOUND_ROWS is set.
- REPLACE deletes the conflicting row and inserts a new one, so it fires delete triggers and resets omitted columns to defaults.
- Multi-table UPDATE and DELETE statements can join tables. UPDATE ... ORDER BY ... LIMIT is allowed for single-table updates.
- GROUP BY with ONLY_FULL_GROUP_BY, the MySQL 8.0 default, rejects selecting nonaggregated columns that are not functionally dependent on grouped columns.
- CTEs use WITH. Recursive CTEs use WITH RECURSIVE and need a termination condition.
- Window functions such as ROW_NUMBER() and SUM() OVER (...) compute across a partition without collapsing rows.
Worked example
Given a table t(id PRIMARY KEY, v), INSERT INTO t VALUES (1,10) ON DUPLICATE KEY UPDATE v = v + 1 first inserts a row. A second identical statement updates v to 11 and reports affected rows = 2 in the mysql client. If v is already 11 and the statement sets v = 11, affected rows is 0 because no data changed.
Traps
- Expecting REPLACE to behave like an upsert. It is delete-plus-insert.
- Running UPDATE or DELETE without a WHERE clause and committing it.
- Comparing with NULL using = instead of IS NULL.
- Assuming COUNT(column) counts NULLs; only COUNT(*) does.
- Using ORDER BY in a view and expecting the outer query to preserve it.