exploring IT: Java Programming Grade 12

Learning Unit 3 Advanced SQL

3.1 Introduction
In Grade 11 we applied SQL queries to databases using SELECT, INSERT, UPDATE and DELETE,
but the SELECT queries were restricted to a single table. A vast number of interesting and relevant
queries can be applied using multiple tables in the same query.

3.2 General Form of SQL SELECT Statement

A SQL statement can have many clauses of which only the word SELECT is compulsory. You can
have a SQL query such as SELECT 5 which will return 5, or SELECT NOW() to return the time. Most
queries have the FROM clause to indicate the table to be used. The rest of the clauses are optional
depending on what the SQL statement needs to determine.
General form of a SELCT SQL statement:
SELECT <fields(s)>
FROM <table(s)>
WHERE condition(s)
GROUP BY expression
HAVING condition
ORDER BY expression

3.3 The WhoGotTalent Database

This database was created by Lizeth van der Merwe, an IT and CAT Educator at Trinityhouse High
School.
The WhoGotTalent database consists of 3 simple tables with a small number of records in each
table. We are going to use to introduce more complex queries that link the tables. Once a database
has been normalised, the data is separated into tables liked by primary and foreign keys. We need to
learn how to combine the tables using the links between the tables. In Grade 11, we used the
UniStudents and this year we will start with the simpler WhoGotTalent database which lends itself to
an introduction to linking tables in SQL queries.
You should have the [Link] database, available on the [Link] site under
database files, to perform the activities and exercises in this Learning Unit. The Design View,
Datasheet View (1st 10 records) and Relationships (links) between the tables are provided below:
tblEntrants Design View:

Learning Unit 3 Advanced SQL Page 71

exploring IT: Java Programming Grade 12

tblEntrants Datasheet View:

tblActs Design View:

tblActs Datasheet View:

tblPoints Design View:

Page 72 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

tblPoints Datasheet View:

The Relationships between the tables linked by primary and foreign keys:

Whenever there is a primary key in one table that links to a foreign key, a primary key in another
table, a relationship exists between the two tables and we can perform queries that make use of that
relationship to gain meaningful information.
The primary/foreign key relationships that exist in this database are:

 ActNum primary key in tblActs links to the foreign key ActNum in tblEntrants; the
relationship is indicated as one-to-many, indicating that one act may have many entrants.
 ActNum primary key in tblActs links to the foreign key ActNum in tblPoints; the
relationship is indicated as one-to-one, indicating that one act only has one set of points.
When there is a one-to-one relationship between primary keys of two tables, the
primary key of each table is considered to be a foreign key in the other table.
We are going to investigate many queries that use the primary/foreign key relationships between
these tables. For example, there is no direct link between tblEntrants and tblPoints, but what if we
wanted to know how many points were attributed to an entrant? We simply use the fact that
tblEntrants is linked to tblActs through the primary/foreign key relationship on ActNum in each
table; then we link the primary/foreign keys ActNum between tblActs and tblPoints to create a
relationship ‘path’ from tblEntrants to tblPoints.

Learning Unit 3 Advanced SQL Page 73

exploring IT: Java Programming Grade 12

3.3.1 Cartesian Product of Two Tables

ACTIVITY 1
Open the WhoGotTalent database and open tblEntrants and tblActs in datasheet view.
Take note of the number of records (rows) there are in each of the tables. If you are using an
original version of the database, you should see that tblEntrants has 47 records and tblActs
has 28 records.
Code the following query join the two tables together:
SELECT FirstName, Surname, ActName, Category
FROM tblEntrants, tblActs
Run the query and you should notice a Result Set similar to the following screenshot:

A total of 1316 records were generated in the Result Set which is exactly the product of the number of
records in the tblEntrants table (47) and the tblActs table (28).
This is known as a Cartesian Product, where EVERY row in one table 'maps' to EVERY row in
another table. As can be seen 47x28 rows produces 1316 rows.
A Cartesian product occurs when the query is not given any specific criteria to HOW to match the
data in the one to table to the other. By specifying how to join the two tables, we can reduce the
number of rows and only match rows that are linked using common fields.

3.3.2 Joining Two Tables Using a WHERE Clause

ACTIVITY 2
Add the following WHERE clause to the query:
SELECT FirstName, Surname, ActName, Category
FROM tblEntrants, tblActs
WHERE [Link] = [Link]

The Result Set now shows exactly the number of records in tblEntrants (47), which indicates that
each entrant has been matched with her/his ActName. When the fields in two tables are combined
in this way we say the tables have been JOINED.
Note: when two tables have the same field name you must include the table name for each field. For
example:

Page 74 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

WHERE [Link] = [Link]

The condition:
WHERE ActNum = ActNum

cannot be interpreted by SQL and will result in an error message.

Change the SQL statement to view the all the possible fields in the Result Set which can also
be considered as a new table created from two existing tables.
SELECT *
FROM tblEntrants, tblActs
WHERE [Link] = [Link]

All fields from both tables are shown in the order that they appear in their respective tables; not all of
the fields are displayed here because of width restrictions on this page.
The query produces 47 records as each Entrant successfully links the existing data about the
Entrant with his/her Act.
Change the SQL statement to list only three fields and add an ORDER BY clause to produce
the Result Set in alphabetical order according to entrant surname and then entrant first name:
SELECT Surname, FirstName, ActName
FROM tblEntrants, tblActs
WHERE [Link] = [Link]
ORDER BY Surname, FirstName
Run the query again to check that the outputs are correct.
Notice that the order is consistent with what was requested and the number of records remains at 47.

Any of the fields selected in the

SELECT statement must come
from the tables indicated in the
FROM statement.
SELECT Surname, FirstName, ActName
A WHERE clause is included to
limit the ResultSet to only include
FROM tblEntrants, tblActs the records where a match exists
between the fields of the tables.
WHERE [Link] = [Link]

If fields have the same name in the

tables used, the fields must be
prefixed with the table’s name in
order to differentiate them.

Learning Unit 3 Advanced SQL Page 75

exploring IT: Java Programming Grade 12

[Link] Another example of a JOIN using WHERE

The entrants into the competition and their acts are listed in two tables. One acts can have many
entrants as shown in the relationship diagram.

To list the name of the act in tblActs together with the first name and surnames of the entrants in
tblEntrants we can see that these fields come from different tables. We need to specify the names of
the fields, the two tables and HOW to join the table.

ACTIVITY 3
Type in the following query to view the entrants together with their acts.
SELECT [Link], [Link], [Link]
FROM tblActs,tblEntrants
WHERE [Link] = [Link]

Suppose we only wanted to see the entrants who are in grade 10.
Change the query to add the WHERE clause to find grade 10 entrants using the Grade field.
Include the Grade field after the SELECT to view the grades.
SELECT [Link], [Link], [Link],
Grade
FROM tblActs,tblEntrants
WHERE [Link] = [Link] AND Grade = 10

Page 76 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

Change the query to find the entrants who can sing or dance. You can remove the table names
that prefix fields that are unique in the database.
SELECT FirstName, Surname, ActName, Category
FROM tblActs,tblEntrants
WHERE [Link] = [Link] AND Category = 'Singing' OR
Category = 'Dancing'

The Result Set contains 245 records with Leda Burnside being involved in six acts. From our previous
query, she was only involved in the ‘Tweetie Birds’ act.

The error is caused by the lack of brackets around the conditions in the WHERE clause to isolate the
singing or dancing categories. We need to force SQL to perform the OR condition before the AND.
Change the query to include brackets.
SELECT FirstName, Surname, ActName, Category
FROM tblActs,tblEntrants
WHERE [Link] = [Link] AND
(Category = 'Singing' OR Category = 'Dancing')
Check that your solution produces 19 records.

Learning Unit 3 Advanced SQL Page 77

exploring IT: Java Programming Grade 12

EXERCISE 1

1. List the entrants who have more than R10 000 in sponsorship money and require no props.
Display the entrant’s name, the name of the act, the sponsorship money and the PropsNeeded
field.
2. List the acts that have more than 8 points for Judge1. List the name of the act and the values for
the Judge1 field.
3. List the acts that have entrants in Grade 10,11 or 12 using the IN operator. Display the relevant
fields.
4. List the acts that have entrants in Grade 10A or 10B.
4.1. Would it possible to use the IN operator for this query?

3.3.3 Joining More Than Two Tables Using a WHERE Clause

We can combine more than two tables using the WHERE clause, as long as you establish the correct
links between the primary/foreign keys between the tables.
The general form of a SQL statement to combine 3 tables using the WHERE clause
SELECT <field1>, <field2>, <field3>, ,<fieldn>
FROM <table1>, <table2>, <table3>
WHERE <table1>.<primary key> = <table2>.<foreign key>
AND <[Link] key> = <[Link] key>

A couple of general rules that will guarantee success when joining many tables:

 For the fields chosen in the SELECT clause, the corresponding tables must be included in
the FROM clause
 The WHERE clause establishes the correct links of primary to foreign keys across the
tables
 The number of links between tables are one less than the number of tables. For example,
if there are three tables then there must be two links.
 Even if fields from a particular table are not used in the SELECT clause, the tables must
be included in the FROM clause if they form part if the WHERE clause links

ACTIVITY 4
We may want to list the points achieved by each entrant.
Run the following query:
SELECT Surname, FirstName, ActName, Judge1, Judge2, Judge3
FROM tblEntrants, tblActs, tblPoints
WHERE [Link] = [Link]
AND [Link] = [Link]

Notice that fields from all three tables are SELECTed; which means that all three tables must be listed
in the FROM clause, and all three tables must be correctly linked in the WHERE clause.

Page 78 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

We may want to determine the total points achieved by each entrant. Under normal circumstances,
we would use GROUP BY, but the way the tables have been designed here, each entrant only
participates in one act, so GROUP BY would not make any difference. Also, the sum of the points for
each entrant would not be an aggregate function SUM as SUM calculates a total for a single field not
across three different fields. The total can be determined by adding the values in the fields Judge1,
Judge2 and Judge3 as a calculated field.
Run the query:
SELECT Surname, FirstName, Judge1 + Judge2 + Judge3 AS TotalPoints
FROM tblEntrants, tblActs, tblPoints
WHERE [Link] = [Link]
AND [Link] = [Link]
We could include ORDER BY to list the records by Surname.
SELECT Surname, FirstName, Judge1 + Judge2 + Judge3 AS TotalPoints
FROM tblEntrants, tblActs, tblPoints
WHERE [Link] = [Link] AND [Link] =
[Link]
ORDER BY Surname

The generated field TotalPoints can be rounded to the nearest whole number.
Change the first line of the query to include the ROUND function:
SELECT Surname, FirstName, ROUND(Judge1 + Judge2 + Judge3,0) AS
TotalPoints
FROM tblEntrants, tblActs, tblPoints
WHERE [Link] = [Link] AND [Link] =
[Link]
ORDER BY Surname

Notice that no field from tblActs was SELECTed, so tblActs may not have been needed in the
FROM clause, but it was needed because it’s the only way to create a link between tblEntrants and
tblPoints, as required in the WHERE clause.

3.3.4 Using GROUP BY when Joining Tables with a WHERE Clause

The administrators of the overall competition are concerned that the judges for the different categories
were not consistently fair in their allocation of points. A good way to check would be to get a
summary of the average of the three Judges’ Points for each Act Category. We need to use
GROUP BY on the Category field in tblActs and determine the averages for the 3 Judge’s scores in
tblPoints for each Category in tblActs, within the link between the two tables.

ACTIVITY 5
Run the following query to get the summary to one decimal place:
SELECT Category,
ROUND(AVG((Judge1+Judge2+Judge3)/3),1) AS JudgesAve
FROM tblActs, tblPoints

Learning Unit 3 Advanced SQL Page 79

exploring IT: Java Programming Grade 12

WHERE [Link] = [Link]

GROUP BY Category

Note that the results differ significantly for each category.

We could determine the number of entrants in each act by

