SQL (Cambridge (CIE) O Level Computer Science)

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

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)

SUM

Adds up and outputs the sum of a field

SELECT SUM(Salary) FROM tbl_people;

COUNT

Counts the number of files which match the set criteria

SELECT COUNT(*)

FROM tbl_people

WHERE Salary > 50000;

1

John Doe

30

New York

USA

2

Jane Doe

25

London

UK

3

Peter Lee

40

Paris

France

1

John Doe

30

3

Peter Lee

40

John Doe

USA

Jane Doe

UK

2

Jane Doe

25

London

UK

3

Peter Lee

40

Paris

France