Assignment 2

Overview

Relational databases give you more power at the expense of having to learn a new “language.” In this exercise, you’ll use SQL to easily perform some analyses that were difficult using a spreadsheet. However, as with the last assignment, the final few exercises will push “simple” SQL to its limits; ideally, these limitations will provide intuition and motivation for some of the more advanced SQL features we’ll be covering next.

Getting Started

Download the YouTunes SQL database, which is a SQLite version of Week 1’s YouTunes spreadsheet, with sheets converted to tables, but, per Week 1’s synchronous class, with more IDs and even more tables!

Additionally, download and install DB Browser for SQLite locally on your computer.

Using the YouTunes SQL database, solve the business problems below. Write the queries that answer the problems in the space provided. As in the previous assignment, if any one problem feels too hard and you find yourself taking more than 20–30 minutes on it, stop and just write a short paragraph explaining what you tried and conceptually, what you need to do to answer the query.

Question 1

Part 1*

Let’s begin with the difficult problem from last assignment. Write a SQL query that creates a new table SongPrices that contains the TrackId, name, and price of each song that has been purchased. (Hint: You will want to take advantage of the UnitPrice column in the Tracks table and use CREATE TABLE SongPrices AS SELECT ...)

Part 2

Will every song that appears in the Track table appear in the SongPrices table?

Part 3

Now, suppose that the UnitPrice column does not exist in the Tracks table. How might you approach Part 1 in this case? Would your answer to Part 2 also change? If so, why?

Question 2

Part 1

Write a SQL query that creates a table called InvoicePrice containing just an InvoiceId and the total value of the invoice.

Part 2

Now, compute the average revenue per invoice.

Part 3

Write a SQL query to compute the total value of all invoices.

Question 3

Write a SQL query that will create a Songs table containing all the columns from the Songs spreadsheet from Assignment 1.

Question 4

Write a SQL query to determine who Lucas Mancini’s Sales Rep is.

Question 5

Write a SQL query that produces a list of songs for the Playlist titled “Brazilian Music”.

Question 6

Write the SQL queries necessary to construct a playlist, named “My Playlist”, that includes the following songs: “Wake Up Alone”, “Until We Fall”, “Wide Awake”, and “Eggtown”. (You need only write the INSERT queries, even though you will undoubtedly use some SELECT queries to gather information.)

Question 7

Part 1

You think that you may have some duplicates in your Track list. Write a SQL query to find tracks that have the same title and composer but have different track ids.

Part 2

Based on the result of your query from Part 1, how many times does a pair of potential duplicate songs appear? Why?

Part 3

If your answer to Part 2 was greater than once per pair, can you think of a way to modify your query from Part 1 to make it return each pair only once? (This one might be a bit difficult so if you cannot get it in a reasonable amount of time, just write down your thought process.)

Question 8

Write a SQL query that finds all sets of Albums that are part of multi-volume or multi-disk sets. There are many ways to interpret this and we expect everyone will make different assumptions.