accessing the ActName from tblActs and the number of Entrants
that took part in that Act. We use GROUP BY and COUNT(*) to
count up how many entrants in tblEntrants have a matching
ActNum for each ActName in tblActs.
Run the following query:
SELECT ActName, COUNT(*) AS NumEntrants
FROM tblActs, tblEntrants
WHERE [Link] = [Link]
GROUP BY ActName

We can work out which acts were solo acts with only one entrant
using HAVING (the WHERE for aggregates) to restrict the rows
based on the aggregate.
Run the following query to list the ‘solo’ acts:
SELECT ActName, COUNT(*) AS NumEntrants
FROM tblActs, tblEntrants
WHERE [Link] = [Link]
GROUP BY ActName
HAVING COUNT(*) = 1
Remove the COUNT(*) AS NumEntrants clause as all the values are 1:
SELECT ActName AS SoloActs
FROM tblActs, tblEntrants
WHERE [Link] = [Link]
GROUP BY ActName
HAVING COUNT(*) = 1

3.3.5 Using NOT IN and an EMBEDDED QUERY

Following on from a previous query that determined the Judges’ average scores for the Act
Categories, the administrators of the overall competition decided that only the Act Categories that
have an overall average >= 8 can be nominated to participate in a national competition. We need to
write a SQL statement to list the categories that have an average below 80.
To start, we need to provide a list of the categories that are disqualified. The previous query:
SELECT Category,
ROUND(AVG((Judge1+Judge2+Judge3)/3),1) AS JudgesAve
FROM tblActs, tblPoints

Page 80 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

WHERE [Link] = [Link]

GROUP BY Category

provided the averages for each category.

ACTIVITY 6
Type in the following query that identifies those that do qualify using a HAVING to limit the rows
to those Categories that have an average >=80%,
SELECT Category, ROUND(AVG((Judge1+Judge2 + Judge3)/3),1) AS JudgesAve
FROM tblActs, tblPoints
WHERE [Link] = [Link]
GROUP BY Category
HAVING ROUND(AVG((Judge1+Judge2 + Judge3)/3),1) >= 8

In MySQL the alias JudgesAve can be used in the condition

HAVING JudgesAve >= 8

We need to find the competitors that do not qualify, which is

the opposite of the above query.
We can embed the previous query into the original query
using a NOT IN in the WHERE clause. From the output
displayed alongside, the categories ‘Çomedy’, ‘Drumming’,
‘Singing’ and ‘Ventriloquist’ all qualify and the other cateories
do not. This will be the basis for the NOT IN condition. We
need to create a Result Set that only lists the categories that qualify.
Adapt the query to isolate the only the categories that qualify by removing JudgesAve field:
SELECT Category
FROM tblActs, tblPoints
WHERE [Link] = [Link]
GROUP BY Category
HAVING ROUND(AVG((Judge1+Judge2 + Judge3)/3),1) >= 8
Now code a query that will only list a category from the table tblActs if the
category is not one that qualifies using NOT IN
SELECT Category AS Disqualified
FROM tblActs
WHERE
Category NOT IN
(SELECT Category
FROM tblActs, tblPoints
WHERE [Link] = [Link]
GROUP BY Category
HAVING ROUND(AVG((Judge1+Judge2 + Judge3)/3),1) >= 8)

Note that the list of categories produced do not contain categories ‘Çomedy’,
‘Drumming’, ‘Singing’ and ‘Ventriloquist’. however many are repeated.
Add DISTINCT after SELECT to ensure the list is unique.
SELECT DISTINCT Category AS Disqualified
FROM tblActs
WHERE
Category NOT IN
(SELECT Category
FROM tblActs, tblPoints
WHERE [Link] = [Link]
GROUP BY Category
HAVING ROUND(AVG((Judge1+Judge2 + Judge3)/3),1) >= 8)

Learning Unit 3 Advanced SQL Page 81

exploring IT: Java Programming Grade 12

It is important to exclude any other data fields in the embedded query since a Category in the
WHERE NOT IN condition can only be compared to a set of Category names.

3.4 Using JOINs Instead of Compound WHERE Clauses

3.4.1 The INNER JOIN
An INNER JOIN connects tables where the primary key matches the foreign key in the related
table. Only rows that meet the requirements are given in the results. Any records that do not have a
matching foreign key field to the primary key field will not be included.
An INNER JOIN is the same as a join using the WHERE clause, although the syntax is quite different
(logical, clear, but different). Both produce the same result. We are going to investigate the INNER
JOIN here by executing exactly the same queries as before, but replacing the WHERE clauses with
INNER JOINs wherever possible. Other JOIN techniques will be discussed later in this Learning Unit,
with other databases.
General form of an INNER JOIN statement:
SELECT field1, field2, field3,…
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

ACTIVITY 7
One of the first queries that link two tables that we performed was:
SELECT Surname, FirstName, ActName
FROM tblEntrants, tblActs
WHERE [Link] = [Link]
Change the query to the following and run it to check that the outcome is the same:
SELECT Surname, FirstName, ActName
FROM
tblEntrants
INNER JOIN tblActs
ON [Link] = [Link]

Notice that there is no longer a WHERE clause that links the primary/foreign keys. The ON keyword
precedes the link.
A later query that linked all three tables was:
SELECT Surname, FirstName, ActName, Judge1, Judge2, Judge3
FROM tblEntrants, tblActs, tblPoints
WHERE [Link] = [Link]
AND [Link] = [Link]

When you link more than two tables, the second table is replaced by a “virtual table” enclosed in
brackets.
SELECT field1, field2, field3,…
FROM first_table
INNER JOIN (virtual_table)
ON first_table.keyfield = second_table.foreign_keyfield

The “virtual table” is a table produced by joining the second and third table.
(tblActs
INNER JOIN
tblPoints

Page 82 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

ON [Link] = [Link])
Type in the following query to join three tables using a nested join:
SELECT Surname, FirstName, ActName, Judge1, Judge2, Judge3
FROM tblEntrants
INNER JOIN
(tblActs
INNER JOIN
tblPoints
ON [Link] = [Link])
ON [Link] = [Link]
Run the query to check that the result set is the same.
Adapt the query to find all the entrants, and the acts they belong to, that had a result of 8 and
above for ALL of the judges.

