Expensive invoices (chinook database)

Easy
4
84.6% Acceptance

In this lab, you'll demonstrate your SQL skills by tackling various challenges based on the tables and relationships in the chinook dataset.

Dataset Overview

The chinook dataset is a sample database representing a digital media store. Below are the key tables you'll be working with:

  • Artist: Contains details about music artists.
  • Album: Contains music album details and is related to the Artist table.
  • Track: Contains details about individual music tracks, including links to Album, MediaType, and Genre tables.
  • Invoice: Provides information about customer invoices, including the total amount billed.
    ... (and so on for other tables)

Important Notes

  1. SQL Syntax: Make sure to use only SQLite-supported syntax.
  2. Table and Column Names: Always use double quotes around table and column names for clarity. For instance, "InvoiceId" and "Total".
  3. Order By Clause: To avoid discrepancies in output due to the default order in which SQLite retrieves records, always use an ORDER BY clause in your SQL queries wherever relevant. This ensures consistency in the results returned.

Challenge Overview

Here's a quick summary of the challenge you'll tackle in this lab:

  • Retrieve invoice records with a total amount greater than $10. Extract the columns "InvoiceId" and "Total". Order the results by the "Total" amount in descending order.

Make sure your SQL query extracts columns in the exact sequence mentioned, and always remember to use the ORDER BY clause to ensure the results match the expected output.

Submission

Once you've written your SQL solution for the challenge, save and submit it. Your submission will be evaluated against a predefined solution. Make sure to test your solution thoroughly before final submission.

Good luck, and happy querying!

Challenges

Challenge 1: Analyzing High-Value Invoices

Objective: Retrieve invoice records where the billed amount exceeds a certain threshold.

Description: In any business, it's crucial to monitor larger transactions as they could indicate significant customers or bulk orders. For this task, you're asked to delve into the Invoice table in the chinook database.

Instructions:

  • Write an SQL query to extract the InvoiceId and Total columns from the Invoice table.
  • Your query should filter the records to only display invoices where the Total amount is greater than $10.
  • The resulting list should be ordered by the Total amount in descending order.

Expected Output Columns:

  • InvoiceId
  • Total

Hint: Ensure to use double quotes for table and column names, like "InvoiceId" and "Total", in your query for clarity.