Structured Query Language (SQL) (AQA GCSE Computer Science)

Revision Note

Test yourself
Robert Hampton

Written by: Robert Hampton

Reviewed by: James Woodhouse

Retrieving Data Using SQL

What is SQL?

  • SQL (Structured Query Language) is a programming language used to interact with a DBMS.

  • The use of SQL allows a user to:

    • Select data (flat file)

    • Select data (relational)

    • Order data

    • Insert data

    • Update data

    • Delete records

Selecting data commands (flat file)

Command

Description

Example

SELECT

Retrieves data from a database table

SELECT * FROM users;
(retrieves all data from the 'users' table)

SELECT name, age
FROM users
(retrieves names and ages from the 'users' table)

FROM

Specifies the tables to retrieve data from

SELECT name, age FROM users;
(retrieves names and ages from the 'users' table)

WHERE

Filters the data based on a specified condition

SELECT * FROM users
WHERE age > 30;
(Retrieves users older than 30)

AND

Combines multiple conditions in a WHERE clause

SELECT * FROM users
WHERE age > 18 AND city = 'New York';
(retrieves users older than 18 and from New York)

OR

Retrieves data when at least one of the conditions is true

SELECT * FROM users
WHERE age < 18 OR city = 'New York';
(retrieves users younger than 18 or from New York)

WILDCARDS

'*' and '%' symbols are used for searching and matching data
'*' used to select all columns in a table
'%' used as a wildcard character in the LIKE operator

SELECT * FROM users;
(retrieves all columns for the 'users' table)

SELECT * FROM users WHERE name LIKE 'J%';
(retrieves users whose names start with 'J')

Nested SELECT

A select within another select statement (nested). A mini select within the main one

SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

(retrieves users with an age greater than the average age)

ORDER BY

How data is organised (sorted) when it is retrieved

SELECT Forename, Lastname FROM Students
WHERE StudentID < 10
ORDER BY Lastname, Forename ASC

(retrieves only the forename and lastname of all students from the students table who have a studentID of less than 10 and displays in ascending order by lastname and forename)

Examples

  • Select all the fields from the Customers table

Command:

SQL query displayed with "SELECT * FROM Customers;" on a black background. The keyword "SELECT" is highlighted in pink.

Output:

Table with column headers: ID, Name, Age, City, Country. Rows of data: 1, John Doe, 30, New York, USA; 2, Jane Doe, 25, London, UK; 3, Peter Lee, 40, Paris, France.
  • Select the ID, name & age of customers who are older than 25

Command:

SQL query to select ID, name, and age from the Customers table where age is greater than 25 with keywords highlighted in pink.

Output:

A table with three columns: ID, Name, and Age. Two rows display data for John Doe (ID 1, Age 30) and Peter Lee (ID 3, Age 40).
  • Select the name and country of customers who are from a country that begins with 'U'

Command:

SQL query: SELECT Name, Country FROM Customers WHERE Country LIKE 'U%';. The text is white on a black background, with SQL keywords in purple.

Output:

Table with two columns: "Name" and "Country." First row: John Doe, USA. Second row: Jane Doe, UK. The header row is highlighted in blue.
  • Select all fields of customers who are from 'London' or 'Paris'

Command:

SQL query displayed with keywords; selects all records from 'Customers' where 'City' is 'London' or 'Paris'. Keywords are highlighted in pink.

Output:

Table with headers ID, Name, Age, City, Country. Rows: (1) Jane Doe, 25, London, UK. (2) Peter Lee, 40, Paris, France.

Nested select

Table: Employees

ID

Name

Salary

Department

City

1

Fynn Roberts

45000

HR

London

2

Zarmeen Azra

52000

Sales

Manchester

3

Ella Stanley

39500

Marketing

Birmingham

  • Select all fields for employees whose salary is bigger than the average salary of all employees

Command:

Command 5_Structured Query Language

Output:

ID

Name

Salary

Department

City

2

Zarmeen Azra

52000

Sales

Manchester

Selecting data commands (relational)

Command

Description

Example

JOIN
(INNER JOIN)

Combines data from two or more tables based on a related column

SELECT users.name, orders.order_id FROM users
INNER JOIN orders
ON users.user_id = orders.user_id;
(retrieves user names and their corresponding order IDs)

Example

Table: Employees

EmployID

Name

Salary

City

1

Fynn Roberts

45000

London

2

