Checkpoint — Modifying data

Q: Why is NOT EXISTS safer than NOT IN in a DELETE?

If the subquery's column contains a NULL, x NOT IN (...) is unknown for every row and nothing is deleted, and rows whose own value is NULL are never matched. NOT EXISTS is simply true or false per row, so it behaves as intended.

25 min · +25 XP · 3 exercises · 12 questions

Exercises0/3

Quiz · 70% to clear12 q

This checkpoint covers the whole module: INSERT with a column list, multi-row values, defaults and INSERT ... SELECT; UPDATE with expressions, CASE and values from another table; DELETE with subqueries, the difference between DELETE, TRUNCATE and DROP, and soft deletes; upserts with ON DUPLICATE KEY UPDATE, REPLACE, INSERT IGNORE and the two-step form; and transactions with COMMIT, ROLLBACK and savepoints.

How it works. Twelve questions and three scripts. You need 70% on the questions and every script accepted to clear the module. You can retake it as often as you like; your best score counts. Each script changes data and ends with the SELECT that shows the result — the closing SELECT is already in the starter.

Before you start, make sure you can answer these from memory:

What does a column left out of an INSERT receive, and does an explicit NULL give you the default?
How do you copy rows from one table into another, and how are the columns matched?
What does an UPDATE with a CASE and no ELSE do to the rows no branch matches?
How do you update one table from another in portable SQL, and how does MySQL spell it with a join?
DELETE, TRUNCATE, DROP: which takes a WHERE, which can be rolled back in MySQL, which resets AUTO_INCREMENT?
On a duplicate key, what do ON DUPLICATE KEY UPDATE, REPLACE and INSERT IGNORE each do to the existing row?
What does ROLLBACK TO SAVEPOINT undo, and what state is the transaction in afterwards?

The three scripts are a deduplicating import with INSERT IGNORE, a month-end close that updates, archives and deletes inside one transaction, and a stock sync that updates the products a delivery mentions and inserts the ones it introduces.

Common questions

What is the difference between DELETE and TRUNCATE?

DELETE removes the rows a WHERE picks, one at a time, logs each and can be rolled back. TRUNCATE removes every row at once by recreating the table, takes no WHERE, resets AUTO_INCREMENT and in MySQL commits implicitly, so ROLLBACK cannot undo it.

What does REPLACE INTO do to columns you did not list?

It sets them to their defaults. On a duplicate key REPLACE deletes the old row and inserts the new one, so any column missing from its column list starts fresh, which is why it is wrong for counters and partial updates.

Why is NOT EXISTS safer than NOT IN in a DELETE?

If the subquery's column contains a NULL, x NOT IN (...) is unknown for every row and nothing is deleted, and rows whose own value is NULL are never matched. NOT EXISTS is simply true or false per row, so it behaves as intended.

SQL INSERT, UPDATE and DELETE Quiz: Data Changes Practice Test

Test yourself: 12 questions and three scripts on INSERT, UPDATE, DELETE, TRUNCATE vs DROP, MySQL upserts, INSERT IGNORE, transactions and savepoints.

Course: SQL study plan
Module: Modifying data
Kind: Checkpoint — cleared at 70%
Reading time: 25 min
Runtime: MySQL 8 syntax on SQLite 3.49

Checkpoint — Modifying data is the checkpoint that closes the Modifying data module: a graded quiz and whole-program exercises, passed at 70%.

Instructions

Before you start, make sure you can answer these from memory:

What does a column left out of an INSERT receive, and does an explicit NULL give you the default?
How do you copy rows from one table into another, and how are the columns matched?
What does an UPDATE with a CASE and no ELSE do to the rows no branch matches?
How do you update one table from another in portable SQL, and how does MySQL spell it with a join?
DELETE, TRUNCATE, DROP: which takes a WHERE, which can be rolled back in MySQL, which resets AUTO_INCREMENT?
On a duplicate key, what do ON DUPLICATE KEY UPDATE, REPLACE and INSERT IGNORE each do to the existing row?
What does ROLLBACK TO SAVEPOINT undo, and what state is the transaction in afterwards?

Common questions

What is the difference between DELETE and TRUNCATE?

What does REPLACE INTO do to columns you did not list?

Why is NOT EXISTS safer than NOT IN in a DELETE?

Exercises

Deduplicate a signup import

Import signups into subscribers (email is the primary key) with one INSERT IGNORE ... SELECT, giving every imported row source = 'import':

a subscriber who already exists keeps their row exactly as it is;
when the same email appears several times in signups, the signup with the lowest id wins.

Then show email, name, source, ordered by email.

subscribers: (ada@x.io, Ada, web).

signups:

id	email	name
1	alan@x.io	Alan
2	ada@x.io	Ada L
3	grace@x.io	Grace
4	alan@x.io	Alan T

email | name | source
ada@x.io | Ada | web
alan@x.io | Alan | import
grace@x.io | Grace | import

Close the month

Close May in one transaction (start it, do the three steps in this order, commit):

every open invoice issued before 2024-06-01 becomes overdue;
every paid invoice is copied into invoices_archive(id, customer, amount, closed_on) with closed_on = '2024-06-30';
the paid invoices are deleted from invoices.

Then print the invoices left (id, customer, status, ordered by id) and the archive (id, customer, amount, closed_on, ordered by id; keep any rows it already had).

id	customer	amount	status	issued_on
1	Asha	100.00	paid	2024-05-03
2	Ben	250.00	open	2024-05-20
3	Chen	75.50	open	2024-06-04
4	Dev	40.00	paid	2024-06-10
5	Asha	60.00	overdue	2024-04-01

id | customer | status
2 | Ben | overdue
3 | Chen | open
5 | Asha | overdue

id | customer | amount | closed_on
1 | Asha | 100 | 2024-06-30
4 | Dev | 40 | 2024-06-30

Sync stock from a delivery

A delivery arrived, loaded into delivery(sku, name, qty, price) with one row per sku. Bring products up to date:

a product the shop already has: add the delivered qty to its stock and take the delivery's price; keep the product's own name;
a sku the shop has never had: insert it with the delivery's name, qty and price.

Use the portable update-then-insert form. Then show sku, name, qty, price, ordered by sku.

products: (A1, Pen, 10, 1.50), (B2, Pad, 0, 3.00), (C3, Ink, 5, 7.25). delivery: (B2, Notepad, 20, 3.40), (D4, Ruler, 15, 2.00), (A1, Pen, 5, 1.50).

sku | name | qty | price
A1 | Pen | 15 | 1.5
B2 | Pad | 20 | 3.4
C3 | Ink | 5 | 7.25
D4 | Ruler | 15 | 2

In this module: Modifying data

← Transactions in practice · CREATE TABLE and data types →