Topic 32 of 48 ยท Full Stack Essentials

Insert, Select, Update, Delete Queries

Lesson TL;DRTopic 2: Insert, Select, Update, Delete Queries ๐Ÿ“– 5 min read ยท ๐ŸŽฏ beginner ยท ๐Ÿงญ Prerequisites: tagsintroductiontotables, cssproperties Why this matters Here's the thing โ€” every app you've ever used, ...
5 min readยทbeginnerยทsql ยท crud ยท insert ยท select

Topic 2: Insert, Select, Update, Delete Queries

๐Ÿ“– 5 min read ยท ๐ŸŽฏ beginner ยท ๐Ÿงญ Prerequisites: tags-introduction-to-tables, css-properties

Why this matters

Here's the thing โ€” every app you've ever used, whether it's booking a train ticket, updating your profile photo, or deleting an old order, is doing exactly four things to a database behind the scenes: adding new data, reading data, changing data, and removing data. These are INSERT, SELECT, UPDATE, and DELETE โ€” and as a full-stack developer, you will write these every single day. In this lesson, we'll learn all four using a simple students table, so by the end you'll know exactly how real applications talk to a database.

What You'll Learn

  • How to add new rows to a table using INSERT INTO
  • How to retrieve data โ€” all of it or just filtered results โ€” using SELECT
  • How to modify existing records with UPDATE โ€ฆ SET โ€ฆ WHERE
  • How to remove records cleanly using DELETE โ€ฆ WHERE

The Analogy

Picture a city library with a card catalogue. Adding a new book is INSERT โ€” you fill out a new card and slip it into the drawer. Looking up books by title or author is SELECT โ€” you search through the cards with a condition in mind. Stamping a book's return date is UPDATE โ€” you find the existing card and change one field without throwing the card away. Pulling a damaged book off the shelf and discarding it entirely is DELETE โ€” the card and the book are both gone. Every interaction a librarian has with that catalogue maps directly onto one of these four SQL commands.

Chapter 1: INSERT โ€” Adding New Records

The INSERT INTO statement places a brand-new row into a table. You name the table, list the columns you are populating, then supply the matching values.

Syntax

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

Suppose we have a students table with columns id, name, and age. To add a student named Alice aged 23:

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

Notice we omitted id โ€” if the column is set to AUTO_INCREMENT in phpMyAdmin, the database assigns it automatically.

Chapter 2: SELECT โ€” Retrieving Data

SELECT is the read operation. It fetches rows from a table, and you can narrow results with a WHERE clause.

Syntax

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

Retrieve every row and every column from students:

SELECT * FROM students;

Retrieve only the names of students older than 20:

SELECT name FROM students WHERE age > 20;

The * wildcard means "all columns." Using explicit column names instead of * is better practice in production โ€” it documents intent and avoids surprises when the schema changes.

Chapter 3: UPDATE โ€” Modifying Existing Records

UPDATE finds rows that match a condition and overwrites one or more column values. Always include a WHERE clause โ€” without it, every row in the table gets changed.

Syntax

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

To change Alice's age to 24:

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

You can update multiple columns in one statement by separating assignments with commas inside SET.

Chapter 4: DELETE โ€” Removing Records

DELETE removes rows that satisfy a condition. Like UPDATE, omitting WHERE deletes every row โ€” use it with care.

Syntax

DELETE FROM table_name
WHERE condition;

Remove Alice from the table entirely:

DELETE FROM students WHERE name = 'Alice';

Chapter 5: Putting It Together โ€” Practical Exercises

the trainer ran the class through eight tasks in sequence, building and trimming the students table live in phpMyAdmin.

Insert Data

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 Data

Task 1 โ€” Retrieve all students:

SELECT * FROM students;

Task 2 โ€” Retrieve the names and ages of students aged over 21:

SELECT name, age FROM students WHERE age > 21;

Update Data

Task 1 โ€” Update Bob's age to 26:

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

Task 2 โ€” Rename Carol to Caroline:

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

Delete Data

Task 1 โ€” Remove the student named Caroline:

DELETE FROM students WHERE name = 'Caroline';

Task 2 โ€” Delete all students aged below 24:

DELETE FROM students WHERE age < 24;

๐Ÿงช Try It Yourself

Open phpMyAdmin, create a students table with columns id (INT, AUTO_INCREMENT, PRIMARY KEY), name (VARCHAR 100), and age (INT). Then run the following four statements in order and check the table contents after each one.

INSERT INTO students (name, age) VALUES ('Dana', 19);
INSERT INTO students (name, age) VALUES ('Evan', 27);
SELECT * FROM students;
UPDATE students SET age = 20 WHERE name = 'Dana';
DELETE FROM students WHERE age > 25;
SELECT * FROM students;

Success criterion: After the final SELECT, only Dana (age 20) should remain in the table. If Evan is gone and Dana's age reads 20, you've executed all four CRUD operations correctly.

๐Ÿ” Checkpoint Quiz

Q1. What does the * symbol mean in SELECT * FROM students;?

A) Select no columns
B) Select all columns
C) Select only the primary key column
D) Select columns whose names contain letters

Q2. Given this snippet, what will happen?

UPDATE students SET age = 30;

A) Only the first row is updated
B) No rows are updated because there is no WHERE clause
C) Every row in students gets age set to 30
D) A syntax error is thrown

Q3. A developer runs the following two statements. What does the SELECT return?

INSERT INTO students (name, age) VALUES ('Mia', 22);
DELETE FROM students WHERE age < 24;
SELECT * FROM students;

A) One row โ€” Mia, age 22
B) Zero rows โ€” Mia was deleted because 22 < 24
C) A syntax error
D) All rows that existed before the INSERT

Q4. You have a products table. How would you retrieve only the product_name of every product whose price is greater than 100?

A1. B โ€” * is the wildcard that selects every column in the result set.

A2. C โ€” Without a WHERE clause, UPDATE applies to all rows in the table. Always scope updates with a condition.

A3. B โ€” Mia's age is 22, which satisfies age < 24, so the DELETE removes her. The final SELECT returns zero rows.

A4.

SELECT product_name FROM products WHERE price > 100;

Use SELECT <column> (not *) to return only the desired column, and WHERE price > 100 to filter.

๐Ÿชž Recap

  • INSERT INTO โ€ฆ VALUES adds a new row; omit auto-incremented columns and the database fills them in.
  • SELECT โ€ฆ FROM โ€ฆ WHERE retrieves rows; * fetches all columns, or list specific ones for precision.
  • UPDATE โ€ฆ SET โ€ฆ WHERE modifies existing rows; always include WHERE or every row is affected.
  • DELETE FROM โ€ฆ WHERE removes rows; again, always include WHERE or the entire table is wiped.
  • These four operations โ€” Create, Read, Update, Delete โ€” are collectively called CRUD and underpin almost every database-backed application.

๐Ÿ“š Further Reading

Like this topic? Itโ€™s one of 48 in Full Stack Essentials.

Block your seat for โ‚น2,500 and join the next cohort.

Course detailsBlock your seat โ†’