# Structured Query Language (SQL)

> CIE A-Level Computer Science · 9618
> Source: https://www.owlsprep.com/study/cie-9618-u8-structured-query-language/

This module covers all core SQL concepts required for CIE 9618, including data definition (DDL), data manipulation (DML), filtering, aggregation and joining multiple relational tables for exam-style questions.

**Prerequisites:** [Relational database concepts (entities, keys, relations)](https://www.owlsprep.com/study/cie-9618-u8-relational-database-concepts/)

## Learning objectives

- Write valid SQL queries to retrieve data from relational tables
- Use DDL commands to create and modify database table structure
- Apply filters, sorting and aggregation to summarize data
- Join multiple related tables to extract connected information

## Core SQL Concepts & DDL Commands

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.

**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.

## DML: Basic SELECT Queries & Filtering

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.

**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;$$

> **tip**
>
> Only use `SELECT *` if the question explicitly asks for all columns. Examiners prefer explicit column selection, which is clearer and avoids unnecessary data.

## Aggregation & Grouping

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.

**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;$$

**Exam command terms**

Exams consistently test the distinction between `WHERE` and `HAVING`:

- **WHERE** — Filters individual rows before grouping/aggregation *(Filters orders placed after 2024 before grouping)*

- **HAVING** — Filters entire groups after aggregation is complete *(Filters groups with total spend over $1000 after grouping)*

## Joining Multiple Tables

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.

**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';$$

> **warning**
>
> Always include an `ON` clause when joining. Omitting it creates a Cartesian product of all rows from both tables, which is almost always incorrect.

## Common pitfalls

- **Wrong:** Using `HAVING` instead of `WHERE` for row-level filtering
  - Why it fails: `HAVING` only filters after aggregation, so this produces incorrect results and loses marks
  - Correct: Use `WHERE` for filtering individual rows before grouping, reserve `HAVING` for filtering aggregated groups
- **Wrong:** Forgetting to add non-aggregated columns to `GROUP BY`
  - Why it fails: SQL requires all non-aggregated columns in `SELECT` to be in `GROUP BY`, this is a common syntax error marked wrong by examiners
  - Correct: List every non-aggregated column from your `SELECT` clause in the `GROUP BY` clause
- **Wrong:** Omitting the `ON` clause when joining tables
  - Why it fails: Without `ON`, SQL returns all combinations of rows from both tables, which is almost never what the question asks for
  - Correct: Always add an `ON` clause that links the primary key of one table to the foreign key of the other
- **Wrong:** Using `CHAR(n)` for variable-length text fields
  - Why it fails: `CHAR` is fixed-length and adds unnecessary trailing padding. CIE expects `VARCHAR` for variable-length text
  - Correct: Use `VARCHAR(n)` for variable-length text fields with maximum length n
- **Wrong:** Writing clauses in the wrong order in a `SELECT` statement
  - Why it fails: SQL requires clauses to follow a fixed order, wrong order causes syntax errors
  - Correct: Follow this order: `SELECT` → `FROM` → `JOIN/ON` → `WHERE` → `GROUP BY` → `HAVING` → `ORDER BY`

## 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; |

## 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.

- [Normalisation](https://www.owlsprep.com/study/cie-9618-u8-normalisation/)
- [Client-server and distributed databases](https://www.owlsprep.com/study/cie-9618-u8-client-server-and-distributed-databases/)
- [Transactions and concurrency control](https://www.owlsprep.com/study/cie-9618-u8-transactions-and-concurrency-control/)

---

From [OwlsPrep](https://www.owlsprep.com) — free study guides for A-Level, IB, AP and IGCSE, written against the official syllabus. Canonical page: https://www.owlsprep.com/study/cie-9618-u8-structured-query-language/