Data Definition Language (DDL) and Data Manipulation Language (DML)

Structured Query Language (SQL) is the industry standard language for working with relational databases. SQL is a declarative language: a program says what result it wants, not how to get it. This is the opposite of imperative programming, where a program gives the sequence of steps the computer must follow.

SQL commands are split into groups. Two of them are in the syllabus:

• Data Definition Language (DDL) has the commands that define and change a database and its parts, such as tables and relationships: CREATE DATABASE, CREATE TABLE and ALTER TABLE.
• Data Manipulation Language (DML) has the commands that query the data and maintain it (insert, update and delete records): SELECT, INSERT INTO, UPDATE and DELETE FROM.

SQL syntax. These rules apply to every SQL statement:

• Statements are made of keywords such as CREATE, SELECT, INSERT and UPDATE. These are reserved words, so they must not be used as identifiers (the names of tables or fields).
• Keywords are usually written in capital letters, which makes statements easier to read. However, SQL is not case sensitive.
• Each statement ends with a semicolon. Some systems do not need it, but it is good practice to always include it.
• Longer statements are usually written over several lines, which again makes them easier to read.

For example, this statement selects the members whose surname is Jones:

SELECT FirstName, LastName, Phone
FROM Member
WHERE LastName = 'Jones';

A set of SQL statements written to do one job is called an SQL script.

DDL is the part of SQL used to create or change the structure of a database. DDL commands only create the structure: they do not put any data into the database.

To create a database, the syntax is:

CREATE DATABASE databasename;

For example, this creates the database for a sports club:

CREATE DATABASE SportsClub;

You cannot create a database with the same name as one that already exists. To create it again, the old version must first be deleted (dropped). Dropping a database deletes all of the data stored in it.

To create a table, the syntax is:

CREATE TABLE tablename (field1 datatype, field2 datatype, ...);

Each field definition is separated from the next by a comma. A statement can be written on one line, but it is much easier to read when split over several lines, as below. When you use several lines, do not forget the brackets and commas. Usually the first line has the opening bracket, and the last line has the matching closing bracket and the semicolon.

This statement sets up the table that stores the details of the sports club members:

CREATE TABLE Member (
    MemberId  CHAR(6),
    FirstName VARCHAR(30),
    LastName  VARCHAR(30),
    Phone     VARCHAR(15),
    PRIMARY KEY (MemberId));

For each field you must give the field name and its data type. Some data types also need a length. The primary key is given by the keywords PRIMARY KEY followed by the field name (or names) in brackets.

As in most programming languages, there are rules about the identifiers that can be used for table and field names:

• Names must begin with a letter.
• Names can only contain letters, numbers and underscores.
• Names cannot be SQL keywords (watch out for ORDER).
• Names should not include spaces.

Table names. A table is usually named after the entity it describes, and the name is usually singular, for example Customer, Booking or Appointment. Some developers start table names with tbl, for example tblCustomer, which makes it clear in program code that the name is a table.

Field names should make their purpose clear, and it is good practice to follow one style. These notes use upper camel case, for example CustomerId.

Each field should hold only a single item of data, because it is much easier for a computer to combine data than to split it. Instead of one field for a customer’s name, use a first name field and a surname field, and perhaps a title and other names too. Addresses are usually split into house name or number, street, town, county and postcode, often with an extra field for a village or other less common parts. Remember that people’s names often do not follow the pattern first name, space, last name, especially outside the English-speaking world, and addresses in other countries may have a very different format.

The same field name can be used in different tables. For example, the Member and Instructor tables both have FirstName and LastName fields, and this causes no problem. Where a field name is ambiguous (it appears in more than one table), put the table name and a full stop in front of it in your SQL statements, for example Member.FirstName. This is good practice.

Every field in a table is given one of these SQL data types:

A primary key is set with the keywords PRIMARY KEY, followed by the field name in brackets. If the table has a composite primary key, put all of its fields inside the brackets, separated by commas:

CREATE TABLE Certificate (
    MemberId       CHAR(6),
    CourseCode     CHAR(6),
    AssessmentDate DATE,
    InstructorId   INT,
    PRIMARY KEY (MemberId, CourseCode));

