Assignment 2
Tackle as many of these problems as you can, submitting your answers along the way via this Google Form, which should autosave your answers. Be sure to click SUBMIT at the bottom of the Google Form when you have completed the assignment. You should receive an email receipt when the form has been properly submitted. You can resubmit, too, as many times as you’d like before the assignment’s deadline. Refer to these instructions for guidance on submitting.
Please be mindful of the course’s policy on academic honesty as you complete this assignment. When you engage with others regarding this assignment in peer learning sessions, be aware of our guidelines for assignment-related discussions. With the above in mind, the solution for the first item below denoted with an asterisk (*) may be openly discussed without restriction during peer learning sessions.
Not to worry if you run into trouble. Do just attend office hours for help! Further, consult our AI-based tool, cs50.ai.
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.