Structured Query Language (SQL)

Q: What are the common SQL data types used when defining table columns?

Common types include VARCHAR(n) for variable text, INT for whole numbers, DECIMAL(p, s) for exact fractional numbers, DATE for calendar dates, and BOOLEAN / BIT for logical true/false values.

Q: How do ALTER TABLE and DROP TABLE modify existing database structures?

ALTER TABLE modifies an existing table's schema (such as adding or removing columns), whereas DROP TABLE permanently deletes an entire table and all of its stored data.

Q: How do the SQL wildcards '%' and '_' operate in a LIKE pattern match?

The percent sign ( % ) matches zero, one, or multiple characters, while the underscore ( _ ) matches exactly one single character.

Q: What are the five core SQL aggregate functions and what do they compute?

The core aggregate functions are COUNT (number of rows/values), SUM (total addition), AVG (arithmetic mean), MIN (smallest value), and MAX (largest value).

Q: What is the difference between the WHERE and HAVING clauses in an aggregated SQL query?

WHERE filters individual records before aggregation takes place, whereas HAVING filters summary groups after the GROUP BY and aggregation have been calculated.

Software Systems Development CL4 · CCEA A Level · 8 min read · Updated: 31 Aug 2026

Structured Query Language (SQL): Master Your Database

Welcome to one of the most practical and exciting chapters in your A2 Software Systems Development course! Have you ever wondered how apps like Spotify retrieve your custom playlists in a split second, or how an e-commerce site knows exactly which items are in your basket? Behind almost every major digital system lies a relational database, and SQL (Structured Query Language) is the universal language we use to communicate with it.

Don't worry if writing code to manage databases sounds intimidating at first. SQL is designed to read very much like plain English. In this guide, we will break down each command step by step, explore real-world examples, and share handy tips to help you ace your exam questions.

1. Understanding SQL: DDL vs DML

Before diving into commands, it is essential to understand the two main categories of SQL statements. Think of building a house: first, you build the physical rooms and walls (the structure), and then you move your furniture in and out (the data).

Data Definition Language (DDL)

DDL statements are used to define, modify, or destroy the structure (schema) of database tables. You are not dealing with individual customer records here; you are building or altering the digital containers that hold them.
Key DDL commands include:
CREATE TABLE: Builds a brand new table from scratch.
ALTER TABLE: Modifies an existing table structure (such as adding or removing a column).
DROP TABLE: Completely deletes a table and all its contents from the database.

Data Manipulation Language (DML)

DML statements are used to manage and retrieve the actual data held within those tables. This is the "furniture" inside the rooms.
Key DML commands include:
SELECT: Retrieves data from one or more tables.
INSERT INTO: Adds new rows of data into a table.
UPDATE: Changes existing values in one or more rows.
DELETE: Removes existing rows from a table.

Quick Memory Aid:
DDL = Design & Definition (structural blueprint).
DML = Data Manipulation (handling the actual contents).

Key Takeaway: DDL changes the blueprint of your database; DML changes the records stored inside it.

2. Defining Table Structures: Data Definition Language (DDL)

When creating tables, we must specify column names, their data types, and any constraints to protect data integrity.

Common SQL Data Types

VARCHAR(n): Variable-length text up to n characters (e.g., VARCHAR(50) for a surname).
INT or INTEGER: Whole numbers with no decimal places (e.g., QuantityInStock).
DECIMAL(p, s): Precise numbers with decimals, where p is precision (total digits) and s is scale (digits after decimal point, e.g., DECIMAL(6,2) for prices like \(9999.99\)).
DATE: Stores calendar dates in the format YYYY-MM-DD.
BOOLEAN or BIT: Stores logical true/false values (such as account active status).

Table Constraints

Constraints enforce business rules and prevent invalid data from entering the database:
PRIMARY KEY: Uniquely identifies each record in the table. It must be unique and cannot contain NULL.
FOREIGN KEY: Creates a link between two tables by referring to the PRIMARY KEY of another table.
NOT NULL: Ensures that a column cannot be left blank.
UNIQUE: Ensures that all values in a column are distinct (e.g., email address).
CHECK: Validates that data meets a specific logical condition (e.g., CHECK (Age >= 17)).
DEFAULT: Assigns a default value if no value is supplied during an insert.

Example: Creating a Table

Imagine creating a table for a driving school to store student details:

CREATE TABLE Student (
  StudentID INT NOT NULL,
  FirstName VARCHAR(30) NOT NULL,
  LastName VARCHAR(30) NOT NULL,
  Email VARCHAR(100) UNIQUE,
  DateOfBirth DATE NOT NULL,
  TotalLessons INT DEFAULT 0,
  PRIMARY KEY (StudentID)
);

Modifying and Removing Tables: ALTER and DROP