3.4.2 Employees and Orders Database

In the Employees and Orders database, an employee, stored in the Employees table, places an
order for a particular product stored on the Orders table. The Employee_ID column is the primary
key of the Employees table, meaning that no two rows can have the same Employee_ID. The
Employee_ID distinguishes two persons even if they have the same name.
When you look at the example tables below, notice that:

 The Employee_ID column is the primary key of the Employees table

Employees Orders

Employee_ID Name Prod_ID Product Employee_ID

01 Hansen, Ola 234 Printer 01

02 Svendson, Tove 657 Table 03
03 Svendson, Stephen 865 Chair 03
04 Pettersen, Kari
 The Prod_ID column is the primary key of the Orders table
 The Employee_ID column in the Orders table is used to refer to the persons in the
Employees table without using their names (foreign key).
Using the two tables, we can perform the following queries:
Example 1
Who has ordered a product, and what did they order?
SELECT [Link], [Link]
FROM Employees, Orders
WHERE Employees.Employee_ID = Orders.Employee_ID;

ACTIVITY 8
Rewrite the above statement using an INNER JOIN.
Result Set

Name Product
Hansen, Ola Printer

Svendson, Stephen Table

Learning Unit 3 Advanced SQL Page 83

exploring IT: Java Programming Grade 12

Svendson, Stephen Chair

Note that the employees that did not place any orders are not included in the table.
Example 2
Who ordered a printer?
SELECT [Link]
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
AND [Link]='Printer'
Result

Name
Hansen, Ola

Change the above query to use an INNER JOIN.

Write a query using INNER JOIN to determine who has ordered a product, and what did they
order?
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in
Employees that do not have matches in Orders, those rows will not be listed.
Result

Name Product
Hansen, Ola Printer

Svendson, Stephen Table

Svendson, Stephen Chair

3.4.3 LEFT JOIN

The LEFT JOIN returns all the rows from the first table, even if there are no matches in the second
table. A LEFT JOIN includes all the records in the first table and finds a match to the second table. If
no match is found, the records from the first table are still listed with null values for the fields in the
second table where no match is found.
Example 3
List all employees, and their orders - if any.
SELECT [Link], [Link]
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

Result

Name Product
Hansen, Ola Printer

Svendson, Tove null

Svendson, Stephen Table

Svendson, Stephen Chair

Page 84 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

Pettersen, Kari null

Tove Svendson and Kari Pettersen have not placed any orders. There is no match for these records
in the Orders table. The LEFT JOIN includes all the records from the Employees table even if no
match is found and places null (a blank) in the Product column.
The previous SQL statement can be changed to list the employees who have not placed any orders:
SELECT [Link], [Link]
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
WHERE [Link] is null
The last line check if the Product field contains a null using the operator is instead of the = sign.
Many LEFT JOINS can be written using NOT IN.
SELECT [Link], [Link]
FROM Employees
WHERE Employees.Employee_ID NOT IN
(SELECT Orders.Employee_ID
FROM Orders)
Check if this is right?

General form of a LEFT JOIN

SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
Note that the first table listed will contain the extra fields produced in the Result Set.

3.4.4 RIGHT JOIN

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in
the first table (Employees). If there had been any rows in Orders that did not have matches in
Employees, those rows also would have been listed.
Example 5
List all orders, and who has ordered - if any.
SELECT [Link], [Link]
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

Result

Name Product
Hansen, Ola Printer

Svendson, Stephen Table

Svendson, Stephen Chair

A RIGHT JOIN is the exact opposite of the LEFT JOIN and can be written as a LEFT JOIN by
swopping the tables around.
SELECT [Link], [Link]
FROM Employees
RIGHT JOIN Orders

Learning Unit 3 Advanced SQL Page 85

exploring IT: Java Programming Grade 12

ON Employees.Employee_ID=Orders.Employee_ID

and
SELECT [Link], [Link]
FROM Orders
LEFT JOIN Employees
ON Employees.Employee_ID=Orders.Employee_ID

produce the same results. So, it is simpler to always use a LEFT JOIN. Make sure that the table that
has extra fields is always listed first. This will include fields from the first table that do not have
matches in the second table.

General form of RIGHT JOIN

SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

3.5 Embedded Queries (JOINS within JOINS)

3.5.1 HomeDecor Database
In order to demonstrate more complex queries, we are going to use a database called
[Link]. A company has created a database of items that they sell, salespeople who sell
the items and clients who have purchased the items. The structure of the tables and some of the data
in them are indicated below:
tblSalesPeople stores the name and phone number of each sales person.

SPID This field contains a unique numerical value for each salesperson (primary key).
SPName This field contains the name of the salesperson.
SPPhone This field contains the 'phone number of the salesperson.

tblClients stores the details of the clients and the sales person they deal with. Each client can only
have one sales person.

clientID This field contains a unique number (primary key) for each client recorded
in the database.
clientName This is a text field contains the client's name.
SPID This numeric field contains the identity number of the sales person who
attended to this client. This field is a foreign key for the tblSalesPeople
table.

Page 86 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

tblItems stores the details of all the items sold by the company.
itemID This field contains a unique number for each item on sale (primary key).
itemDescription This text field contains a description of the sales item.
itemCategory This field contains either the value 'Interior' or 'Exterior'. It is used to classify
each sales item.
itemCostPrice This real number field contains the actual cost of the sales item.

tblInvoices records the information that would be stored on an invoice. Each client can have many
invoices and each item can appear many times on the invoice. For each item, we need to record the
number of items bought as well as the date of the invoice.

Learning Unit 3 Advanced SQL Page 87

exploring IT: Java Programming Grade 12

itemID This field contains the number of the item that was purchased. This field is a
foreign key for the tblItems table.
clientID This field contains the number of the client who purchased the item. This
field is a foreign key for the tblClients table.
quantity This integer field contains the quantity of the item that was purchased.
invoiceDate The date of the invoice in the format yyyy/mm/dd.