A foreign key is a field whose values exist as the primary key of another table. It is set with FOREIGN KEY, the field name in brackets, then REFERENCES followed by the other table and its primary key field in brackets. The Certificate table has three foreign keys:

CREATE TABLE Certificate (
    MemberId       CHAR(6),
    CourseCode     CHAR(6),
    AssessmentDate DATE,
    InstructorId   INT,
    PRIMARY KEY (MemberId, CourseCode),
    FOREIGN KEY (MemberId) REFERENCES Member (MemberId),
    FOREIGN KEY (CourseCode) REFERENCES Course (CourseCode),
    FOREIGN KEY (InstructorId) REFERENCES Instructor (InstructorId));

Note A foreign key line must always name the table and field it refers to. FOREIGN KEY (MemberId) on its own is an error.

Table constraints are rules that data must follow to be accepted into a table. They are usually set up as part of a CREATE TABLE statement. These are the common ones:

• NOT NULL makes sure a field cannot be left empty (null).
• UNIQUE makes sure every value in a field is different.
• PRIMARY KEY makes sure every record has a value in the field, and that the value is unique.
• FOREIGN KEY sets up referential integrity: a record with a foreign key value can only be added if a record with the same primary key value exists in the other table. This makes sure the links between related tables are kept.
• CHECK makes sure the values in a field meet a condition, for example that a field only accepts positive numbers.

This new definition of the Instructor table uses NOT NULL, so an instructor record cannot be created without a first name and a last name:

CREATE TABLE Instructor (
    InstructorId INT,
    FirstName    VARCHAR(30) NOT NULL,
    LastName     VARCHAR(30) NOT NULL,
    Email        VARCHAR(30),
    PRIMARY KEY (InstructorId));

When an exam question asks you to include constraints (restrictions) on the data, NOT NULL on the fields that must always have a value, and the primary key, are the usual answers. The primary key can also be given on the field’s own line, for example StaffID INTEGER NOT NULL PRIMARY KEY,

ALTER TABLE changes the definition of a table that already exists. The most common change is to add a new field: give its name and data type after ADD.

ALTER TABLE Course
ADD StartDate DATE;

More than one field can be added at once, separated by commas (this is the form mark schemes use; some DBMSs need the word ADD before each field):

ALTER TABLE Member
ADD DateJoined DATE, Junior BOOLEAN;

ALTER TABLE can also add a primary key or a foreign key to a table that was created without one:

ALTER TABLE Certificate
ADD PRIMARY KEY (MemberId, CourseCode);

ALTER TABLE Certificate
ADD FOREIGN KEY (MemberId) REFERENCES Member (MemberId);

Users of any database need to add new data, update it to keep it correct, and delete it when it is no longer needed. SQL DML has commands for all three tasks.

New records are added to a table with INSERT INTO. The syntax is:

INSERT INTO table (field1, field2, ...)
VALUES (value1, value2, ...);

For example, the sports club has a new basketball course. This statement adds it to the Course table:

INSERT INTO Course (CourseCode, Description, Fee)
VALUES ('CR7531', 'Basketball', 25);

When you insert records, the data must follow any constraints set in the table design. Some fields may not be left blank, or may only accept certain values, and a foreign key constraint may mean the DBMS checks another table for a matching primary key value.

You must also think about data types. Here CourseCode and Description are text fields, so their values go in quote marks. Fee is a number, so it does not.

More than one record can be added at a time, with a comma between each set of values. The sports club has taken on three more instructors for the basketball course. This statement adds them to the Instructor table:

INSERT INTO Instructor (InstructorId, FirstName, LastName, Email)
VALUES (6, 'Bailey', 'Campbell', 'cbailey@example.net'),
       (7, 'Jaden', 'Beckford', 'jaden.beckford@example.edu'),
       (8, 'Jasmine', 'Ansari', 'jansari@example.com');

If you give a value for every field, in the same order as the fields in the table, the list of field names can be left out:

