SQL DML: CRUD Operations

Four Operations, Everything Else Is a Detail

Think about a physical file cabinet. There are exactly four things you can do to any record inside it: put a new one in, read one, change one, or throw one away. Everything else — searching, sorting, reporting — is a variation of reading.

Databases are no different. The four fundamental operations are Create, Read, Update, Delete — and in SQL: INSERT, SELECT, UPDATE, DELETE.

Master these four, and you can do anything with data. Every complex query, every dashboard, every report is built from these primitives.

Starting Data: The Library Tables

Throughout this lesson, we'll use these tables. Here's the starting state:

books

members

INSERT: Adding New Records

Single row INSERT

INSERT INTO members (first_name, last_name, email, membership_type)
VALUES ('Clara', 'Smith', 'clara@email.com', 'student');

Result — members table after:

Multiple rows in one statement

INSERT INTO books (title, genre, copies_available, author_id)
VALUES
    ('Thinking, Fast and Slow', 'Psychology', 2, 4),
    ('The Pragmatic Programmer', 'Technology', 4, 5),
    ('Dune', 'Fiction', 1, 6);

One round trip to the database, three rows inserted. Much faster than three separate INSERT statements for bulk data.

INSERT ... SELECT (copy data from another table)

INSERT INTO archive_loans (loan_id, book_id, member_id, return_date)
SELECT loan_id, book_id, member_id, return_date
FROM loans
WHERE return_date < '2024-01-01';

This reads from one table and writes directly into another — no application code needed to move data.

SELECT: Reading Data

SELECT is the most-used SQL command. It never changes data — it only reads it.

Select all columns

SELECT * FROM books;

The * means "all columns." Fine for exploration; avoid it in production queries (brittle if columns change).

Select specific columns

SELECT title, genre, copies_available
FROM books;

Result:

WHERE: Filtering rows

SELECT title, copies_available
FROM books
WHERE genre = 'Technology';

Result:

ORDER BY: Sorting results

SELECT title, copies_available
FROM books
ORDER BY copies_available DESC;

Result:

ASC (ascending, A→Z, 0→9) is the default. DESC reverses it.

LIMIT and OFFSET: Pagination

-- First 2 books
SELECT title FROM books LIMIT 2;

-- Skip 2, return next 2 (page 2 of results)
SELECT title FROM books LIMIT 2 OFFSET 2;

Common WHERE Operators

UPDATE: Modifying Existing Rows

Update a single row

UPDATE books
SET copies_available = 5
WHERE book_id = 1;

books before:

books after:

Update multiple columns at once

UPDATE members
SET membership_type = 'premium',
    email = 'ben.carter@email.com'
WHERE member_id = 2;

Update based on a calculation

-- Reduce all Technology books by 1 copy (someone borrowed them)
UPDATE books
SET copies_available = copies_available - 1
WHERE genre = 'Technology';

DELETE: Removing Rows

Delete a specific row

DELETE FROM members
WHERE member_id = 3;

members before:

members after:

Delete multiple rows matching a condition

DELETE FROM loans
WHERE return_date < '2023-01-01';

The missing WHERE clause — the most dangerous mistake in SQL

-- This deletes EVERY row in the table:
DELETE FROM members;

-- This updates EVERY row in the table:
UPDATE books SET copies_available = 0;

A missing WHERE on UPDATE or DELETE is one of the most common production database disasters. The DBMS executes exactly what you wrote.

The Golden Rule: SELECT Before UPDATE/DELETE

Before running any UPDATE or DELETE in production, run the equivalent SELECT first.

-- Step 1: See what you're about to change
SELECT * FROM loans WHERE return_date < '2023-01-01';

-- Step 2: If the output looks right, delete it
DELETE FROM loans WHERE return_date < '2023-01-01';

This takes 10 extra seconds and has saved countless databases. The SELECT shows you exactly which rows the WHERE clause matches before any data is changed.

Wrap critical operations in a transaction for additional safety:

BEGIN;

DELETE FROM loans WHERE return_date < '2023-01-01';

-- Verify the deletion before committing
SELECT COUNT(*) FROM loans;

-- If everything looks right:
COMMIT;

-- If something went wrong:
-- ROLLBACK;

DML Operations Summary

The Habits That Separate Good from Dangerous

1. Always include WHERE on UPDATE and DELETE — unless you truly want to affect all rows
2. Test with SELECT first — see the rows before you change them
3. Use transactions for multi-step changes — ROLLBACK is your undo button
4. Never run destructive SQL on production without a recent backup — some mistakes are irreversible even with ROLLBACK

CRUD operations are simple. The discipline around using them safely is what matters.

Next lesson: Joins — combining data from multiple tables to answer real-world questions.