In this assignment we have Choose ten 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.From the database we have different queries to gain insight of rating information.
library(RMySQL)## Loading required package: DBIdrv = dbDriver("MySQL")
con <- dbConnect(drv, user = 'root', password = 'mysql', dbname = 'mysql')SELECT * FROM mysql.movie_list| movie_name | movie_id | movie_year | genre |
|---|---|---|---|
| Peter Rabbit | 1 | 2018 | Animation |
| jumanji:welcome to jungle | 2 | 2017 | Action |
| wonder | 3 | 2017 | Drama |
| padmavat | 4 | 2018 | Historical |
| when we first met | 5 | 2018 | Romance |
| Coco | 6 | 2017 | Animation |
| Spiderman Homecoming | 7 | 2017 | Action |
| Phantom Thread | 8 | 2017 | Drama |
| The Black Prince | 9 | 2017 | Historical |
| Beauty and the Beast | 10 | 2017 | Romance |
SELECT * FROM mysql.movie_rating| rating_id | rating_description |
|---|---|
| 1 | poor |
| 2 | fair |
| 3 | good |
| 4 | very good |
| 5 | Excellent |
SELECT * FROM mysql.user_list| user_id | user_first_name | user_last_name | user_age | user_country |
|---|---|---|---|---|
| 1 | Rick | singh | 5 | USA |
| 2 | Tony | shoker | 33 | CANADA |
| 3 | Jag | singh | 40 | CANADA |
| 4 | Nam | Deep | 45 | USA |
| 6 | Pam | Dhal | 50 | INDIA |
| 5 | Bal | Deep | 65 | INDIA |
SELECT * FROM mysql.user_movie_ratinglist| user_id | movie_id | rating_id |
|---|---|---|
| 1 | 1 | 5 |
| 1 | 2 | 3 |
| 1 | 3 | 3 |
| 1 | 4 | 1 |
| 1 | 5 | 1 |
| 2 | 1 | 2 |
| 2 | 2 | 3 |
| 2 | 3 | 4 |
| 2 | 4 | 5 |
| 2 | 5 | 5 |
In this query we want to find out the rating of a movie based on its genre. This queries give an idea about how a pertiuclar movie is doing within its category.
rating_genre <- dbSendQuery(con, "select distinct movies.genre, movies.movie_name, movies.movie_year, ratings.rating_description
from mysql.movie_list movies, mysql.movie_rating ratings, mysql.user_list userlist, mysql.user_movie_ratinglist ratingLists
where ratingLists.user_id = userlist.user_id
and ratingLists.movie_id = movies.movie_id
and ratingLists.rating_id = ratings.rating_id
order by movies.genre, ratings.rating_description ")
dbFetch(rating_genre)## genre movie_name movie_year rating_description
## 1 Action Spiderman Homecoming 2017 Excellent
## 2 Action jumanji:welcome to jungle 2017 fair
## 3 Action Spiderman Homecoming 2017 fair
## 4 Action jumanji:welcome to jungle 2017 good
## 5 Action Spiderman Homecoming 2017 good
## 6 Animation Peter Rabbit 2018 Excellent
## 7 Animation Peter Rabbit 2018 fair
## 8 Animation Coco 2017 fair
## 9 Animation Coco 2017 good
## 10 Animation Coco 2017 poor
## 11 Animation Peter Rabbit 2018 poor
## 12 Animation Coco 2017 very good
## 13 Drama wonder 2017 fair
## 14 Drama wonder 2017 good
## 15 Drama Phantom Thread 2017 good
## 16 Drama wonder 2017 very good
## 17 Drama Phantom Thread 2017 very good
## 18 Historical padmavat 2018 Excellent
## 19 Historical The Black Prince 2017 Excellent
## 20 Historical The Black Prince 2017 good
## 21 Historical padmavat 2018 good
## 22 Historical padmavat 2018 poor
## 23 Historical The Black Prince 2017 poor
## 24 Romance Beauty and the Beast 2017 Excellent
## 25 Romance when we first met 2018 Excellent
## 26 Romance when we first met 2018 good
## 27 Romance Beauty and the Beast 2017 good
## 28 Romance when we first met 2018 poor
## 29 Romance Beauty and the Beast 2017 poorIn this query we want to find how a movie is performing across different age group.We want to see if there is a correlation between age group and movie genre.
rating_age <- dbSendQuery(con, "select userlist.user_age, movies.movie_name, movies.genre, ratings.rating_description
from mysql.movie_list movies, mysql.movie_rating ratings, mysql.user_list userlist, mysql.user_movie_ratinglist ratingLists
where ratingLists.user_id = userlist.user_id
and ratingLists.movie_id = movies.movie_id
and ratingLists.rating_id = ratings.rating_id
order by userlist.user_age, ratings.rating_description, movies.genre")
dbFetch(rating_age)## user_age movie_name genre rating_description
## 1 5 Spiderman Homecoming Action Excellent
## 2 5 Peter Rabbit Animation Excellent
## 3 5 jumanji:welcome to jungle Action good
## 4 5 Phantom Thread Drama good
## 5 5 wonder Drama good
## 6 5 The Black Prince Historical poor
## 7 5 padmavat Historical poor
## 8 5 Beauty and the Beast Romance poor
## 9 5 when we first met Romance poor
## 10 5 Coco Animation very good
## 11 33 Spiderman Homecoming Action Excellent
## 12 33 padmavat Historical Excellent
## 13 33 when we first met Romance Excellent
## 14 33 Beauty and the Beast Romance Excellent
## 15 33 Peter Rabbit Animation fair
## 16 33 jumanji:welcome to jungle Action good
## 17 33 Coco Animation good
## 18 33 Phantom Thread Drama good
## 19 33 The Black Prince Historical poor
## 20 33 wonder Drama very good
## 21 40 when we first met Romance Excellent
## 22 40 Peter Rabbit Animation fair
## 23 40 Spiderman Homecoming Action good
## 24 40 jumanji:welcome to jungle Action good
## 25 40 Phantom Thread Drama good
## 26 40 wonder Drama good
## 27 40 The Black Prince Historical good
## 28 40 padmavat Historical good
## 29 40 Beauty and the Beast Romance good
## 30 40 Coco Animation poor
## 31 45 padmavat Historical Excellent
## 32 45 jumanji:welcome to jungle Action fair
## 33 45 Spiderman Homecoming Action fair
## 34 45 Coco Animation fair
## 35 45 The Black Prince Historical good
## 36 45 when we first met Romance good
## 37 45 Peter Rabbit Animation poor
## 38 45 Beauty and the Beast Romance poor
## 39 45 wonder Drama very good
## 40 45 Phantom Thread Drama very good
## 41 65 Spiderman Homecoming Action Excellent
## 42 65 The Black Prince Historical Excellent
## 43 65 jumanji:welcome to jungle Action fair
## 44 65 Peter Rabbit Animation fair
## 45 65 wonder Drama fair
## 46 65 Phantom Thread Drama good
## 47 65 when we first met Romance good
## 48 65 Coco Animation poor
## 49 65 padmavat Historical poor
## 50 65 Beauty and the Beast Romance poorin this query we want to find how a movie is performing across different countries. We want to see if there is a correlation between people’s interest in movies across different countries.
rating_country <- dbSendQuery(con, "select distinct userlist.user_country, movies.genre, movies.movie_name,ratings.rating_description
from mysql.movie_list movies, mysql.movie_rating ratings, mysql.user_list userlist, mysql.user_movie_ratinglist ratingLists
where ratingLists.user_id = userlist.user_id
and ratingLists.movie_id = movies.movie_id
and ratingLists.rating_id = ratings.rating_id
order by userlist.user_country, ratings.rating_description, movies.genre")
dbFetch(rating_country)## user_country genre movie_name rating_description
## 1 CANADA Action Spiderman Homecoming Excellent
## 2 CANADA Historical padmavat Excellent
## 3 CANADA Romance Beauty and the Beast Excellent
## 4 CANADA Romance when we first met Excellent
## 5 CANADA Animation Peter Rabbit fair
## 6 CANADA Action jumanji:welcome to jungle good
## 7 CANADA Action Spiderman Homecoming good
## 8 CANADA Animation Coco good
## 9 CANADA Drama wonder good
## 10 CANADA Drama Phantom Thread good
## 11 CANADA Historical The Black Prince good
## 12 CANADA Historical padmavat good
## 13 CANADA Romance Beauty and the Beast good
## 14 CANADA Animation Coco poor
## 15 CANADA Historical The Black Prince poor
## 16 CANADA Drama wonder very good
## 17 INDIA Action Spiderman Homecoming Excellent
## 18 INDIA Historical The Black Prince Excellent
## 19 INDIA Action jumanji:welcome to jungle fair
## 20 INDIA Animation Peter Rabbit fair
## 21 INDIA Drama wonder fair
## 22 INDIA Drama Phantom Thread good
## 23 INDIA Romance when we first met good
## 24 INDIA Animation Coco poor
## 25 INDIA Historical padmavat poor
## 26 INDIA Romance Beauty and the Beast poor
## 27 USA Action Spiderman Homecoming Excellent
## 28 USA Animation Peter Rabbit Excellent
## 29 USA Historical padmavat Excellent
## 30 USA Action jumanji:welcome to jungle fair
## 31 USA Action Spiderman Homecoming fair
## 32 USA Animation Coco fair
## 33 USA Action jumanji:welcome to jungle good
## 34 USA Drama Phantom Thread good
## 35 USA Drama wonder good
## 36 USA Historical The Black Prince good
## 37 USA Romance when we first met good
## 38 USA Animation Peter Rabbit poor
## 39 USA Historical The Black Prince poor
## 40 USA Historical padmavat poor
## 41 USA Romance Beauty and the Beast poor
## 42 USA Romance when we first met poor
## 43 USA Animation Coco very good
## 44 USA Drama wonder very good
## 45 USA Drama Phantom Thread very gooddbDisconnect(con)## Warning: Closing open result sets## [1] TRUE