You insert records with INSERT INTO, modify existing records with UPDATE, and remove records with DELETE FROM. For UPDATE and DELETE, a WHERE clause identifies which records should be affected.
These commands are part of SQL data manipulation language (DML) because they change the data stored in a relational database.
| Operation | Purpose | General syntax |
|---|---|---|
INSERT | Adds a new record | INSERT INTO table (columns) VALUES (values); |
UPDATE | Changes existing records | UPDATE table SET column = value WHERE condition; |
DELETE | Removes existing records | DELETE FROM table WHERE condition; |
Inserting a record
Suppose a Student table contains the fields studentID, name, and yearGroup:
INSERT INTO Student (studentID, name, yearGroup)
VALUES (104, 'Mina', 12);
The values must correspond to the listed fields and use compatible data types. Text values require quotation marks, while numerical values usually do not.
Updating a record
UPDATE Student
SET yearGroup = 13
WHERE studentID = 104;
The database searches for records satisfying the condition and changes their yearGroup value. The primary key is often used in the condition because it uniquely identifies one record.
Deleting a record
DELETE FROM Student
WHERE studentID = 104;
This removes the matching record but does not remove the table itself. The common misconception is that DELETE and DROP are equivalent: DELETE removes records, whereas DROP TABLE Student; removes the entire table structure.
A missing WHERE clause is especially dangerous:
DELETE FROM Student;
This deletes every record in the table. Similarly, an UPDATE statement without WHERE modifies every record.
IB Exam Technique
For A3.3 Database programming questions, use exact SQL syntax, match field names to values, quote strings correctly, and terminate statements with a semicolon. When updating or deleting one record, include a precise WHERE condition, preferably using the primary key, preferably and explain that omitting it affects all records.
