0x50Lesson 6 of 6

Change data with care

Insert, update and delete records while protecting the rows you intend to keep.

12 min 5-question quiz 1 code exercise

By the end of this lesson you can

Recognize data-changing statements and use WHERE carefully.

INSERT adds rows, UPDATE changes existing values, and DELETE removes rows. An UPDATE or DELETE without a WHERE clause may affect every row in the target table. Transactions let you group changes and commit or roll them back together.

SQL example

1INSERT INTO customers (customer_id, name, city)
2VALUES (42, 'Sam Lee', 'Boston');
3
4UPDATE customers
5SET city = 'Cambridge'
6WHERE customer_id = 42;
7
8BEGIN;
9DELETE FROM customers
10WHERE customer_id = 42;
11ROLLBACK;

Practice destructive statements on sample data first. Transactions and exact transaction commands vary by database, and some tools automatically commit statements.

Key takeaways

Recognize data-changing statements and use WHERE carefully.
Use table and column names that make the query easy to read.
Check which rows a query affects before relying on its result.

Lesson quiz

5 questions · pass with 4 correct · up to 50 XP

Passing this quiz completes the lesson and keeps your streak going. Questions you miss come back in review sessions later.

Practice: write SQL queries

Write queries against small sample databases and run them locally in your browser. Each test starts from a fresh SQLite database.

Exercise 1

Update one customer safely

+25 XP

Change the city to Cambridge only for the customer whose ID is 2, then return all customer IDs and cities in ID order.

Only customer 2 changes

SQL dialectChoose the database engine used when you run tests.

Sample tables and rows

Each test rebuilds an in-memory database for the selected dialect before running your query.

sample-data.sql

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, city TEXT); INSERT INTO customers VALUES (1, 'Boston'), (2, 'Salem'), (3, 'Quincy');

query.sql

Loading editor…

Using SQLite 3 (sql.js), an embedded database. Queries run locally in a worker against a fresh in-memory database for each test.

Questions about this lesson

Stuck? Ask. Figured something out? Share it. Explaining is one of the best ways to learn.

Loading posts…