Install and load “RMySQL” package
Create the MySQL driver to connect to the database
con <- dbConnect(RMySQL::MySQL(),
dbname = "movieratings",
host = "localhost",
port = 3306,
user = "user",
password = "")The ‘movieratings’ database has 3 tables; ‘users’, ‘movies’, and ‘ratings’. Below is the ‘users’ table. ‘user_id’ is the primary key
uquery <- "SELECT * FROM users;"
queryUsersTable <- dbGetQuery(con, uquery)
queryUsersTable## user_id user_name
## 1 1 Meredith
## 2 2 Mom
## 3 3 Dad
## 4 4 Bryan
## 5 5 ElizabethBelow is the ‘movies’ table. ‘movie_id’ is the primary key
mquery <- "SELECT * FROM movies;"
queryMoviesTable <- dbGetQuery(con, mquery)
queryMoviesTable## movie_id title
## 1 1 Moana
## 2 2 Captian America: Civil War
## 3 3 Cars 3
## 4 4 Logan
## 5 5 Rogue One
## 6 6 Hidden FiguresBelow is the ‘ratings’ table. ‘user_id’ and ‘movie_id’ are foreign keys
rquery <- "SELECT * FROM ratings;"
queryRatingsTable <- dbGetQuery(con, rquery)
queryRatingsTable## user_id movie_id rating
## 1 1 1 5
## 2 1 2 4
## 3 1 3 4
## 4 1 4 3
## 5 1 5 4
## 6 1 6 5
## 7 2 1 5
## 8 2 2 2
## 9 2 3 3
## 10 2 4 1
## 11 2 5 1
## 12 2 6 5
## 13 3 1 4
## 14 3 2 5
## 15 3 3 4
## 16 3 4 4
## 17 3 5 4
## 18 3 6 4
## 19 4 1 3
## 20 4 2 5
## 21 4 3 4
## 22 4 4 5
## 23 4 5 5
## 24 4 6 4
## 25 5 1 5
## 26 5 2 4
## 27 5 3 4
## 28 5 4 3
## 29 5 5 4
## 30 5 6 5This query calls 3 columns, one from each table, by joining ‘users’ and ‘ratings’ tables on matching keys then joining the result of the previous two tables to ‘movies’ table on matching keys
fullquery <- "SELECT users.user_name, movies.title, ratings.rating
FROM users JOIN ratings ON users.user_id=ratings.user_id
JOIN movies ON movies.movie_id=ratings.movie_id;"
queryTableJoin <- dbGetQuery(con, fullquery)
queryTableJoin## user_name title rating
## 1 Meredith Moana 5
## 2 Meredith Captian America: Civil War 4
## 3 Meredith Cars 3 4
## 4 Meredith Logan 3
## 5 Meredith Rogue One 4
## 6 Meredith Hidden Figures 5
## 7 Mom Moana 5
## 8 Mom Captian America: Civil War 2
## 9 Mom Cars 3 3
## 10 Mom Logan 1
## 11 Mom Rogue One 1
## 12 Mom Hidden Figures 5
## 13 Dad Moana 4
## 14 Dad Captian America: Civil War 5
## 15 Dad Cars 3 4
## 16 Dad Logan 4
## 17 Dad Rogue One 4
## 18 Dad Hidden Figures 4
## 19 Bryan Moana 3
## 20 Bryan Captian America: Civil War 5
## 21 Bryan Cars 3 4
## 22 Bryan Logan 5
## 23 Bryan Rogue One 5
## 24 Bryan Hidden Figures 4
## 25 Elizabeth Moana 5
## 26 Elizabeth Captian America: Civil War 4
## 27 Elizabeth Cars 3 4
## 28 Elizabeth Logan 3
## 29 Elizabeth Rogue One 4
## 30 Elizabeth Hidden Figures 5