The big idea: INSERT adds a row. UPDATE changes values in rows that already exist. DELETE removes rows.
All three are DML — the table's structure is untouched.
INSERT INTO Student (StudentID, Name, YearGroup)
VALUES (7, 'Eli', 11);
UPDATE Student
SET YearGroup = 12
WHERE StudentID = 7;
DELETE FROM Student
WHERE StudentID = 7;Free preview
This is the free notes preview
You're reading the free notes. Aimnova Pro unlocks the full study experience — and you can try it with your first topic free to keep:
- FlashcardsLock in vocabulary and key terms with spaced repetition.
- Practice questionsAnswer exam-style questions and get instant AI marking.
- Mock exams & past-paper vaultSit full mocks and see exactly how examiners award marks.
- Personalised study planA daily plan built around your exam date and weak areas.
The most expensive mistake in SQL: An UPDATE or DELETE with no WHERE clause applies to every row in the table.
The database does not warn you. It does exactly what you asked, to all of it.
With a WHERE clause
- UPDATE Student SET YearGroup = 12 WHERE StudentID = 7
- Changes one row — the one identified
- Run a SELECT with the same WHERE first, to see what it will hit
- Habit worth building before you ever touch real data
Without one
- UPDATE Student SET YearGroup = 12
- Puts every student in Year 12
- No warning, no confirmation, and no undo
- The same omission on DELETE empties the table
The rule of thumb: Write the WHERE clause first, test it with a SELECT, and only then change SELECT into UPDATE or DELETE.
It costs ten seconds and it is why professionals still do it.
Stop wasting time on topics you know
Our AI identifies your weak areas and focuses your study time where it matters. No more overstudying easy topics.
An index is a second structure: An index is a separate sorted structure the database keeps so it can find rows quickly, instead of reading the whole table.
It makes reads fast — and it has to be kept up to date whenever the data changes.
Why an index speeds reads up
Without one, finding rows means checking every row in the table.
With one, the database goes more or less straight to them — the same reason a book's index beats reading every page.
Why it slows writes down
Every INSERT, UPDATE and DELETE on an indexed column must change the index as well as the row.
More indexes means faster reads and slower writes. That is the trade, and it is unavoidable.
Fragmentation
After a great many changes, an index becomes untidy — its entries no longer sit in an efficient order — and it gradually stops helping as much.
This is why a heavily-updated table can slow down over months with no change to the queries.
Rebuilding and reorganising
Reorganising tidies the index in place. Rebuilding constructs it again from scratch, which is more thorough and more disruptive.
Both are usually scheduled for quiet hours, because they load the system heavily.
The bulk-load trick: When loading a very large amount of data, administrators often drop the indexes first, load everything, and rebuild them afterwards.
Building an index once at the end is far cheaper than updating it for every row on the way in.
How this is tested — you must write the statement with a correct WHERE, or explain the index trade. It comes up two ways:
Paper 1 Section A
- Explain how data is updated, or write the statement, 2-4 marks
- State the effect of omitting WHERE
- Explain the performance implications of indexes
Paper 1 Section B — case study
- Write the statements the case study would need
- Explain why their updates have become slow
The classic trap: Saying indexes "make the database faster". They make reads faster and writes slower. A question about slow updates is usually asking you to notice exactly that.
A shop's stock table is indexed on ProductID and QuantityInStock. Staff report that stock updates have become very slow. Explain why, and what could be done.
Model answer plan
See the answer broken into clear points, with guidance on how each point earns marks.