library(DBI)
## Warning: package 'DBI' was built under R version 3.5.1
library(RMySQL)
## Warning: package 'RMySQL' was built under R version 3.5.1
#create a connection to your database
db = dbConnect(MySQL(), user='root', password='sudhan1984', dbname='movieratings', host='localhost')

#get all the ratings from the table
ratings = dbSendQuery(db, "select * from ratings")
movies = fetch(ratings)

#now close the database connection
dbDisconnect(db)
## Warning: Closing open result sets
## [1] TRUE
#overall description about the database
str(movies)
## 'data.frame':    15 obs. of  7 variables:
##  $ tstamp                    : chr  "9/4/2018 14:23" "9/4/2018 21:54" "9/5/2018 11:35" "9/5/2018 4:45" ...
##  $ a_quiet_place             : int  4 1 3 4 3 4 2 0 0 0 ...
##  $ black_panther             : int  4 5 4 4 4 5 4 0 0 0 ...
##  $ mission_impossible_fallout: int  5 5 4 5 4 5 4 0 0 0 ...
##  $ equalizer_2               : int  3 4 3 3 3 4 3 0 0 0 ...
##  $ deadpool_2                : int  4 4 3 4 3 4 3 0 0 0 ...
##  $ avengers_infinity_war     : int  4 5 4 4 4 5 3 0 0 0 ...
# using the libraries
library(tidyr)
## Warning: package 'tidyr' was built under R version 3.5.1
library(dplyr)
## Warning: package 'dplyr' was built under R version 3.5.1
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(knitr)
## Warning: package 'knitr' was built under R version 3.5.1
movies = as.tbl(movies)

movies = movies %>% gather(movie, rating, a_quiet_place:avengers_infinity_war)

# cleaning the tables
movies = mutate(movies, rating = as.integer(rating)) %>% filter(complete.cases(rating))
## Warning: package 'bindrcpp' was built under R version 3.5.1
# displaying the output
kable(movies[1:5,])
tstamp movie rating
9/4/2018 14:23 a_quiet_place 4
9/4/2018 21:54 a_quiet_place 1
9/5/2018 11:35 a_quiet_place 3
9/5/2018 4:45 a_quiet_place 4
9/5/2018 2:24 a_quiet_place 3 
table(movies$movie)
## 
##              a_quiet_place      avengers_infinity_war 
##                         15                         15 
##              black_panther                 deadpool_2 
##                         15                         15 
##                equalizer_2 mission_impossible_fallout 
##                         15                         15
library(ggplot2)
## Warning: package 'ggplot2' was built under R version 3.5.1
qplot(data = movies, x = movie, y = rating, geom = "boxplot", fill = movie) + coord_flip() + ggtitle("Movie Ratings for the selected six movies")

## in my friends circle, I think people like more action packed movies that's why the highest rated movie is Mission Impossible