CS36: SQL
SQL commands for querying and modifying databases: SELECT, INSERT, UPDATE, DELETE with WHERE, ORDER BY, and practical examples.
📋 What is SQL?
Definition: SQL (Structured Query Language) is the standard language used to create, read, update, and delete data in relational databases. It allows users to communicate with a database to retrieve and modify data.
SQL is used by nearly all relational database management systems (MySQL, PostgreSQL, SQLite, SQL Server, Oracle). You need to know four main SQL commands for GCSE: SELECT, INSERT, UPDATE, and DELETE.
- SQL is a declarative language - you specify WHAT data you want, not HOW to get it
- Keywords are usually written in UPPERCASE (convention, not required)
- String values must be enclosed in single quotes: 'Alice'
- Numeric values do not need quotes: 1001
Sample Tables Used Throughout
Students Table:
| StudentID | FirstName | LastName | Form | YearGroup |
|---|---|---|---|---|
| 1001 | Alice | Smith | 10A | 10 |
| 1002 | Bob | Jones | 10B | 10 |
| 1003 | Clara | Patel | 10A | 10 |
| 1004 | David | Smith | 11A | 11 |
| 1005 | Eve | Williams | 11B | 11 |
Courses Table:
| CourseID | CourseName | Teacher | Room |
|---|---|---|---|
| CS01 | Computer Science | Mr Khan | R12 |
| MA01 | Mathematics | Mrs Brown | R5 |
| EN01 | English | Ms Taylor | R8 |
🔍 SELECT - Reading Data
Basic SELECT Syntax:
SELECT field1, field2 FROM tablename
SELECT * FROM tablename
With conditions:
SELECT field1, field2 FROM tablename WHERE condition
With sorting:
SELECT field1, field2 FROM tablename WHERE condition ORDER BY field ASC/DESC
SELECT field1, field2 FROM tablename
SELECT * FROM tablename
With conditions:
SELECT field1, field2 FROM tablename WHERE condition
With sorting:
SELECT field1, field2 FROM tablename WHERE condition ORDER BY field ASC/DESC
Basic SELECT
Select all fields from Students
SELECT * FROM Students
Returns all columns and all rows from the Students table.
Select specific fields
SELECT FirstName, LastName FROM Students
Returns only the FirstName and LastName columns for all students.
SELECT with WHERE
WHERE clause: The WHERE clause filters records - only records that match the condition are returned. It is used with SELECT, UPDATE, and DELETE.
Filter by exact match
SELECT FirstName, LastName FROM Students WHERE Form = '10A'
Returns: Alice Smith, Clara Patel (only students in form 10A)
Filter by numeric comparison
SELECT FirstName, LastName FROM Students WHERE YearGroup > 10
Returns: David Smith, Eve Williams (only students in year 11)
WHERE Operators
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | WHERE Form = '10A' |
| != or <> | Not equal to | WHERE Form != '10A' |
| > | Greater than | WHERE YearGroup > 10 |
| < | Less than | WHERE YearGroup < 11 |
| >= | Greater than or equal to | WHERE YearGroup >= 10 |
| <= | Less than or equal to | WHERE YearGroup <= 10 |
| LIKE | Pattern matching | WHERE LastName LIKE 'S%' (starts with S) |
Combining conditions with AND / OR
SELECT FirstName, LastName FROM Students WHERE Form = '10A' AND YearGroup = 10
Returns: Alice Smith, Clara Patel (must match BOTH conditions)
SELECT FirstName, LastName FROM Students WHERE Form = '10A' OR Form = '11A'
Returns: Alice Smith, Clara Patel, David Smith (matches EITHER condition)
SELECT with ORDER BY
ORDER BY clause: The ORDER BY clause sorts the results. ASC sorts in ascending order (A-Z, 1-9). DESC sorts in descending order (Z-A, 9-1). Default is ASC if not specified.
Sort ascending
SELECT FirstName, LastName FROM Students ORDER BY LastName ASC
Returns: Bob Jones, Clara Patel, Alice Smith, David Smith, Eve Williams
(Alphabetical order by last name)
Sort descending
SELECT FirstName, LastName FROM Students ORDER BY YearGroup DESC, LastName ASC
Returns year 11 students first (DESC), then within each year, sorted by last name (ASC).
Complex SELECT Examples
Combined WHERE and ORDER BY
SELECT FirstName, LastName, Form FROM Students WHERE YearGroup = 10 ORDER BY LastName ASC
Returns year 10 students sorted alphabetically by last name: Bob Jones, Clara Patel, Alice Smith
LIKE with wildcards
SELECT FirstName, LastName FROM Students WHERE LastName LIKE 'S%'
Returns: Alice Smith, David Smith (last names starting with 'S')
The % symbol matches any sequence of characters. 'S%' means "starts with S followed by anything."
➕ INSERT - Adding Data
INSERT Syntax:
INSERT INTO tablename (field1, field2, field3) VALUES ('value1', 'value2', 'value3')
INSERT INTO tablename (field1, field2, field3) VALUES ('value1', 'value2', 'value3')
Insert a new student
INSERT INTO Students (StudentID, FirstName, LastName, Form, YearGroup) VALUES (1006, 'Frank', 'Chen', '10B', 10)
This adds a new record: Frank Chen, StudentID 1006, Form 10B, YearGroup 10.
Important: String values must be in single quotes ('Frank'). Numeric values do not need quotes (1006, 10). The order of values must match the order of fields listed. If inserting into all fields, you can omit the field list:
INSERT INTO Students VALUES (1006, 'Frank', 'Chen', '10B', 10)
✏️ UPDATE - Modifying Data
UPDATE Syntax:
UPDATE tablename SET field1 = 'newvalue' WHERE condition
Multiple fields:
UPDATE tablename SET field1 = 'value1', field2 = 'value2' WHERE condition
UPDATE tablename SET field1 = 'newvalue' WHERE condition
Multiple fields:
UPDATE tablename SET field1 = 'value1', field2 = 'value2' WHERE condition
Update a single field
UPDATE Students SET Form = '11A' WHERE StudentID = 1001
This changes Alice Smith's form from 10A to 11A.
Update multiple fields
UPDATE Students SET Form = '11A', YearGroup = 11 WHERE StudentID = 1001
This changes both Alice's form and year group.
Update multiple records
UPDATE Students SET YearGroup = 11 WHERE YearGroup = 10
This promotes ALL year 10 students to year 11. Be careful - without a specific WHERE clause, you could update every record!
Warning: Always include a WHERE clause with UPDATE. If you write
UPDATE Students SET Form = '10A' without a WHERE clause, it would change the Form of EVERY student in the table to 10A!
🗑️ DELETE - Removing Data
DELETE Syntax:
DELETE FROM tablename WHERE condition
DELETE FROM tablename WHERE condition
Delete a specific record
DELETE FROM Students WHERE StudentID = 1005
This removes Eve Williams from the Students table.
Delete multiple records
DELETE FROM Students WHERE YearGroup = 11
This removes ALL year 11 students from the table.
Warning: Always include a WHERE clause with DELETE.
DELETE FROM Students without a WHERE clause would delete EVERY record in the table! The data would be permanently lost.
📊 SQL Command Summary
| Command | Purpose | Key Clauses | Example |
|---|---|---|---|
| SELECT | Read/query data | FROM, WHERE, ORDER BY | SELECT * FROM Students WHERE YearGroup = 10 |
| INSERT | Add new records | INTO, VALUES | INSERT INTO Students VALUES (1006, 'Frank', 'Chen', '10B', 10) |
| UPDATE | Modify existing records | SET, WHERE | UPDATE Students SET Form = '11A' WHERE StudentID = 1001 |
| DELETE | Remove records | FROM, WHERE | DELETE FROM Students WHERE StudentID = 1005 |
⚠️ Common Mistakes to Avoid
| Mistake | Why It's Wrong | How to Fix It |
|---|---|---|
| Forgetting single quotes around strings | SQL will treat it as a column name, not a value | Use 'Alice' not Alice for string values |
| UPDATE or DELETE without WHERE | Changes or deletes ALL records in the table | Always include a WHERE clause |
| Wrong order in INSERT | Values must match the order of fields | Ensure VALUES order matches field list |
| Using = instead of LIKE for pattern matching | = only matches exact values | Use LIKE with % for partial matches |
| Forgetting to specify ASC/DESC | Default is ASC which may not be what you want | Always specify the sort direction explicitly |
❓ Practice Questions
Q1: Write an SQL query to show the first name and last name of all students in form 10B.
Q2: Write an SQL query to show all details of students whose last name starts with 'S', sorted by first name alphabetically.
Q3: Write an SQL statement to insert a new student: StudentID 1007, Grace Lee, form 10A, year 10.
Q4: Write an SQL statement to change Bob Jones's form from 10B to 11B and his year group to 11.
Q5: Write an SQL statement to delete all students in year group 11.
✅ Answers
SELECT FirstName, LastName FROM Students WHERE Form = '10B'SELECT * FROM Students WHERE LastName LIKE 'S%' ORDER BY FirstName ASCINSERT INTO Students (StudentID, FirstName, LastName, Form, YearGroup) VALUES (1007, 'Grace', 'Lee', '10A', 10)UPDATE Students SET Form = '11B', YearGroup = 11 WHERE StudentID = 1002DELETE FROM Students WHERE YearGroup = 11
🎯 Exam Tips
- Always use single quotes for string values: 'Alice', '10A'
- Never forget the WHERE clause with UPDATE and DELETE
- Write SQL keywords in UPPERCASE for clarity
- For SELECT, think: what fields? FROM what table? WHERE what condition? ORDER BY what?
- LIKE with % for pattern matching: 'S%' = starts with S, '%son%' = contains 'son'
- ORDER BY defaults to ASC - specify DESC if you want descending
⚠️ Common Errors
✗ Forgetting the WHERE clause filters rows BEFORE results are returned ✓ WHERE filters which records are included in the query results. Without WHERE, ALL records in the table are returned. Always include WHERE when you need specific records.
✗ Confusing SELECT and INSERT SQL statements ✓ SELECT retrieves/reads data from a database. INSERT adds new records to a table. SELECT is a query; INSERT modifies data.
✗ Not using quotes around text values in WHERE clauses ✓ Text/string values in SQL must be enclosed in single quotes: WHERE name = 'Alice'. Numeric values do not need quotes: WHERE age = 16.
✗ Forgetting that SQL keywords are not case-sensitive but data values are ✓ SQL keywords (SELECT, FROM, WHERE) can be uppercase or lowercase. However, string comparisons ARE case-sensitive in many databases: 'alice' ≠ 'Alice'.
✍️ Model Answer
Full-Mark Response
A table called Students has fields: StudentID, Name, TutorGroup, Grade. Write SQL statements to: (1) Show all students in tutor group '7B' with their names and grades. (2) Add a new student with ID 105, name 'Kai', tutor group '7B', grade 'B'. (3) Update the grade of student ID 105 to 'A'. [4 marks]
1. SELECT Name, Grade FROM Students WHERE TutorGroup = '7B'; 2. INSERT INTO Students (StudentID, Name, TutorGroup, Grade) VALUES (105, 'Kai', '7B', 'B'); 3. UPDATE Students SET Grade = 'A' WHERE StudentID = 105; Note: Text values are enclosed in single quotes. The WHERE clause in the UPDATE ensures only student 105's grade is changed — without it, ALL students' grades would be updated to 'A'.
📊 AO Deep Dive
Assessment Objective Analysis
AO1 (Computational Thinking — 40%): Demonstrate knowledge and understanding of the principles and concepts of computer science, including SQL: SELECT, INSERT, UPDATE and DELETE statements for AQA 8525, OCR J277 & Edexcel 1CP2.
AO2 (Application — 40%): Apply knowledge and understanding of computer science, including SQL: SELECT, INSERT, UPDATE and DELETE statements to analyse problems in computational terms and to design, write and evaluate solutions.
AO3 (Evaluation — 20%): Evaluate the effectiveness, correctness and efficiency of computational solutions, including SQL: SELECT, INSERT, UPDATE and DELETE statements, and make reasoned judgements about trade-offs.
📝 Exam Technique
GCSE Computer Science Exam Tips:
Know SQL commands: SELECT (read), INSERT (add), UPDATE (modify), DELETE (remove). Always use WHERE with UPDATE and DELETE to avoid affecting all records. Single quotes for text values, no quotes for numbers. End statements with semicolons. SELECT * returns all fields. Use AND/OR for multiple conditions. ORDER BY for sorting. For joins, use table.field syntax to avoid ambiguity.
Know SQL commands: SELECT (read), INSERT (add), UPDATE (modify), DELETE (remove). Always use WHERE with UPDATE and DELETE to avoid affecting all records. Single quotes for text values, no quotes for numbers. End statements with semicolons. SELECT * returns all fields. Use AND/OR for multiple conditions. ORDER BY for sorting. For joins, use table.field syntax to avoid ambiguity.
📝 Exam Questions by Topic
🎬 Video Resources
Ready to ace your GCSE Computer Science exams?
Get the best revision books and guides to boost your grades.