Example

A list of all pupils who are members of the orchestra should be returned, provided that the pupil receives tuition. The list should be sorted in descending order of surname.

Design

Query Design: Return a list of all pupils who are members of the Orchestra and receive tuition

Field(s)	First Name, Surname, Tuition
Table(s)	Pupil, Orchestra
Criteria	Tuition = True
Sort Order	Surname DESC

Field(s)

First Name, Surname, Tuition

Table(s)

Pupil, Orchestra

Criteria

Tuition = True

Sort Order

Surname DESC

Implementation

SELECT Pupil.First Name, Pupil.Surname, Orchestra.Tuition

FROM Orchestra

INNER JOIN Pupil ON Orchestra.PupilID = Pupil.PupilID

WHERE Orchestra.Tuition = ‘No’

ORDER BY Pupil.Surname DESC;

Testing

Predicted result

The predicted result shows what the developer expects the query to return.

First Name	Surname	Tuition
James	McNamee	Yes
Tabiso	Matazinadze	Yes

First Name	James
Surname	McNamee
Tuition	Yes

First Name	Tabiso
Surname	Matazinadze
Tuition	Yes

Actual result

The actual result of the query is then recorded. It can be compared to the expected result to make sure that the query has executed as expected.

First Name	Surname	Tuition
Beatrice	Panner	No
Amy	McNamee	No
Amy	Jones	No

First Name	Beatrice
Surname	Panner
Tuition	No

First Name	Amy
Surname	McNamee
Tuition	No

First Name	Amy
Surname	Jones
Tuition	No

Testing and evaluationExample