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.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

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      | Rao

The 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.20

Warning: 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?

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.

  1. ```sql UPDATE tblBike SET DailyRate = DailyRate - 2 WHERE BikeType = 'Hybrid'; DELETE FROM tblHireItem WHERE BikeID = 'B06'; DELETE FROM tblBike WHERE BikeID = 'B06'; ```
  2. Check first with SELECT * FROM tblBike WHERE BikeType = 'Hybrid' to see which rows will change.
  3. 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.

Key idea: INSERT INTO table (fields) VALUES (...); UPDATE table SET field = value WHERE ...; DELETE FROM table WHERE .... Always use WHERE and test it as a SELECT first; referential integrity can block deletes. Access equivalents: append, update and delete queries.

Check yourself

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.