Movie ratings MySQL Version

Load Library

library(RMySQL)

## Loading required package: DBI

library(knitr)

Connect to the movie ratings database

 con = dbConnect(MySQL(), user='data607a', password='data607a', dbname='movie_ratings', host='localhost')

Read movies tables and display

movies <- dbReadTable(con, 'movies')
#Structure
str(movies)

## 'data.frame':    6 obs. of  3 variables:
##  $ id   : int  1 2 3 4 5 6
##  $ name : chr  "PADDINGTON 2" "BLACK PANTHER" "MISSION: IMPOSSIBLE - FALLOUT 2" "INCREDIBLES 2" ...
##  $ genre: chr  "Animation" "SCI-FI" "Action" "Animation" ...

movies

##   id                            name     genre
## 1  1                    PADDINGTON 2 Animation
## 2  2                   BLACK PANTHER    SCI-FI
## 3  3 MISSION: IMPOSSIBLE - FALLOUT 2    Action
## 4  4                   INCREDIBLES 2 Animation
## 5  5               CRAZY RICH ASIANS    Comedy
## 6  6          AVENGERS: INFINITY WAR    SCI-FI

Select movie names using SQL

movieNames <- 'select distinct name from movies'
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(*) as NumReviews
FROM
    movies
        JOIN
    movieratings ON movies.id = movieratings.movie_id
GROUP BY movies.name'
dbGetQuery(con, ratingsPerMoive)

##                              name NumReviews
## 1                    PADDINGTON 2          4
## 2                   BLACK PANTHER          6
## 3 MISSION: IMPOSSIBLE - FALLOUT 2          5
## 4                   INCREDIBLES 2          6
## 5               CRAZY RICH ASIANS          7
## 6          AVENGERS: INFINITY WAR          8

Highest rating per movie

highest_rating_per_movie <- 'SELECT 
    name, max(movieratings.rating) as Rating
FROM
    movies
        JOIN
    movieratings ON movies.id = movieratings.movie_id
GROUP BY movies.name'
dbGetQuery(con, highest_rating_per_movie)

##                              name Rating
## 1                    PADDINGTON 2    5.0
## 2                   BLACK PANTHER    4.5
## 3 MISSION: IMPOSSIBLE - FALLOUT 2    5.0
## 4                   INCREDIBLES 2    5.0
## 5               CRAZY RICH ASIANS    5.0
## 6          AVENGERS: INFINITY WAR    5.0

Lowest rating per movie

lowest_rating_per_movie <- 'SELECT 
    name, min(movieratings.rating) as Rating
FROM
    movies
        JOIN
    movieratings ON movies.id = movieratings.movie_id
GROUP BY movies.name'  
dbGetQuery(con, lowest_rating_per_movie)

##                              name Rating
## 1                    PADDINGTON 2    2.5
## 2                   BLACK PANTHER    3.0
## 3 MISSION: IMPOSSIBLE - FALLOUT 2    3.5
## 4                   INCREDIBLES 2    3.0
## 5               CRAZY RICH ASIANS    3.5
## 6          AVENGERS: INFINITY WAR    3.0

Average rating per movie

average_rating_per_movie <- 'SELECT 
    name, avg(movieratings.rating)  as AverageRating
FROM
    movies
        JOIN
    movieratings ON movies.id = movieratings.movie_id
GROUP BY movies.name'  
dbGetQuery(con, average_rating_per_movie)

##                              name AverageRating
## 1                    PADDINGTON 2      4.250000
## 2                   BLACK PANTHER      3.750000
## 3 MISSION: IMPOSSIBLE - FALLOUT 2      4.400000
## 4                   INCREDIBLES 2      4.083333
## 5               CRAZY RICH ASIANS      4.285714
## 6          AVENGERS: INFINITY WAR      4.062500

Most reviewd movie

leastReviews<- 'SELECT  
    name, count(*)  as NumReviews
    
FROM
    movies
        JOIN
    movieratings ON movies.id = movieratings.movie_id
GROUP BY movies.name 
order by  count(*) desc
LIMIT 1'
dbFetch(dbSendQuery(con ,leastReviews))

##                     name NumReviews
## 1 AVENGERS: INFINITY WAR          8

Least reviewd movie

leastReviews<- 'SELECT  
    name, count(*) as NumReivews
    
FROM
    movies
        JOIN
    movieratings ON movies.id = movieratings.movie_id
GROUP BY movies.name 
order by  count(*) asc
LIMIT 1'
dbFetch(dbSendQuery(con ,leastReviews))

##           name NumReivews
## 1 PADDINGTON 2          4