Skip to main content
ScotlandComputer ScienceSyllabus dot point

How do you add, change and remove records in a relational database, and what are the consequences?

Changing the data in a relational database with SQL INSERT, UPDATE and DELETE on prepopulated linked tables, and the implications of these operations.

An SQA Higher Computing Science answer on changing data in a relational database, covering the SQL INSERT, UPDATE and DELETE operations on prepopulated linked tables and the implications of each.

Generated by Claude Opus 4.811 min answer

Reviewed by: AI editorial process; not yet individually human-reviewed

Have a quick question? Jump to the Q&A page

Jump to a section
  1. What this key area is asking
  2. INSERT: adding records
  3. UPDATE: changing records
  4. DELETE: removing records
  5. Implications across linked tables
  6. Examples in context
  7. Try this

What this key area is asking

The SQA wants you to change the data in a relational database using SQL: INSERT to add records, UPDATE to change them and DELETE to remove them, on prepopulated databases of three or more linked tables. You should also understand the implications of these operations, especially the danger of a missing WHERE and the role of referential integrity.

INSERT: adding records

INSERT INTO Member (memberID, name, joinDate)
VALUES (104, 'Ravi Kaur', '2026-06-16');

The values must match the fields by position and type. If the table links to others, referential integrity means any foreign key you insert must refer to a record that already exists (you cannot record a loan for a member who is not in the Member table).

UPDATE: changing records

UPDATE changes the values in existing records. SET assigns the new value(s), and a WHERE clause restricts which records are affected.

UPDATE Product
SET stock = stock - 1
WHERE productID = 51;

This reduces the stock of one specific product. Removing the WHERE would reduce the stock of every product, which is almost never intended.

DELETE: removing records

DELETE removes records, with a WHERE choosing which.

DELETE FROM Loan
WHERE dateReturned IS NOT NULL;

This removes only loans that have been returned. As with UPDATE, the WHERE is essential:

Implications across linked tables

Because tables are linked, a change in one can affect others. Referential integrity controls this. Inserting a record with an invalid foreign key is rejected. Deleting a record that other records refer to is either blocked until the dependents are dealt with, or cascaded so the dependents are removed too, depending on the rules set in the design. This is what stops a deletion leaving orphan records pointing at something that no longer exists.

Examples in context

Every app that stores data uses these operations constantly: signing up runs an INSERT, editing your profile an UPDATE, closing an account a DELETE. The missing-WHERE disaster is real and famous - production systems have been wiped by an UPDATE or DELETE run without its WHERE, which is why developers test the WHERE with a SELECT first and keep backups. Referential integrity is why an online shop keeps your order history even after a product is discontinued: the database will not let the product be deleted while orders reference it. These operations complete the SQL picture begun by querying.

Try this

Q1. State which SQL operation adds a new record to a table. [1 mark]

  • Cue. INSERT.

Q2. State what happens if an UPDATE is run without a WHERE clause. [1 mark]

  • Cue. Every record in the table is updated.

Q3. Explain why referential integrity affects a DELETE on a record that other tables refer to. [2 marks]

  • Cue. To avoid orphan records, the database either blocks the delete or cascades it to the dependent records, keeping the foreign-key links valid.

Exam-style practice questions

Practice questions written in the style of SQA exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.

SQA Higher (style)4 marksA Product table has fields productID, name, price and stock. Write SQL to add a new product, then write SQL to reduce the price of every product over 100 by 10 percent.
Show worked answer →

Adding a record uses INSERT; changing existing records uses UPDATE with a WHERE.

INSERT INTO Product (productID, name, price, stock)
VALUES (51, 'Desk Lamp', 24.99, 30);

UPDATE Product
SET price = price * 0.9
WHERE price > 100;

The INSERT lists the fields and a matching set of values. The UPDATE sets the new price as a computed value (90 percent of the old) and the WHERE restricts it to products over 100.

Markers reward a correct INSERT with fields and matching values, and an UPDATE that sets price to 90 percent with the WHERE price > 100 condition.

SQA Higher (style)3 marksExplain what would happen if a DELETE statement to remove one member were run without a WHERE clause, and why referential integrity matters when deleting a member who has loans.
Show worked answer →

A DELETE without a WHERE clause deletes every record in the table, not just the one intended. So "DELETE FROM Member;" would remove all members, a serious data-loss error.

If a member who still has loans is deleted, referential integrity matters because the Loan table's foreign keys would then point at a member who no longer exists (orphan records). The database will either block the delete until the loans are removed, or remove the dependent loans too, depending on the rule set, so the links stay valid.

Markers reward the point that DELETE without WHERE removes all records, and that referential integrity prevents orphan loans either by blocking the delete or cascading it.

Related dot points

Sources & how we know this