Studyledge

Databases and SQL: Question 4

Syllabus 9.1, 9.4

Structured 8 marks

A school makerspace tracks every 3D-printing job in a single database table called PRINTJOB, shown below. The Priority field always stores a single letter: H (high), M (medium) or L (low).

JobID StudentName Priority FilamentGrams Completed
PJ01 Amara Chen H 45 True
PJ02 Femi Osei M 120 False
PJ03 Amara Chen L 30 True
PJ04 Ravi Patel H 60 False
PJ05 Femi Osei M 90 True
PJ06 Tomasz Nowak L 15 False

(a) State the most suitable basic data type for the Priority field. [1]

(b) Write an SQL statement to output the JobID and FilamentGrams of every job with Priority "H" or Priority "M", with the job using the largest amount of filament listed first. [4]

(c) Write an SQL statement to calculate the total number of grams of filament used by jobs that have already been completed. [3]

Show worked solution Hide worked solution

Worked solution

Part (a): Choosing a data type for Priority

Priority only ever holds a single letter (H, M or L) never a longer word, so the most suitable basic data type is character, not text (text/string is used for fields that may need more than one character).

Part (b): Writing the ORDER BY / OR statement

Two separate Priority values need to be included, so the condition needs OR:

SELECT JobID, FilamentGrams
FROM PRINTJOB
WHERE Priority = "H" OR Priority = "M"
ORDER BY FilamentGrams DESCENDING;

Checking against the table: rows with Priority “H” or “M” are PJ01 (45), PJ02 (120), PJ04 (60) and PJ05 (90); PJ03 and PJ06 are excluded because their Priority is “L”. Sorting these four by FilamentGrams from largest to smallest gives:

JobIDFilamentGrams
PJ02120
PJ0590
PJ0460
PJ0145

Part (c): Writing the SUM statement

Only completed jobs should be included, so a WHERE clause is needed alongside SUM. Completed is a Boolean field, so it can be tested directly:

SELECT SUM(FilamentGrams)
FROM PRINTJOB
WHERE Completed;

Checking against the table: the jobs with Completed = True are PJ01 (45 g), PJ03 (30 g) and PJ05 (90 g). Adding these together:

45 + 30 + 90 = 165

So the statement returns 165.

Final answers

  • (a) Priority = character
  • (b) SELECT JobID, FilamentGrams FROM PRINTJOB WHERE Priority = "H" OR Priority = "M" ORDER BY FilamentGrams DESCENDING;, outputs PJ02/120, PJ05/90, PJ04/60, PJ01/45
  • (c) SELECT SUM(FilamentGrams) FROM PRINTJOB WHERE Completed;, returns 165