3.5.2 The Relationships between the Tables

In order to analyse which table/s would be necessary to perform multiple table queries, we need to
know how the tables are linked via their primary and foreign keys. Only the primary/foreign key
relationships need be considered; and MS Access provides the relationship feature to link the tables
using the primary and foreign keys.

[Link] Creating Relationships for the HomeDecor Database

ACTIVITY 9
Open the database called HomeDecor.
Click on the Database Tools tab and click on the Relationship button.
Create relationships between all four tables using the primary and foreign keys. The
relationships should be as follows:

Page 88 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

Looking at the HomeDecor database, we can create a query to list all the sales people with their
clients and the date of their invoices.

ACTIVITY 10
Create the following query to join the tables tblSalesPeople, tblClients and tblInvoices using
WHERE to join the tables.
SELECT SPName, ClientName, InvoiceDate
FROM tblClients, tblInvoices, tblSalesPeople
WHERE [Link] = [Link]
AND [Link] = [Link]

If we were to use INNER JOINS, we will need to create embedded queries where a sub query is used
to join two tables and then the resulting table is joined to the third table.
Adapt the above query to use an embedded query with INNER JOINS check that it returns the
same result as the previous SQL statement.
SELECT SPName, ClientName, InvoiceDate
FROM tblSalesPeople
INNER JOIN
(tblClients
INNER JOIN tblInvoices
ON [Link] = [Link])
ON [Link] = [Link]

The sub query in brackets first combines tblClients and tblInvoices using the link between the
clientID fields producing a new virtual table. This new table is then joined to tblSalesPeople using
the SPID field as a link.

EXERCISE 2

The SQL queries in this exercise contain joins and revise other queries.
Are some of these too difficult for this exercise – should they be placed later?
1. List all the sales people and their clients.
2. Are there any sales people without clients?
3. List all the clients and the items that have ordered.
4. How many clients have ordered ‘interior’ items?
5. What is the average value of each invoice?
6. Which client has spent the most?
7. List all the clients who bought something in July.
8. The ‘Diffusion Lighting System’ is available for exterior as well as interior. Write an INSERT with
a SELECT to add a second ‘Diffusion Lighting System’ with the same values as the first except
for itemCategory which is ‘exterior’.

Learning Unit 3 Advanced SQL Page 89

exploring IT: Java Programming Grade 12

9. Rewrite the following queries using embedded queries.

9.1.
SELECT clientName, SUM(itemCostPrice * Quantity) * 1.35 AS [Total
Spend]
FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY clientName
9.2.
SELECT clientName, SUM(itemCostPrice * Quantity) * 1.35 AS [Total
Spend]
FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY clientName
HAVING SUM(itemCostPrice * Quantity) * 1.35 >= 100000
ORDER BY SUM(itemCostPrice * Quantity) * 1.35 DESC
9.3.
SELECT SPName, SUM(Quantity)*10 AS Bonus
FROM tblClients, tblInvoices, tblSalesPeople
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY SPName
HAVING SUM(Quantity) >= 20
10. HomeDecor database.
10.1. Add a sales person called Fred with a cell number of 083 123 4567. Fred will be a sales
person without any clients allocated to him.
10.2. Create a query to join the sales people with their clients. List all the sales people even if
they do not have any clients.
10.3. List the sales persons who do not have any clients allocated.
11. List the total number of invoices that have been issued to each client. You may assume that
each time a client purchased, a new invoice was issued.
12. Determine how much revenue/sales were made each day using the invoices.
13. What is the total amount on the invoices for Mr O Lu?

3.6 More Advanced Queries

ACTIVITY 11
Consider the following query:
'Display all of the sales people's names and the number of sales items each one has sold'.
Before we look at each sales person, let's work out the total number of sales items sold. This is a
very simple, single table query.
Open the database [Link] and perform the following query:
SELECT SUM(Quantity) AS TotalNumberofPurchases
FROM tblInvoices

If you haven't performed any updates (INSERT, UPDATE or DELETE) on this database, then the total
number of items sold should be 642.

Page 90 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

We want to display the sales person's name (in tblSalesPeople, field SPName) and the quantity (in
tblInvoices, aggregate field SUM(Quantity)) s/he has sold.
Note: we want to know the total (SUM) of items the sales person has sold, not the number of times
(COUNT) the sales person performed a sale. Also, we want to return results for each sales person,
so a GROUP BY would need to be included.
Some of the query is then easy to code:
SELECT SPName, SUM(Quantity) AS [Total Items Sold]
FROM ???
WHERE ???
GROUP BY SPName

In order to replace the '???' items in the query, we need to establish the relationships between the
tables to avoid incorrect queries and/or queries that have partial or full Cartesian products involved.
Looking at the structures of the relationships between the tables concerned, it is clear that there's no
direct relationship between tblSalesPeople (for SPName) and tblInvoices (for Quantity), so we will
have to make use of any other relationships to get the required Result Set for the query.

Looking back at the relationships, you can see that:

 tblSalesPeople relates to tblClients on [Link] = [Link]

 tblClients relates to tblInvoices on [Link] = [Link]
 tblInvoices relates to tblItems on [Link] = [Link].
Note that to retrieve data from tblInvoices and tblSalesPeople we will have to use tblClients. From
the above, we will need to use three tables which will be listed in the FROM clause of the SQL
statement.
SELECT SPName, SUM(Quantity) AS [Total Items Sold]
FROM tblClients, tblInvoices, tblSalesPeople
WHERE ???
GROUP BY SPName

The WHERE clause is must specify how to link the tables together by using a compound AND
combination that describes the relationships above.
SELECT SPName, SUM(Quantity) AS [Total Items Sold]

Learning Unit 3 Advanced SQL Page 91

exploring IT: Java Programming Grade 12

FROM tblClients, tblInvoices, tblSalesPeople

WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY SPName
Code the query above and, if correct, you should see the output
alongside:
You can add the totals for each sales person and check that they total
642. Note that the GROUP BY clause automatically sorts according
to the field that is being grouped. If we wanted to order the Result Set
to a different order, we would need to add a specific ORDER BY
clause.
List the rank order (highest to lowest), based on the number of items
sold?
Add the bold line to the query:
SELECT SPName, SUM(Quantity) AS [Total Items
Sold]
FROM tblClients, tblInvoices, tblSalesPeople
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY SPName
ORDER BY SUM(Quantity) DESC Note that we cannot use the alias Total
Items Sold (enclosed in square brackets as
the field name contains spaces), we need to
use the original function in the ORDER BY
clause.

Note the ORDER BY clause is always after the GROUP BY clause. The GROUP BY clause sorts
according to the item it groups. If you want the Result Set in a different order, you will need to add
and ORDER BY clause.
Change the SQL statement to list the top three sales persons and their number of sales.
Add the bold type to the query:
SELECT TOP 3 SPName, SUM(Quantity) AS [Total Items Sold]
FROM tblClients, tblInvoices, tblSalesPeople
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY SPName
ORDER BY SUM(Quantity) DESC

List the sales persons (alphabetically) and number of sales for those sales people who sold 20 or
more items:
Remove the Top 3 part of the SELECT statement and the ORDER BY clause and include the
bold line at the end:
SELECT SPName, SUM(Quantity) AS [Total Items Sold]
FROM tblClients, tblInvoices, tblSalesPeople
WHERE [Link] = [Link]
AND clientID = clientID
GROUP BY SPName
HAVING SUM(Quantity) >= 20

The HAVING clause removes rows that have been produced by the GROUP BY clause. The
HAVING clause is not to be confused with the WHERE clause which remove rows before the GROUP
BY clause is executed.
Give a bonus of R10.00 per item sold to each sales person who sold more than (or equal to) 20 items:

Page 92 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

Change the SQL statement again to include a calculated field as part of the SELECT clause.
SELECT SPName, SUM(Quantity)*10 AS Bonus
FROM tblClients, tblInvoices, tblSalesPeople
WHERE [Link] = [Link]
AND clientID = clientID
GROUP BY SPName
HAVING SUM(Quantity) >= 20

We have now successfully done several queries using multiple tables.

ACTIVITY 12
Consider a query that would also involve the missing table tblItems to show all of the sales people's
names and the sales items they sold (itemDescription) and how many of each item was sold by
each sales person'.
Code the query as follows:
SELECT SPName, itemDescription, SUM(Quantity) AS [Total Items Sold]
FROM tblClients, tblInvoices, tblItems, tblSalesPeople
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
GROUP BY SPName, itemDescription

Notice that we have to include the relationship between tblInvoices and tblItems to be able to
access the itemDescription field in tblItems; this means that tblItems must then be included in the
FROM clause and the relationship [Link] = [Link] must be included in the
compound WHERE clause. Also, showing each sales person and each item sold by each sales
person requires the itemDescription field to be added in the GROUP BY clause. Remember that we
can include additional HAVING and ORDER BY clauses after the GROUP BY to enhance the query.

3.7 A General Strategy for Solving Multiple Table Queries

It was easy enough to perform queries last year on single tables and, to avoid a Cartesian product, a
relationship between 2 tables needs to be established (primary key in one table = foreign key in the
other table) and used in a WHERE clause and the query will be successful. But, owing to the
structure of a database in terms of the tables' fields and primary/foreign key relationships, there is
often not a direct relationship between the tables that provide the fields for the required query. If we
want to perform a query on two or more tables where direct relationships don't exist, we will have to
use those which do exist to create a 'path' that includes the required tables or the query cannot be
performed with the database in its current structure.
Using another example to demonstrate, let's perform the following query:
'Display all of the clients' names and how much each client spent in total'.
This is a simple statement, but might not be very easy to implement!

ACTIVITY 13
We will complete this activity by performing a number of analysis steps:
Step 1: Identify the field/s required and the tables containing those fields:
The SELECT clause must contain the required fields; client names from tblClients and the selling
prices of sales items from tblItems. We may require additional information/calculations to determine
how much each client spent, in total, but the selling price of each item purchased will certainly be
required as a starting point. The client's name field is clientName in tblClients and the selling price
field is itemCostPrice in tblItems.

Learning Unit 3 Advanced SQL Page 93

exploring IT: Java Programming Grade 12

Start the following query, using the information above:

SELECT clientName, itemCostPrice AS [Total Spend]
FROM tblClients, tblItems

Unfortunately, this query will generate a Cartesian product of all clients (70) and all sales items (22)
(giving a meaningless Result Set of 70 x 22 = 1540 rows) as no criteria have been set for the actual
sales items that were purchased by each client.
Step 2: Identify the required tables according to the primary/foreign key relationships:
We've already identified the tables of the required fields; tblClients and tblItems. These tables are
essential as at least one of their fields is required for the query. But, is there no direct relationship
between the tables, so we need to find a 'path' that links these tables using other table/s that do have
separate primary/foreign key relationships to these tables, otherwise the query can't be performed.
Let's look again at where the relationships do exist:

We can link tblClients to tblInvoices on [Link] = [Link] and tblInvoices

to tblItems on [Link] = [Link],
As long as this 'path' exists that can link tblClients to tblItems through tblInvoices, the query can be
performed.
Add tblInvoices to the FROM clause and code the WHERE clause so that it correctly indicates
the primary/foreign key relationships between the relevant tables:
SELECT clientName, itemCostPrice AS [Total Spend]
FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link]
AND [Link] = [Link]

The rest of this query doesn't relate to the relationships between the tables; those have correctly been
established above and the query will work, but it will show each client multiple times for each sales
item that was purchased. This isn't exactly what the original query statement requires.
Step 3: Analyse the original query statement to determine any further coding (grouping,
calculations and/or ordering) required:
We only want each client mentioned once with his/her total amount spent. This requires a GROUP
BY on the clients' name field and a SUM of the items' sales prices. By only using a GROUP BY
clause you will not see the total of the individual items' sales prices, you will need the aggregate
function (SUM).
Change the query to include the bold code:
SELECT clientName, SUM(itemCostPrice) AS [Total Spend]
FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY clientName

