Revision NotesExam QuestionsPast Papers
A LevelComputer ScienceOCRRevision Notes3. Exchanging Data3.2 DatabasesSQL

SQL (OCR A Level Computer Science)

Revision Note

Robert Hampton

Written by: Robert Hampton

Reviewed by: James Woodhouse

Selecting Data (Single Table)

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 (single table)

    • Select data (multiple tables)

    • Insert data

    • Delete records

    • Delete tables

Selecting Data (Single Table) Commands


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)

LIKE

Filters the data based on a specific pattern

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

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')

Examples

  • Select all the fields from the Customers table

Command:

code

Output:

ID

Name

Age

City

Country

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:

code

Output:

ID

Name

Age

1

John Doe

30

3

Peter Lee

40

  • Select the name and country of customers who are from a country that begins with 'U'

Command:

code

Output:

Name

Country

John Doe

USA

Jane Doe

UK

  • Select all fields of customers who are from 'London' or 'Paris'

Command:

code

Output:

ID

Name

Age

City

Country

2

Jane Doe

25

London

UK

3

Peter Lee

40

Paris

France

Worked Example

Customers’ details are stored in the flat file database table Customer. An extract of the table is shown below

relational-database

Write the SQL statement that would show only the CustomerID and Surname fields for customers with the Title “Miss” or “Mrs”

[4]

Answer:

SELECT CustomerID, Surname [1]
FROM Customer [1]
WHERE Title="Miss" [1]
OR Title = "Mrs" [1]

Selecting Data (Multiple Tables)


Command


Description

Example

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)

JOIN
(INNER JOIN)

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

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

Examples

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:

code

Output:

ID

Name

Salary

Department

City

2

Zarmeen Azra

52000

Sales

Manchester

Inserting Data


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)

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

  • Insert a new employee into the Employees table with the 'Name', 'Salary', 'Department' and 'City' fields

Command:

code

Output:

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

Deleting Records


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:

code

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]

Deleting Tables


Command


Description

Example

DROP

Deletes a table in a database

DROP TABLE users;
(deletes the 'users' table)

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 the Employees table

Command:

code

 Output

code

Last updated:

You've read 0 of your 5 free revision notes this week

Sign up now. It’s free!

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

the (exam) results speak for themselves:

    Test yourself

    Did this page help you?

    Previous:Capturing, Selecting, Managing & Exchanging DataNext:Referential Integrity
    Author
    Reviewer
    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.