SQL (OCR A Level Computer Science)

Revision Note

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:

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:

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:

Output:

Name	Country
John Doe	USA
Jane Doe	UK

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

Command:

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

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)

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:

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:

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)

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:

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:

Output

Last updated: 15 April 2024

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?

Previous:Capturing, Selecting, Managing & Exchanging DataNext:Referential Integrity

SQL (OCR A Level Computer Science)

Revision Note

Selecting Data (Single Table)

What is SQL?

Selecting Data (Single Table) Commands

Examples

Worked Example

Selecting Data (Multiple Tables)

Examples

Table: Employees

Inserting Data

Example

Deleting Records

Example

Worked Example

Deleting Tables

Example

You've read 0 of your 10 free revision notes

Unlock more, it's free!

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

Author: Robert Hampton

Author: James Woodhouse

SQL (OCR A Level Computer Science)

Revision Note

Selecting Data (Single Table)

What is SQL?

Selecting Data (Single Table) Commands

Examples

Selecting Data (Multiple Tables)

ExamplesTable: Employees

Inserting Data

Example

Deleting Records

Example

Deleting Tables

Example

You've read 0 of your 10 free revision notes

Unlock more, it's free!

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

Author: Robert Hampton

Author: James Woodhouse

Examples

Table: Employees