Data607 Assignment 2

Load DB from Sql DB

Use RMySQL library

library(RMySQL)

## Loading required package: DBI

con <- dbConnect(RMySQL::MySQL(),dbname = "movie_rate", user="root", password="Password123")
query <- dbSendQuery(
  con,
  "SELECT
  Movies.MovieName AS 'MovieName',
  Users.UserName AS 'UserName',
  Rating.Rating AS 'Rating'
  FROM
  Rating
  LEFT JOIN
  Users ON Users.UserID = Rating.UserID
  LEFT JOIN
  Movies ON Movies.MovieID = Rating.MovieID
  ORDER BY UserName;"
  )

movie_rate <- dbFetch(query)

head(movie_rate)

##          MovieName UserName Rating
## 1     Transformers    David      4
## 2 Fast and Furious    David      5
## 3    Despicable Me    David      1
## 4       Spider Man    David      3
## 5         Iron Man    David      4
## 6  Captain America    David      3