Multi-table Subquery

Hard
22
79.3% Acceptance

Welcome to this SQLite lab where you will be honing your SQL skills using the Chinook dataset. The Chinook dataset represents a digital media store, including tables for artists, albums, media tracks, invoices, and customers.

Objective

Your goal is to complete SQL challenges based on the data and relationships in the Chinook database.

Dataset Description

The Chinook dataset consists of the following tables:

  • "Artist" with columns: "ArtistId" and "Name".
  • "Album" with columns: "AlbumId", "Title", and "ArtistId".
  • "Track" with columns: "TrackId", "Name", "AlbumId", "MediaTypeId", "GenreId", "Composer", "Milliseconds", "Bytes", and "UnitPrice".
  • ... [and so on for each table]

Challenges

Retrieve the track with the longest duration for each genre.

Instructions:

  1. From the "Track" table, fetch the "GenreId" and the name of the track corresponding to the maximum duration for each genre.
  2. Your result should have two columns: "GenreId" and the track's name with the maximum duration, named as "MaxDurationTrackName".
  3. Order your results by "GenreId" in ascending order to ensure consistency in output.

Expected Output Columns:

  • "GenreId"
  • "MaxDurationTrackName"

To ensure success:

  • Stick closely to the given schema, table names, and column names.
  • Use double quotes around table and column names for clarity.
  • Remember to order your results when necessary to match the expected output.