🎓 Velnov

🗄️ SQL · Topic 16 of 20

INSERT, UPDATE, DELETE — SQL Questions with Answers

30 practice questions on INSERT, UPDATE, DELETE, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

3 easy · 13 medium · 14 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

INSERTDMLBest practicesBulk insertINSERT SELECTETLUPDATESafetySETJOINDELETETRUNCATEDROPSELECTUPSERTON CONFLICTExpressionsForeign keysON DELETE CASCADERETURNINGOUTPUTBatchingLocksDEFAULT

Jump to: Easy · Medium · Hard

Easy questions (3)

Q1. easy

Which statement adds a new row to a table?

INSERT INTO students (name, marks) VALUES ('Ana', 92);
  1. A INSERT INTO
  2. B ADD ROW
  3. C CREATE ROW
  4. D PUT INTO

Answer: A — INSERT INTO

INSERT INTO table (columns) VALUES (...) adds rows. Listing the columns explicitly is safest, so the statement keeps working if the table's column order changes.

INSERTDML

Q5. easy

Which statement modifies existing rows?

UPDATE students SET marks = 95 WHERE id = 1;
  1. A UPDATE
  2. B MODIFY
  3. C CHANGE
  4. D SET ROW

Answer: A — UPDATE

UPDATE t SET col = value WHERE ... changes rows that match the condition. The SET clause lists the columns to change.

UPDATEDML

Q9. easy

Which statement removes rows?

DELETE FROM students WHERE id = 5;
  1. A DELETE FROM
  2. B REMOVE FROM
  3. C DROP ROW
  4. D ERASE

Answer: A — DELETE FROM

DELETE FROM t WHERE ... removes matching rows. Without WHERE it empties the table (but keeps its structure).

DELETEDML

Medium questions (13)

Q2. medium

Why list column names in an INSERT?

  1. A It makes the insert robust to column additions/reordering and documents intent
  2. B It is required syntax
  3. C It is faster
  4. D No reason

Answer: A — It makes the insert robust to column additions/reordering and documents intent

INSERT INTO t VALUES (...) relies on exact column order and count. Naming columns lets you omit ones with defaults and survives schema changes.

INSERTBest practices

Q3. medium

How do you insert MULTIPLE rows in one statement?

  1. A INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);
  2. B Three separate INSERTs only
  3. C INSERT MANY
  4. D It is not possible

Answer: A — INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);

A multi-row VALUES list inserts several rows at once - far faster than many single inserts because it is one statement and one round trip.

INSERTBulk insert

Q7. medium

How do you update multiple columns at once?

  1. A SET col1 = v1, col2 = v2 - comma-separated assignments
  2. B SET col1 = v1 AND col2 = v2
  3. C SET (col1, col2)
  4. D Two UPDATE statements only

Answer: A — SET col1 = v1, col2 = v2 - comma-separated assignments

One UPDATE can change many columns: SET status = 'shipped', shipped_at = NOW(). Use AND only in the WHERE clause, never in SET.

UPDATESET

Q12. medium

What is the safest habit before running a DELETE or UPDATE?

  1. A Run a SELECT with the SAME WHERE clause first to preview affected rows
  2. B Run it immediately
  3. C Disable indexes
  4. D Delete a backup

Answer: A — Run a SELECT with the SAME WHERE clause first to preview affected rows

Previewing shows exactly which rows will change. Combined with a transaction (BEGIN ... ROLLBACK if wrong), it prevents most accidental data loss.

SafetySELECT

Q14. medium

How do you increment a column's value?

  1. A UPDATE t SET views = views + 1 WHERE id = 5;
  2. B UPDATE t SET views++
  3. C INCREMENT views
  4. D It cannot be done

Answer: A — UPDATE t SET views = views + 1 WHERE id = 5;

Reference the column in its own SET expression. The database reads the current value and writes the new one atomically within the statement.

UPDATEExpressions

Q16. medium

What does the RETURNING clause do (PostgreSQL)?

INSERT INTO users (name) VALUES ('Ana') RETURNING id;
  1. A Returns the inserted/updated/deleted rows - e.g. to get an auto-generated id
  2. B Rolls back the change
  3. C Returns nothing
  4. D Repeats the statement

Answer: A — Returns the inserted/updated/deleted rows - e.g. to get an auto-generated id

INSERT ... RETURNING id gives back the new primary key in one round trip. SQL Server uses the OUTPUT clause for the same purpose.

RETURNINGOUTPUT

Q18. medium

How do you insert a row using DEFAULT values?

INSERT INTO orders (customer_id) VALUES (5);  -- status uses its default
  1. A Omit those columns from the INSERT, or use the keyword DEFAULT
  2. B You must specify every column
  3. C Insert NULL always
  4. D It is impossible

Answer: A — Omit those columns from the INSERT, or use the keyword DEFAULT

Columns with a DEFAULT (or that allow NULL, or auto-increment) can be left out of the column list. The database fills them in automatically.

DEFAULTINSERT

Q20. medium

What does INSERT do if a UNIQUE constraint is violated?

  1. A It fails with a unique-constraint error unless you handle it (e.g. with ON CONFLICT)
  2. B It overwrites silently
  3. C It inserts a duplicate
  4. D It updates the row

Answer: A — It fails with a unique-constraint error unless you handle it (e.g. with ON CONFLICT)

Constraints protect integrity by rejecting the insert. UPSERT syntax lets you decide to update instead, or IGNORE to skip the conflicting row.

UNIQUEConstraints

Q22. medium

How do you delete duplicate rows keeping one copy?

DELETE FROM t WHERE id IN (
  SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM t) x WHERE rn > 1
);
  1. A Use a window function (ROW_NUMBER) or a self join to identify extras, then DELETE them
  2. B DELETE DISTINCT
  3. C TRUNCATE the table
  4. D It is impossible

Answer: A — Use a window function (ROW_NUMBER) or a self join to identify extras, then DELETE them

Number the duplicates with ROW_NUMBER() OVER (PARTITION BY key) and delete those with rn > 1. A very common data-cleaning task.

DuplicatesROW_NUMBER

Q24. medium

What does DELETE return or affect besides removing rows?

  1. A It reports the number of rows affected and can fire triggers and cascade to related tables
  2. B Only removes rows silently
  3. C It returns the rows
  4. D Nothing else

Answer: A — It reports the number of rows affected and can fire triggers and cascade to related tables

The affected-row count confirms the scope of the operation. Foreign key cascades and triggers may perform additional work - worth knowing before running big deletes.

DELETETriggersCascade

Q26. medium

How do you empty a table but keep its structure quickly?

  1. A TRUNCATE TABLE t;
  2. B DROP TABLE t;
  3. C DELETE TABLE t;
  4. D CLEAR t;

Answer: A — TRUNCATE TABLE t;

TRUNCATE removes all rows almost instantly and resets auto-increment counters (in most databases), while leaving the table definition intact.

TRUNCATE

Q28. medium

How do you set a column to NULL?

  1. A UPDATE t SET middle_name = NULL WHERE id = 5;
  2. B DELETE middle_name
  3. C SET middle_name = ''
  4. D REMOVE middle_name

Answer: A — UPDATE t SET middle_name = NULL WHERE id = 5;

Assigning NULL clears a value (if the column allows NULL). This differs from '' (empty string) - NULL means 'no value', empty string means 'a known blank'.

NULLUPDATE

Q30. medium

Which clause limits how many rows an UPDATE or DELETE affects (MySQL)?

  1. A LIMIT - e.g. DELETE FROM logs WHERE ... LIMIT 1000 for batching
  2. B TOP only
  3. C FETCH only
  4. D You cannot limit them

Answer: A — LIMIT - e.g. DELETE FROM logs WHERE ... LIMIT 1000 for batching

MySQL allows LIMIT on DELETE/UPDATE for batch processing. SQL Server uses DELETE TOP (n). Batching large modifications reduces locking and log growth.

