Assignment 2: Part 1. SQL - Chinook Database
Assignment 2: Part 1. SQL
Directions
- pay attention to where your file is saved as you will need to upload it to Brightspace
- fill in the appropriate command for each instruction statement
- when completed, upload your .ipynb file to Brightspace.
- refer to the chinook database schema below
image.png Please type your name here: Please type your student number here:
import sqlite3 as sql3
import pandas as pd
In [6]:
conn=sql3.connect('Chinook.db') -
(3pts) Write an SQL query that returns all the Album titles that start with 'S'. In [ ]:
-
(6 pts) Write an SQL query that returns all the Genres' names and the number of associated tracks (ie. count up the tracks for each Genre). Name the column that contains the count of genre tracks NumGenre. Order the results highest to lowest by NumGenre. In [ ]:
-
(4 pts) Write an SQL query that returns all the Track Names and Composers for the Bossa Nova genre. In [ ]:
-
(7 pts) Write an SQL query that returns Genre name and its associated number of tracks (ie. count up the tracks for each Genre) for the genres that have more than 300 tracks associated with it. Name the column that contains the count of genre tracks NumGenre. Order the results alphabetically by the Genre Name. In [ ]:
-
(5 pts) Write an SQL query that returns the first name, last name, and Title of Employees and the first and last names of their associated Customers. Display all Employees whether or not they have Customers, except those with a Title of General Manager. Use the following aliases for the outputted columns: EmpFName, EmpLName, EmpTitle, CustFName, CustLName. Order the results by EmpLName and then by EmpFName. In [ ]:
-
(5 pts) Write an SQL query that returns the number of invoices that customer Rojas has. Display the number of invoices in a column named NumInvoice. Ensure you display the Customer's last name as well. In [ ]:
-
(9 pts) Write an SQL query that returns the InvoiceID, InvoiceDate, and Track Names for all of customer Rojas's invoices. Please include the customer's last name and order the results by the InvoiceDate from newest to oldest. In [ ]:
-
(5 pts) Write an SQL query that lists out all the track names for the Classical playlist. Also return the Composer for each track. In [ ]:
-
(8 pts) Write an SQL query that calculates the total price for all tracks in the Classical playlist. Display the name of playlist, the number of tracks and the playlist price. In [ ]:
-
(6 pts) Write an SQL query that returns genre name, track name and media type name for all tracks that are in either the rock or pop genre. Sort your results by track genre name
