library(RSQLite)
library(sqldf)
library(DBI)
chinook_db <- dbConnect(SQLite(), "Chinook_Sqlite.sqlite")
Aggregation (slide 12)
dbGetQuery(chinook_db, "select GenreId, max(Bytes / power(2, 20)) as LargestMB from Track
group by GenreId having GenreId >= 1 and GenreId <= 10")
## GenreId LargestMB
## 1 1 50.058893
## 2 2 28.054029
## 3 3 24.763794
## 4 4 17.299500
## 5 5 2.495748
## 6 6 18.273713
## 7 7 17.254580
## 8 8 11.526608
## 9 9 10.075287
## 10 10 12.138298
## Can also use the "between" operator
# dbGetQuery(chinook_db, "select GenreId, max(Bytes / power(2, 20)) as LargestMB from Track
# group by GenreId having GenreId between 1 and 10")
Joins (slide 17)
dbGetQuery(chinook_db, "select TrackId, Track.Name, AlbumId, Track.MediaTypeId,
MediaType.Name
from Track inner join MediaType
on Track.MediaTypeId = MediaType.MediaTypeId
where AlbumId = 10")
## TrackId Name AlbumId MediaTypeId Name
## 1 85 Cochise 10 1 MPEG audio file
## 2 86 Show Me How to Live 10 1 MPEG audio file
## 3 87 Gasoline 10 1 MPEG audio file
## 4 88 What You Are 10 1 MPEG audio file
## 5 89 Like a Stone 10 1 MPEG audio file
## 6 90 Set It Off 10 1 MPEG audio file
## 7 91 Shadow on the Sun 10 1 MPEG audio file
## 8 92 I am the Highway 10 1 MPEG audio file
## 9 93 Exploder 10 1 MPEG audio file
## 10 94 Hypnotize 10 1 MPEG audio file
## 11 95 Bring'em Back Alive 10 1 MPEG audio file
## 12 96 Light My Way 10 1 MPEG audio file
## 13 97 Getaway Car 10 1 MPEG audio file
## 14 98 The Last Remaining Light 10 1 MPEG audio file
A Three-way Join (slide 22)
dbGetQuery(chinook_db, "select trackid, t.name as TrackName, t.albumid,
al.title as AlbumTitle, g.name as GenreName
from track t
inner join album al on t.albumid = al.albumid
inner join genre g on t.genreid = g.genreid
where t.albumid = 250")
## TrackId TrackName AlbumId AlbumTitle
## 1 3178 The Dundies 250 The Office, Season 2
## 2 3179 Sexual Harassment 250 The Office, Season 2
## 3 3180 Office Olympics 250 The Office, Season 2
## 4 3181 The Fire 250 The Office, Season 2
## 5 3182 Halloween 250 The Office, Season 2
## 6 3183 The Fight 250 The Office, Season 2
## 7 3184 The Client 250 The Office, Season 2
## 8 3185 Performance Review 250 The Office, Season 2
## 9 3186 Email Surveillance 250 The Office, Season 2
## 10 3187 Christmas Party 250 The Office, Season 2
## 11 3188 Booze Cruise 250 The Office, Season 2
## 12 3189 The Injury 250 The Office, Season 2
## 13 3190 The Secret 250 The Office, Season 2
## 14 3191 The Carpet 250 The Office, Season 2
## 15 3192 Boys and Girls 250 The Office, Season 2
## 16 3193 Valentine's Day 250 The Office, Season 2
## 17 3194 Dwight's Speech 250 The Office, Season 2
## 18 3195 Take Your Daughter to Work Day 250 The Office, Season 2
## 19 3196 Michael's Birthday 250 The Office, Season 2
## 20 3197 Drug Testing 250 The Office, Season 2
## 21 3198 Conflict Resolution 250 The Office, Season 2
## 22 3199 Casino Night - Season Finale 250 The Office, Season 2
## GenreName
## 1 TV Shows
## 2 TV Shows
## 3 TV Shows
## 4 TV Shows
## 5 TV Shows
## 6 TV Shows
## 7 TV Shows
## 8 TV Shows
## 9 TV Shows
## 10 TV Shows
## 11 TV Shows
## 12 TV Shows
## 13 TV Shows
## 14 TV Shows
## 15 TV Shows
## 16 TV Shows
## 17 TV Shows
## 18 TV Shows
## 19 TV Shows
## 20 TV Shows
## 21 TV Shows
## 22 TV Shows
Aggregation with Join (Slide 23)
dbGetQuery(chinook_db, "select avg(Bytes) / power(2, 20) as AvgSizeMB, t.genreid,
g.name as GenreName from track t
inner join genre g on t.genreid = g.genreid
group by t.genreid")
## AvgSizeMB GenreId GenreName
## 1 8.590101 1 Rock
## 2 9.048592 2 Jazz
## 3 8.806775 3 Metal
## 4 7.334712 4 Alternative & Punk
## 5 2.024901 5 Rock And Roll
## 6 8.225990 6 Blues
## 7 7.353390 7 Latin
## 8 7.855886 8 Reggae
## 9 4.525822 9 Pop
## 10 7.715961 10 Soundtrack
## 11 6.903703 11 Bossa Nova
## 12 5.875128 12 Easy Listening
## 13 9.035828 13 Heavy Metal
## 14 6.271292 14 R&B/Soul
## 15 10.196616 15 Electronica/Dance
## 16 6.798998 16 World
## 17 6.231992 17 Hip Hop/Rap
## 18 483.588204 18 Science Fiction
## 19 324.498823 19 TV Shows
## 20 508.242060 20 Sci Fi & Fantasy
## 21 483.462302 21 Drama
## 22 302.223650 22 Comedy
## 23 5.610918 23 Alternative
## 24 4.979044 24 Classical
## 25 2.728909 25 Opera