Structured Query Language (SQL) (AQA GCSE Computer Science): Revision Note

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)

1

Fynn Roberts

45000

HR

London

2

Zarmeen Azra

52000

Sales

Manchester

3

Ella Stanley

39500

Marketing

Birmingham

2

Zarmeen Azra

52000

Sales

Manchester

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)

1

Fynn Roberts

45000

London

2

Zarmeen Azra

52000

Manchester

3

Ella Stanley

39500

Birmingham

1

1

HR

Sally Jones

[email protected]

2

2

Sales

Peter Evans

[email protected]

3

3

Marketing

Stuart Davies

[email protected]

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)

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)

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 FROM Employees
WHERE Department = 'Marketing' ;

1

Fynn Roberts

45000

HR

London

2

Zarmeen Azra

52000

Sales

Manchester

3

George Rope

47250

Sales

Leeds