Computer ScienceExam code: 0984

Database Concepts

Databases

A typical SQL query has up to three clauses:

SELECT  field1, field2, ...
FROM    TableName
WHERE   condition;

Each line has a specific job:

ClauseWhat it does
SELECTLists which fields (columns) to display in the result
FROMNames the table to read from
WHEREFilters which records (rows) to include

The semicolon at the end is the SQL "end of statement" marker.

Common exam question

Saying what an SQL statement does

Question: State what SELECT, FROM and WHERE are each used for, or explain the purpose of SQL statements you are given (3 marks).

Asked in 2 of the 17 papers, one mark per point. For the keywords, SELECT lists the fields to be displayed, FROM names the table, and WHERE gives the condition the records must meet.

For a whole statement, say what it works out and for which records. One using SUM finds the total of the values in its field, one using COUNT finds how many records there are, and the WHERE condition that limits them to one group of records is a mark of its own. Name the real field and value in your answer ("the total stock of all the blue items"), not just "it adds them up".

SELECT every field

To return every column for every record, use the wildcard * after SELECT:

SELECT *
FROM Customers;

This returns the whole table.

SELECT specific fields

To return only certain columns, list them by name:

SELECT FirstName, LastName, Age
FROM Customers;

Only those three columns appear in the result, even if the table has many more.

SELECT with a WHERE filter

To return only certain rows, add a WHERE clause with a condition:

SELECT *
FROM Customers
WHERE Age > 25;

Only customers older than 25 are returned.

Common exam question

Completing an SQL statement

Question: Fill in the gaps in an SQL statement so that it displays, or totals, the records the question describes (2–5 marks).

Asked in 4 of the 17 papers, usually one mark per line or gap. SELECT takes only the fields asked for, FROM the table's name as printed, and WHERE the field tested, an operator and the value. In all four papers the field tested was not one displayed.

Put a text value in quotes (WHERE Colour = 'Blue';). For a yes/no field the schemes accept NOT Paid, Paid = False, Paid = No or Paid = "No". For the number of items in stock, when each record holds a quantity, SELECT needs SUM of that field: COUNT only counts the records.

Two papers then ask how a different field could give the same result: name that field and the value it is tested for (zero for an out-of-stock quantity); one scheme gives a mark for each.

A customers table to practise on

Consider this Customers table:

IDNameAgeCityCountry
101Maya Hassan32TorontoCanada
102Liam O'Brien25DublinIreland
103Hiroshi Tanaka47OsakaJapan
104Priya Gupta19MumbaiIndia

Query 1: SELECT * FROM Customers; returns all five fields for all four records (the whole table).

Query 2: SELECT ID, Name, Age FROM Customers WHERE Age > 25;

IDNameAge
101Maya Hassan32
103Hiroshi Tanaka47

(Liam O'Brien is excluded because his age is exactly 25, while the query asked for strictly greater than 25. Priya Gupta is excluded because 19 < 25.)

Quick check

4 short questions on this note. See what has stuck, and where to look if something hasn't.

About 2 minutes

Build on this topic