Load required libray. Please install the “RMySQL” package if it is not already installed, with the comment “install.packages(”RMySQL“)”.
library(RMySQL)## Loading required package: DBICreate a connection to the mySQL database “data607” with user name “root” and password blank. You may need to modify the user name, password and/or database according to your local database setup.
drv <- dbDriver("MySQL")
con <- dbConnect(drv, username="root", password="", dbname ="data607", host="localhost")List the first name and last name of the reviewers from the reviewer table:
sql <- "select * from reviewers"
res <- dbGetQuery(con,sql)
res## reviewerID firstName lastName
## 1 1 James John
## 2 2 Robert Michael
## 3 3 William David
## 4 4 Mary Patricia
## 5 5 Jennifer Linda
## 6 6 Elizabeth BarbaraList the names of the movies from the movies table:
sql <- "select * from movies"
res <- dbGetQuery(con,sql)
res## movieID movieName
## 1 1 Angel Has Fallen
## 2 2 Good Boys
## 3 3 Overcomer
## 4 4 The Lion King
## 5 5 Don't Let GoList the ratings of the movies from the reviews table, linked by the reviewer ID from the reviewers table and the movie ID from the movies table:
sql <- "select * from reviews"
res <- dbGetQuery(con,sql)
res## reviewID reviewerID movieID rating
## 1 1 1 1 4
## 2 2 2 1 2
## 3 3 3 1 1
## 4 4 4 1 1
## 5 5 5 1 3
## 6 6 6 1 5
## 7 7 1 2 3
## 8 8 2 2 2
## 9 9 3 2 3
## 10 10 4 2 3
## 11 11 5 2 3
## 12 12 6 2 1
## 13 13 1 3 2
## 14 14 2 3 2
## 15 15 3 3 2
## 16 16 4 3 1
## 17 17 5 3 3
## 18 18 6 3 5
## 19 19 1 4 4
## 20 20 2 4 4
## 21 21 3 4 4
## 22 22 4 4 1
## 23 23 5 4 5
## 24 24 6 4 2
## 25 25 1 5 5
## 26 26 2 5 3
## 27 27 3 5 5
## 28 28 4 5 5
## 29 29 5 5 4
## 30 30 6 5 1Now, we do a join query to fetch the firt name, last name of the reviewers and their ratings to each of the movies:
sql <- "select reviewers.firstName, reviewers.Lastname, movies.movieName, reviews.rating
from reviewers
left join reviews on reviewers.reviewerID = reviews.reviewerID
left join movies on movies.movieID = reviews.movieID
order by movies.movieName, reviewers.firstName, reviewers.Lastname;"
res <- dbGetQuery(con,sql)
res## firstName Lastname movieName rating
## 1 Elizabeth Barbara Angel Has Fallen 5
## 2 James John Angel Has Fallen 4
## 3 Jennifer Linda Angel Has Fallen 3
## 4 Mary Patricia Angel Has Fallen 1
## 5 Robert Michael Angel Has Fallen 2
## 6 William David Angel Has Fallen 1
## 7 Elizabeth Barbara Don't Let Go 1
## 8 James John Don't Let Go 5
## 9 Jennifer Linda Don't Let Go 4
## 10 Mary Patricia Don't Let Go 5
## 11 Robert Michael Don't Let Go 3
## 12 William David Don't Let Go 5
## 13 Elizabeth Barbara Good Boys 1
## 14 James John Good Boys 3
## 15 Jennifer Linda Good Boys 3
## 16 Mary Patricia Good Boys 3
## 17 Robert Michael Good Boys 2
## 18 William David Good Boys 3
## 19 Elizabeth Barbara Overcomer 5
## 20 James John Overcomer 2
## 21 Jennifer Linda Overcomer 3
## 22 Mary Patricia Overcomer 1
## 23 Robert Michael Overcomer 2
## 24 William David Overcomer 2
## 25 Elizabeth Barbara The Lion King 2
## 26 James John The Lion King 4
## 27 Jennifer Linda The Lion King 5
## 28 Mary Patricia The Lion King 1
## 29 Robert Michael The Lion King 4
## 30 William David The Lion King 4