Multiple choiceQuestion 1 · Recall
Loading…
By the end you can write INSERT INTO, UPDATE and DELETE FROM statements that change the
right rows, and explain why leaving out WHERE is the classic way to destroy a table.
SELECT only reads. The three statements that change stored data are:
| Statement | Does | Rows affected |
|---|---|---|
INSERT INTO |
adds a new row | one per VALUES list |
UPDATE |
changes values in existing rows | those matching WHERE |
DELETE FROM |
removes rows | those matching WHERE |
All three are DML, because they manipulate data. None of them changes the table's structure — that job belongs to DDL.
The safest form names the fields, then gives the values in the same order:
INSERT INTO Student (StudentID, FirstName, Average)
VALUES (9, 'Iris', 63.5);
The fields you leave out get NULL (or their DEFAULT value, if the field defines one). The
number of values must match the number of fields, and the types must fit: text in single
quotes, numbers bare, TRUE/FALSE for booleans.
If you list no fields, you must supply a value for every field, in the order the table was defined:
INSERT INTO Student
VALUES (10, 'Jonas', TRUE, 58.0, '2026-09-01');
Miss one value, or put them in the wrong order, and the DBMS either errors or stores the wrong data in the wrong field — hence the field list is worth the typing.
UPDATE Student
SET Average = 68.0
WHERE StudentID = 9;
Read it as: "in the Student table, set Average to 68.0 for the rows where StudentID
is 9."
Several fields can change in one statement, separated by commas:
UPDATE Student
SET Average = 68.0, FeePaid = TRUE
WHERE StudentID = 9;
SET names the field, then =, then the new value. The WHERE decides which rows are
touched; without it, every row is updated.
UPDATE Student SET Average = 0;
No WHERE, so there is no condition to fail. Every row in the table now has an average of 0 —
including the rows you never meant to touch. The statement is perfectly valid SQL, which is
exactly the problem: the DBMS is doing what you asked, not what you meant.
DELETE FROM Student; is the same story: every row is deleted and the table is left empty but
still existing.
Two habits that prevent this:
WHERE clause first, then fill in SET.SELECT before updating, to see exactly which rows will change.This is also why DBMS backup tools matter: an accidental UPDATE is a data-loss incident, and
recovery means restoring the backup and replaying the transaction log.
DELETE FROM Student WHERE StudentID = 10;
The row is gone; the table and its other rows are untouched.
Compare the three statements that students mix up:
| Statement | What survives |
|---|---|
DELETE FROM Student WHERE ... |
the table and all rows not matching |
DELETE FROM Student; |
the table, but no rows at all |
DROP TABLE Student; |
nothing — the table definition goes too |
Deleting rows is not always the right answer. If a student leaves, a school may keep the record
and add a Left date, because exam boards and audits need the history. DELETE is for data
that should never have been there.
Worked example: Worked example: a term's worth of changes
A Student table already holds rows for StudentID 1 to 8. Write the statements to:
(a) add Iris, StudentID 9, with an average of 63.5;
(b) record that Iris has now paid her fee;
(c) remove StudentID 4, who was entered twice by mistake.
The table is Student (StudentID, FirstName, FeePaid, Average, Enrolled).