Studyledge

Databases and SQL: Question 10

Syllabus 9.4

Structured 9 marks

Neon Arcade tracks every game played in a single database table called GAME_SCORE, shown below. PrizeClaimed records whether a prize has already been given for that score.

ScoreID PlayerName GameName Points PrizeClaimed
SC01 Leah Fischer Pixel Racer 3800 True
SC02 Marco Silva Star Blaster 3100 False
SC03 Leah Fischer Star Blaster 5600 True
SC04 Devon Clarke Pixel Racer 2800 False
SC05 Marco Silva Pixel Racer 6200 True
SC06 Devon Clarke Star Blaster 3900 False

(a) Write an SQL statement to output the PlayerName and Points of every score for "Pixel Racer" where the Points are greater than 3000, with the highest Points listed first. [4]

(b) Write an SQL statement to find the highest Points value recorded for "Star Blaster". [2]

(c) Write an SQL statement to output the PlayerName and GameName of every score where a prize has already been claimed and the Points exceed 4000. [3]

Show worked solution Hide worked solution

Worked solution

Part (a): Filtering, combining conditions and sorting

Two conditions must both be true, GameName is “Pixel Racer” and Points is greater than 3000, so AND is needed, followed by ORDER BY ... DESCENDING to put the highest Points first:

SELECT PlayerName, Points
FROM GAME_SCORE
WHERE GameName = "Pixel Racer" AND Points > 3000
ORDER BY Points DESCENDING;

Checking against the table: the Pixel Racer rows are SC01 (3800), SC04 (2800) and SC05 (6200). SC04 fails Points > 3000, so only SC01 and SC05 remain. Sorted from largest to smallest Points:

PlayerNamePoints
Marco Silva6200
Leah Fischer3800

Part (b): Finding the highest score for one game

MAX must be restricted to Star Blaster rows only, using a WHERE clause:

SELECT MAX(Points)
FROM GAME_SCORE
WHERE GameName = "Star Blaster";

Checking against the table: the Star Blaster rows are SC02 (3100), SC03 (5600) and SC06 (3900). The largest of these is 5600, so the statement returns 5600. (Without the WHERE clause, MAX would compare every row in the table and wrongly return 6200. Marco Silva’s Pixel Racer score.)

Part (c): Combining a Boolean field with a numeric condition

PrizeClaimed is a Boolean field, so it can be tested directly in the WHERE clause without comparing it to “True” in quotation marks; it is combined with the Points condition using AND:

SELECT PlayerName, GameName
FROM GAME_SCORE
WHERE PrizeClaimed AND Points > 4000;

Checking against the table: SC01 has PrizeClaimed True but Points is only 3800, so it fails the second condition. SC03 (5600, True) and SC05 (6200, True) both satisfy both conditions:

PlayerNameGameName
Leah FischerStar Blaster
Marco SilvaPixel Racer

Final answers

  • (a) SELECT PlayerName, Points FROM GAME_SCORE WHERE GameName = "Pixel Racer" AND Points > 3000 ORDER BY Points DESCENDING;, outputs Marco Silva/6200, Leah Fischer/3800
  • (b) SELECT MAX(Points) FROM GAME_SCORE WHERE GameName = "Star Blaster";, returns 5600
  • (c) SELECT PlayerName, GameName FROM GAME_SCORE WHERE PrizeClaimed AND Points > 4000;, outputs Leah Fischer/Star Blaster, Marco Silva/Pixel Racer