SQL: INSERT, UPDATE, DELETE, and CREATE TABLE

So far you have used SQL to interrogate data with SELECT. But before a single row can be queried, the tables must be defined, populated, and kept up to date. This lesson covers the SQL statements that build and change a database: CREATE TABLE (which defines structure), and INSERT, UPDATE and DELETE (which add, modify and remove rows). These statements are where the abstract design work of earlier lessons — your entity-relationship diagram, your normalised relations, your chosen primary and foreign keys — finally becomes a real, working schema. They are also where the single most dangerous SQL mistake lives: a missing WHERE clause on an UPDATE or DELETE that silently rewrites or destroys an entire table. By the end of this lesson you should be able to write a complete CREATE TABLE statement with the correct data types and constraints, populate it, and modify it safely — and explain exactly why each constraint matters.

---

Spec Mapping

This lesson addresses the AQA A-Level Computer Science (7517) Fundamentals of databases content on defining a database structure and manipulating data using SQL. It covers CREATE TABLE with appropriate data types and the constraints PRIMARY KEY, FOREIGN KEY and NOT NULL; the Data Manipulation Language statements INSERT INTO, UPDATE ... SET ... WHERE and DELETE FROM ... WHERE; and the consequences of omitting a WHERE clause. It builds directly on the SELECT/WHERE and join lessons (the same Students / Courses / Enrolments schema is reused) and underpins the transactions, integrity and security lessons that follow.

---

DDL vs. DML — Two Sublanguages of SQL

SQL is conventionally divided into sublanguages by what the statement does. Two matter most here:

Sublanguage	Full name	Statements	Acts on
DDL	Data Definition Language	CREATE, ALTER, DROP	The structure (tables, columns, constraints)
DML	Data Manipulation Language	SELECT, INSERT, UPDATE, DELETE	The data (rows) inside existing tables

A third, DCL (Data Control Language — GRANT and REVOKE), governs permissions and is covered in the security lesson. Telling DDL from DML is a common short-answer question: the test is "does it change the shape of the database (DDL) or the contents (DML)?". CREATE TABLE makes an empty table with no rows — that is structure, so it is DDL. INSERT puts a row into a table that already exists — that is data, so it is DML.

The lifecycle of a table runs through both sublanguages in turn: you define it with DDL (CREATE TABLE), then populate and maintain it with DML (INSERT, UPDATE, DELETE), querying it throughout with SELECT, and finally — if ever — remove it with DDL again (DROP TABLE). A useful mental check is that DDL statements are typically run rarely (when the application is first set up or upgraded) and by a privileged designer, whereas DML statements run constantly as the application does its everyday work. This division also maps onto permissions: an everyday application account is usually granted DML rights on specific tables but not DDL rights, so a bug or attack cannot drop or restructure tables — a least-privilege idea developed in the security lesson.

---

CREATE TABLE — Defining Structure

CREATE TABLE turns a relation from your design (e.g. Student(StudentID, FirstName, Surname, …)) into a real table. Each column is declared with a name, a data type, and zero or more constraints.

General form

CREATE TABLE TableName (
    ColumnName DataType [constraints],
    ...
    PRIMARY KEY (ColumnName),
    FOREIGN KEY (ColumnName) REFERENCES OtherTable(OtherColumn)
);

Worked CREATE — the Student table

CREATE TABLE Student (
    StudentID  INTEGER      PRIMARY KEY,
    FirstName  VARCHAR(50)  NOT NULL,
    Surname    VARCHAR(50)  NOT NULL,
    DateOfBirth DATE,
    Email      VARCHAR(100) UNIQUE,
    FormGroup  CHAR(3)
);

Effect: this creates a new, empty Student table whose structure is described below. No rows exist yet — SELECT * FROM Student; would return an empty result.

Column	Type	Constraint	Meaning
StudentID	INTEGER	PRIMARY KEY	Unique, not-null identifier for each student
FirstName	VARCHAR(50)	NOT NULL	Up to 50 characters; must be supplied
Surname	VARCHAR(50)	NOT NULL	Up to 50 characters; must be supplied
DateOfBirth	DATE	(none)	May be NULL (unknown)
Email	VARCHAR(100)	UNIQUE	No two students may share an email
FormGroup	CHAR(3)	(none)	Fixed 3-character code, e.g. '10A'

Common data types

Data type	Use	Example value
INTEGER / INT	Whole numbers, IDs, counts	1001
VARCHAR(n)	Variable-length text up to n characters	'Alice'
CHAR(n)	Fixed-length text of exactly n characters	'10A'
DATE	Calendar date (YYYY-MM-DD)	'2007-09-15'
REAL / FLOAT	Decimal / fractional numbers	72.5
BOOLEAN	A truth value	TRUE

Choosing the right type is a data-integrity decision: a DATE column simply cannot hold "banana", and an INTEGER cannot hold "10A", so the wrong value is rejected at the door. CHAR(3) for a fixed-width code and VARCHAR(50) for a free-text name are deliberate choices — CHAR(n) pads to a fixed width, VARCHAR(n) stores only the characters present.

Constraints — the rules the DBMS enforces

Constraint	What it enforces
PRIMARY KEY	Column value is unique and not null (entity integrity)
NOT NULL	A value must be supplied for this column
UNIQUE	No two rows may share this column's value (but NULLs allowed)
FOREIGN KEY ... REFERENCES	The value must match an existing primary key elsewhere (referential integrity)
DEFAULT value	If no value is given on insert, use this one
CHECK (condition)	The value must satisfy a boolean condition (a domain rule)

Choosing data types is a design decision

Selecting a column's type is not a formality — it shapes what the database will accept and how efficiently it stores and processes data. Three considerations guide the choice:

• Integrity: the type defines the column's base domain, so the wrong value is rejected automatically. Declaring DateOfBirth DATE makes it impossible to store '31st Febtober'; declaring StudentID INTEGER makes it impossible to store 'ABC'. The type is your first, free validation rule.
• Storage and performance: an INTEGER occupies a small fixed number of bytes and supports fast arithmetic and comparison; the same number stored as VARCHAR text wastes space and cannot be summed or compared numerically ('100' < '99' is true as text, because comparison is character-by-character). Choosing INTEGER for a quantity and DATE for a date keeps both the storage compact and the operations meaningful.
• CHAR(n) vs VARCHAR(n): use CHAR(n) for values that are always the same length (a form code '10A', a UK postcode area), because fixed-width storage is simple and fast; use VARCHAR(n) for values of varying length (names, emails), because it stores only the characters actually present rather than padding to the maximum. Declaring FirstName CHAR(50) would pad 'Eve' with 47 spaces — wasteful and a source of subtle comparison bugs.

These are exactly the justifications an exam expects when a question asks you to "state an appropriate data type for each field and justify your choice": not merely "VARCHAR for the name" but why — variable-length text, so VARCHAR avoids padding.

Adding a foreign key and a CHECK

Now the Enrolment table, which links students to courses and carries a grade. It declares two foreign keys and uses a CHECK to constrain the domain of Grade:

CREATE TABLE Enrolment (
    StudentID INTEGER NOT NULL,
    CourseID  CHAR(5) NOT NULL,
    EnrolmentDate DATE DEFAULT CURRENT_DATE,
    Grade     CHAR(1) CHECK (Grade IN ('A','B','C','D','E','U')),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID)  REFERENCES Course(CourseID)
);

Effect: Enrolment now has a composite primary key (StudentID, CourseID) — a student may take a given course only once. The two FOREIGN KEY clauses mean the DBMS will reject any enrolment whose StudentID is not a real student or whose CourseID is not a real course. The CHECK confines Grade to a valid set of letters, and DEFAULT CURRENT_DATE fills in today's date when none is supplied. This single statement is where the E-R diagram's many-to-many link entity and the relational-model integrity rules become enforced reality.

---

INSERT — Adding Rows

INSERT INTO adds new rows. The safe form names the columns explicitly, then lists matching values:

INSERT INTO Student (StudentID, FirstName, Surname, DateOfBirth, Email, FormGroup)
VALUES (1004, 'David', 'Williams', '2007-11-05', 'david@school.uk', '10A');

Effect: the Student table gains one row:

StudentID	FirstName	Surname	DateOfBirth	Email	FormGroup
1004	David	Williams	2007-11-05	david@school.uk	10A

Key points:

• Values must line up with the named columns, in order.
• Text and dates are wrapped in single quotes; numbers are not.
• Any column you omit must allow NULL or have a DEFAULT; omitting a NOT NULL column with no default raises an error.

Inserting several rows at once

INSERT INTO Student (StudentID, FirstName, Surname, DateOfBirth, FormGroup)
VALUES
    (1005, 'Eve',   'Brown',  '2007-06-20', '10B'),
    (1006, 'Frank', 'Taylor', '2008-02-14', '10A');

