Community resourceWorksheet

OCR H446 1.3.2 SQL INSERT, DELETE and DROP

Part 10 of 13 · H446 1.3.2 · Databases

INSERT, DELETE and DROP change a database at three very different scales, and confusing them is a common way to lose marks in H446 1.3.2. Students predict the resulting state of each statement, repair ones that are incomplete or unsafe, and consider what referential integrity does to a deletion.

Students will:

  • distinguish adding a row, removing selected rows and removing a whole table
  • write an INSERT that names its fields explicitly rather than relying on field order
  • add a condition so that a deletion removes only the intended records
  • correct SQL statements with missing keywords or missing conditions
  • explain why deleting a referenced record may be rejected and give a safer approach

Inside: 7 explanation cells, 2 multiple-choice questions, 2 fill-in-the-blanks cells, 3 written answers and 1 Python task. 31 marks, about 25 to 40 minutes.

Series: H446 1.3.2 · Databases, part 10 of 13.

Shared by Coding PathwayVerified teacher

  • 15 cells
  • About 30 minutes
  • CC BY-SA 4.0
  • Shared 31 Aug 2026
  • Updated 3 Sept 2026

Preview

The whole resource, exactly as a class sees it. Answers and marking are held back.

SQL INSERT, DELETE and DROP

Modification statements change database state. INSERT adds data, DELETE removes selected records and DROP removes a database object such as a whole table.

By the end, you will be able to

  • interpret and write INSERT, DELETE and DROP;
  • distinguish their effects on rows and table structure;
  • predict database state before executing a statement;
  • repair unsafe or invalid modification SQL.

Reactivate: WHERE selects matching rows; referential integrity may reject a change that would create an invalid reference.

Predict the scale of the change

Different scales of SQL modificationINSERTadd a new rowtable remainsDELETEremove matching rowstable remainsDROPremove tabledata and structure goneBefore executing a modifying statement, predict exactly which rows or structure should remain.

Examples:

INSERT INTO Workshop VALUES ('W3', 'Data Art', 12)
DELETE FROM Workshop WHERE spaces = 0
DROP TABLE Workshop

A DELETE without a WHERE condition removes every row but leaves the empty table. DROP TABLE removes the table definition and its data. That distinction must be explicit.

Worked INSERT anatomy

INSERT INTO Workshop VALUES ('W3', 'Data Art', 12) supplies one value for each field in table order.

A safer explicit form is INSERT INTO Workshop(workshop_id, title, spaces) VALUES ('W3', 'Data Art', 12). Both show the complete new record. Text values need quotes; numeric values do not.

Before writing: check field order, data types, key uniqueness and any foreign-key reference.

Worked example
import sqlite3
mod_db = sqlite3.connect(':memory:')
m = mod_db.cursor()
m.execute('CREATE TABLE Workshop(workshop_id TEXT, title TEXT, spaces INTEGER)')
m.executemany('INSERT INTO Workshop VALUES (?, ?, ?)', [('W1','Robotics',5),('W2','Web Design',0)])
m.execute("INSERT INTO Workshop VALUES ('W3', 'Data Art', 12)")
m.execute("DELETE FROM Workshop WHERE spaces = 0")
for row in m.execute('SELECT * FROM Workshop'):
    print(row)
Multiple choice1 mark

Which statement removes all rows with no spaces while keeping the Workshop table?

  • ADROP TABLE Workshop WHERE spaces = 0
  • BDELETE FROM Workshop WHERE spaces = 0
  • CDELETE Workshop WHERE spaces = 0
  • DDROP FROM Workshop
Multiple choice1 mark

Which statement removes only inactive rows while keeping the table?

  • ADELETE * FROM Account WHERE status = 'inactive'
  • BDROP TABLE Account WHERE status = 'inactive'
  • CDELETE status FROM Account
  • DDELETE FROM Account WHERE status = 'inactive'
Fill in the blanks3 marks
gap 1 INTO Workshop VALUES ('W4', 'Networks', 6). gap 2 FROM Workshop WHERE workshop_id = 'W4'. gap 3 TABLE Workshop.
  • INSERT
  • DELETE
  • DROP
  • SELECT
  • WHERE

Guided checkpoint: repair three statements

  1. INSERT Workshop ('W5', 'Music Tech', 10)
  2. DELETE FROM Workshop when only W2 should be removed
  3. DROP Workshop

For each: name the intended effect, identify what is missing and write the corrected SQL.

Written answer8 marks

Correct the three SQL statements in the guided checkpoint.

Use W2's workshop_id in the DELETE condition.

Students type their answer here.

Written answer4 marks

EVENT E9 has several Booking rows referring to it. Explain why DELETE FROM Event WHERE event_id = 'E9' may be rejected and give one integrity-safe approach.

Use parent record, foreign keys and orphan records.

Students type their answer here.

Independent transfer: course enrolment

COURSE(course_id, title, capacity). Write each statement separately and state the resulting database state.

Written answer10 marks

Write SQL to (1) insert C17, 'Cyber Security', 24; (2) delete only course C09; and (3) remove the entire Course table. Explain the different result of statements 2 and 3.

Use INSERT, DELETE and DROP from Appendix 5d.

Students type their answer here.

Closed-book checkpoint

Complete each sentence from memory. There is no answer bank and correctness is held for teacher review.

Fill in the blanks4 marks
A completion 1 clause limits which records a deletion affects. Without it, every completion 2 is removed but the table remains. Removing the table itself also removes its completion 3 and stored completion 4.

Review your understanding

Before submitting, check that you can explain the main distinction in your own words, apply it in an unfamiliar context and justify each consequence rather than only naming a feature.