Load SQL Lite Library

library(RSQLite)
library(knitr)
sqlite <- dbDriver("SQLite")

Create db file and table

getwd()
## [1] "C:/Users/JJohn1/Google Drive/DataScience/DATA607/Week2"
con <- dbConnect(sqlite, "MovieRatings1.db")
columns <-c('Name'='Varchar(100)','Rating'='Number' )
if(dbExistsTable(con,"Movies"))
  dbRemoveTable(con, "Movies")  
dbCreateTable(con, "Movies",columns )

Begin new transaction and insert rows

m <- data.frame(Name =rep('PADDINGTON 2' , each = 4) , Rating = c(5,4.5,5,2.5))
m2 <- data.frame(Name =rep('BLACK PANTHER' , each = 6) , Rating = c(3.5,4,4.5,3,4,3.5))
m3 <- data.frame(Name =rep('MISSION: IMPOSSIBLE - FALLOUT 2' , each = 6) , Rating = c(5,4,5,4.5,3.5,5))
m4 <- data.frame(Name =rep('INCREDIBLES 2' , each = 6) , Rating = c(3,5,4,5,4,3.5))
m5 <- data.frame(Name =rep('CRAZY RICH ASIANS' , each = 7) , Rating = c(5,4,5,4.5,4,4,3.5))
m6 <- data.frame(Name =rep('AVENGERS: INFINITY WAR' , each = 8) , Rating = c(4.5,5,3.5,4.5,4,4.5,3.5,3))
dbBegin(con)
dbWriteTable(con, 'MovieRatings', m, append =T)
dbWriteTable(con, 'MovieRatings', m2, append =T)
dbWriteTable(con, 'MovieRatings', m3, append =T)
dbWriteTable(con, 'MovieRatings', m4, append =T)
dbWriteTable(con, 'MovieRatings', m5, append =T)
dbWriteTable(con, 'MovieRatings', m6, append =T)
dbCommit(con)
movies <- dbReadTable(con, 'MovieRatings')
head(movies)
##            Name Rating
## 1  PADDINGTON 2    5.0
## 2  PADDINGTON 2    4.5
## 3  PADDINGTON 2    5.0
## 4  PADDINGTON 2    2.5
## 5 BLACK PANTHER    3.5
## 6 BLACK PANTHER    4.0

Select movie names using SQL

movieNames <- 'select distinct name from MovieRatings'
dbGetQuery(con, movieNames)
##                              Name
## 1                    PADDINGTON 2
## 2                   BLACK PANTHER
## 3 MISSION: IMPOSSIBLE - FALLOUT 2
## 4                   INCREDIBLES 2
## 5               CRAZY RICH ASIANS
## 6          AVENGERS: INFINITY WAR

Numbers of ratings per movie

ratingsPerMoive<- 'select name, count(Rating) as [Number of Ratings] from MovieRatings group by name'
dbGetQuery(con, ratingsPerMoive)
##                              Name Number of Ratings
## 1          AVENGERS: INFINITY WAR                 8
## 2                   BLACK PANTHER                 6
## 3               CRAZY RICH ASIANS                 7
## 4                   INCREDIBLES 2                 6
## 5 MISSION: IMPOSSIBLE - FALLOUT 2                 6
## 6                    PADDINGTON 2                 4

Highest rating per movie

highest_rating_per_movie <- 'select name, max(Rating) as [Number of Ratings] from MovieRatings group by name'
dbGetQuery(con, highest_rating_per_movie)
##                              Name Number of Ratings
## 1          AVENGERS: INFINITY WAR               5.0
## 2                   BLACK PANTHER               4.5
## 3               CRAZY RICH ASIANS               5.0
## 4                   INCREDIBLES 2               5.0
## 5 MISSION: IMPOSSIBLE - FALLOUT 2               5.0
## 6                    PADDINGTON 2               5.0

Lowest rating per movie

lowest_rating_per_movie <- 'select name, min(Rating) as [Number of Ratings] from MovieRatings group by name'  
dbGetQuery(con, lowest_rating_per_movie)
##                              Name Number of Ratings
## 1          AVENGERS: INFINITY WAR               3.0
## 2                   BLACK PANTHER               3.0
## 3               CRAZY RICH ASIANS               3.5
## 4                   INCREDIBLES 2               3.0
## 5 MISSION: IMPOSSIBLE - FALLOUT 2               3.5
## 6                    PADDINGTON 2               2.5

Most reviewd movie

leastReviews<- 'select name, max(ratings) as [Number of Ratings] from (select name, count(Rating) as ratings from movieRatings  group by name)'
dbFetch(dbSendQuery(con ,leastReviews))
##                     name Number of Ratings
## 1 AVENGERS: INFINITY WAR                 8

Least reviewd movie

leastReviews<- 'select name, min(ratings) as[Number of Ratings] from (select name, count(Rating) as ratings from movieRatings  group by name)'
dbFetch(dbSendQuery(con ,leastReviews))
## Warning: Closing open result set, pending rows
##           name Number of Ratings
## 1 PADDINGTON 2                 4