SQL (Cambridge (CIE) IGCSE Computer Science)

Revision Note

James Woodhouse

Written by: James Woodhouse

Reviewed by: Lucy Kirkham

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

    • Order data

    • Sum data

    • Count data

Selecting data 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)

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;

Examples

  • Select all the fields from the Customers table

Command:

SELECT * FROM Customers;


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:

SELECT ID, name, age 
FROM Customers
WHERE Age > 25;


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:

SELECT Name, Country
FROM Customers
WHERE Country LIKE 'U%';


Output:

Name

Country

John Doe

USA

Jane Doe

UK

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

Command:

SELECT *
FROM Customers
WHERE City = 'London' OR City = 'Paris';


Output:

ID

Name

Age

City

Country

2

Jane Doe

25

London

UK

3

Peter Lee

40

Paris

France

Worked Example

Below is a table of animals called tbl_animals

Animal

Breeding

Number of Young

Red Fox

Yes

4-6

Rabbit

Yes

4-12

African Elephant

Yes

1

Blue Whale

No

1

Orangutan

Yes

1

Polar Bear

Yes

1-3

Dolphin

Yes

1

Kangaroo

Yes

1

Lion

Yes

1-6

Penguin

Yes

1


Complete this SQL statement to display all of the animal breeds that are currently breeding and there was only one young born this year [3]

SELECT .................................................

FROM .................................................

WHERE .................................................

Answer

SELECT Animal [1]

FROM tbl_animals [1]

WHERE Number of Young == 1 [1]

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:

Did this page help you?

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.

Lucy Kirkham

Author: Lucy Kirkham

Expertise: Head of STEM

Lucy has been a passionate Maths teacher for over 12 years, teaching maths across the UK and abroad helping to engage, interest and develop confidence in the subject at all levels.Working as a Head of Department and then Director of Maths, Lucy has advised schools and academy trusts in both Scotland and the East Midlands, where her role was to support and coach teachers to improve Maths teaching for all.