SQL

Structured Query Language is a declarative language used to create, modify and interrogate databases.

For the OCR A Level exam you will need to know the following SQL keywords:

SELECT - specify which fields are to be returned
FROM - specify which table(s) to use
WHERE - set criteria for which records are selected
LIKE - specify a pattern for matching records

AND
OR

DELETE - allows you to delete a record or records

INSERT INTO - allows you to add a new record

DROP - allows you to delete an entire table

JOIN - allows you to join tables when returning records

The following is not in the appendix of the OCR syllabus but did come up in the mark scheme on the 2017 A Level Paper 1

UPDATE - allows you to update an existing record

The best way to learn about SQL and retain the information you need for the exam is to actually use it. Use the sample database linked to at the bottom of this page, and the free program DB Browser for SQL lite to do so.

SELECT

The SELECT query is the simplest type of question you might be asked on an exam. It will return a single record or set of records depending on the WHERE clause. If there is no WHERE clause, all records in the table will be returned. For example:

SELECT TrackName, TrackArtist FROM tracks

… will return all the records from the tracks table, but only the TrackName and TrackArtist fields will be returned.

Download this database file load it into DB Browser for SQL Lite, and then use it to run the above query in the “Execute SQL” tab.

SELECT TrackName, TrackArtist FROM tracks 
WHERE TrackID >= 20 AND TrackID <= 32

The WHERE clause in the above query will mean only records that contain a value in the TrackID field between 20 and 32 will be returned. Again, try this in DB Browser for SQL Lite.

LIKE

When you want to select records that don’t make an exact match to a particular value, the LIKE clause is useful. It is used with the % or _ characters like so:

SELECT TrackName, TrackArtist FROM tracks 
WHERE TrackName LIKE 'S%'

The above will return all tracks that start with S. The wildcard character ‘%’ indicates there can be zero or more characters of any type that follow the S.

The wildcard character ‘_’ indicates there should be a match of exactly one character of any type.

SELECT TrackName, TrackArtist FROM tracks 
WHERE TrackName LIKE '_ %'

The above will return all tracks that start with exactly one character followed by a space.

INSERT

INSERT INTO allows you to add a new record to a table. You can supply the values for each field using the VALUES keyword.

INSERT INTO Customers VALUES(12, 'SupplyCo', '02085550111')

If you don’t want to supply all the values, you can specify which columns you want to specify values for. This is useful when the database itself inserts an ID number for a primary key automatically:

INSERT INTO Customers(name, phone) VALUES('SupplyCo', '02085550111')

JOIN

Join is used in relational databases to link two tables together into a single virtual table according to the criteria in a SELECT query. There are several types of JOIN, but the only one you need for the exam is the INNER JOIN. If you write JOIN on its own you will by default perform an inner JOIN. The syntax is as follows:

SELECT orders.orderID, customers.name 
FROM orders
JOIN customers
WHERE orders.customerID = customers.customerID

If you miss out the WHERE clause in a JOIN, you will get what is known as a cross product where every record in one table is joined with every record in the other table – usually this will be huge and not very useful.

In the example above, the orders table is joined to the customers table, and we only return one field from each table (orderID and name). In order for the correct rows to match up, we need a WHERE clause to match the right record in the customer table.

You normally join a primary key in one table to a foreign key in another table.

DELETE and UPDATE

These two are used in a similar way, you select a single record or records using a WHERE clause, and either DELETE that record or records, or UPDATE them with a SET statement that gives a new value to a particular field or fields.

DELETE FROM Customers 
WHERE CustomerID = 100

UPDATE Customers
SET LoyaltyBonus = 20
WHERE Spend > 200

Activities

Create queries for the following:

  1. Use LIKE to select all tracks in the tracks table that start with a 0 or 1
  2. Add a new record into the albums table with an AlbumName of ‘The Very Best of Motown’, an AlbumPath of ‘C:\Music\Motown’ and an AlbumArtist of ‘Various’
  3. Use a JOIN to show the TrackName, AlbumName and TrackArtist for all the records in the tracks table
  4. Change the record in the albums table that has an AlbumID of 47 to the value ‘Greatest Hits’ using UPDATE
  5. Use DELETE to delete the record you added to the albums table earlier

Knowledge Check

  • Which keyword do you need to use in a SELECT query to match a field to an inexact value?
  • What is the difference between % and _ when used in a query?
  • What do you do if you don’t want to give a value for every field when inserting a new record?
  • What happens if you don’t provide a WHERE clause to an UPDATE or DELETE query?