INSERT INTO Course
VALUES ('CR7531', 'Basketball', 25);

Records are changed with an UPDATE statement. The syntax is:

UPDATE table
SET field1 = value1, field2 = value2, ...
WHERE criteria;

For example, the sports club has decided to lower the price of one of its courses. This statement changes the fee for Tennis in the Course table from 30 to 25:

UPDATE Course
SET Fee = 25
WHERE Description = 'Tennis';

Think carefully about the criteria in the WHERE clause. The same result could have been achieved with:

WHERE CourseCode = 'DG0011';

If there were two courses with the description Tennis, this version would only change the fee of the course with the code DG0011, not both. Neither method is wrong: it depends on what you want to do. But to update the phone number of Abdel Patel, it would not be wise to use FirstName = 'Abdel' as the criteria, because there may be several members called Abdel and every one of them would get the new number.

Records are deleted from a table with DELETE FROM. The syntax is:

DELETE FROM table
WHERE criteria;

This statement deletes Joe Donald’s record from the Member table:

DELETE FROM Member
WHERE MemberId = '131092';

The record to delete has been chosen by its primary key. If the criteria had been WHERE FirstName = 'Joe' AND LastName = 'Donald', every record that matched would be deleted. As with UPDATE, a DELETE FROM statement with no WHERE clause deletes every record in the table.

The SELECT examples in the rest of 8.3 use this sample data from the sports club database. Primary keys are underlined in a design; here they are the first field of each table, and Certificate has the composite primary key (MemberId, CourseCode).

Member (MemberId, FirstName, LastName, Phone)

Course (CourseCode, Description, Fee)

Instructor (InstructorId, FirstName, LastName, Email)

Certificate (MemberId, CourseCode, AssessmentDate, InstructorId)

SELECT is used to extract data from a database. At its simplest, the syntax is:

SELECT field1, field2, ...
FROM table;

SELECT is followed by a list of field (attribute) names, and FROM is followed by the name of the table the fields come from. For example:

SELECT FirstName, LastName, Phone
FROM Member;

This gives the result below. The columns are in the order given in the field list, and the field names are the column headings.

Keywords such as SELECT and FROM are written in capitals because it makes statements easier to read, even though SQL is not case sensitive. Put a semicolon at the end of the last line to finish the statement.

Selecting every field. Instead of listing the fields, you can return every field with SELECT *. The Instructor table has the fields Instructor (InstructorId, FirstName, LastName, Email), and this query returns all of them:

SELECT *
FROM Instructor;

Only use SELECT * when every field is needed. Extra data makes the results harder to work with. For example, when you search for a song on a music streaming service, you do not want to see every field stored about it, such as SongID, SongURL, MusicLabel and ReleasedDate.

The results of a SELECT statement can be sorted by adding an ORDER BY clause:

SELECT field1, field2, ...
FROM table
ORDER BY field(s) ASC|DESC;

• Ascending order (ASC): from low to high, A to Z or 1 to 9.
• Descending order (DESC): from high to low, Z to A or 9 to 1.

Ascending order is the default, so ASC does not have to be written:

SELECT MemberId, FirstName, LastName
FROM Member
ORDER BY LastName;

Jack and Emily both have the surname Marr. SQL does not promise any order for records with equal values in the sort field. To be sure of the order, sort on a second field as well: ORDER BY LastName, FirstName puts Emily before Jack. A table can also be sorted by a field that is not in the list of selected fields.

To sort in descending order, write DESC after the field name. This sorts by FirstName from Z to A:

SELECT MemberId, FirstName, LastName
FROM Member
ORDER BY FirstName DESC;

On a music service you rarely want every song in the database: perhaps only the songs by one artist, or on one album. A WHERE clause filters the records, so only those that meet the criteria are returned:

SELECT field1, field2, ...
FROM table
WHERE selection-criteria;

For example, a sports club member wants to know which courses cost less than £25:

SELECT Description, Fee
FROM Course
WHERE Fee < 25;

Think about the data type of each field in the criteria. Fee is stored as a number, so it is compared with a number, without quotes. Text fields need quotation marks:

