Employee first names (chinook database)

Easy
5
77.4% Acceptance

In this lab, you will test and improve your SQL skills using the popular chinook dataset. This lab is tailored specifically for SQLite, ensuring that you get hands-on experience with SQLite syntax.

Lab Objective

Your task is to create SQL queries to retrieve specific data from the Chinook database based on the given challenges. The results of your queries will be validated against the expected outputs, and you need to ensure that your output matches the expected output in structure and order.

Dataset Overview

The chinook database is a fictitious digital music shop dataset. Here's a brief overview of the tables you'll work with:

  • Artist: Contains artist names.
  • Album: Includes album titles and their corresponding artists.
  • Track: Contains tracks, their associated albums, genre, media type, and other details.
  • MediaType: Describes types of media.
  • Genre: Holds music genres.
  • Playlist: Contains playlist names.
  • PlaylistTrack: A junction table connecting playlists with tracks.
  • Invoice: Provides invoice details.
  • InvoiceLine: Contains invoice line items.
  • Customer: Holds customer data.
  • Employee: Contains employee data including hierarchy.

Instructions

  1. Start by reading the challenge objectives carefully.
  2. Use SQLite syntax to craft your SQL queries. Ensure you're referencing the exact table and column names as given in the Chinook database schema.
  3. For clarity, always use double quotes around table and column names (e.g., SELECT "FirstName" FROM "Employee").
  4. Ensure you always have an ORDER BY clause in your queries when specified to avoid potential output mismatches.
  5. Once you've crafted your SQL query for a challenge, run it to test your solution.

Remember, accuracy is key! The order and structure of your results should match the expected output. Best of luck!

Challenges Information

Challenge 1: Reporting Structure

Objective: Retrieve the first names of employees who directly report to a specific supervisor.

Background: Every company has a hierarchical structure. In the chinook database, the Employee table keeps track of this with the 'ReportsTo' column. For this challenge, determine which employees directly report to the employee with ID 2.

Tables to Use: Employee

Instructions:

  1. Navigate to the Employee table.
  2. Filter records based on the 'ReportsTo' column having a value of 2.
  3. Your result should display only the FirstName of the employees.
  4. Arrange the results by the FirstName in ascending order.

Expected Output Columns: FirstName ordered by FirstName.