Homework 3 SQL commands

CSCI 330 Database Systems: Homework 3
Total Point: 20 (5% of course grade)
Goal
The goal of this homework is to learn and practice more SQL commands (both basic and
intermediate) for MySQL.
What to do
1. We will use a publically available database named Chinook
https://github.com/lerocha/chinook-database
Below is the schema of this database:
2. Download the SQL script (Chinook_MySql.sql) available on canvas. We collected this SQL
script from the website. Execute this script to create the Chinook database and insert
values for the tables. It might take several minutes to finish executing the entire script
(~16K lines of SQL code).
3. Write SQL queries for the following.
a. Find distinct track names that start with “Z.” Sort the output alphabetically. (3
points)
b. Find the first names of the employees who are older than their supervisor. Hint:
ReportsTo attribute in Employee table stores the EmployeeId of the supervisor.
Sort the output alphabetically. (3 points)
c. Find the name of the highest-priced track. If more than one track has the highest
price, return the names of all such tracks. Sort the output alphabetically based on
the track name. (3 points)
d. Find a list containing the total amount spent by a customer. Include the
customer’s id and the last names along with the total amount. For customers
who did not make any purchase, make sure to include them as well. (3 points)
e. Find the title of the highest-priced album. (3 points)
f. Find a distinct list containing the titles of albums that are never sold. Consider an
album never sold if none of its tracks are sold. Sort the output alphabetically. (3
points)
g. Create a view that returns customers’ first and last names along with
corresponding sums of all their invoice totals. Name the view as
“CustomerInvoices.” (2 points)
What to submit
You have to submit all the SQL queries and the output of the query. If the output contains
more than six tuples, then give the top six tuples.
Submission Instructions
 Put all SQL queries and output in one single doc/docx file.
 Convert the file to a pdf file. The file name should be YourLastName-330-HW3.pdf.
 Upload the pdf file on canvas.
Late Policy:
 No late work will be accepted.
Questions?
If you have any questions, please first check the FAQ page on canvas to see if the question is
already answered.