Which of the following is a valid pair of schema change statements in SQL?
2022
Which of the following is a valid pair of schema change statements in SQL?
Answer: C. DROP, ALTER — Concept — DDL versus DML. SQL commands are grouped by what they act upon. Data Definition Language (DDL) statements act on the schema, the stored definition…
- A.
DELETE, ALTER
- B.
DROP, UPDATE
- C.
DROP, ALTER
- D.
DELETE, UPDATE
Attempted by 2259 students.
Show answer & explanation
Correct answer: C
Concept — DDL versus DML. SQL commands are grouped by what they act upon. Data Definition Language (DDL) statements act on the schema, the stored definition of database objects such as tables, views and indexes; CREATE, ALTER, DROP, TRUNCATE and RENAME belong to this group. Data Manipulation Language (DML) statements act on the data held inside those objects and leave the definition untouched; SELECT, INSERT, UPDATE and DELETE belong to that group. "Schema change statement" is another name for a DDL statement, so the pair being asked for is the pair whose two members are both DDL.
Application. Classify each of the four keywords that appear across the pairs offered:
Statement | Category | What it acts on |
|---|---|---|
DROP | DDL (schema change) | Erases an entire object's definition from the data dictionary |
ALTER | DDL (schema change) | Edits an existing definition: adds, drops or modifies a column, adds a constraint |
DELETE | DML (data change) | Removes rows from a table; the stored definition is left as it stands |
UPDATE | DML (data change) | Changes values inside existing rows; the stored definition is left as it stands |
Contrast — the pairs offered.
DELETE, ALTER pairs a row-level command with a definition-level command, so this pair is mixed.
DROP, UPDATE likewise pairs a definition-level command with a row-level command, so this pair is mixed as well.
DELETE, UPDATE pairs two row-level commands, so the stored definition is never touched.
DROP, ALTER pairs two definition-level commands, so both members change the schema.
Cross-check. Two independent behaviours confirm the split. A WHERE clause can restrict DELETE and UPDATE — DELETE FROM emp WHERE id = 7 — because rows are their target, whereas DROP and ALTER accept no WHERE clause at all. And in engines such as Oracle and MySQL a DDL statement triggers an implicit COMMIT and cannot be rolled back, while DELETE and UPDATE remain inside the open transaction and can be rolled back; that difference follows directly from one pair editing the catalogue and the other editing rows.
Result. The valid pair of schema change statements in SQL is DROP, ALTER.