SQL INSERT, UPDATE and DELETE: Safe Data Changes

SQL beginner course · Chapter 5 of 10

SQL INSERT, UPDATE and DELETE: Safe Data Changes explained in simple steps. Examples use practice tables such as students and scores; adapt names to your database.

Follow the concepts from left to right, then run the examples below.

In this lesson

1. INSERT
2. UPDATE
3. DELETE
4. Transaction check

INSERT

INSERT adds a new row. Name the columns so a future table change is less likely to break the statement.

Example

INSERT INTO students (student_id, student_name, grade) VALUES (11, 'Maya', 8);

Expected result: One new student is added.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

UPDATE

UPDATE changes existing rows. First use SELECT with the same WHERE condition to see what will be affected.

Example

UPDATE students SET grade = 9 WHERE student_id = 11;

Expected result: Only Maya’s row changes when 11 is unique.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

DELETE

DELETE removes selected rows. A missing WHERE can delete every row in the table; check the condition carefully.

Example

DELETE FROM students WHERE student_id = 11;

Expected result: The one matching student is removed.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Transaction check

Wrap related changes in a transaction when supported. ROLLBACK cancels uncommitted work; COMMIT saves it.

Example

BEGIN TRANSACTION; UPDATE students SET grade = 9 WHERE student_id = 11; ROLLBACK;

Expected result: The test update is undone.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Quick review

Explain each concept in your own words. Recreate one example without looking at the code. Compare your result with the expected result above.

Browse the full SQL learning path. This is original teaching material; the syllabus was checked against the w3resource SQL tutorial index.