SELECT FirstName, LastName, Phone
FROM Member
WHERE LastName = 'Marr';

The criteria after WHERE can be as complex as needed, and can combine several operators:

For example, this query finds all the members who do not have the surname Marr:

SELECT *
FROM Member
WHERE LastName <> 'Marr';

Criteria can be joined with the logical operators AND, OR and NOT. Brackets can be used to force the order in which they are worked out, or just to make the statement easier to read.

SELECT *
FROM Member
WHERE FirstName = 'Emily'
AND LastName = 'Marr';

A common mistake is to write:

WHERE CourseCode = 'DG0011' OR 'CR0001'

The correct version is:

WHERE CourseCode = 'DG0011' OR CourseCode = 'CR0001'

The field name must be repeated in the second part. Without it, the second part is not a comparison at all, so the query gives the wrong records (often only the DG0011 ones) or an error.

Note Use AND when a record must meet both conditions, and OR when it may meet either. "Members called Emily Marr" needs AND; "courses DG0011 or CR0001" needs OR.

Two more keywords are useful in a WHERE clause:

LIKE matches text against a pattern made with wildcards. Check which wildcard symbols your DBMS uses. The most common are:

• the percent sign %, which stands for zero, one or more characters
• the underscore _, which stands for exactly one character

So LIKE 'SO%' finds every code that starts with SO, and LIKE 'CR00_4' finds CR0014. Microsoft Access uses * instead of %, and mark schemes accept either. LIKE is how you count, for example, the courses whose code starts with CR:

SELECT COUNT(CourseCode)
FROM Course
WHERE CourseCode LIKE 'CR%';

On the sample data this gives 3 (CR0001, CR0020 and CR0014).

BETWEEN selects values in a range, and includes both end values: Fee BETWEEN 30 AND 50 gives the courses that cost exactly 30 or exactly 50 as well as anything in between.

Text. A string value in the criteria must always be enclosed in quotation marks. Usually single or double quotes can be used, although single quotes are the standard:

SELECT FirstName, LastName, Phone
FROM Member
WHERE LastName = 'Patel';

If the string itself contains a quotation mark, it must be escaped by writing it twice. To find O’Brien, write:

WHERE LastName = 'O''Brien'

In an exam answer, just use double quotes around the string: WHERE LastName = "O'Brien".

The text inside the quotation marks can be case sensitive (it depends on the DBMS), so match the case of the stored data exactly. In many systems WHERE LastName = 'jones' will not match a surname entered as Jones.

Numbers. When data is stored as a number, give the value with no quotes:

WHERE InstructorId = 3

Fields for money are stored without a currency symbol. WHERE Fee > £5 gives the error "No such column: £5", because the DBMS thinks £5 is the name of a field. Write WHERE Fee > 5.

Dates. In exam questions, write a date in the same format as the sample data, between single quotes or between # marks:

WHERE AssessmentDate > '01/06/2019'

WHERE AssessmentDate <= #31/12/2020#

Take care with a range of dates. To select the dates in January 2020, write either of these:

WHERE AssessmentDate >= '01/01/2020'
  AND AssessmentDate <= '31/01/2020'

WHERE AssessmentDate
  BETWEEN '01/01/2020' AND '31/01/2020'

In the first form, use >= and <=, not > and <, or you will miss the first and last days of the month.

Many queries need data from more than one table. The two tables must share a common field: a primary key in one table and the matching foreign key in the other. The syntax is:

SELECT field1, field2, ...
FROM table1, table2
WHERE join-criteria;

In the query below, each field name starts with its table name. This is not always needed, but it prevents problems with ambiguous field names: for example, Member and Instructor both have FirstName and LastName fields. The join criteria use MemberId, the primary key of Member, and MemberId, the foreign key in Certificate. Both fields are called MemberId, so here the table names are essential.

SELECT Member.FirstName, Member.LastName,
       Certificate.CourseCode, Certificate.AssessmentDate
FROM Member, Certificate
WHERE Member.MemberId = Certificate.MemberId;

Note In the syllabus, a query uses at most two tables.

