All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Inserting, updating and deleting data
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Specification 19.1.1 asks you to update data structures and insert, modify and delete data. You can type into a table or form, run action queries in the design view, or use SQL. All three change stored data, so you must target exactly the right records.
INSERT: adding records
Name the fields, then give the values in the same order. Text and dates go in quotes; numbers do not. Leaving out an AutoNumber-style key lets the database generate it:
INSERT INTO tblCustomer (FirstName, Surname, Town, Email)
VALUES ('Gita', 'Rao', 'Millbrook', 'gita.r@example.com');Result (1 row):
CustomerID | FirstName | Surname
-----------+-----------+--------
7 | Gita | RaoThe new customer was given the next ID automatically. In Access, an append query adds records from one table to another (useful after importing a file into a holding table).
UPDATE: changing records
UPDATE changes field values in every record that matches the WHERE clause. Mountain bike rates go up by 10%:
UPDATE tblBike SET DailyRate = DailyRate * 1.1
WHERE BikeType = 'Mountain';Result (2 rows):
BikeID | Model | DailyRate
-------+-----------+----------
B01 | Trail 500 | 19.80
B05 | Ridge Pro | 24.20Warning: without WHERE, every record is updated. Run the same WHERE as a SELECT first to check which records will change. In Access this is an update query (Update To row in the design grid).
DELETE: removing records
DELETE removes every record matching the WHERE clause:
DELETE FROM tblCustomer WHERE CustomerID = 6;This works because customer 6 has no hires. Referential integrity would reject deleting a customer with hires (unless cascade delete is on). DELETE FROM tblCustomer with no WHERE would try to remove everyone. In Access this is a delete query. Deletion cannot be undone, so back up before running action queries.
Data, structure, or both?
- Changing data: INSERT, UPDATE, DELETE, or editing in datasheet or form view.
- Changing structure: CREATE TABLE, ALTER TABLE, DROP TABLE, or editing in design view.
In the exam, screen print the design view of an action query (showing the criteria in full) and the effect on the data, for example the updated records or the record count.
Worked example
All Hybrid bikes' rates must fall by 2.00 and the Commuter (B06) is being sold. Write the SQL and say what to check first.
- ```sql UPDATE tblBike SET DailyRate = DailyRate - 2 WHERE BikeType = 'Hybrid'; DELETE FROM tblHireItem WHERE BikeID = 'B06'; DELETE FROM tblBike WHERE BikeID = 'B06'; ```
- Check first with SELECT * FROM tblBike WHERE BikeType = 'Hybrid' to see which rows will change.
- B06 appears in tblHireItem, so referential integrity blocks deleting it until its hire items are removed (or the bike is flagged as sold instead, which keeps the hire history: usually the better design).
Answer: An UPDATE with WHERE, and a delete that must respect referential integrity.
Check yourself
- After this insert:
```sql
INSERT INTO tblCustomer (FirstName, Surname, Town, Email) VALUES ('Gita', 'Rao', 'Millbrook', 'gita.r@example.com');
```
what does this query return?
```sql
SELECT COUNT(*) FROM tblCustomer
```
Show answer
7. There were 6 customers; after the insert there are 7.
- After this update:
```sql
UPDATE tblBike SET DailyRate = DailyRate * 1.1 WHERE BikeType = 'Mountain';
```
what is the new daily rate of B01?
```sql
SELECT ROUND(DailyRate, 2) FROM tblBike WHERE BikeID = 'B01'
```
Show answer
19.80. B01 (Trail 500) is a Mountain bike at 18.00; up 10% gives 19.80.
- How many rows does UPDATE tblBike SET DailyRate = DailyRate * 1.1 WHERE BikeType = 'Mountain' change?
Show answer
2. Two bikes are Mountain bikes: B01 and B05.
- What happens if you run UPDATE tblBike SET DailyRate = 15; with no WHERE clause?
- Only the first bike changes
- Nothing happens
- The statement is rejected as invalid
- Every bike's rate becomes 15
Show answer
Every bike's rate becomes 15. UPDATE without WHERE affects every row: a classic costly mistake.
- Why is DELETE FROM tblCustomer WHERE CustomerID = 3; rejected in the Harbour Bike Hire database?
- Customer 3 does not exist
- Customer 3 has hires, and referential integrity is enforced
- DELETE needs ORDER BY
- Customers can only be deleted by ID 6
Show answer
Customer 3 has hires, and referential integrity is enforced. SQLite reports: FOREIGN KEY constraint failed.
- In Access, which type of query adds records from an imported holding table to tblHire?
- A select query
- A crosstab query
- A parameter query
- An append query
Show answer
An append query. Append queries insert records into an existing table.
- Which INSERT statement is correctly written?
- INSERT tblBike VALUES B07, Gravel X, Road, 24;
- INSERT INTO tblBike (BikeID, Model, BikeType, DailyRate) VALUES ('B07', 'Gravel X', 'Road', 24);
- INSERT INTO tblBike SET BikeID = 'B07';
- ADD INTO tblBike ('B07', 'Gravel X', 'Road', 24);
Show answer
INSERT INTO tblBike (BikeID, Model, BikeType, DailyRate) VALUES ('B07', 'Gravel X', 'Road', 24);. INSERT INTO table (fields) VALUES (values), text in quotes.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: IAL Information Technology scheme of work for Unit 4
Last updated 8 November 2026. Spotted a mistake? Tell us.