CS 0479 Sections 002 and 003 Assignment #4
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
CS 0479 Sections 002 and 003
Assignment #4
The questions in this assignment refer to the Chinook database, which is a database about a fictitious music store. It has been provided along with this assignment as an SQLite file. It has the following tables:
• albums: information about the albums that the store sells individual tracks from
• tracks: information about the tracks present in an album like name, genre, composer, milliseconds etc.
• artists: information about the artists (individual musicians or groups) that made the albums and tracks
• customers: information about the customers of the store
• employees: information about the employees of the store
• genres: information about the musical genres associated with tracks
• invoice Items: information about Individual tracks sold, linked to invoices
• invoices: information about each sale to a customer of one or more tracks
• media types: formats in which tracks are sold
• playlists: information about playlists, which each consist of a set of tracks
• playlist_track: relationships between playlists and tracks
For each of the questions below, do not only give the answer, but also give the SQL you wrote to get the answer. You can hand in a .sql or a .ipynb file.
Each question is worth 7 points, except for the last two, which are worth 8.
1. Using the invoices table, find out the BillingCity in the United Kingdom that has the second most invoices? (Note that the invoices table has orders from many countries.)
2. What is the average number of tracks purchased per customer? (Note you can ignore the Quantity field in the invoice_items table which is always one.) Round your answer to two places after the decimal point.
3. How many albums have jazz tracks?
4. How many comedy tracks were purchased in total? (Note you can ignore the Quantity field in the invoice_items table which is always one.)
5. What is the name of the customer who purchased the most Blues tracks?
6. On the Grunge playlist, what is the name of the track that comes second alphabetically?
7. How many albums sold more than $20 worth of tracks? (Note you can ignore the Quantity field in the invoice_items table which is always one.)
8. What is the average number of customers supported by each support rep, rounded to one place after the decimal point? Note that SupportRepid in the Customers table corresponds to Employeeid in the Employees table.
9. How many genres had sales exceeding $200?
10. What artist has the third most albums in the database?
11. Find the names of the tracks on the album "Fireball" and put them in alphabetical order. What is the name of the third track (in alphabetical order, not in the order in which they appeared on the album)?
12. How many artists in the database do not have any albums in the database?
13. What is the name of the second shortest album, in terms of the total milliseconds of its tracks stored in the database?
14. Find the number of invoices per billing city. Although there are 53 billing cities in the invoices table, there are are only a few distinct values of invoices per city. What are all of these values?
2022-12-12