Working with Subqueries

Easy
26
91.7% Acceptance

Welcome to this SQL coding lab! The objective is to deepen your understanding of subqueries in SQL using the Chinook dataset. We will focus on incorporating subqueries in the SELECT and WHERE clauses to perform more advanced queries.

Lab Environment

  • Database: SQLite
  • Dataset: Chinook

Tables You'll Use

  • "Album"
  • "Track"

Challenge Overview

You will be tasked with listing the names of albums from the "Album" table that contain tracks in the "Track" table with "UnitPrice" higher than the average unit price across all available tracks.

Key SQL Concepts

  • SELECT
  • WHERE
  • Subquery
  • AVG()

Instructions

  1. Explore the Chinook Database: You don't need to download anything. The Chinook database is already set up in your environment. Feel free to explore the "Album" and "Track" tables to familiarize yourself with the data.

  2. Write Your SQL Code: Start writing your query. Make sure to stick to the SQLite syntax, as this lab environment uses SQLite.

  3. Run the Query: Once you're satisfied with your SQL query, go ahead and run it. Make sure it executes without errors and review the output to ensure it meets the challenge criteria.

  4. Submission: Save your final SQL query. Your submission will be automatically evaluated against the correct answer to determine your score.

Challenge

Objective:
Your task is to list the distinct names of albums from the Album table that contain tracks in the Track table with UnitPrice higher than the average unit price across all available tracks. The result should be sorted by AlbumId in ascending order.

Concepts to Master:

  • SELECT to choose the specific columns: Album.Title.
  • WHERE to filter records based on conditions.
  • Subquery to perform a secondary query within the main query for average UnitPrice.
  • AVG() to calculate the average unit price of all tracks.

Requirements:

  1. Use only the Album and Track tables from the Chinook dataset.
  2. Your query should only display the distinct names of qualifying albums, meaning each album name should appear only once in your result.
  3. The result should be sorted by AlbumId in ascending order.
  4. Utilize a subquery in the WHERE clause to find the average unit price across all tracks. Compare each track's unit price against this average to determine if it exceeds the average.
  5. The output column should be named Title.