SQL (CIE IGCSE Computer Science)

• Records in a database can be searched and data can be manipulated using Structured Query Language (SQL)
• SQL statements can be written to query the data in the database and extract useful information
• SQL statements follow this structure:
• SELECT the fields you want to display 
• FROM the table/tables containing the data you wish to search
• WHERE the search criteria 

A Database Table Containing Movie Details

MovieID	Name	Genre	Certificate	Rating
M23	Moana	Family	U	8.1
M8	Shaun of the Dead	Comedy	18	8.7
M56	Die Hard	Action	18	8.4
M34	Big	Family	PG	8.5

Example

SELECT Name, Rating

FROM Movie

WHERE Rating>8.4;

The results of this query would be:

Name	Rating
Shaun of the Dead	8.7
Big	8.5

• The two fields -  Name and Rating have been extracted from the Movie table and then the records have been filtered by Rating
• This example uses the > operator to search for records where the rating is greater than 8.4
• There are several other comparison operators which can be used to create the filter criteria in the WHERE line of a SQL query

SQL Comparison Operators

Operator	Description
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
=	Equal to
<>	Not equal to

Example

SELECT Name,Rating

FROM Movie

WHERE Genre=”Family” AND Certificate=”U”;

The results of this query would be:

Name	Rating
Moana	8.1

• The two fields Name and Rating have been extracted from the Movie table and the records have been filtered by both Genre and Certificate
• This query uses the AND logical operator to include multiple criteria in the WHERE line of the SQL query
• Another logical operator which can be used in the WHERE statement is OR
• For example, WHERE Genre=”Comedy” OR Genre=”Family”

• You can enter a fourth line to the statement using the ORDER BY command, followed by ASC or DESC
• If you enter ASC the results of the query will be sorted in ascending order
• If you enter DESC the results of the query are sorted in descending order

Example

SELECT Name,Genre, Certificate, Rating

FROM Movie

ORDER BY Name ASC

• The results of this query would be:

Name	Genre	Certificate	Rating
Big	Family	PG	8.5
Die Hard	Action	18	8.4
Moana	Family	U	8.1
Shaun of the Dead	Comedy	18	8.7

• The query has returned four fields and all records because there were no WHERE criteria. The records are sorted by Name alphabetically
• If numbers are sorted in ascending order they go from the lowest number at the top of the table to the highest number at the bottom
• Descending order is the highest number to the lowest

• The SUM command can be used to add numerical data
• The COUNT command can be used to count items of data

ProductID	ProductName	Price	QuantityInStock
1	Sausages	1.99	3
2	Chips	2.99	2
3	Beans	2.50	5
4	Bananas	2.10	12
5	Avocado	1.00	3

Example

SELECT SUM(QuantityInStock)

FROM ProductTable;

• This query will add up all of the numbers in the QuantityInStock field
• The result of this query would be 25 

Example

SELECT COUNT(*)

FROM ProductTable

WHERE Price>2;

• This query will count all the records with a price greater than 2
• The result of this query would be 3
• This is because there are three products with a price greater than £2 (Chips, Beans, Bananas)