Computer ScienceCore24 min read

SQL: Defining and Querying a Database

Two sublanguages — one builds the tables, the other asks them questions

This topic appears in:

A Level Computer Science (9618) · ch.8
01

DDL and DML

SQL splits into two parts with different jobs. Data Definition Language creates and alters the structure — tables, columns, data types, keys. Data Manipulation Language works with the contents — inserting, updating, deleting and above all querying rows.

A useful way to hold the distinction: DDL is used rarely, when the database is designed or changed. DML runs constantly, every time the application does anything at all.

SublanguageCommandsActs on
DDLCREATE TABLE, ALTER TABLE, DROP TABLEthe structure
DDLPRIMARY KEY, FOREIGN KEY, NOT NULLconstraints on the structure
DMLSELECT, INSERT, UPDATE, DELETEthe data in the rows
CREATE TABLE Student (StudentIDINTEGERPRIMARY KEY,SurnameVARCHAR(30) NOT NULL,DateOfBirth DATE,ClassIDINTEGER,FOREIGN KEY (ClassID) REFERENCES Class(ClassID));the primary key enforces uniqueness; the foreign key enforces referential integrity
PRIMARY KEY
uniquely identifies a rowcannot be null or duplicated
FOREIGN KEY
points at another table's keyprevents references to rows that do not exist
NOT NULL
a constraintthe column must always hold a value
02

The shape of a query

Almost every question you will be asked to answer takes the same six-clause shape, and the clauses must appear in this order even though the database does not evaluate them in it.

What surprises people is that WHERE filters individual rows before any grouping, while HAVING filters groups after aggregation. So a condition on a raw column belongs in WHERE, and a condition on a COUNT or SUM belongs in HAVING. Putting an aggregate in a WHERE clause is an error.

SELECTcolumns, or aggregate functionsFROMtableJOINother ON matching conditionWHEREcondition on individual rowsGROUP BY columnHAVINGcondition on the groupsORDER BY column ASC | DESC;written in this order, but WHERE is applied before GROUP BY and HAVING after it
WHERE
filters rowsbefore grouping — no aggregates allowed
GROUP BY
collapses rows into groupsone output row per distinct value
HAVING
filters groupsafter aggregation — aggregates allowed here
Worked example

Using tables Student(StudentID, Surname, ClassID) and Class(ClassID, ClassName), write a query listing each class name with the number of students in it, for classes with more than 20 students, largest first.

  1. The two tables must be joined on ClassID: FROM Student JOIN Class ON Student.ClassID = Class.ClassIDThe class name lives in one table and the students in the other, so a join is required.
  2. Group by class name: GROUP BY Class.ClassNameOne output row per class, which is what "each class" asks for.
  3. Count the students: SELECT Class.ClassName, COUNT(*) AS TotalCOUNT(*) counts the rows in each group. Naming it with AS makes the output readable.
  4. Filter the groups: HAVING COUNT(*) > 20This is a condition on an aggregate, so it must be HAVING, not WHERE.
  5. Order the results: ORDER BY Total DESC;DESC gives largest first. The alias defined in SELECT can be used here.

SELECT Class.ClassName, COUNT(*) AS Total FROM Student JOIN Class ON Student.ClassID = Class.ClassID GROUP BY Class.ClassName HAVING COUNT(*) > 20 ORDER BY Total DESC;

Follow the rows through each clause. WHERE removes individual rows before grouping; HAVING removes whole groups afterwards — which is exactly why an aggregate condition cannot go in WHERE.

WHERE cannot contain an aggregate

WHERE COUNT(*) > 20 is invalid, because WHERE is applied to individual rows before any counting has happened — there is nothing to count yet. The condition belongs in HAVING. Conversely, filtering on a plain column belongs in WHERE, where it removes rows early and makes the query faster.

03

Changing the data

Three DML commands modify rows rather than reading them, and two of them are dangerous in the same way: they act on every row that matches, and if no condition is given, that means every row in the table.

INSERT INTO Student (StudentID, Surname, ClassID)VALUES (1024, 'Ahmed', 7);UPDATE StudentSETClassID = 8WHERE StudentID = 1024;DELETE FROM StudentWHERE StudentID = 1024;UPDATE and DELETE without a WHERE clause affect every row in the table
INSERT INTO
adds a rowthe column list and value list must correspond
UPDATE … SET
changes existing rowsalways needs a WHERE unless you mean all of them
DELETE FROM
removes rowsDROP TABLE removes the table itself, which is different

Points examiners test

  1. DELETE removes rows; DROP removes the whole table structure.
  2. A missing WHERE on UPDATE or DELETE affects every row.
  3. Aggregate functions: COUNT, SUM, AVG, MAX, MIN.
  4. Strings go in single quotes; numbers do not.
  5. LIKE 'A%' matches anything starting with A — % is any sequence of characters.
  6. Qualify column names as Table.Column whenever a join makes a name ambiguous.
04

Joins: bringing two tables together

Normalisation deliberately splits data across tables so nothing is stored twice. The cost is that answering a real question usually needs data from more than one of them, and a join is how they are recombined.

