Topic 2: Insert, Select, Update, Delete Queries

📖 8 min read · 🎯 intermediate · 🧭 Prerequisites: the-model-view-controller-pattern, configuring-express

Why this matters

Up until now, your Express server can receive requests — but it has nowhere to actually store or fetch anything. It's like building a shop with no shelves and no inventory. That changes today. The four SQL operations — INSERT, SELECT, UPDATE, and DELETE — are how your backend talks to your database. You want to save a new user? INSERT. Fetch their profile? SELECT. Change their email? UPDATE. Remove their account? DELETE. Master these four, and your server stops being a skeleton and starts doing real work.

What You'll Learn

Write an INSERT statement to add new rows to a table
Write SELECT queries to retrieve all rows or filtered subsets
Write an UPDATE statement to modify existing records
Write a DELETE statement to remove records, safely using a WHERE clause

The Analogy

Think of your database table as a physical library shelf. INSERT is the librarian placing a brand-new book on the shelf. SELECT is a visitor scanning the shelves to find books that match their search. UPDATE is the editor who pulls a book off the shelf, corrects a typo on page 12, and puts it back. DELETE is the librarian removing a worn-out book entirely. The shelf itself — the table — just holds the collection; these four actions are how anyone interacts with it.

Chapter 1: INSERT — Adding New Records

The INSERT statement places one or more new rows into a table. You name the columns you're filling, then provide matching values.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example — adding a student to a students table (columns: id, name, age):

INSERT INTO students (name, age) VALUES ('Alice', 23);

You don't need to supply id if it's an AUTO_INCREMENT primary key — the database assigns it automatically.
Column order in the list must match value order in VALUES.
String values go in single quotes; numeric values do not.

Chapter 2: SELECT — Retrieving Data

The SELECT statement reads rows out of a table. You can pull every column, specific columns, or only rows that satisfy a condition.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example — retrieve every student:

SELECT * FROM students;

The * wildcard means "all columns." Use it for quick exploration; name columns explicitly in production queries.

Example — retrieve only names where age is greater than 20:

SELECT name FROM students WHERE age > 20;

WHERE is optional; omitting it returns every row.
Conditions can use =, >, <, >=, <=, !=, AND, OR, LIKE, and more.

Chapter 3: UPDATE — Modifying Existing Records

The UPDATE statement rewrites one or more column values in rows that match a condition.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example — change Alice's age to 24:

UPDATE students SET age = 24 WHERE name = 'Alice';

Warning: Omitting the WHERE clause updates every row in the table. Always double-check your condition before running an UPDATE in production.

Chapter 4: DELETE — Removing Records

The DELETE statement removes rows from a table that match a condition.

Syntax:

DELETE FROM table_name
WHERE condition;

Example — remove Alice from the table:

DELETE FROM students WHERE name = 'Alice';

Warning: Just like UPDATE, a DELETE without WHERE wipes the entire table. Many teams run a SELECT with the same WHERE clause first, confirm the rows look right, then swap SELECT * for DELETE FROM.

Chapter 5: Putting It Together — Practical Exercises

Work through these eight tasks in order against a students table with columns id, name, and age.

INSERT Tasks

Task 1 — Add a new student named Bob, aged 25:

INSERT INTO students (name, age) VALUES ('Bob', 25);

Task 2 — Add a new student named Carol, aged 22:

INSERT INTO students (name, age) VALUES ('Carol', 22);

SELECT Tasks

Task 3 — Retrieve all students:

SELECT * FROM students;

Task 4 — Retrieve the names and ages of students aged over 21:

SELECT name, age FROM students WHERE age > 21;

UPDATE Tasks

Task 5 — Update Bob's age to 26:

UPDATE students SET age = 26 WHERE name = 'Bob';

Task 6 — Change Carol's name to Caroline:

UPDATE students SET name = 'Caroline' WHERE name = 'Carol';

DELETE Tasks

Task 7 — Remove the student named Caroline:

DELETE FROM students WHERE name = 'Caroline';

Task 8 — Delete all students aged below 24:

DELETE FROM students WHERE age < 24;

🧪 Try It Yourself

Task: Open phpMyAdmin (or any SQL client), create a students table, then run all eight practice queries above in order — starting from an empty students table so the result is reproducible.

Success criterion: Working from an empty table and running only the eight tasks: Task 1 adds Bob (25), Task 2 adds Carol (22), Tasks 5–6 update Bob to age 26 and rename Carol → Caroline (still 22), Task 7 deletes Caroline, and Task 8 deletes everyone aged under 24 — Bob (age 26) is the only row left whose age is not less than 24. A final SELECT * FROM students; returns exactly one row: Bob, age 26.

Note: the in-chapter examples (which insert Alice and later delete her) are illustrative — they are not part of the eight tasks above. If you also ran the Alice INSERT / Alice DELETE / Alice age=24 from the chapter examples, the math still ends at "only Bob" because Alice is deleted in Chapter 4's example.

Starter — create the table first:

CREATE TABLE students (
  id  INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age  INT NOT NULL
);

Then run the INSERT, SELECT, UPDATE, and DELETE statements from the exercises above, checking the table contents after each step.

🔍 Checkpoint Quiz

Q1. You want to retrieve only the name column for students whose age equals exactly 22. Which query is correct?

A) SELECT * FROM students WHERE age = 22; B) SELECT name FROM students WHERE age = 22; C) SELECT name FROM students; D) SELECT name WHERE age = 22;

Q2. Given this table state after the INSERT tasks:

id	name	age
1	Alice	23
2	Bob	25
3	Carol	22

What does this query return?

SELECT name FROM students WHERE age > 22;

A) Alice, Bob, Carol B) Alice, Bob C) Bob only D) Alice only

Q3. A teammate runs the following without testing it first:

UPDATE students SET age = 99;

What happens, and how should the query have been written?

Q4. You need to remove all students who are exactly 23 years old. Write the correct DELETE statement.

A1. B — SELECT name FROM students WHERE age = 22; names the specific column and applies the exact condition. Option A returns all columns; C has no filter; D is missing the FROM clause and is invalid SQL.

A2. B — Alice (23) and Bob (25) are both older than 22; Carol (22) fails the > 22 condition and is excluded.

A3. Without a WHERE clause, the UPDATE applies to every row, setting every student's age to 99. The correct form is UPDATE students SET age = 99 WHERE <specific condition>; — for example WHERE name = 'Alice' to target one student.

A4.

DELETE FROM students WHERE age = 23;

🪞 Recap

INSERT INTO … VALUES (…) adds a new row; column order must match value order.
SELECT … FROM … WHERE … retrieves data — omit WHERE for all rows, add it to filter.
UPDATE … SET … WHERE … modifies existing rows; always include a WHERE clause or you rewrite the entire table.
DELETE FROM … WHERE … removes matching rows; a missing WHERE deletes everything.
Running a matching SELECT before a destructive UPDATE or DELETE is a safe habit that prevents accidental data loss.

📚 Further Reading

MySQL INSERT documentation — the source of truth on INSERT syntax and options
MySQL SELECT documentation — full reference for SELECT, WHERE, ORDER BY, and beyond
SQLBolt Interactive SQL Tutorial — browser-based exercises that let you run real queries instantly
⬅️ Previous: Configuring Express
➡️ Next: Mac Installation