19_Subqueries_2.pdf - CITS1402 Relational Database...

This preview shows page 1 - 7 out of 20 pages.

The preview shows page 5 - 7 out of 20 pages.
CITS1402Relational Database Management SystemsVideo 19 — Subqueries IIGordon RoyleDepartment of Mathematics & Statistics
Derived TablesA subquery can be used inFROMinstead of named tables — this iscalled aderived table.SELECTGenre.name,COUNT(*)ASnumTracksFROMTrackJOINGenreONTrack.genreId = Genre.genreIdGROUP BYTrack.genreId;
Derived TablesWe can just “pretend” that this is an actual database table anduse it in a query.SELECTname, numTracksFROM(SELECTGenre.name,COUNT(*)ASnumTracksFROMTrackJOINGenreONTrack.genreId = Genre.genreIdGROUP BYTrack.genreId)WHEREname ='Rock'ORname ='Jazz';
Long tracksWhat is thelengthof the longest track in each genre?SELECTGenre.name,MAX(Track.milliseconds)FROMGenreJOINTrackONGenre.genreId = Track.genreIdGROUP BYTrack.genreId;
Dangerous SQLite magicWhat is thenameof the longest track in each genre?SELECTGenre.name, Track.name,MAX(Track.milliseconds)FROMGenreJOINTrackONGenre.genreId = Track.genreIdGROUP BYTrack.genreId;
This should not workIt is informative to recognise why this islogically incorrect.The issue is thatITheTrack.genreIdfield is one of theGROUP BYfieldsITheTrack.namefield isnotone of theGROUP BYfieldsTherefore,within each group,Ievery row has thesame valueforTrack.genreId, butIeach row may have adifferent valueforTrack.name.

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 20 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
One
Professor
N/A
Tags
Relational Database, Relational model

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture