Structured Query Language (SQL)

CIE A-Level Computer Science· Unit 8: Databases, Topic 3· 20 min read

1. Core SQL Concepts & DDL Commands★★☆☆☆⏱ 5 min

SQL is split into two core subsets for A-Level: Data Definition Language (DDL) for creating and modifying database structure, and Data Manipulation Language (DML) for accessing and changing stored data.

📘 Definition

Data Definition Language (DDL)

The subset of SQL used to define and modify the structure of database objects such as tables, constraints and schemas.

Example:

Common DDL commands: CREATE, ALTER, DROP

📐 Worked Example

Write a SQL CREATE TABLE statement for a Customer table with: customer_id (integer, primary key), customer_name (varchar 100, not null), email (varchar 100), loyalty_points (integer, default 0)

1
Start with the command keyword and table name:
2
$CREATE TABLE Customer ($
3
Add each column definition with required constraints:
4
$customer_id INT PRIMARY KEY, customer_name VARCHAR(100) NOT NULL, email VARCHAR(100), loyalty_points INT DEFAULT 0$
5
Close the statement with correct syntax:
6
$);$

Exam tip:

CIE examiners require correct syntax including semicolons at the end of statements. Capitalizing keywords improves clarity and avoids confusion.

2. DML: Basic SELECT Queries & Filtering★★☆☆☆⏱ 5 min

Data Manipulation Language (DML) is used to interact with data stored in tables. The most widely used DML command is SELECT, which retrieves data matching given criteria from one or more tables.

📘 Definition

SELECT Statement

The core SQL command used to retrieve specified data from tables that match user-defined criteria.

📐 Worked Example

Retrieve customer_name and loyalty_points from Customer where loyalty_points > 100, ordered by loyalty_points descending

1
List the columns to retrieve after SELECT:
2
$SELECT customer_name, loyalty_points$
3
Specify the source table after FROM:
4
$FROM Customer$
5
Add the filter condition with WHERE:
6
$WHERE loyalty_points > 100$
7
Add sorting and close the statement:
8
$ORDER BY loyalty_points DESC;$

3. Aggregation & Grouping★★★☆☆⏱ 5 min

Aggregation functions calculate summary statistics across groups of rows, rather than returning individual records. The GROUP BY clause splits rows into logical groups before aggregation is applied.

📘 Definition

Aggregation Function

A function that performs a calculation on multiple rows and returns a single summary value. Common examples include COUNT(), SUM(), AVG(), MAX() and MIN().

📐 Worked Example

For an Order table linked to Customer via customer_id, find total orders and total order value per customer, only showing customers with total value > \$1000

1
Select the grouping key and required aggregate functions:
2
$SELECT customer_id, COUNT(order_id) AS total_orders, SUM(order_value) AS total_spend$
3
Specify the source table:
4
$FROM Order$
5
Group rows by customer_id to calculate aggregates per customer:
6
$GROUP BY customer_id$
7
Filter aggregated groups with HAVING:
8
$HAVING SUM(order_value) > 1000;$

4. Joining Multiple Tables★★★★☆⏱ 5 min

Most practical queries require data from multiple related tables, linked via primary and foreign keys. A JOIN operation combines rows from two or more tables based on a shared related column.

📘 Definition

INNER JOIN

The most common join type, which returns only rows where there is a matching value in both joined tables.

📐 Worked Example

Write a query to get customer name and order date for all orders placed in 2024. Tables: Customer(customer_id, customer_name), Order(order_id, customer_id, order_date)

1
Specify required columns, prefixed with table names for clarity:
2
$SELECT Customer.customer_name, Order.order_date$
3
Declare the join type and tables:
4
$FROM Customer INNER JOIN Order$
5
Add the join condition linking the keys:
6
$ON Customer.customer_id = Order.customer_id$
7
Filter for 2024 orders and close the statement:
8
$WHERE Order.order_date BETWEEN '2024-01-01' AND '2024-12-31';$

5. Common Pitfalls

Wrong move:

Using HAVING instead of WHERE for row-level filtering

Why:

HAVING only filters after aggregation, so this produces incorrect results and loses marks

Correct move:

Use WHERE for filtering individual rows before grouping, reserve HAVING for filtering aggregated groups

Wrong move:

Forgetting to add non-aggregated columns to GROUP BY

Why:

SQL requires all non-aggregated columns in SELECT to be in GROUP BY, this is a common syntax error marked wrong by examiners

Correct move:

List every non-aggregated column from your SELECT clause in the GROUP BY clause

Wrong move:

Omitting the ON clause when joining tables

Why:

Without ON, SQL returns all combinations of rows from both tables, which is almost never what the question asks for

Correct move:

Always add an ON clause that links the primary key of one table to the foreign key of the other

Wrong move:

Using CHAR(n) for variable-length text fields

Why:

CHAR is fixed-length and adds unnecessary trailing padding. CIE expects VARCHAR for variable-length text

Correct move:

Use VARCHAR(n) for variable-length text fields with maximum length n

Wrong move:

Writing clauses in the wrong order in a SELECT statement

Why:

SQL requires clauses to follow a fixed order, wrong order causes syntax errors

Correct move:

Follow this order: SELECT → FROM → JOIN/ON → WHERE → GROUP BY → HAVING → ORDER BY

6. Quick Reference Cheatsheet

Clause/Command	Purpose	Example
CREATE TABLE	Create new table	CREATE TABLE Customer (id INT PRIMARY KEY, name VARCHAR(100));
SELECT ... FROM	Retrieve data	SELECT name FROM Customer;
WHERE	Filter individual rows	WHERE points > 100
GROUP BY	Group rows for aggregation	GROUP BY customer_id
HAVING	Filter grouped results	HAVING SUM(value) > 1000
INNER JOIN	Join two tables	INNER JOIN Order ON Customer.id = Order.customer_id
INSERT	Add new record	INSERT INTO Customer (id, name) VALUES (1, 'Alice');
UPDATE	Modify records	UPDATE Customer SET points = 200 WHERE id = 1;
DELETE	Delete records	DELETE FROM Customer WHERE id = 1;

7. Frequently Asked

Do I need to remember all SQL syntax for the exam?

Yes, CIE 9618 requires you to recall standard syntax for all common commands, including CREATE TABLE, SELECT, JOIN, INSERT, UPDATE and DELETE.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping is applied, while HAVING filters groups after aggregation. This is the most commonly tested distinction.

When this came up on past exams

AI-estimated based on syllabus patterns — cross-check with official past papers for accuracy. Use only as revision-focus signals.

2022 · 2
Write queries to retrieve data
2023 · 2
Create table and join query
2024 · 2
Aggregate grouped data

Going deeper

syllabusOfficial CIE 9618 SyllabusSyllabus topic reference

What's Next

SQL is a core skill for CIE A-Level Computer Science, and forms the majority of marks for the databases unit in Paper 2. Mastery of SQL syntax and common query patterns will help you secure straightforward marks in this section, as most SQL questions are well-structured and predictable once you learn the core rules. Beyond the exam, SQL is one of the most practical and widely used technical skills in software development, data analysis and information systems, so the concepts you master here are useful long after you complete your qualification. Next, you can consolidate your knowledge by exploring related database concepts.