Exercise: Subquery Fundamentals

Find all tracks whose duration_seconds is greater than the average track duration. Show title and duration_seconds from the tracks table. Order by duration_seconds descending.

Use a scalar subquery in WHERE to return the title and duration of the longest track on the tracks table.

Find tracks whose duration_seconds is greater than the average track duration on the album with album_id = 1 (Ocean Drive). Show title and duration_seconds from the tracks table. Order by duration_seconds descending.

Find tracks belonging to albums in the Pop genre using IN with a subquery on the albums and genres tables. Show title and duration_seconds from the tracks table. Order by title.

Use a derived table to return each user's total duration_listened from the streams table, joined back to users for the username. Show username and total_seconds. Only include users with at least one stream. Order by total_seconds descending.

Find tracks whose duration_seconds is at least half the longest duration on the platform. Use a scalar subquery with arithmetic in the WHERE clause on the tracks table. Show title and duration_seconds. Order by duration_seconds descending.