SQL | Computer for GCSE/IGCSE - Year 11 PDF Download

SQL

• Structured Query Language (SQL) enables searching and manipulation of data in databases.
• SQL statements are crafted to interrogate the database and retrieve pertinent information.
• SQL statements adhere to this structure:
  • SELECT the desired fields for display.
  • FROM the table/tables housing the data to be searched.
  • WHERE specifies the search criteria.

A Database Table Containing Movie Details

Example:
SELECT Name, Rating
FROM Movie
WHERE Rating>8.4;

The results of this query would be:

• Two fields, Name and Rating, have been extracted from the Movie table, and subsequently, the records have been filtered based on the Rating.
• In this instance, the > operator is employed to search for records with a rating exceeding 8.4.
• Numerous other comparison operators can be utilized to establish filter criteria within the WHERE clause of a SQL query.

SQL Comparison Operators

Example:
SELECT Name,Rating
FROM Movie
WHERE Genre=”Family” AND Certificate=”U”;

The results of this query would be:

• 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 employs the AND logical operator to incorporate multiple criteria in the WHERE clause of the SQL query.
• Another logical operator applicable in the WHERE statement is OR:
  • For instance, WHERE Genre="Comedy" OR Genre="Family".

ORDER BY

An additional line can be appended to the statement using the ORDER BY command, followed by ASC or DESC:

• When ASC is specified, the query results are sorted in ascending order.
• Conversely, when DESC is specified, the query results are sorted in descending order.

Example:
SELECT Name,Genre, Certificate, Rating
FROM Movie
ORDER BY Name ASC

The results of this query would be:

The query has retrieved four fields and all records due to the absence of WHERE criteria. The records are sorted alphabetically by Name.

• When sorted in ascending order, numbers ascend from the lowest at the top to the highest at the bottom.
• Conversely, in descending order, numbers descend from the highest at the top to the lowest at the bottom.

SUM and COUNT commands

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

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)