SQL: INSERT, UPDATE, DELETE, and CREATE TABLE

Beyond querying data with SELECT, SQL provides statements to modify data and define table structures. These are part of the Data Manipulation Language (DML) and Data Definition Language (DDL).

---

Data Definition Language (DDL) — CREATE TABLE

DDL statements define the structure of the database. The most important DDL statement at A-Level is CREATE TABLE.

Basic Syntax

CREATE TABLE TableName (
    Column1 DataType CONSTRAINT,
    Column2 DataType CONSTRAINT,
    ...
    PRIMARY KEY (Column1)
);

Example

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

Common Data Types

Data Type	Description
INTEGER / INT	Whole numbers
VARCHAR(n)	Variable-length text up to n characters
CHAR(n)	Fixed-length text of exactly n characters
DATE	Date value (YYYY-MM-DD)
FLOAT / REAL	Decimal numbers
BOOLEAN	TRUE or FALSE
TEXT	Large text blocks

Common Constraints

Constraint	Effect
PRIMARY KEY	Uniquely identifies each row; cannot be NULL
NOT NULL	The field must contain a value
UNIQUE	No two rows can have the same value in this field
FOREIGN KEY	References the primary key of another table
DEFAULT	Sets a default value if none is provided
CHECK	Validates data against a condition

Foreign Key Example

CREATE TABLE Grades (
    GradeID INTEGER PRIMARY KEY,
    StudentID INTEGER NOT NULL,
    Subject VARCHAR(50) NOT NULL,
    Score INTEGER CHECK (Score >= 0 AND Score <= 100),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

---

Data Manipulation Language (DML)

INSERT — Adding New Records

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

Key points:

• The values must match the order of the specified columns.
• String and date values are enclosed in single quotes.
• If a column has a DEFAULT value or allows NULL, you can omit it from the column list.

Inserting multiple rows:

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

---

UPDATE — Modifying Existing Records

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

Key points:

• The WHERE clause is essential. Without it, every row in the table will be updated.
• You can update multiple columns at once:

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

Dangerous — updating all rows: