SQL Queries

Overview

Structured Query Language (SQL) is the universal language for managing and querying relational databases. For an Edexcel A-Level Computer Science student, proficiency in SQL is not just a desirable skill but a core requirement, frequently tested in Paper 1 and Paper 2. This topic, specification reference A6.4, requires you to move beyond simple data retrieval and master the art of constructing complex, multi-table queries that filter, sort, and aggregate data with precision. Examiners are looking for candidates who can not only write syntactically correct SQL but also interpret a given database schema to solve a specific problem. A typical exam question will present a scenario with a relational schema and ask you to write a specific query to extract information, often for 4-6 marks. This topic has strong synoptic links to 'Databases' (A6.1) and 'Relational Databases' (A6.2), as understanding database structure is fundamental to querying it effectively.

Key Concepts

Concept 1: Data Manipulation Language (DML) vs. Data Definition Language (DDL)

SQL commands are broadly split into two categories. DML (Data Manipulation Language) is used to interact with the data within the tables. This is what you'll use most in exams. It includes the 'big four':

• SELECT: Retrieves data from one or more tables.
• INSERT: Adds a new row of data to a table.
• UPDATE: Modifies existing data in a table.
• DELETE: Removes rows from a table.

DDL (Data Definition Language) is used to define and manage the structure of the database itself. Think of it as the blueprint for your data.

• CREATE TABLE: Builds a new table, defining its columns and their data types.
• ALTER TABLE: Modifies an existing table, for example by adding or removing a column.
• DROP TABLE: Deletes an entire table and all its data.

Examiners test your ability to distinguish between these. If a question asks you to 'add a new customer', you need an INSERT statement (DML). If it asks you to 'add a new field for customer email', you need an ALTER TABLE statement (DDL).

Concept 2: The SELECT Statement and Filtering with WHERE

The SELECT statement is the cornerstone of data retrieval. Its fundamental structure is SELECT [columns] FROM [table]. To retrieve all columns, you can use the asterisk wildcard (*), but marks are typically awarded for selecting the specific columns requested in the question.

To filter the rows returned, you use the WHERE clause. This is where the logic of your query resides.

Example: To find all customers named 'Smith' from a 'Customers' table.
SELECT CustomerID, FirstName, Postcode FROM Customers WHERE LastName = 'Smith';

Crucial Examiner Point: Notice the single quotes around 'Smith'. Any string or date value in a WHERE clause must be enclosed in single quotes. Forgetting this is one of the most common ways candidates lose a mark. Numeric values, however, do not use quotes (e.g., WHERE Age > 18). You can build complex conditions using boolean operators AND (both conditions must be true) and OR (at least one condition must be true).

Concept 3: Joining Tables

Real-world databases are relational, meaning data is split across multiple tables linked by keys. To retrieve data from multiple tables in a single query, you must use a JOIN. The most common type you'll need is the INNER JOIN. An INNER JOIN returns only the rows where the key exists in both tables.

Example: You have a Students table (with CourseID) and a Courses table (with CourseID and CourseName). To list each student's name and the name of their course, you join them on their common CourseID field.

SELECT Students.Name, Courses.CourseName FROM Students INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

Crucial Examiner Point: When a column name exists in both tables (like CourseID here), you must prefix it with the table name (e.g., Students.CourseID). This is called 'qualifying the column' and failure to do so when ambiguity exists will cost you a mark.

Concept 4: Aggregation with GROUP BY

Sometimes you don't want individual rows; you want a summary. This is achieved using aggregate functions combined with the GROUP BY clause. Aggregate functions perform a calculation on a set of rows and return a single summary value.

The five you must know are:

• COUNT(): Counts the number of rows.
• SUM(): Adds up the values in a column.
• AVG(): Calculates the average of the values in a column.
• MIN(): Finds the minimum value in a column.
• MAX(): Finds the maximum value in a column.

Example: To count the number of students in each course.
SELECT CourseID, COUNT(StudentID) FROM Students GROUP BY CourseID;

The GROUP BY CourseID clause tells SQL to bundle all the rows with the same CourseID together and then apply the COUNT function to each bundle. If you need to filter the results of an aggregate function (e.g., only show courses with more than 10 students), you use the HAVING clause, which works like WHERE but for groups: HAVING COUNT(StudentID) > 10.

Mathematical/Scientific Relationships

While SQL isn't mathematical in the sense of physics equations, the logical structure is paramount. The order of clauses is a strict rule that must be memorised. It is not flexible.

The Mandatory SQL Clause Order (Must Memorise):

1. SELECT
2. FROM
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. ORDER BY

Think of it as a filtering pipeline. Data flows from the FROM clause, is filtered by WHERE, grouped by GROUP BY, filtered again by HAVING, and finally sorted by ORDER BY before being output by SELECT. Getting this order wrong results in a syntax error and will score zero marks.

Practical Applications

SQL is one of the most commercially valuable skills in computer science. It powers:

• E-commerce sites: Finding products, managing customer orders, and analysing sales data.
• Social Media: Storing user profiles, posts, connections, and serving content feeds.
• Banking Systems: Managing customer accounts, transactions, and generating statements.
• Booking Systems: Handling flight, hotel, or event bookings and checking availability.

In your A-Level project, if you are creating a dynamic web application with a database backend, you will be using SQL (or an ORM that generates SQL) extensively to manage your application's data. This is a chance to demonstrate practical application of this topic, which examiners value highly.