Lecture 13 demo code

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

Tracks between 286MB and 381MB (slide 13)

dbGetQuery(chinook_db, "select TrackId, Name, AlbumId, Bytes from Track
                          where Bytes > 300000000 and Bytes < 400000000")

##   TrackId                         Name AlbumId     Bytes
## 1    3199 Casino Night - Season Finale     250 327642458
## 2    3206               Branch Closing     251 358761786
## 3    3207                   The Merger     251 345960631
## 4    3212   Producer's Cut: The Return     251 337219980
## 5    3217              The Negotiation     251 371663719
## 6    3220         Women's Appreciation     251 338778844
## 7    3221                  Beach Games     251 333671149
## 8    3428               Branch Closing     251 360331351
## 9    3429                   The Return     251 343877320

dbGetQuery(chinook_db, "select TrackId, Name, AlbumId, Bytes from Track
                          where Bytes between 300000000 and 400000000")

##   TrackId                         Name AlbumId     Bytes
## 1    3199 Casino Night - Season Finale     250 327642458
## 2    3206               Branch Closing     251 358761786
## 3    3207                   The Merger     251 345960631
## 4    3212   Producer's Cut: The Return     251 337219980
## 5    3217              The Negotiation     251 371663719
## 6    3220         Women's Appreciation     251 338778844
## 7    3221                  Beach Games     251 333671149
## 8    3428               Branch Closing     251 360331351
## 9    3429                   The Return     251 343877320

Computations (slide 22)

dbGetQuery(chinook_db, "select TrackId, Name as TrackName, AlbumId,
                          Milliseconds / 60000.0 as Minutes from track
                          where albumid = 18 and Minutes > 3")

##   TrackId                   TrackName AlbumId  Minutes
## 1     167   Body Count's In The House      18 3.404183
## 2     169                  Body Count      18 5.298933
## 3     171         Bowels Of The Devil      18 3.720267
## 4     175                      Voodoo      18 5.012017
## 5     176            The Winner Loses      18 6.537567
## 6     177 There Goes The Neighborhood      18 5.836183
## 7     179                   Evil Dick      18 3.983667
## 8     181   Momma's Gotta Die Tonight      18 6.192317
## 9     182           Freedom Of Speech      18 4.687233

Good practice to disconnect at the end.

dbDisconnect(chinook_db)