Studyledge

Databases and SQL: Question 3

Syllabus 9.4

Structured 7 marks

Echo Peak Studios records every recording session it hosts in a single database table called SESSION, shown below.

SessionID ArtistName Genre DurationMins Mastered
S01 Nova Ray Pop 90 True
S02 Echo Drift Rock 120 False
S03 Nova Ray Jazz 60 True
S04 Static Bloom Rock 75 True
S05 Kite String Pop 105 False
S06 Echo Drift Jazz 45 False

(a) A studio assistant runs this SQL statement:

SELECT ArtistName, Genre, DurationMins
FROM SESSION
WHERE Genre = "Rock" OR Genre = "Jazz"
ORDER BY DurationMins DESCENDING;

Using the table, write out the output produced by this statement. [4]

(b) Write an SQL statement that will output the SessionID and ArtistName of every session where the Genre is "Pop" and the session has already been mastered. [3]

Show worked solution Hide worked solution

Worked solution

Part (a): Tracing the SQL statement

Step 1. Apply the WHERE clause. Keep only rows where Genre is “Rock” or “Jazz”:

SessionIDArtistNameGenreDurationMins
S02Echo DriftRock120
S03Nova RayJazz60
S04Static BloomRock75
S06Echo DriftJazz45

(S01 and S05 are excluded because their Genre is “Pop”.)

Step 2. Apply the SELECT clause. Keep only the ArtistName, Genre and DurationMins fields (this has already been done above, since SessionID is not requested).

Step 3, apply ORDER BY DurationMins DESCENDING. Sort the remaining rows from the largest DurationMins to the smallest: 120, 75, 60, 45.

The final output is:

ArtistNameGenreDurationMins
Echo DriftRock120
Static BloomRock75
Nova RayJazz60
Echo DriftJazz45

Part (b): Writing a new SQL statement

The requirement combines two conditions that must both be true, Genre is “Pop” and the session is mastered, so the two conditions need AND. Mastered is a Boolean field, so it can be tested directly without comparing it to “True” in quotation marks:

SELECT SessionID, ArtistName
FROM SESSION
WHERE Genre = "Pop" AND Mastered;

Checking against the table: S01 has Genre “Pop” and Mastered is True, so it matches. S05 has Genre “Pop” but Mastered is False, so it does not match. The statement therefore outputs only:

SessionIDArtistName
S01Nova Ray

Final answers

  • (a) Output, in order: Echo Drift/Rock/120, Static Bloom/Rock/75, Nova Ray/Jazz/60, Echo Drift/Jazz/45
  • (b) SELECT SessionID, ArtistName FROM SESSION WHERE Genre = "Pop" AND Mastered;, outputs only S01, Nova Ray