BatchingLIMIT

Hard questions (14)

Q4. hard

How do you insert rows from a SELECT (copying data)?

  1. A INSERT INTO archive (id, total) SELECT id, total FROM orders WHERE year < 2020;
  2. B COPY orders TO archive
  3. C INSERT SELECT INTO
  4. D It cannot be done

Answer: A — INSERT INTO archive (id, total) SELECT id, total FROM orders WHERE year < 2020;

INSERT ... SELECT populates a table from a query - used for archiving, summarising and ETL. The SELECT's columns must line up with the target columns.

INSERT SELECTETL

Q6. hard

What is the danger of UPDATE without a WHERE clause?

  1. A It updates EVERY row in the table
  2. B It errors
  3. C It updates nothing
  4. D It updates one row

Answer: A — It updates EVERY row in the table

UPDATE students SET marks = 0 with no WHERE sets every student's marks to 0. Always double-check the WHERE - and wrap risky changes in a transaction you can roll back.

  • The classic 'I forgot the WHERE' disaster.

UPDATESafety

Q8. hard

How do you UPDATE using values from another table?

UPDATE orders o SET total = s.amt
FROM summary s WHERE s.order_id = o.id;   -- PostgreSQL
  1. A UPDATE ... SET col = (SELECT ...) or a JOIN-based update (UPDATE ... FROM in PostgreSQL)
  2. B You cannot reference other tables
  3. C Use INSERT
  4. D Use UNION

Answer: A — UPDATE ... SET col = (SELECT ...) or a JOIN-based update (UPDATE ... FROM in PostgreSQL)

A correlated subquery or a JOIN in the UPDATE lets you set values based on related data - for example, updating a cached total from a summary table.

UPDATEJOIN

Q10. hard

What is the difference between DELETE and TRUNCATE?

  1. A DELETE removes rows one by one (logged, can have WHERE, fires triggers); TRUNCATE instantly empties the whole table
  2. B They are identical
  3. C TRUNCATE can use WHERE
  4. D DELETE is faster for whole tables

Answer: A — DELETE removes rows one by one (logged, can have WHERE, fires triggers); TRUNCATE instantly empties the whole table

TRUNCATE is much faster for clearing an entire table because it deallocates data pages instead of deleting rows individually, but it cannot filter and usually cannot be rolled back the same way.

DELETETRUNCATE

Q11. hard

What is the difference between DELETE, TRUNCATE and DROP?

  1. A DELETE removes rows (optionally filtered), TRUNCATE empties the table, DROP removes the table itself
  2. B They are the same
  3. C DROP keeps the structure
  4. D TRUNCATE removes the table

Answer: A — DELETE removes rows (optionally filtered), TRUNCATE empties the table, DROP removes the table itself

DELETE and TRUNCATE keep the table (its columns and indexes remain); DROP TABLE deletes the entire object. Choose based on whether you need the empty table afterward.

DELETETRUNCATEDROP

Q13. hard

What is an UPSERT?

INSERT INTO counters (id, n) VALUES (1, 1)
ON CONFLICT (id) DO UPDATE SET n = counters.n + 1;
  1. A Insert a row, but UPDATE it instead if it already exists (INSERT ... ON CONFLICT / ON DUPLICATE KEY / MERGE)
  2. B A faster insert
  3. C Deleting then inserting
  4. D An error type

Answer: A — Insert a row, but UPDATE it instead if it already exists (INSERT ... ON CONFLICT / ON DUPLICATE KEY / MERGE)

UPSERT handles 'insert or update' atomically. PostgreSQL uses INSERT ... ON CONFLICT DO UPDATE; MySQL uses ON DUPLICATE KEY UPDATE; the standard is MERGE.

UPSERTON CONFLICT

Q15. hard

What happens to child rows when you DELETE a parent with a foreign key?

  1. A It depends on the FK's ON DELETE rule: RESTRICT blocks it, CASCADE deletes children, SET NULL nulls the reference
  2. B Children are always deleted
  3. C Nothing happens
  4. D It always errors

