19_Subqueries_2.pdf - CITS1402 Relational Database...

CITS1402
Relational Database Management Systems
Video 19 — Subqueries II
Gordon Royle
Department of Mathematics & Statistics

Derived Tables
A subquery can be used in
FROM
instead of named tables — this is
called a derived table .
SELECT Genre.name, COUNT (*) AS numTracks
FROM Track JOIN Genre
ON Track.genreId = Genre.genreId
GROUP BY Track.genreId;

Derived Tables
We can just “pretend” that this is an actual database table and
use it in a query.
SELECT name, numTracks FROM
( SELECT Genre.name, COUNT (*) AS numTracks
FROM Track JOIN Genre
ON Track.genreId = Genre.genreId
GROUP BY Track.genreId)
WHERE name = ' Rock ' OR name = ' Jazz ' ;

Long tracks
What is the length of the longest track in each genre?
SELECT Genre.name, MAX (Track.milliseconds)
FROM Genre JOIN Track
ON Genre.genreId = Track.genreId
GROUP BY Track.genreId;

Dangerous SQLite magic
What is the name of the longest track in each genre?
SELECT Genre.name, Track.name, MAX (Track.milliseconds)
FROM Genre JOIN Track
ON Genre.genreId = Track.genreId
GROUP BY Track.genreId;

This should not work
It is informative to recognise why this is logically incorrect .
The issue is that
I
The Track.genreId field is one of the
GROUP BY
fields
I
The Track.name field is not one of the
GROUP BY
fields
Therefore, within each group ,
I
every row has the same value for Track.genreId , but
I
each row may have a different value for Track.name .