Structured Query Language (SQL)

What you'll learn

• How SQL is used to define tables in a relational database.
• How to retrieve data using SELECT, including from multiple related tables.
• How to insert, update and delete records safely.
• How to avoid common exam mistakes with keys, joins and WHERE clauses.

The database ideas SQL relies on

Before writing SQL, you need the relational database vocabulary.

A relational database stores data in tables. A table is made up of records and fields.

For example:

• Student(StudentID, Forename, Surname)
• Course(CourseID, Title)
• Enrolment(StudentID, CourseID, Grade)

The bold attributes are the entity-identifier attributes: they identify a record uniquely.

In the example, Enrolment.StudentID refers to Student.StudentID, and Enrolment.CourseID refers to Course.CourseID.

This is what allows SQL to combine data from several tables.

What SQL is

Declarative means you describe what result you want, rather than giving a step-by-step algorithm for how the database management system should find it.

SQL keywords are conventionally written in uppercase, such as SELECT, FROM, WHERE and CREATE TABLE. Table and field names are usually written in a consistent style chosen by the database designer.

Defining a database table

SQL can be used as a data definition language, meaning it can define the structure of the database.

The main command you need here is CREATE TABLE.

A table definition usually states:

• the table name
• each field name
• each field’s data type
• key constraints, such as PRIMARY KEY and FOREIGN KEY
• other constraints, such as NOT NULL or UNIQUE

Common data types include:

A table definition for Student could be:

CREATE TABLE Student (
    StudentID INTEGER PRIMARY KEY,
    Forename VARCHAR(30) NOT NULL,
    Surname VARCHAR(30) NOT NULL
);

A linking table such as Enrolment can have a composite primary key, where two fields together identify a record:

CREATE TABLE Enrolment (
    StudentID INTEGER,
    CourseID INTEGER,
    Grade VARCHAR(2),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Retrieving data with SELECT

SQL retrieves data using SELECT.

The basic pattern is:

SELECT field1, field2
FROM TableName
WHERE condition;

• SELECT says which fields to output.
• FROM says which table to use.
• WHERE filters records so only matching rows are returned.

For example:

SELECT Forename, Surname
FROM Student
WHERE StudentID = 104;

This retrieves the forename and surname of the student whose StudentID is 104.

You can also sort results:

SELECT Forename, Surname
FROM Student
ORDER BY Surname ASC;

ASC means ascending order. DESC means descending order.

Conditions in WHERE

A WHERE condition can use comparison operators such as:

You can combine conditions using AND, OR and NOT.

For text pattern matching, SQL often uses LIKE:

SELECT Forename, Surname
FROM Student
WHERE Surname LIKE 'Patel%';

This retrieves students whose surname starts with Patel.

Retrieving data from multiple tables

To retrieve related data from multiple tables, use a JOIN.

The most common join for A-Level questions is an INNER JOIN, which returns only records where the join condition matches in both tables.

SELECT Student.Surname, Course.Title, Enrolment.Grade
FROM Student
INNER JOIN Enrolment
    ON Student.StudentID = Enrolment.StudentID
INNER JOIN Course
    ON Enrolment.CourseID = Course.CourseID
WHERE Course.Title = 'Computer Science';

The dot notation TableName.FieldName makes it clear which table a field comes from.

Inserting data

SQL uses INSERT INTO to add new records.

The safest form names the fields explicitly:

INSERT INTO Student (StudentID, Forename, Surname)
VALUES (105, 'Grace', 'Hopper');

When inserting related data, insert the parent record before the child record. For example, the student and course must exist before you insert an enrolment that refers to them.

INSERT INTO Enrolment (StudentID, CourseID, Grade)
VALUES (105, 12, 'A');

Updating data

SQL uses UPDATE to change existing records.

UPDATE Enrolment
SET Grade = 'A'
WHERE StudentID = 105
  AND CourseID = 12;

This changes only the grade for student 105 on course 12.

Deleting data

SQL uses DELETE FROM to remove records.

DELETE FROM Enrolment
WHERE StudentID = 105
  AND CourseID = 12;

This removes Grace Hopper’s enrolment on course 12, but does not delete the student or the course.

When tables are linked by foreign keys, the order of deletion matters. You usually delete child records first. For example, delete a student’s enrolments before deleting the student record itself, unless the database has been set up to cascade deletions.

SQL command summary