library(RMySQL)
## Loading required package: DBI
Connecting R to MySQL
database <- dbConnect(MySQL(), user = 'Data 607', password = 'password1', dbname = 'movie_ratings', host = 'localhost')
Loading movies table from MySQL to R dataframe
movies <- dbGetQuery(database, "select * From movies")
movies
## Movie_id Title Length
## 1 100 IT Chapter 2 169
## 2 200 Godzilla: King of the Monsters 132
## 3 300 Avengers: Endgame 181
## 4 400 John Wick: Chapter 3 - Parabellum 131
## 5 500 Once Upon a Time...in Hollywood 161
## 6 600 Toy Story 4 100
Loading ratings table from MySQL to R dataframe
ratings <- dbGetQuery(database, "Select * From Ratings")
ratings
## Rating_id Movie_id Users_name Rating Text_review
## 1 1 100 Alexis 4 Exciting and Suspenseful
## 2 2 200 Alexis 3 It was okay
## 3 3 300 Alexis 5 Left me on the edge of my seat
## 4 4 400 Alexis 4 Action packed
## 5 5 500 Alexis 1 Was not good
## 6 6 600 Alexis 5 A masterpiece
## 7 7 100 Joseph 2 Was not even that scary
## 8 8 200 Joseph 5 Love seeing monster fights
## 9 9 300 Joseph 4 Good but was too long for me
## 10 10 400 Joseph 5 Love seeing John Wick fight
## 11 11 500 Joseph 4 Great storyline
## 12 12 600 Joseph 1 Hopefully this is the last one
## 13 13 100 Jack 4 Great sequel
## 14 14 200 Jack 4 Nice action
## 15 15 300 Jack 2 Kind of boring
## 16 16 400 Jack 3 It was okay
## 17 17 500 Jack 5 Best movie of 2019
## 18 18 600 Jack 5 Walked out crying
## 19 19 100 Kimberly 5 So scary
## 20 20 200 Kimberly 5 Love seeing Godzilla
## 21 21 300 Kimberly 5 Best.Movie.Ever
## 22 22 400 Kimberly 3 Not that good
## 23 23 500 Kimberly 1 Walked out half way through
## 24 24 600 Kimberly 4 Can not believe it ended that way
## 25 25 100 Rosa 4 So good
## 26 26 200 Kimberly 4 Great fight scenes
## 27 27 300 Kimberly 3 So bad
## 28 28 400 Kimberly 5 Left me hyped for the next one
## 29 29 500 Kimberly 3 Not that interesting
## 30 30 600 Kimberly 5 My new favorite movie
Joining the two tables
movies_ratings <- dbGetQuery(database, "Select * From Movies Join Ratings Using(movie_id)")
movies_ratings
## Movie_id Title Length Rating_id Users_name
## 1 100 IT Chapter 2 169 1 Alexis
## 2 200 Godzilla: King of the Monsters 132 2 Alexis
## 3 300 Avengers: Endgame 181 3 Alexis
## 4 400 John Wick: Chapter 3 - Parabellum 131 4 Alexis
## 5 500 Once Upon a Time...in Hollywood 161 5 Alexis
## 6 600 Toy Story 4 100 6 Alexis
## 7 100 IT Chapter 2 169 7 Joseph
## 8 200 Godzilla: King of the Monsters 132 8 Joseph
## 9 300 Avengers: Endgame 181 9 Joseph
## 10 400 John Wick: Chapter 3 - Parabellum 131 10 Joseph
## 11 500 Once Upon a Time...in Hollywood 161 11 Joseph
## 12 600 Toy Story 4 100 12 Joseph
## 13 100 IT Chapter 2 169 13 Jack
## 14 200 Godzilla: King of the Monsters 132 14 Jack
## 15 300 Avengers: Endgame 181 15 Jack
## 16 400 John Wick: Chapter 3 - Parabellum 131 16 Jack
## 17 500 Once Upon a Time...in Hollywood 161 17 Jack
## 18 600 Toy Story 4 100 18 Jack
## 19 100 IT Chapter 2 169 19 Kimberly
## 20 200 Godzilla: King of the Monsters 132 20 Kimberly
## 21 300 Avengers: Endgame 181 21 Kimberly
## 22 400 John Wick: Chapter 3 - Parabellum 131 22 Kimberly
## 23 500 Once Upon a Time...in Hollywood 161 23 Kimberly
## 24 600 Toy Story 4 100 24 Kimberly
## 25 100 IT Chapter 2 169 25 Rosa
## 26 200 Godzilla: King of the Monsters 132 26 Kimberly
## 27 300 Avengers: Endgame 181 27 Kimberly
## 28 400 John Wick: Chapter 3 - Parabellum 131 28 Kimberly
## 29 500 Once Upon a Time...in Hollywood 161 29 Kimberly
## 30 600 Toy Story 4 100 30 Kimberly
## Rating Text_review
## 1 4 Exciting and Suspenseful
## 2 3 It was okay
## 3 5 Left me on the edge of my seat
## 4 4 Action packed
## 5 1 Was not good
## 6 5 A masterpiece
## 7 2 Was not even that scary
## 8 5 Love seeing monster fights
## 9 4 Good but was too long for me
## 10 5 Love seeing John Wick fight
## 11 4 Great storyline
## 12 1 Hopefully this is the last one
## 13 4 Great sequel
## 14 4 Nice action
## 15 2 Kind of boring
## 16 3 It was okay
## 17 5 Best movie of 2019
## 18 5 Walked out crying
## 19 5 So scary
## 20 5 Love seeing Godzilla
## 21 5 Best.Movie.Ever
## 22 3 Not that good
## 23 1 Walked out half way through
## 24 4 Can not believe it ended that way
## 25 4 So good
## 26 4 Great fight scenes
## 27 3 So bad
## 28 5 Left me hyped for the next one
## 29 3 Not that interesting
## 30 5 My new favorite movie