Artists info (chinook database)

Easy
4
31.9% Acceptance

In this lab, you will be presented with a series of challenges that test your SQL querying skills using the SQLite-supported chinook database schema.

Dataset Overview

The chinook database contains a rich set of tables that capture information about artists, albums, tracks, genres, playlists, invoices, and more. The schema of some critical tables is as follows:

  • Artist

    • "ArtistId": The primary key.
    • "Name": Artist's name.

  • Album

    • "AlbumId": The primary key.
    • "Title": Album title.
    • "ArtistId": Foreign key to the Artist table.

  • Track

    • Various columns including "Name" for the track name and "AlbumId" for foreign key relation to the Album table.

Instructions

  1. You will be provided with a set of challenges. Each challenge will require you to write SQL queries to extract, transform, or analyze the data in the chinook database.

  2. Ensure you use double quotes around table and column names for clarity and to avoid potential issues with reserved words or special characters.

  3. Given the diverse nature of the data in the database, it's essential to order your results to ensure consistent outputs. When crafting your solutions, always use an ORDER BY clause unless instructed otherwise.

  4. Pay close attention to the exact column names mentioned in each challenge. Your solutions should match these column names exactly, as they will be used for evaluation purposes. For instance, in one of the challenges, you'll be retrieving artist names and album titles. Your output columns should be named "ArtistName" and "AlbumTitle", exactly as defined in the solution.

  5. Ensure you understand the relationships between tables, especially where foreign keys are involved. Use the correct type of JOIN operation based on the requirements of each challenge.

Evaluation

Your solutions will be automatically evaluated against a set of expected outputs. Make sure you thoroughly test your queries to ensure they meet the requirements of each challenge. Any deviation from the expected column names, ordering, or output will result in the challenge being marked as incomplete.

Happy querying, and good luck!

Challenges Information

Challenge 1: Artist Names and Album Titles

Objective: Your task is to extract a list showcasing the artist names along with their respective album titles.

Tables Involved:

  • Artist
  • Album

Instructions:

  1. Columns to Retrieve:

    • Artist.Name: This represents the name of the artist.
    • Album.Title: This denotes the title of the album.

  2. Ordering:

    • The resulting data should be ordered by the artist's name (Artist.Name).

  3. Joining Tables:

    • You need to join the Artist and Album tables using the ArtistId. Your SQL query should utilize the JOIN operation based on this relationship.

Hint:

  • Your resulting dataset should have columns named ArtistName and AlbumTitle
  • Remember to use double quotes around table and column names for clarity.