If the driving school later decides to track phone numbers, we alter the existing structure:
ALTER TABLE Student ADD PhoneNumber VARCHAR(15);

To remove a column that is no longer needed:
ALTER TABLE Student DROP COLUMN TotalLessons;

If we want to delete the entire table structure and every record inside it forever:
DROP TABLE Student;

Key Takeaway: Use CREATE TABLE with strict data types and constraints to ensure data integrity right from the start.

3. Managing Records: Data Manipulation Language (DML)

Once our table structure exists, we populate, modify, and delete the actual records.

1. Inserting Records: INSERT INTO

To add a new student into our Student table:
INSERT INTO Student (StudentID, FirstName, LastName, Email, DateOfBirth, TotalLessons)
VALUES (101, 'Sophie', 'Clarke', 'sophie.c@email.com', '2005-04-12', 4);

2. Updating Records: UPDATE

When Sophie completes another lesson, we update her record:

UPDATE Student
SET TotalLessons = 5
WHERE StudentID = 101;

Crucial Exam Warning: Always include the WHERE clause when updating or deleting! If you omit WHERE StudentID = 101, SQL will happily update every single record in the table to have 5 lessons!

3. Deleting Records: DELETE

To delete a specific student who has passed their test and left the school:
DELETE FROM Student
WHERE StudentID = 101;

Key Takeaway: Always double-check your WHERE clause before running UPDATE or DELETE commands.

4. Querying and Filtering Data with SELECT

The SELECT statement is the most frequently tested SQL command in your A2 exam. It allows you to search, filter, and display data.

Basic SELECT Syntax

SELECT Column1, Column2
FROM TableName
WHERE Condition;

To select all columns at once, use the asterisk wildcard (*):
SELECT * FROM Student;

Filtering with Operators

SQL provides powerful operators to refine your search in the WHERE clause:

Comparison Operators: \(=\), \(<>\) (not equal), \(>\), \(<\), \(>=\), \(<=\).
Example: WHERE TotalLessons >= 10

Logical Operators (AND, OR, NOT):
WHERE TotalLessons > 5 AND LastName = 'Clarke'

Range Search (BETWEEN ... AND ...):
Finds values inclusive of the boundary endpoints.
WHERE TotalLessons BETWEEN 5 AND 15

List Matching (IN):
Matches any value from a specified list.
WHERE LastName IN ('Clarke', 'Patel', 'O''Neill')

Pattern Matching (LIKE with Wildcards):
% represents zero, one, or multiple characters.
_ represents exactly one single character.
Example: WHERE LastName LIKE 'Mc%' (finds any surname starting with 'Mc', like McDonald or McKenna).
Example: WHERE FirstName LIKE '_am' (finds 3-letter names ending in 'am', like Sam or Pam).

Sorting Results: ORDER BY

To arrange your output alphabetically or numerically, append ORDER BY at the end of the query. Use ASC for ascending (default) or DESC for descending.
SELECT FirstName, LastName, TotalLessons
FROM Student
ORDER BY TotalLessons DESC, LastName ASC;

Key Takeaway: Use WHERE with operators like LIKE, BETWEEN, and IN to isolate precise data, and organize results using ORDER BY.

5. Aggregate Functions and Grouping Data

Sometimes you don't want individual rows; you want statistical summaries (such as the total number of students or average test scores).

The 5 Core Aggregate Functions

COUNT(column): Counts the number of non-null values in a column. COUNT(*) counts total rows.
SUM(column): Calculates the total addition of numeric values.
AVG(column): Calculates the arithmetic mean of numeric values.
MIN(column): Finds the smallest value.
MAX(column): Finds the largest value.

Example:
SELECT COUNT(*) AS TotalStudents, AVG(TotalLessons) AS AverageLessons
FROM Student;

Grouping Data: GROUP BY

The GROUP BY clause groups rows that have the same values into summary rows. It is almost always used alongside aggregate functions.
For example, to find how many lessons have been taken in each city:

SELECT City, SUM(TotalLessons) AS CityTotalLessons
FROM Student
GROUP BY City;

Filtering Groups: WHERE vs HAVING

This is a classic exam question! Understanding the difference between WHERE and HAVING is vital:

WHERE filters individual records before they are grouped or aggregated.
HAVING filters groups after the aggregation has taken place.

Example: Show cities with more than 50 total lessons booked:
SELECT City, SUM(TotalLessons) AS CityTotalLessons
FROM Student
GROUP BY City
HAVING SUM(TotalLessons) > 50;

Key Takeaway: Remember: WHERE filters individual rows; HAVING filters grouped results produced by GROUP BY.

6. Relational Queries: Joining Multiple Tables

In a well-designed relational database, data is split across multiple tables to eliminate redundancy (data normalisation). To combine related data back together in a query, we use an INNER JOIN.

How an INNER JOIN Works

An INNER JOIN matches records between two tables based on a shared common field—typically the PRIMARY KEY of the parent table and the FOREIGN KEY of the child table.

Let's consider two tables:
Instructor (InstructorID, InstructorName, CarReg)
Student (StudentID, FirstName, LastName, InstructorID)

To display every student along with their instructor's name:

SELECT Student.FirstName, Student.LastName, Instructor.InstructorName
FROM Student
INNER JOIN Instructor ON Student.InstructorID = Instructor.InstructorID
ORDER BY Instructor.InstructorName ASC;

Step-by-Step Join Breakdown

1. SELECT: List the columns you want to view, prefixing with table names (Table.Column) if the column name appears in both tables.
2. FROM: Specify the first table.
3. INNER JOIN: Specify the second table to connect.
4. ON: Define the equality relationship connecting the foreign key to the primary key.

Key Takeaway: An INNER JOIN matches rows from two tables where the join condition (Foreign Key = Primary Key) evaluates to true.

7. Complete Query Clause Order & Revision Summary

The Standard SQL Execution Order Mnemonic

When writing complex SQL queries, follow this standard sequence of clauses:

Some Frogs Will Grow Huge Orchids
1. SELECT (Specify fields/aggregates)
2. FROM (Specify base table and any JOINs)
3. WHERE (Filter individual rows)
4. GROUP BY (Group common values)
5. HAVING (Filter groups)
6. ORDER BY (Sort the final output)

Quick Review Checklist

Before sitting your exam, check that you can confidently:
• Distinguish clearly between DDL (structure) and DML (data).
• Write a valid CREATE TABLE statement with appropriate constraints (PRIMARY KEY, NOT NULL, CHECK).
• Construct accurate INSERT, UPDATE, and DELETE queries with safety conditions.
• Write queries using LIKE wildcards (% and _) and range operators (BETWEEN, IN).
• Correctly apply aggregate functions with GROUP BY and HAVING.
• Connect related tables using INNER JOIN ... ON.

Quick check

Can you answer these now?

Open each question to check the key ideas from this chapter.

What is the fundamental difference between Data Definition Language (DDL) and Data Manipulation Language (DML)?

DDL commands define, modify, or delete database structure and schemas, whereas DML commands manage, insert, update, and retrieve the actual data rows stored inside those tables.

What are the common SQL data types used when defining table columns?

Common types include VARCHAR(n) for variable text, INT for whole numbers, DECIMAL(p, s) for exact fractional numbers, DATE for calendar dates, and BOOLEAN / BIT for logical true/false values.

What role do constraints such as PRIMARY KEY, FOREIGN KEY, and CHECK play in table definitions?

Constraints enforce business rules and data integrity by restricting the values that can be inserted into table columns.

How do ALTER TABLE and DROP TABLE modify existing database structures?

ALTER TABLE modifies an existing table's schema (such as adding or removing columns), whereas DROP TABLE permanently deletes an entire table and all of its stored data.

Why is the WHERE clause essential when executing UPDATE or DELETE statements?

Omitting the WHERE clause causes the UPDATE or DELETE operation to apply to every single row in the table, potentially overwriting or erasing all records.

How do the SQL wildcards '%' and '_' operate in a LIKE pattern match?

The percent sign (%) matches zero, one, or multiple characters, while the underscore (_) matches exactly one single character.

How does the ORDER BY clause sort query results in SQL?

ORDER BY arranges query output alphabetically or numerically, using ASC for ascending order (default) or DESC for descending order.

What are the five core SQL aggregate functions and what do they compute?

The core aggregate functions are COUNT (number of rows/values), SUM (total addition), AVG (arithmetic mean), MIN (smallest value), and MAX (largest value).

What is the difference between the WHERE and HAVING clauses in an aggregated SQL query?

WHERE filters individual records before aggregation takes place, whereas HAVING filters summary groups after the GROUP BY and aggregation have been calculated.

How does an INNER JOIN combine data from two related tables?

An INNER JOIN matches records between tables based on a join condition specified in the ON clause, typically equating a Foreign Key in one table to a Primary Key in another.

What is the standard sequence of clauses when writing a complex SQL query?

The standard order is SELECT, FROM (with JOINs), WHERE, GROUP BY, HAVING, and ORDER BY.

Ready to test yourself?

Turn these notes into exam-style practice. Get unlimited AI questions on this topic with instant marking and explanations.

Practise This Topic

More Software Systems Development CL4 chapters

* The content provided by thinka is generated by AI and may not always be accurate or up-to-date. Please use it as a supplementary resource and verify with official materials.

Put These Notes into Practice

Reading the notes is a great start. Now practise with unlimited AI-generated questions and get instant feedback. 100,000+ students are already improving their grades.

Start Practising Now View Pricing

Done reading? Test yourself with AI practice questions

Practise This Topic Now