Movies

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.

Installing RMySQL, DBI, connecting to MySql Database

# install.packages("RMySQL")
# install.packages("DBI")
library(RMySQL)

## Loading required package: DBI

conbection <- dbConnect(MySQL(),
                 user="root", password="olga123",
                 dbname="607_assignments", host="localhost")

results <- "SELECT * from movies"
results <- dbGetQuery(conbection, results)
results

##    id   name                    movie rating
## 1   1 Olivia             Forrest Gump      5
## 2   1 Olivia            The Godfather      4
## 3   1 Olivia          The Dark Knight      4
## 4   1 Olivia The Shawshank Redemption      3
## 5   1 Olivia                Star Wars      5
## 6   1 Olivia               The Matrix      3
## 7   2   Vlad             Forrest Gump      4
## 8   2   Vlad            The Godfather      5
## 9   2   Vlad          The Dark Knight      4
## 10  2   Vlad The Shawshank Redemption      5
## 11  2   Vlad                Star Wars      5
## 12  2   Vlad               The Matrix      5
## 13  3   Kate             Forrest Gump      5
## 14  3   Kate            The Godfather      3
## 15  3   Kate          The Dark Knight      5
## 16  3   Kate The Shawshank Redemption      3
## 17  3   Kate                Star Wars      3
## 18  3   Kate               The Matrix      5
## 19  4   Mick             Forrest Gump      3
## 20  4   Mick            The Godfather      5
## 21  4   Mick          The Dark Knight      5
## 22  4   Mick The Shawshank Redemption      4
## 23  4   Mick                Star Wars      5
## 24  4   Mick               The Matrix      5
## 25  5  Steve             Forrest Gump      5
## 26  5  Steve            The Godfather      5
## 27  5  Steve          The Dark Knight      5
## 28  5  Steve The Shawshank Redemption      5
## 29  5  Steve                Star Wars      5
## 30  5  Steve               The Matrix      5

Selecting movie with the highest rating

best_movie <- "SELECT Movie, avg(Rating) from movies group by Movie order by avg(Rating) desc"
best_movie <- dbGetQuery(conbection, best_movie)
best_movie

##                      Movie avg(Rating)
## 1                Star Wars         4.6
## 2               The Matrix         4.6
## 3          The Dark Knight         4.6
## 4             Forrest Gump         4.4
## 5            The Godfather         4.4
## 6 The Shawshank Redemption         4.0

Selecting person who loves the selected movies most.

person <- "SELECT Name, avg(Rating) from movies group by Name order by avg(Rating) desc"
person <- dbGetQuery(conbection, person)
person

##     Name avg(Rating)
## 1  Steve     5.00000
## 2   Vlad     4.66667
## 3   Mick     4.50000
## 4 Olivia     4.00000
## 5   Kate     4.00000

Creating R dataframe.

df<-data.frame(results)
head(df)

##   id   name                    movie rating
## 1  1 Olivia             Forrest Gump      5
## 2  1 Olivia            The Godfather      4
## 3  1 Olivia          The Dark Knight      4
## 4  1 Olivia The Shawshank Redemption      3
## 5  1 Olivia                Star Wars      5
## 6  1 Olivia               The Matrix      3

Disconnecting from database.

dbDisconnect(conbection)

## [1] TRUE