Answer: A — It depends on the FK's ON DELETE rule: RESTRICT blocks it, CASCADE deletes children, SET NULL nulls the reference

Referential actions defined on the foreign key control this. RESTRICT/NO ACTION protects data, CASCADE cleans up dependents, SET NULL orphans them safely.

Foreign keysON DELETE CASCADE

Q17. hard

Why can a large single DELETE be problematic?

  1. A It can lock rows, bloat the transaction log, and run long - batching (delete in chunks) is often safer
  2. B It is always fine
  3. C It never locks
  4. D It deletes too little

Answer: A — It can lock rows, bloat the transaction log, and run long - batching (delete in chunks) is often safer

Deleting millions of rows in one transaction holds locks and grows the log. Deleting in batches (e.g. 10,000 rows at a time) reduces contention and log pressure.

DELETEBatchingLocks

Q19. hard

What does a conditional UPDATE with CASE achieve?

  1. A Sets different values per row based on conditions in a single statement
  2. B It filters rows
  3. C It deletes rows
  4. D Nothing

Answer: A — Sets different values per row based on conditions in a single statement

UPDATE t SET tier = CASE WHEN spend > 1000 THEN 'gold' ELSE 'silver' END updates every row with the appropriate value in one pass.

UPDATECASE

Q21. hard

What is a soft delete?

  1. A Marking a row as deleted (e.g. deleted_at timestamp) instead of physically removing it
  2. B A slow DELETE
  3. C DELETE without WHERE
  4. D TRUNCATE

Answer: A — Marking a row as deleted (e.g. deleted_at timestamp) instead of physically removing it

Soft deletes preserve history and allow recovery: queries add WHERE deleted_at IS NULL. The trade-off is extra filtering and eventual cleanup of old rows.

Soft deleteAuditing

Q23. hard

Why should DML changes often be wrapped in a transaction?

  1. A So a multi-step change is all-or-nothing and can be rolled back if something goes wrong
  2. B To make it faster
  3. C Transactions are required for INSERT
  4. D No reason

Answer: A — So a multi-step change is all-or-nothing and can be rolled back if something goes wrong

BEGIN ... COMMIT groups related changes; ROLLBACK undoes them if an error occurs. This keeps data consistent - for example, debit one account and credit another together.

TransactionsAtomicity

Q25. hard

How do you copy a table's structure AND data into a new table?

  1. A CREATE TABLE new AS SELECT * FROM old; (CTAS)
  2. B COPY TABLE
  3. C INSERT INTO new
  4. D It is impossible

Answer: A — CREATE TABLE new AS SELECT * FROM old; (CTAS)

CREATE TABLE AS SELECT (CTAS) creates and fills a table in one step - handy for backups and staging. Note it usually does NOT copy indexes or constraints.

CTASCREATE TABLE

Q27. hard

What is the risk of updating a column used in the WHERE of the same statement?

  1. A Generally SQL applies the change atomically per the original match set, but semantics can surprise you - test carefully
  2. B It always loops forever
  3. C It is a syntax error
  4. D It never works

Answer: A — Generally SQL applies the change atomically per the original match set, but semantics can surprise you - test carefully

SQL evaluates the WHERE against the pre-update state within the statement, so it does not re-match rows mid-update. Still, such statements deserve careful testing on sample data.

UPDATESemantics

Q29. hard

Why is INSERT ... SELECT preferred over row-by-row inserts for bulk loads?

  1. A It is a single set-based operation - far fewer round trips and log entries than thousands of individual inserts
  2. B It is slower but safer
  3. C It skips constraints
  4. D There is no difference

Answer: A — It is a single set-based operation - far fewer round trips and log entries than thousands of individual inserts

Set-based operations let the database work in bulk. Looping single inserts from application code is dramatically slower due to per-statement overhead.

Bulk insertPerformance

Practice “INSERT, UPDATE, DELETE” as a quiz