Relevant Information: Choose six recent popular movies. Ask at least five people that you know (friends, family, classmates, imaginary friends) to rate each of these movie that they have seen on a scale of 1 to 5. Take the results (observations) and store them in a SQL database. Load the information into an R dataframe. Your deliverables should include your SQL scripts and your R Markdown code, posted to GitHub. This is by design a very open ended assignment. A variety of reasonable approaches are acceptable. You can (and should) blank out your SQL password if your solution requires it; otherwise, full credit requires that your code is “reproducible,” with the assumption that I have the same database server and R software.
#install.packages("shiny")
#install.packages("DBI")
#install.packages("dplyr")
#install.packages("dbplyr")
#install.packages("RMySQL")
#devtools::install_github("rstudio/pool")
#install.packages("getPass")
library(getPass)
library(RMySQL)## Loading required package: DBIdb_user <- 'root'
db_password <- getPass::getPass("Enter the password: ")## Please enter password in TK window (Alt+Tab)db_name <- 'MV'
db_table <- 'Reviewers'
db_host <- '127.0.0.1' # for local access
db_port <- 3306
mydb <- dbConnect(MySQL(), user = db_user, password = db_password,
dbname = db_name, host = db_host, port = db_port)
s <- paste0("select * from ", db_table)
rs <- dbSendQuery(mydb, s)
df <- fetch(rs, n = -1)
on.exit(dbDisconnect(mydb))## Warning: Closing open result setsdf## VideoID ReviewerID User_First_Name User_Last_Name Rating
## 1 1 1 Andy Bernard 5
## 2 1 2 Michael Scott 5
## 3 1 3 Dwight Schriwt 0
## 4 1 4 Paul Rovalino 5
## 5 1 5 Gus Rovalino 5
## 6 2 1 Andy Bernard 1
## 7 2 2 Michael Scott 2
## 8 2 3 Dwight Schriwt 5
## 9 2 4 Paul Rovalino 5
## 10 2 5 Gus Rovalino 5
## 11 3 1 Andy Bernard 1
## 12 3 2 Michael Scott 1
## 13 3 3 Dwight Schriwt 5
## 14 3 4 Paul Rovalino 5
## 15 3 5 Gus Rovalino 4
## 16 4 1 Andy Bernard 5
## 17 4 2 Michael Scott 5
## 18 4 3 Dwight Schriwt 2
## 19 4 4 Paul Rovalino 5
## 20 4 5 Gus Rovalino 5
## 21 5 1 Andy Bernard 1
## 22 5 2 Michael Scott 1
## 23 5 3 Dwight Schriwt 5
## 24 5 4 Paul Rovalino 5
## 25 5 5 Gus Rovalino 0
## 26 6 1 Andy Bernard 0
## 27 6 2 Michael Scott 0
## 28 6 3 Dwight Schriwt 5
## 29 6 4 Paul Rovalino 0
## 30 6 5 Gus Rovalino 0
## ReviewText
## 1 Awesome
## 2 Best
## 3 Worst Movie Ever!
## 4 Awesome
## 5 Best
## 6 Worst Movie Ever!
## 7 Worst Movie Ever!
## 8 Awesome!
## 9 Awesome
## 10 Best
## 11 Worst Movie Ever!
## 12 Worst Movie Ever!
## 13 Awesome!
## 14 Awesome
## 15 Best
## 16 Worst Movie Ever!
## 17 Worst Movie Ever!
## 18 Awesome!
## 19 Awesome
## 20 Best
## 21 Worst Movie Ever!
## 22 Worst Movie Ever!
## 23 Classic!
## 24 Awesome
## 25 Stunk
## 26 Worst Movie Ever!
## 27 Worst Movie Ever!
## 28 Wil be a Classic
## 29 Not Good
## 30 Stunk