Page 94 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

3.7.1 Tracing a Result Set

Step 4: Trace into the Result Set to check that the query delivers what is required:
We need to be sure that the queries produce the correct results. We can simplify the process by
generating a sub-set of the Result Set and compare it to expected outcomes. For example, let's look
at a query that takes into account the results for only 1 client with all sales items shown and the
quantities purchased:

ACTIVITY 14
Create the following trace query to determine how much Mr. A Moosajee, ID 15, has spent.
SELECT clientName, itemDescription, itemCostPrice AS [Total Spend],
Quantity, itemCostPrice * Quantity AS [Grand Total]
FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'Mr A Moosajee'

This query will produce the following Result Set.

We need to add each value in the Total Spend and the Grand Total fields. We can do this by
exporting the query to Excel and using the SUM function, by adapting the SQL query to add the fields
or by simply using a calculator.
Exporting to Excel:
Make sure the query that produced the above result set is visible.
In MS Access version 2010, click on the External Data tab.
Select the Export to Excel button to generate a spreadsheet using the table.
Open the file in Excel and use the SUM function to add the items in a column.
Alternatively, adapt the query to find the SUM of the itemCostPrice field and the itemCostPrice *
Quantity fields.
Adapt the query to include the SUM function and to Group BY the client's name limited to only
'Mr. A Moosajee' using the HAVING clause.
SELECT clientName, SUM(itemCostPrice ) AS [Total Spend],
SUM(itemCostPrice * Quantity) AS [Grand Total]
FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY clientName
HAVING [Link] = 'Mr A Moosajee'

The following Result Set should be produced:

Learning Unit 3 Advanced SQL Page 95

exploring IT: Java Programming Grade 12