A join matches rows from two tables wherever a specified condition holds — almost always a foreign key in one table equalling a primary key in the other. The result behaves like a single wide table for the rest of the query.

The one that appears in nearly every exam question is the INNER JOIN, which keeps only rows that match on both sides. A student with no class, or a class with no students, simply does not appear in the output — which is usually what is wanted, and occasionally a trap.

SELECT Student.Surname, Class.ClassNameFROMStudentINNER JOIN ClassONStudent.ClassID = Class.ClassID;the ON condition is nearly alwaysforeign key = primary keyrows without a match on both sides are excluded
INNER JOIN
the default kindkeeps only matching rows
ON
the matching conditionusually foreign key = primary key
Table.Column
qualified namesrequired when a name appears in both tables

Forgetting the ON condition

A join written without its ON clause pairs every row of one table with every row of the other — a cross join. Two tables of a thousand rows produce a million meaningless rows. If a query returns far more than expected, a missing or wrong join condition is the first thing to check.

05

Reading a query the way the database does

SQL is written in one order and evaluated in another, and knowing the evaluation order explains several rules that otherwise look arbitrary.

The database starts with FROM and any joins, assembling the working set of rows. Then WHERE discards rows. Then GROUP BY collapses what remains into groups, at which point aggregates are computed. Then HAVING discards groups. Only then is SELECT applied, choosing the columns, and finally ORDER BY sorts the output.

That order explains why an aggregate cannot appear in WHERE — the grouping has not happened yet. It also explains why an alias defined in SELECT can be used in ORDER BY but not in WHERE: by the time the sort runs the alias exists, but when the filter ran it did not.

Order runClauseWhat it does
1FROM / JOINassemble the rows
2WHEREdiscard individual rows
3GROUP BYcollapse rows into groups
4HAVINGdiscard whole groups
5SELECTchoose the columns
6ORDER BYsort the result

Practice questions

5 questions · 15 marks · full working on every one

Try each one on paper first, then open the working. The marks are shown where they are actually awarded, because that is where they are actually lost.

Short questions

4 · 9 marks

Two marks each, in the style of the short-question section of the paper. Answer in two or three lines.

SQ1[2 marks]
State the difference between DDL and DML, giving one command from each.
Model answer

DDL defines or alters the structure of the database — for example CREATE TABLE. DML operates on the data held in the rows — for example SELECT or INSERT.

Examiner tip. One mark for the distinction, one for correct example commands from each.

SQ2[2 marks]
Explain why WHERE COUNT(*) > 5 is not valid, and state what should be used instead.
Model answer

WHERE is applied to individual rows before grouping, so at that point no counting has taken place and there is nothing to compare. A condition on an aggregate must appear in a HAVING clause, which is applied to the groups after aggregation.

Examiner tip. The order of evaluation is the reason, and stating HAVING alone will not earn both marks.

SQ3[3 marks]
Write an SQL statement to create a table Book with fields ISBN (primary key), Title (text, required) and YearPublished (integer).
Model answer

CREATE TABLE Book (ISBN VARCHAR(13) PRIMARY KEY, Title VARCHAR(100) NOT NULL, YearPublished INTEGER);

Examiner tip. Three marks: the CREATE TABLE structure, the primary key, and NOT NULL for the required field. Data types need only be sensible.

SQ4[2 marks]
State the difference between DELETE FROM Student; and DROP TABLE Student;
Model answer

DELETE FROM Student; removes every row but leaves the table structure in place, so it can still be used. DROP TABLE Student; removes the table itself — structure and data — so it no longer exists.

Examiner tip. The distinction between the rows and the structure is the mark.

Exam questions

1 · 6 marks

Multi-part questions with a full mark scheme.

Q1[6 marks]
A database has Employee(EmpID, Name, DeptID, Salary) and Department(DeptID, DeptName).
(a) Write a query listing the names of employees earning more than 50000, alphabetically.
(b) Write a query giving each department name and its average salary.
(c) Modify (b) to show only departments whose average salary exceeds 40000.
(d) State what happens if an UPDATE statement is run without a WHERE clause.
Mark scheme
  1. (a) SELECT Name FROM Employee WHERE Salary > 50000 ORDER BY Name;A condition on a plain column belongs in WHERE.[1]
  2. ORDER BY Name gives alphabetical order (ASC is the default).The ordering clause is a separate mark.[1]
  3. (b) SELECT Department.DeptName, AVG(Salary) FROM Employee JOIN Department ON Employee.DeptID = Department.DeptIDThe join is needed because the two pieces of information sit in different tables.[1]
  4. GROUP BY Department.DeptName;Grouping is what produces one row per department.[1]
  5. (c) Add HAVING AVG(Salary) > 40000 after the GROUP BY clause.An aggregate condition, so HAVING rather than WHERE.[1]
  6. (d) Every row in the table is updated, because there is no condition restricting which rows match.The consequence must be stated as affecting all rows.[1]

(a) WHERE + ORDER BY; (b) JOIN + GROUP BY; (c) HAVING; (d) every row is changed