Effect: two rows are added. Note Email was omitted; because Email allows NULL, both rows store NULL for it. Had Email been NOT NULL, this statement would have been rejected.

Referential integrity at insert time

INSERT INTO Enrolment (StudentID, CourseID, Grade)
VALUES (9999, 'CS101', 'A');

Effect: rejected. Student 9999 does not exist in Student, so the foreign-key constraint refuses the insert. This is referential integrity doing its job — it is impossible to enrol a non-existent student.

---

UPDATE — Modifying Existing Rows

UPDATE changes values in rows that match its WHERE clause:

UPDATE Student
SET FormGroup = '10C'
WHERE StudentID = 1004;

Effect: the single row for David Williams changes form group; every other row is untouched.

StudentID	FirstName	Surname	FormGroup
1004	David	Williams	10C

You can set several columns in one statement, separated by commas:

UPDATE Student
SET Email = 'david.w@school.uk', FormGroup = '10B'
WHERE StudentID = 1004;

Effect: both Email and FormGroup are changed for student 1004 only.

The missing-WHERE catastrophe

The WHERE clause is what restricts an UPDATE to chosen rows. Omit it and the change applies to every row in the table:

-- DANGER: no WHERE clause
UPDATE Student
SET FormGroup = '10A';

Effect: every student in the school is forced into form 10A. The original form groups are gone and cannot be recovered without a backup. The DBMS does not warn you — the statement is perfectly valid SQL. Treat a WHERE-less UPDATE as a red flag and only write one when the question genuinely asks you to change all rows.

---

DELETE — Removing Rows

DELETE FROM removes rows that match its WHERE clause:

DELETE FROM Student
WHERE StudentID = 1006;

Effect: Frank Taylor's row is removed; the table structure and all other rows remain. You can delete by condition rather than by key:

DELETE FROM Enrolment
WHERE Grade = 'U';

Effect: every enrolment with grade 'U' is deleted, however many that is.

The missing-WHERE catastrophe, again

-- DANGER: no WHERE clause
DELETE FROM Student;

Effect: every student row is deleted, leaving an empty table. Crucially this differs from DROP TABLE Student, which would also remove the structure: after DELETE FROM Student the table still exists (you can INSERT into it immediately); after DROP TABLE Student you would have to CREATE it again first.

Referential integrity at delete time

DELETE FROM Student
WHERE StudentID = 1004;

Effect: if student 1004 still has rows in Enrolment, this delete is rejected (unless a cascade rule was defined), because removing the student would leave "orphan" enrolment rows whose StudentID referenced nobody. Referential integrity protects against orphans on deletion just as it does on insertion.

---

ALTER TABLE and DROP TABLE — Changing Structure

Both are DDL: they change the shape of the database.

-- Add a column
ALTER TABLE Student ADD PhoneNumber VARCHAR(15);

-- Remove a column
ALTER TABLE Student DROP COLUMN PhoneNumber;

Effect: the first adds an (initially NULL) PhoneNumber column to every existing row; the second removes it and its data entirely.

DROP TABLE Enrolment;

Effect: the whole Enrolment table — structure and every row — is permanently removed. Contrast the three "remove" operations:

Statement	Removes data?	Removes structure?	Sublanguage
DELETE FROM Enrolment WHERE ...	Matching rows	No	DML
DELETE FROM Enrolment	All rows	No	DML
DROP TABLE Enrolment	All rows	Yes	DDL

---

Worked Example — Building a Clubs Subsystem

Scenario: the school wants to record after-school clubs and which students belong to them. We define two tables, populate them, then make a couple of changes.

-- 1. Define the parent table
CREATE TABLE Club (
    ClubID     INTEGER PRIMARY KEY,
    ClubName   VARCHAR(50) NOT NULL,
    MeetingDay VARCHAR(10),
    RoomNumber VARCHAR(10)
);

-- 2. Define the link table (resolves the Student-Club many-to-many)
CREATE TABLE Membership (
    StudentID INTEGER NOT NULL,
    ClubID    INTEGER NOT NULL,
    JoinDate  DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (StudentID, ClubID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (ClubID)    REFERENCES Club(ClubID)
);

-- 3. Populate clubs
INSERT INTO Club (ClubID, ClubName, MeetingDay, RoomNumber)
VALUES (1, 'Chess Club',  'Tuesday',  'G12'),
       (2, 'Coding Club', 'Thursday', 'IT1');