library(RMySQL)
## Loading required package: DBI
movie = dbConnect(MySQL(), user='root', password='mohamed', dbname='movie', host='localhost')
dbListTables(movie)
## [1] "movierating" "oscars2019"
Oscars2019 <- dbSendQuery(movie, "select * from movie.Oscars2019")
Oscars2019_List <- fetch(Oscars2019)
Oscars2019_List
## MovieId MovieName Category
## 1 1 BLACK PANTHER PG-13
## 2 2 BLACKkKLANSMAN R
## 3 3 BOHEMIAN RHAPSODY PG-13
## 4 4 THE FAVOURITE R
## 5 5 GREEN BOOK PG-13
## 6 6 ROMA R
## 7 7 A STAR IS BORN R
## 8 8 VICE R
Ratings <- dbSendQuery(movie, "select MovieID, RatingCategory, GivenRating from movie.MovieRating")
## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numeric
MovieRatings <- fetch(Ratings)
MovieRatings
## MovieID RatingCategory GivenRating
## 1 1 Family 4.0
## 2 1 Friend 4.0
## 3 1 Kids 5.0
## 4 1 OnlineCritics 3.5
## 5 1 GeneralAudience 4.0
## 6 2 Family 3.0
## 7 2 Friend 3.0
## 8 2 Kids 2.0
## 9 2 OnlineCritics 3.5
## 10 2 GeneralAudience 3.0
## 11 3 Family 4.0
## 12 3 Friend 4.0
## 13 3 Kids 2.5
## 14 3 OnlineCritics 3.5
## 15 3 GeneralAudience 3.5
## 16 4 Family 3.5
## 17 4 Friend 4.0
## 18 4 Kids 2.0
## 19 4 OnlineCritics 3.5
## 20 4 GeneralAudience 3.0
## 21 5 Family 4.0
## 22 5 Friend 4.0
## 23 5 Kids 5.0
## 24 5 OnlineCritics 3.5
## 25 5 GeneralAudience 3.0
## 26 6 Family 3.5
## 27 6 Friend 4.5
## 28 6 Kids 3.0
## 29 6 OnlineCritics 3.5
## 30 6 GeneralAudience 4.0
## 31 7 Family 4.0
## 32 7 Friend 3.5
## 33 7 Kids 2.0
## 34 7 OnlineCritics 3.0
## 35 7 GeneralAudience 3.0
## 36 8 Family 3.2
## 37 8 Friend 4.0
## 38 8 Kids 2.0
## 39 8 OnlineCritics 4.0
## 40 8 GeneralAudience 3.5
BestMovieRating <- dbSendQuery(movie, "select m.MovieName, CAST(AVG(r.GivenRating) AS DECIMAL(3,2)) as AvgRating from movie.Oscars2019 m inner join movie.MovieRating r on r.MovieId = m.MovieId group by m.MovieName order by AvgRating desc")
## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numeric
Rating_List <- fetch(BestMovieRating)
Rating_List
## MovieName AvgRating
## 1 BLACK PANTHER 4.10
## 2 GREEN BOOK 3.90
## 3 ROMA 3.70
## 4 BOHEMIAN RHAPSODY 3.50
## 5 VICE 3.34
## 6 THE FAVOURITE 3.20
## 7 A STAR IS BORN 3.10
## 8 BLACKkKLANSMAN 2.90
library(ggplot2)
ggplot(data=Rating_List, aes(x=MovieName, y=AvgRating, fill=MovieName)) + geom_bar(stat="identity") + coord_flip() + geom_text(aes(label=AvgRating), vjust=0) + ggtitle("Movie Rating - Oscars 2019") + theme(plot.title = element_text(hjust = 0.5))
dbDisconnect(movie)
## Warning: Closing open result sets
## [1] TRUE