Track Length Categories (chinook database)

Medium
23
2
93.8% Acceptance

This lab aims to test and enhance your SQL querying skills using the Chinook dataset. You'll tackle challenges that range from simple selects to complex joins and aggregations. Your goal is to complete the challenges by writing SQL queries that produce the exact desired outputs.

Database Overview

The Chinook database consists of various tables. For the current challenge, you will focus primarily on the "Track" table. The schema for the "Track" table is as follows:

  • "Track"
    • "TrackId" (Primary Key)
    • "Name"
    • "AlbumId" (Foreign Key to "Album")
    • "MediaTypeId" (Foreign Key to "MediaType")
    • "GenreId" (Foreign Key to "Genre")
    • "Composer"
    • "Milliseconds"
    • "Bytes"
    • "UnitPrice"

Task

Your task is to categorize each track based on its duration into three categories: 'Short', 'Medium', and 'Long'. This classification should be based on the track's duration (in the "Milliseconds" column) as per the following criteria:

  • Short: 0 - 120,000 milliseconds (0 - 2 minutes)
  • Medium: 120,001 - 300,000 milliseconds (2 - 5 minutes)
  • Long: Above 300,000 milliseconds (5 minutes and above)

Expected Output:

  • Produce a result set with two columns:
    • "Name": The name of the track.
    • "LengthCategory": The category ('Short', 'Medium', 'Long') the track belongs to based on its duration.

Order By:

  • Sort your results first by "LengthCategory", and then by "Name".

Important Tips:

  • Ensure you always use double quotes around column and table names for clarity.
  • Remember to match the column names and ordering EXACTLY as described in the challenges to pass the lab tests.
  • Ensure your final queries are clear, optimized, and return results in the specified order.

Best of luck, and happy querying!