SQL Queries — Edexcel A-Level Study Guide

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