Loading...
Please wait while we prepare your content
The Chinook Database
Note: For a better view of the exercise and solution panel, please close the left sidebar.
Introduction
This is the Chinook sample database representing a digital media store. The database contains information about the store's music catalog, including artists, albums, tracks, and media types, as well as customer data, invoices, and employee information.
The dataset includes various tables that allow you to explore relationships between artists and their albums, track sales and customer purchases, employee hierarchies, and customer demographics across different countries. You can analyse sales performance, customer behavior, music catalog statistics, and business operations from multiple perspectives.
Entity Relationship Diagram
Questions
1. From table Customers, find all unique cities in Germany.
Your resulting table should have city names only.
2. Find all customers in the USA.
Sort the table by customer first names alphabetically, then by their last names in reverse alphabetically.
3. Find the three most expensive bills in Boston, USA.
Sort the table by the bill amount from highest to lowest.
4. Find the third and fourth most expensive bill in 2012.
Sort the table by the bill total from highest to lowest.
5. Which employee has worked in the company for the longest time?
6. Find all the countries from table Invoices whose BillingState is NULL.
Your resulting table should have unique country names only. Sort the table by country names alphabetically.
7. Find all customers who work at Google, Microsoft or Apple.
Sort the table by state then city names, both alphabetically.
8. Find all the unique cities from table Invoices that DO NOT start with vowels ('a','e','i','o','u').
Your resulting table should have unique city names only. Sort the table by city names alphabetically.
9. Find all employees who started working after May 2003.
Sort the table by their last names.
10. Find all invoices in the third quarters of 2012 and 2013.
Sort the table by InvoiceDate from earliest to latest.
11. Show the most expensive invoices in July, 2013.
If there are multiple invoices with the same price, show the first one that appears in alphabetical order of Billing Postal Code.
12. Assuming that one month has 30 days, display the number of months that each employee has worked until January 1, 2010.
Round the number of months down to the nearest integer. For example: 20 months 28 days will be considered 20 months.
Your resulting table should have employee last name, first name and the number of months they have worked for.
- Hint 1: How to calculate the difference between two dates in SQLite
- Hint 2: Datetime format in SQL is: 'YYYY-MM-DD'
- Hint 3: CAST function can be helpful to rounding real number in Sqlite. Syntax for CAST: CAST(expr AS INTEGER)