You can see that the sum of the values in the Total Spend column amounts to 48807 and the sum of
the values in the Grand Total column amounts to 81879. The original query request stated that the
total amount spent by each client should be indicated in the Result Set, so the original query doesn't
deliver the correct results – it only shows the total spent by each client if the client bought only 1 of
each item. The quantity of each item purchased has been ignored as it wasn't included in any of the
calculations. We need to include a calculation to calculate the product of itemCostPrice and
Quantity and use that result as the parameter to the SUM aggregate function.
Change the SELECT clause of the query as follows:
SELECT clientName, SUM(itemCostPrice * Quantity) AS [Total Spend]
FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY clientName;
and you will see that Mr A Moosajee (at least) has the correct total of 81879.
If you notice, the name of the field itemCostPrice indicates that it stores the cost of the item and not
the selling price of the item. If each item is increased by 35% to get the selling price then we would
have the correct result.
Change the query to multiply the itemCostPrice field by 1.35 to add 35% to the itemCostPrice.
SELECT clientName, SUM(itemCostPrice * 1.35 * Quantity) AS [Total
Spend]
FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY clientName;
What if we wanted to list the clients in highest to lowest order of total spent?
Simply add an ORDER BY to the query:
SELECT clientName, SUM(itemCostPrice * 1.35 * Quantity) AS [Total
Spend]
FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY clientName
ORDER BY SUM(itemCostPrice * Quantity) DESC
Remember in MS Access, if you want to ORDER BY a derived field or aggregate function, you
must include the complete code of the derived field or aggregate function as it was stated in the
SELECT clause (you can't use an alias (AS) reference in an ORDER BY clause).
If we only want results for a total spend of >= R100 000.00?
Add a HAVING clause (because it relates to an aggregate function result):
SELECT clientName, SUM(itemCostPrice * 1.35 * Quantity) AS [Total
Spend]
FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY clientName
HAVING SUM(itemCostPrice * Quantity) >= 100000
ORDER BY SUM(itemCostPrice * Quantity) DESC
and only for purchases of 'Interior' items?
Add another AND condition to the WHERE clause:
SELECT clientName, SUM(itemCostPrice * Quantity) AS [Total Spend]
FROM tblClients, tblItems, tblInvoices
WHERE clientID = clientID AND itemID = itemID
AND itemCategory = "Interior"

Page 96 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

GROUP BY clientName
HAVING SUM(itemCostPrice * Quantity) >= 100000
ORDER BY SUM(itemCostPrice * Quantity) DESC

The itemCategory field can be included in the WHERE clause as it is a field in one of the tables
required for the original query; it is not included in a HAVING clause as it is not an aggregate
function. Interestingly, the query only produces 3 results as there is only 3 people who purchased
interior sales items to a value of >= R100,000.00.
The last 3 examples show that you can make simple additions to queries that produce meaningful and
varied Result Sets; but, you need to follow the analysis steps to get the original query correct first.

EXERCISE 3

1. Adapt the previous query:

SELECT clientName, SUM(itemCostPrice * 1.35 * Quantity) AS [Total
Spend] FROM tblClients, tblItems, tblInvoices
WHERE [Link] = [Link] AND
[Link] = [Link]
GROUP BY clientName;

so that it produces a Result Set of clients that qualify for a discount. A 10% discount applies to
all total spends of >= R100,000.00. The query must only show the clients' names and the value
of the discount. You should use the ROUND(x,2) function to produce results that have 2
decimal places only for the discounts.
2. Write a query that will show how many of each sales item was sold during the promotion. The
query must have column headings 'Sales Item' and 'Number Sold'. Show the results in rank
order (highest to lowest) of number sold.
3. Adapt the previous query to show only items that are in the 'Interior' category.
4. Write a query that will determine the total profit generated by each sales person. The query
must have columns headings 'Sales Person Name' and 'Total Profit'. Remember, the profit
portion is 35% of the itemCostPrice.
5. Write a query that will determine how many clients each sales person sold to during the sales
promotion. The query must have column headings 'Sales Person' and 'Number of Clients'.
6. Write a query that will display each sales person and each client that the sales person sold to,
together with the number of items that were sold by each sales person to each client and the
total sales value (including 35% profit) of the items sold. The query must have column headings
'Sales Person', 'Client Name', 'Number of Items' and 'Total Value'.

3.8 SQL Statement to Create a Table

Consider a Bank database that contains a number of different tables. Two of the tables in the Bank
database, tblAccount and tblCustomer, are listed in the following tables. The table, tblAccount,
contains the fields AccNo, CustID, BranchID and Amount. This table stores the branch at which
each customer has their account, as well as the amount that the customer has in the bank. The table,
tblCustomer, has two fields, CustID and CustName.

tblAccount tblCustomer
AccNo CustID BranchID Amount CustID CustName AccNo
12345 1 Sandton 5000 1 Modise 12345
63547 3 City 10000 2 Thompson 98765

Learning Unit 3 Advanced SQL Page 97

exploring IT: Java Programming Grade 12

98765 2 Randburg 2500 3 Naidoo 63547

ACTIVITY 15
Write down
 the primary key of tblAccount
 the primary key of tblCustomer
 the foreign key of tblAccount
What field links the 2 tables?
Load MS Access and create a new database called Bank.
Create a query to create a table using a SQL statement for the table tblAccount.
CREATE TABLE tblAccount
(AccNo CHAR(15),
CustID CHAR(5),
BranchID CHAR(20),
Amount INTEGER,
PRIMARY KEY (AccNo));

The data type for each field must be specified. For String data, use CHAR with the maximum number
of characters in brackets. For numeric data, use INTEGER or DOUBLE. The primary keys are also
specified when the tables are created.
The table should be created and can be viewed in design view. Note that the AccNo field has been
set as the primary key.

Create another query to create the table tblCustomers. The CustID field is set to
AUTOINCREMENT to generate sequential numeric values.
CREATE TABLE tblCustomer
(CustID AUTOINCREMENT,
CustName CHAR(20),
PRIMARY KEY (CustID));

General form of the CREATE TABLE statement

CREATE TABLE table
(field1 data type1,
field2 data type2,
.
.
.
fieldn data typen,
PRIMARY KEY (primary key);

Page 98 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

EXERCISE 4

The following exercise is based on the Bank database you have just created.
1. Using the Database Tools relationship function in MS Access, link the tables using the CustNo
field to join the tables. Enforce referential integrity.
2. Exit the Database Tools and save each table using the names given.
3. In MS Access, in the Datasheet view of each table, add the data that is shown in the tables
above. You will have to add the data to tblCustomer first as tblAccount requires a related
customer ID.
4. Save the tables again once the data has been added.
5. Create a SQL statement to display each customer's name together with the name of the branch
their account is at, without using a Join.
6. Change the SQL statement to display each customer's name, together with the amount that
each has in his/her bank account.
7. Change the code to perform an Inner Join on Customer and Account Tables. You should get
the following result:
Modise 5000
Thompson 2500
Naidoo 10000
8. Change the inner join SQL query so that it returns the customer names and the Branch ID, as
well as the amount each customer has.
9. Add customers Adams and Vos to tblCustomer, with CustID's 4 and 5, respectively.
Assume for the purposes of this exercise that the details of Adams and Vos have not yet been
recorded in the table, tblAccount, because they are new bank customers, so do NOT make
any changes to the tblAccount table.
10. Run a left join on the Customer and Account Tables, which should produce:
Modise 2
Thompson 3
Naidoo 1
Adams
Vos
11. Add the following record to tblAccount:

AccNo CustID Branch Amount

67453 5 Randburg 500

12. Perform a right join on tblCustomer and tblAccount so that ALL the account numbers are
displayed.
13. Create a new table called tblBranch, using SQL statements. This table must contain the fields,
BranchID, BranchName and City. The primary key is BranchID. Use appropriate data types
for each of the fields. Note: The data type for the BranchID must be set to 'AUTOINCREMENT'
so that any inserts to the table must automatically allocate an auto-number value for the primary
key.
14. Check that the table has been created and that it contains no data.
15. Check the fields of each field in design view and that a primary key has been created.
16. Create a SQL statement to insert data into tblBranch. Use an INSERT statement that will
automatically assign a value for the primary key. Insert a record called 'City' located in
Johannesburg.
17. Change the SQL statement to insert more branches.

Learning Unit 3 Advanced SQL Page 99

exploring IT: Java Programming Grade 12

3.9 Jeppe Database

The Jeppe database was used in the previous Learning Unit. It has four tables: one to store the
details of the runners, one to store the details of the schools they run in, one for the details of the
races the runners participate in and one to store the results of the races.

ACTIVITY 16
Load the Jeppe database.
View the relationships between each of the four tables to remind yourself how the tables are
linked.

Perform the following SQL queries using the database:

A list of all the runners, sorted alphabetically.
A list of all the races that have been run.
The average age of all the runners.
Display all the runners' names and the name of the school in which they run.
List all the runners and their race results according to date and then time.
Display all the runners together with the races they have run and their times. All the runners
must be displayed even if they have not run a race. Which runner has not run any races?

EXERCISE 5

Open the Jeppe database and type in SQL statements to do the following:
1. Display the average time per school across all the races.
2. Display a list of all the runners' names who ran the Johnson Crane 25, with their finishing times.
3. Display all runners (and their times) who participated in any Johnson Crane races and have
times that are above average for all races.
4. Display a list of all the runners' names in Andy's school together with the rate that the school
runs at.
5. Display the average distance run per runner (correct to two decimal places).
6. Generate a code for each runner's name using two random digits (0 - 9 incl.), followed by the
second, third and fourth letter of their name, followed by the remainder of his/her age after
dividing by seven.
7. Display the total number of runners in each school only if there are more than 5 runners in the
school.
8. Find the oldest runner to have run 'Springs Striders 32'. Display the name and the time taken.
9. Find all the runners whose ages are 20, 30, 40, 50, 60 years old.

Page 100 Learning Unit 3 Advanced SQL

 exploring IT: Java Programming Grade 12

10. Find the runners who have not run any races.
11. Find the number of runners in each school.
12. List all the names of the races without duplicates.

Learning Unit 3 Advanced SQL Page 101