##List of Movies
movies <- dbSendQuery(mydb, "SELECT * FROM movies")
dbFetch(movies)## id title
## 1 1 Good Girls
## 2 2 Bad Santa
## 3 3 Bad Boys
## 4 4 Smile
## 5 5 Hangover
## 6 6 Notebook##List of Movies’ RATINGS
ratings <- dbSendQuery(mydb, "SELECT * FROM ratings")
dbFetch(ratings)## id movie_id user_name rating_score
## 1 1 1 Barb 5
## 2 2 2 Barb 4
## 3 3 4 Barb 2
## 4 4 6 Barb 1
## 5 5 1 Kyle 2
## 6 6 2 Kyle 4
## 7 7 3 Kyle 4
## 8 8 4 Kyle 5
## 9 9 5 Kyle 1
## 10 10 6 Kyle 1
## 11 11 2 Vic 1
## 12 12 3 Vic 3
## 13 13 6 Vic 5
## 14 14 1 Bob 5
## 15 15 2 Bob 1
## 16 16 3 Bob 3
## 17 17 4 Bob 5
## 18 18 6 Bob 4
## 19 19 3 Lola 1
## 20 20 1 Mila 1
## 21 21 2 Mila 3
## 22 22 3 Mila 2
## 23 23 5 Mila 3
## 24 24 6 Mila 5##Ratings by title and participant
movie_ratings <- dbSendQuery(mydb, "SELECT
m.title,
r.user_name,
r.rating_score
FROM movies m
RIGHT JOIN ratings r
on m.id = r.movie_id
ORDER BY m.title;")
dbFetch(movie_ratings)## title user_name rating_score
## 1 Bad Boys Kyle 4
## 2 Bad Boys Vic 3
## 3 Bad Boys Bob 3
## 4 Bad Boys Lola 1
## 5 Bad Boys Mila 2
## 6 Bad Santa Barb 4
## 7 Bad Santa Kyle 4
## 8 Bad Santa Vic 1
## 9 Bad Santa Bob 1
## 10 Bad Santa Mila 3
## 11 Good Girls Barb 5
## 12 Good Girls Kyle 2
## 13 Good Girls Bob 5
## 14 Good Girls Mila 1
## 15 Hangover Kyle 1
## 16 Hangover Mila 3
## 17 Notebook Barb 1
## 18 Notebook Kyle 1
## 19 Notebook Vic 5
## 20 Notebook Bob 4
## 21 Notebook Mila 5
## 22 Smile Barb 2
## 23 Smile Kyle 5
## 24 Smile Bob 5##Summary of rating by title
movie_ratings <- dbSendQuery(mydb, "SELECT
m.title,
SUM(r.rating_score)
FROM movies m
RIGHT JOIN ratings r
on m.id = r.movie_id
GROUP BY
m.title ;")## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numericdbFetch(movie_ratings)## title SUM(r.rating_score)
## 1 Good Girls 13
## 2 Bad Santa 13
## 3 Smile 12
## 4 Notebook 16
## 5 Bad Boys 13
## 6 Hangover 4##Conclusion We can conclude from the survey that the highest score receive movie called “Notebook”.