In a query that takes data from more than one table, you must always say how the tables are joined.

The query in the last section makes what is called an INNER JOIN. It shows the details of every member who has one or more certificates. A member with no certificates would not appear in the results.

The Venn diagram shows how the tables are combined. Each table holds a set of records, with no repeats because each record has its own primary key. An INNER JOIN gives the intersection of the two sets: only the records that have a match in both tables.

There is another way to write the same query:

SELECT field1, field2, ...
FROM table1
INNER JOIN table2
ON join-criteria;

SELECT Member.FirstName, Member.LastName,
       Certificate.CourseCode, Certificate.AssessmentDate
FROM Member
INNER JOIN Certificate
ON Member.MemberId = Certificate.MemberId;

Both forms give exactly the same result, and both are accepted in the exam.

Adding more criteria. Often you also need other selection criteria. This query (in the first form) selects the certificates that were awarded on or after 1 June 2019:

SELECT Member.FirstName, Member.LastName,
       Certificate.CourseCode, Certificate.AssessmentDate
FROM Member, Certificate
WHERE Member.MemberId = Certificate.MemberId
AND Certificate.AssessmentDate >= '01/06/2019';

With INNER JOIN, the join goes after ON and the extra criteria go in a WHERE clause:

SELECT Member.FirstName, Member.LastName,
       Certificate.CourseCode, Certificate.AssessmentDate
FROM Member
INNER JOIN Certificate
ON Member.MemberId = Certificate.MemberId
WHERE Certificate.AssessmentDate >= '01/06/2019';

SQL can do calculations on the selected records. Three functions are in the syllabus:

• SUM(field) adds up the values in a numeric field.
• AVG(field) works out the mean of the values in a numeric field.
• COUNT(field) counts how many records there are.

SELECT AVG(Fee)
FROM Course;

This gives one value: 260 ÷ 7 = 37.14 (to 2 decimal places).

SELECT SUM(Fee)
FROM Course;

This gives 260, the total of all seven course fees.

Naming the result. A calculated column has no field name of its own. Exam questions often ask for "a suitable field name": give one with AS after the calculation. With a WHERE clause, only the records that meet the criteria are counted:

SELECT COUNT(CourseCode) AS CheapCourses
FROM Course
WHERE Fee < 25;

The functions work on two tables too. This counts the certificates held by Precious Jones:

SELECT COUNT(Certificate.CourseCode)
       AS Certificates
FROM Member
INNER JOIN Certificate
ON Member.MemberId = Certificate.MemberId
WHERE Member.FirstName = 'Precious'
AND Member.LastName = 'Jones';

GROUP BY puts the selected records into groups that have the same value in a field, and then works out SUM, AVG or COUNT once for each group. Use it when a question says "for each" or "of each".

This query gives the number of certificates awarded for each course:

SELECT CourseCode, COUNT(MemberId)
FROM Certificate
GROUP BY CourseCode;

The field you group by is usually also in the SELECT list, so you can see which group each total belongs to. Any other field in the SELECT list should be inside SUM, COUNT or AVG, or be a field that has only one value in each group, such as a member’s name when you group by MemberId (as in the worked example below). Add an ORDER BY clause if the groups must be in a particular order.

The clauses always go in this order:

SELECT ...
FROM ...
INNER JOIN ... ON ...
WHERE ...
GROUP BY ...
ORDER BY ...;

Exam questions also give you an SQL script and ask what it does, or ask you to find and correct its errors. Read it one clause at a time and check each line against the syntax in these notes.

Worked example 1. This script should create a Booking table for the sports club, with BookingId as the primary key and MemberId as a foreign key:

01 CREATE TABLE Booking (
02     BookingId INTEGER
03     MemberId CHAR(6),
04     BookingDate DATE,
05     PRIMARY KEY BookingId,
06     FOREIGN KEY (MemberId));

Worked example 2. This query should return the number of certificates held by the member with the surname Jones:

SELECT SUM(Certificate.CourseCode) AS Certificates
FROM Member, Certificate
WHERE MemberId = MemberId
OR LastName = Jones;