Lecture 15 demo code

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

Association Table (slide 9)

The following queries require joining on an association table which is necessary for many-to-many relationships.

Retrieve the track name and playlist names of the track with TrackId = 1.

dbGetQuery(chinook_db, "select t.TrackId, t.Name as TrackName,
                          p.PlaylistId, p.Name as PlaylistName
                        from playlisttrack pt
                          inner join track t on pt.trackid = t.trackid
                          inner join playlist p on p.playlistid = pt.playlistid
                        where t.trackid = 1")

##   TrackId                               TrackName PlaylistId
## 1       1 For Those About To Rock (We Salute You)          1
## 2       1 For Those About To Rock (We Salute You)          8
## 3       1 For Those About To Rock (We Salute You)         17
##          PlaylistName
## 1               Music
## 2               Music
## 3 Heavy Metal Classic

Retrieve the track names and playlist name of the tracks in the playlist with id = 8.

dbGetQuery(chinook_db, "select t.TrackId, t.Name as TrackName,
                          p.PlaylistId, p.Name as PlaylistName
                        from playlisttrack pt
                          inner join track t on pt.trackid = t.trackid
                          inner join playlist p on p.playlistid = pt.playlistid
                        where p.playlistid = 8 limit 3")

##   TrackId                               TrackName PlaylistId PlaylistName
## 1       1 For Those About To Rock (We Salute You)          8        Music
## 2       2                       Balls to the Wall          8        Music
## 3       3                         Fast As a Shark          8        Music