PSTAT 10 Homework 4 Solutions

For this worksheet, we need the following packages along with a connection to the Chinook database.

library(RSQLite)
library(sqldf)
library(DBI)
chinook_db <- dbConnect(SQLite(), "../Chinook_Sqlite.sqlite")

For full credit, the field names in your output must match the field names in my output, whenever the output is given. Remember this can be done by using aliases.

Problem 1

Write a single query that returns the CustomerId, FirstName, LastName of the customer with CustomerId = 10.

dbGetQuery(chinook_db, "select customerid, firstname, lastname from customer
                          where customerid = 10")

##   CustomerId FirstName LastName
## 1         10   Eduardo  Martins

Write a single query that returns the InvoiceId, CustomerId, and Total for invoices billed to the customer with CustomerId = 10 with the total bill exceeding 5. The output is given.

dbGetQuery(chinook_db, "select invoiceid, customerid, total from invoice where customerid = 10 and total > 5")

##   InvoiceId CustomerId Total
## 1        25         10  8.91
## 2       199         10  5.94
## 3       383         10 13.86

Write a single query that combines the previous results, returning the InvoiceId, CustomerId, Total, FirstName, LastName of invoices billed to the customer with CustomerId = 10 with total bill exceeding 5.

dbGetQuery(chinook_db, "select i.invoiceid, i.customerid, total, c.firstname, c.lastname
                          from invoice i inner join customer c on i.customerid = c.customerid
                        where i.customerid = 10 and total > 5")

##   InvoiceId CustomerId Total FirstName LastName
## 1        25         10  8.91   Eduardo  Martins
## 2       199         10  5.94   Eduardo  Martins
## 3       383         10 13.86   Eduardo  Martins

Problem 2

What is the title of the highest ranking employee of the store? You may find this by any method.

dbGetQuery(chinook_db, "select EmployeeId, LastName, FirstName, Title, ReportsTo from employee e")

##   EmployeeId LastName FirstName               Title ReportsTo
## 1          1    Adams    Andrew     General Manager        NA
## 2          2  Edwards     Nancy       Sales Manager         1
## 3          3  Peacock      Jane Sales Support Agent         2
## 4          4     Park  Margaret Sales Support Agent         2
## 5          5  Johnson     Steve Sales Support Agent         2
## 6          6 Mitchell   Michael          IT Manager         1
## 7          7     King    Robert            IT Staff         6
## 8          8 Callahan     Laura            IT Staff         6

General Manager is the highest title; he reports to no one.

Which employee of the store has acted as the support rep for the most customers? Retturn the EmployeeId, FirstName, LastName, Title, and total number of customers.

dbGetQuery(chinook_db, "select e.employeeid, e.FirstName, e.LastName, e.Title,
                          count(*) as TotalCustomers
                          from employee e
                          inner join customer c on e.employeeid = c.supportrepid
                          group by e.employeeid")

##   EmployeeId FirstName LastName               Title TotalCustomers
## 1          3      Jane  Peacock Sales Support Agent             21
## 2          4  Margaret     Park Sales Support Agent             20
## 3          5     Steve  Johnson Sales Support Agent             18

Problem 3

The total length of an album is the sum of the lengths of every track in the album.

Write a single SQL query to retrieve the AlbumId, Title, and total length in minutes of albums whose total length exceeds 100 minutes. Order by decreasing total length.

dbGetQuery(chinook_db, "select al.albumid, al.title,
                        sum(t.milliseconds)/60000 as TotalLength from track t
                         inner join album al on t.albumid = al.albumid
                         group by t.albumid
                           having TotalLength > 100
                         order by TotalLength desc")

##    AlbumId                                    Title TotalLength
## 1      229                           Lost, Season 3        1177
## 2      253 Battlestar Galactica (Classic), Season 1        1170
## 3      230                           Lost, Season 1        1080
## 4      231                           Lost, Season 2        1054
## 5      228                         Heroes, Season 1         996
## 6      227           Battlestar Galactica, Season 3         879
## 7      261                           LOST, Season 4         657
## 8      251                     The Office, Season 3         638
## 9      250                     The Office, Season 2         477
## 10     141                            Greatest Hits         251
## 11      73                                Unplugged         135
## 12     249                     The Office, Season 1         132
## 13      23                           Minha Historia         131

Problem 4

Write a single query to retrieve the TrackId, TrackName, PlaylistId, and PlaylistName, ordered by increasing PlaylistId, then by increasing TrackId. Limit the result to 5 records. The result is provided. For full credit, alias the field names to match my output.

dbGetQuery(chinook_db, "select t.TrackId, t.name as TrackName, p.playlistid, p.name as PlaylistName from track t
                          inner join PlaylistTrack pt on t.TrackId = pt.TrackId
                          inner join Playlist p on pt.playlistid = p.playlistid
                        order by p.playlistid, t.trackid
                        limit 5")

##   TrackId                               TrackName PlaylistId PlaylistName
## 1       1 For Those About To Rock (We Salute You)          1        Music
## 2       2                       Balls to the Wall          1        Music
## 3       3                         Fast As a Shark          1        Music
## 4       4                       Restless and Wild          1        Music
## 5       5                    Princess of the Dawn          1        Music

Write a single query to retrieve the PlaylistId, PlaylistName, and count of all tracks (TrackCount) within the playlist. Your query should return all of the results.

dbGetQuery(chinook_db, "select p.playlistid, p.name as PlaylistName,
                        count(*) as TrackCount from track t
                          inner join PlaylistTrack pt on t.TrackId = pt.TrackId
                          inner join Playlist p on pt.playlistid = p.playlistid
                        group by p.playlistid")

##    PlaylistId               PlaylistName TrackCount
## 1           1                      Music       3290
## 2           3                   TV Shows        213
## 3           5                 90’s Music       1477
## 4           8                      Music       3290
## 5           9               Music Videos          1
## 6          10                   TV Shows        213
## 7          11            Brazilian Music         39
## 8          12                  Classical         75
## 9          13  Classical 101 - Deep Cuts         25
## 10         14 Classical 101 - Next Steps         25
## 11         15 Classical 101 - The Basics         25
## 12         16                     Grunge         15
## 13         17        Heavy Metal Classic         26
## 14         18                On-The-Go 1          1

Problem 5

The following queries explore how much customers have spent at the store.

Which customers have spent the most in a single order? To answer this, retrieve the FirstName, LastName, and Total for each invoice, ordered by decreasing total. Limit your answer to the first 10 rows.

dbGetQuery(chinook_db, "select c.firstName, c.lastName, total from invoice inv
                          inner join customer c on inv.customerid = c.customerid
                          order by total desc limit 10")

##    FirstName    LastName Total
## 1     Helena        Holý 25.86
## 2    Richard  Cunningham 23.86
## 3   Ladislav      Kovács 21.86
## 4       Hugh    O'Reilly 21.86
## 5     Astrid      Gruber 18.86
## 6     Victor     Stevens 18.86
## 7       Luis       Rojas 17.91
## 8  František Wichterlová 16.86
## 9   Isabelle     Mercier 16.86
## 10     Frank     Ralston 15.86

Which customers have spent the most across all orders? Order the result by decreasing sum total. The first 3 results are shown, but limit your results to 10 rows.

dbGetQuery(chinook_db, "select c.firstName, c.lastName, sum(total) from invoice inv
                          inner join customer c on inv.customerid = c.customerid
                          group by c.customerid
                          order by sum(total) desc limit 10")

##    FirstName   LastName sum(total)
## 1     Helena       Holý      49.62
## 2    Richard Cunningham      47.62
## 3       Luis      Rojas      46.62
## 4   Ladislav     Kovács      45.62
## 5       Hugh   O'Reilly      45.62
## 6      Julia    Barnett      43.62
## 7      Frank    Ralston      43.62
## 8       Fynn Zimmermann      43.62
## 9     Astrid     Gruber      42.62
## 10    Victor    Stevens      42.62

Which country has spent the most across all invoices by all people from that country? Order the result by decreasing CountryTotal. The first three rows are given, but limit your result to 10.

dbGetQuery(chinook_db, "select c.country, sum(total) as CountryTotal from invoice inv
                          inner join customer c on inv.customerid = c.customerid
                          group by country
                          order by CountryTotal desc limit 10")

##           Country CountryTotal
## 1             USA       523.06
## 2          Canada       303.96
## 3          France       195.10
## 4          Brazil       190.10
## 5         Germany       156.48
## 6  United Kingdom       112.86
## 7  Czech Republic        90.24
## 8        Portugal        77.24
## 9           India        75.26
## 10          Chile        46.62