This assignment has five (5) problems worth 100 points total. For each problem, write an  SQL query against the Chinook Database v1.4. Each query must run successfully using DB Browser for SQLite. A description of the correct result set for each problem is provided – your query must reproduce this result exactly (including attribute names/order and row order/contents).
To help, you have been provided an SQLiteDiff utility to compare the output of your query versus a supplied answer in CSV format. To use this program, create a text file that contains only your SQL query for a particular problem. Then run the program, supplying first the path to the supplied CSV file to compare against, then the path to your SQL file:
$ java -jar SQLiteDiff.jar p1.csv p1.sql
The program will either report success, or indicate the row/column where something differs. Note that each question warns against using numeric ids (i.e. internal foreign key values). If your solution uses such identifiers, or tries to “game the system” (i.e. write a query that produces the correct output but does not adhere to the spirit/constraints of the question),  you will receive no credit.
Each question also indicates a required sorting of the resulting rows. Because a database management system may produce rows in an arbitrary order, it is always good practice to explicitly indicate sorting in your SQL. Thus, if the result set has more than one row and your SQL does not fully specify row-sorting order (according to the problem) you will lose 50% credit, even if the output happens to match the answer. To receive credit, submit to Blackboard a single ZIP file that contains only the following files:
• p1.sql: your SQL query for problem 1
• p2.sql: your SQL query for problem 2
• p3.sql: your SQL query for problem 3
• p4.sql: your SQL query for problem 4
• p5.sql: your SQL query for problem 5
Rubric (per question):
-05: minor mistake (but doesn’t affect output)
-05: good attempt (but does not reproduce results)
-10: major mistake (either reproduce or didn’t reproduce results)
-10: incorrect submission format
-20: no submission, didn’t use JOIN, used soft JOIN

1. Problem 1 (20 points). Write a query to produce a discography of the band “Led Zeppelin”. The result set should have a single column, titled album_title, and should be sorted alphabetically. Your query must not hardcode any numeric ids (e.g. artist_id).

2. Problem 2 (20 points). Write a query to produce a track list for the two-disc album “Live” by the band “The Black Crowes.” The result set should have the following columns: album_title (title of the album), track_id (numeric track identifier), track_name (name of the track), minutes (full minutes in the song), and seconds (number of seconds, rounded). The rows should be sorted first by the album title (such that all of the disc 1 tracks appear before disc 2), and then by the track_id (smallest first). The minutes/seconds should be computed from the milliseconds field: the ROUND1 and CAST2 functions will be useful, and you may want to use Wolfram Alpha3 to sanity check your calculations. Your query must not hardcode any numeric ids (e.g. artistId, albumId).

3. Problem 3 (20 points). There is one invoice that totals more than $25. For that order, write a query to produce all information necessary for an invoice report: for each purchased track, include the invoice_line_id (invoice line), track_id (track identifier), album_title (title of the corresponding album), artist_name (name of the corresponding artist), track_name (name of the track), media_type (type of media), unit_price (unit price of the track), and qty (number purchased of the track). To format the price you may find it useful to use either string concatenation (see lecture) or the PRINTF4 function. The information result should be sorted by the album_title (alphabetically), then the artist_name (alphabetically), then the track_name (alphabetically). Your query must not hardcode any numeric ids (e.g. invoice_id, album_id).

4. Problem 4 (20 points). There are 26 tracks in the “Heavy Metal Classic” playlist – write a query to find the set of 9 artists that are
represented by these tracks. The result set should have a single column (artist_name) and should be sorted alphabetically. Your query must not hardcode any numeric ids (e.g. playlistId).

5. Problem 5 (20 points). There is a single album that has any “Bossa Nova” tracks – write a query to find it. The result set should have a single column (album_title). Your query must not hardcode any numeric ids (e.g. genreId, albumId). You do not need to indicate any sorting for this query.