skip to content

DDL and DML

The statements that define and change data: CREATE, ALTER and DROP TABLE with types and constraints, identity generation, INSERT/UPDATE/DELETE/TRUNCATE/MERGE, BEGIN, COMMIT, ROLLBACK and savepoints, and GRANT and REVOKE. Interviewers ask because constraints are where correctness is actually enforced, and DELETE versus TRUNCATE inside a transaction is a standard check.

part ofSQLread it →
on this pageshow

questions

72 · 4 sections

What value do existing rows get when you run ALTER TABLE ... ADD COLUMN on a populated table?

level: juniorimportance: must knowfreq 72%
basics
~20 s

Existing rows get NULL unless the new column declares a DEFAULT, in which case every existing row is populated with that default value. Adding a NOT NULL column with no DEFAULT to a table that already holds rows is rejected.

open as a page

In CREATE TABLE, when must a constraint be written at table level instead of inline on a column?

level: juniorimportance: must knowfreq 70%
basics
~20 s

Any rule covering more than one column — a composite PRIMARY KEY, UNIQUE or FOREIGN KEY, or a CHECK comparing two columns — must be written as a table constraint after the column list. Single-column rules may be written either way.

open as a page

What does DEFAULT CURRENT_TIMESTAMP in a CREATE TABLE column definition mean, and when is it evaluated?

level: juniorimportance: must knowfreq 70%
basics
~20 s

DEFAULT names the value the engine stores when an INSERT supplies none for that column. The expression is evaluated at insert time, so each row records its own insertion moment rather than one value frozen when the table was created.

open as a page

Why is FLOAT the wrong type for a money column, and what should you use instead?

level: juniorimportance: must knowfreq 82%
basics
~20 s

FLOAT and REAL store binary approximations, so a value such as 0.10 is never held exactly and the error accumulates across sums and multiplications. Money needs an exact type: DECIMAL/NUMERIC with a declared precision and scale.

open as a page

How do you declare an auto-numbered surrogate key column in standard SQL?

level: juniorimportance: must knowfreq 70%
basics
~20 s

Standard SQL uses an identity column: id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY. The engine supplies each value from an attached sequence generator, whose START WITH, INCREMENT BY, CYCLE and MINVALUE/MAXVALUE options you set in parentheses.

open as a page

What does DELETE FROM orders WHERE status = 'DRAFT' remove, and what happens if you omit WHERE?

level: juniorimportance: must knowfreq 80%
basics
~10 s

It removes every whole row of orders whose status is 'DRAFT'; the table and all other rows stay. With no WHERE clause, DELETE FROM orders removes every row in the table.

open as a page

Why should an INSERT statement name its target columns instead of relying on column order?

level: juniorimportance: must knowfreq 82%
basics
~20 s

An INSERT without a column list binds values to columns by position, so adding, dropping or reordering a column silently shifts every value. Naming the columns pins each value to its column and lets omitted columns take their declared defaults.

open as a page

How does MERGE INTO ... USING ... ON perform an upsert in a single statement?

level: juniorimportance: must knowfreq 60%
basics
~20 s

MERGE joins a target table to a source rowset through an ON condition, then applies one action per source row: WHEN MATCHED THEN UPDATE for keys that already exist in the target, WHEN NOT MATCHED THEN INSERT for the rest.

open as a page

What does adding RETURNING order_id to an INSERT statement produce?

level: juniorimportance: must knowfreq 55%
basics
~10 s

RETURNING makes the INSERT also yield a result set: one row per inserted row, containing the listed columns with their final stored values — generated identity keys, applied defaults and computed columns included.

open as a page

What is the difference between TRUNCATE TABLE and DELETE FROM with no WHERE clause?

level: juniorimportance: must knowfreq 88%
basics
~20 s

TRUNCATE TABLE empties a table in one bulk operation: no WHERE clause, no per-row DELETE triggers, usually an identity reset, and far less logging. DELETE removes rows one at a time as ordinary DML and can be filtered.

