Finding errors

In the example on the previous page, the predicted result and actual result do not match.

When this happens, it is necessary to go back to the design and implementation phases to try to identify the error.

Design

Query Design: Return a list of all pupils grouped by their pastoral care teacher and ordered by their merits points in descending order

Field(s)	First Name, Surname, Pastoral Care Teacher, Merit Points
Table(s)	Pupil
Criteria	GROUP BY Pastoral Care Teacher
Sort Order	Merit Points DESC

Field(s)

First Name, Surname, Pastoral Care Teacher, Merit Points

Table(s)

Pupil

Criteria

GROUP BY Pastoral Care Teacher

Sort Order

Merit Points DESC

The design for the query is fine.

Implementation

A mistake has been made during implementation.

SELECT firstname, surname, pastoralCareTeacher, meritPoints

FROM Pupil

ORDER BY meritPoints DESC

GROUP BY pastoralCareTeacher;

The reason our actual result is not the same as our predicted result is because GROUP BY must precede ORDER BY. If we swapped the order of these SQL clauses, then the query should work as intended.

The SQL code should have read:

SELECT firstname, surname, pastoralCareTeacher, meritPoints

FROM Pupil

GROUP BY pastoralCareTeacher;

ORDER BY meritPoints DESC

This would return:

PupilID	First Name	Surname	Class	Pastoral Care Teacher	Merit Points
0010	Thomas	Glacney	1M1	Miss Armstrong	91
0011	Frankie	Al-Robeye	1M2	Mr Gilchrist	71
0003	Amy	Jones	1M1	Mr Gilchrist	55
0006	Antonia	Gallagher	1M2	Mr Gilchrist	21
0004	James	McNamee	1T1	Mr Harley	88
0009	Beatrice	Panner	1T2	Mr Harley	77
0001	Zainab	Singh	1T1	Mr Harley	74
0007	Amy	McNamee	1T1	Mr Harley	63
0008	Aysha	Matazinadze	1T1	Mr Harley	45
0005	Pawel	McTavish	1I1	Mrs Aitken	19
0002	David	Boland	1I2	Mrs Aitken	12

PupilID	0010
First Name	Thomas
Surname	Glacney
Class	1M1
Pastoral Care Teacher	Miss Armstrong
Merit Points	91

PupilID	0011
First Name	Frankie
Surname	Al-Robeye
Class	1M2
Pastoral Care Teacher	Mr Gilchrist
Merit Points	71

PupilID	0003
First Name	Amy
Surname	Jones
Class	1M1
Pastoral Care Teacher	Mr Gilchrist
Merit Points	55

PupilID	0006
First Name	Antonia
Surname	Gallagher
Class	1M2
Pastoral Care Teacher	Mr Gilchrist
Merit Points	21

PupilID	0004
First Name	James
Surname	McNamee
Class	1T1
Pastoral Care Teacher	Mr Harley
Merit Points	88

PupilID	0009
First Name	Beatrice
Surname	Panner
Class	1T2
Pastoral Care Teacher	Mr Harley
Merit Points	77

PupilID	0001
First Name	Zainab
Surname	Singh
Class	1T1
Pastoral Care Teacher	Mr Harley
Merit Points	74

PupilID	0007
First Name	Amy
Surname	McNamee
Class	1T1
Pastoral Care Teacher	Mr Harley
Merit Points	63

PupilID	0008
First Name	Aysha
Surname	Matazinadze
Class	1T1
Pastoral Care Teacher	Mr Harley
Merit Points	45

PupilID	0005
First Name	Pawel
Surname	McTavish
Class	1I1
Pastoral Care Teacher	Mrs Aitken
Merit Points	19

PupilID	0002
First Name	David
Surname	Boland
Class	1I2
Pastoral Care Teacher	Mrs Aitken
Merit Points	12

Now that the actual result matches the predicted result, testing is complete for this query.

Fitness for purpose

Example

��ý

Testing and evaluationFinding errors

Finding errors

Design

Implementation

More guides on this topic

����ý

Finding errors

Design

Implementation

More guides on this topic

��ý