Zarmeen Azra

52000

Manchester

3

Ella Stanley

39500

Birmingham

Table: Departments

DepartID

EmployID

Department

Manager

Email

1

1

HR

Sally Jones

[email protected]

2

2

Sales

Peter Evans

[email protected]

3

3

Marketing

Stuart Davies

[email protected]

  • Select the name, manager and email address of employees in the sales department

Command:

SQL query selecting Name, Manager, and Email from Employees with an inner join on Departments where the department is 'Sales'. Text in black background.

Output:

A table with columns for Name, Manager, and Email. The data row contains "Zarmeen Azra," "Peter Evans," and "sales@myemail.com."

Inserting Data Using SQL

Command

Description

Example

INSERT

Adds new data to a database table

INSERT INTO users (name, age)
VALUES ('John Doe',25);
(inserts a new user with the name 'John Doe' and age 25)

UPDATE

Edit data in a database table

UPDATE users
SET name = 'Bob', age = 56
WHERE ID = 4;
(updates name and age details for user ID = 4)

Example

Table: Employees

Table listing employee details: ID, Name, Salary, Department, City. Three entries: Fynn Roberts, Zarmeen Azra, George Rope with respective details.
  • Insert a new employee into the Employees table with the 'Name', 'Salary', 'Department' and 'City' fields

Command:

Command 7_Structured Query Language

Output:

Table listing employees with columns for ID, Name, Salary, Department, and City. Highlighted row shows George Rope, ID 4, salary 47250, in Sales at Leeds.

Table: Employees

Table listing employees with columns for ID, Name, Salary, Department, and City. Example: Fynn Roberts, HR, £45,000, based in London.
  • Update employee ID 3 to a salary of 47500 and city to London

Command:

SQL query updating Employees table: SETS Salary to 47500 and City to London for the employee with ID = 3. Keywords are highlighted in pink.

Output

Table showing employee details with columns for ID, Name, Salary, Department, and City. Employees: Fynn Roberts, Zarmeen Azra, and Ella Stanley.

Deleting Data Using SQL

Command

Description

Example

DELETE

Removes data from a database table

DELETE FROM users
WHERE age < 18;
(deletes all users younger than 18 from the 'users' table)

DELETE FROM users
WHERE name="John";
(deletes a record where the name is John)

Example

Table: Employees

ID

Name

Salary

Department

City

1

Fynn Roberts

45000

HR

London

2

Zarmeen Azra

52000

Sales

Manchester

3

Ella Stanley

39500

Marketing

Birmingham

4

George Rope

47250

Sales

Leeds

  • Delete all records from the Employees table whose department is 'Marketing'

Command:

DELETE FROM Employees
WHERE Department = 'Marketing' ;

Output:

ID

Name

Salary

Department

City

1

Fynn Roberts

45000

HR

London

2

Zarmeen Azra

52000

Sales

Manchester

3

George Rope

47250

Sales

Leeds

Worked Example

A database stores information about songs on a music streaming service. One of the tables called Song has the fields Title, Artist, Genre and Length

A band called RandomBits removes their permission for their songs to be streamed. The company removes all the songs belonging to RandomBits from their service.

Write an SQL statement that will remove all songs by RandomBits from the table Song [2]

Answer

DELETE FROM Song [1]
WHERE Artist = “RandomBits” [1]

Last updated:

You've read 0 of your 10 free revision notes

Unlock more, it's free!

Join the 100,000+ Students that ❤️ Save My Exams

the (exam) results speak for themselves:

Did this page help you?

Robert Hampton

Author: Robert Hampton

Expertise: Computer Science Content Creator

Rob has over 16 years' experience teaching Computer Science and ICT at KS3 & GCSE levels. Rob has demonstrated strong leadership as Head of Department since 2012 and previously supported teacher development as a Specialist Leader of Education, empowering departments to excel in Computer Science. Beyond his tech expertise, Robert embraces the virtual world as an avid gamer, conquering digital battlefields when he's not coding.

James Woodhouse

Author: James Woodhouse

Expertise: Computer Science

James graduated from the University of Sunderland with a degree in ICT and Computing education. He has over 14 years of experience both teaching and leading in Computer Science, specialising in teaching GCSE and A-level. James has held various leadership roles, including Head of Computer Science and coordinator positions for Key Stage 3 and Key Stage 4. James has a keen interest in networking security and technologies aimed at preventing security breaches.