open as a page

What do START TRANSACTION, COMMIT and ROLLBACK do, and what happens without them?

level: juniorimportance: must knowfreq 82%
basics
~20 s

START TRANSACTION (or BEGIN) opens an explicit transaction; COMMIT ends it and makes all its work permanent; ROLLBACK ends it and discards all its work. Without them, autocommit wraps each statement in its own transaction that commits immediately.

open as a page

Does ROLLBACK TO SAVEPOINT end the transaction, and what happens to earlier work?

level: juniorimportance: must knowfreq 52%
basics
~10 s

ROLLBACK TO SAVEPOINT undoes only the statements executed after that savepoint. The transaction stays open, everything done before the savepoint is still pending, and you must still issue COMMIT or ROLLBACK to finish.

open as a page

Does CREATE TABLE inside an open transaction commit the work already done by that transaction?

level: middleimportance: must knowfreq 52%
basics
~20 s

It depends on the engine. MySQL and Oracle commit implicitly before and after DDL, so a later ROLLBACK undoes nothing that came earlier. PostgreSQL, SQL Server and SQLite treat DDL as transactional and roll it back with everything else.

open as a page

What does SET TRANSACTION configure, and when must you issue it?

level: middleimportance: should knowfreq 38%
basics
~20 s

SET TRANSACTION sets a transaction's characteristics — its isolation level and its access mode, READ ONLY or READ WRITE — not any data. It must be issued before the transaction executes its first query or data-modifying statement, or the engine rejects it.

open as a page

After ROLLBACK TO SAVEPOINT s1, which savepoints in the transaction are still usable?

level: middleimportance: should knowfreq 28%
basics
~10 s

s1 itself stays established and can be rolled back to again. Every savepoint created after s1 is destroyed by the rewind, and naming one afterwards raises an invalid-savepoint error rather than doing nothing.

open as a page

In SQL, what exact statements let a service account write to the orders table, and what takes that access back?

level: juniorimportance: must knowfreq 45%
basics
~20 s

GRANT INSERT, UPDATE, DELETE ON orders TO svc_orders; gives the write access, and REVOKE INSERT, UPDATE, DELETE ON orders FROM svc_orders; removes it. SQL has no single write privilege, so the statement lists each operation.

open as a page

In SQL, what statements create a reporting role, give it read access to the orders table, and add the user alice?

level: juniorimportance: should knowfreq 40%
basics
~10 s

CREATE ROLE reporting; GRANT SELECT ON orders TO reporting; GRANT reporting TO alice; The last form has no ON clause, because it grants membership in a role instead of a privilege on an object.

open as a page

In SQL, which REVOKE keyword makes it fail when team_lead has passed SELECT on orders to others, and what are both forms?

level: middleimportance: should knowfreq 28%
basics
~10 s

RESTRICT makes it fail: REVOKE SELECT ON orders FROM team_lead RESTRICT;. CASCADE also removes what team_lead passed on: REVOKE SELECT ON orders FROM team_lead CASCADE;. PostgreSQL assumes RESTRICT when neither is written.

open as a page

In SQL, what exact GRANT gives an analyst read access to only the email and signup_date columns of a customers table?

level: middleimportance: should knowfreq 38%
basics
~20 s

GRANT SELECT (email, signup_date) ON customers TO analyst; puts the column list in parentheses right after the privilege. The analyst can select those columns by name, but SELECT * is refused because it asks for every column.

open as a page

In SQL, how do you write a GRANT that lets team_lead pass on SELECT on orders, and a REVOKE that removes only that ability?

level: middleimportance: should knowfreq 33%
basics
~20 s

GRANT SELECT ON orders TO team_lead WITH GRANT OPTION; adds the right to pass the privilege on. REVOKE GRANT OPTION FOR SELECT ON orders FROM team_lead; removes that right and leaves team_lead's own SELECT in place.

open as a page