INSTALLING PACKAGES
#install.packages('RMySQL', type = 'source')
#install.packages('knitr', type = 'source')
library(RMySQL)
## Loading required package: DBI
ESTABLISH CONNECTION
con <- dbConnect(MySQL(),
user = 'root',
password = 'yahoo77_',
host = 'localhost',
dbname='Movies')
CHECK ACCESSIBILITY
dbReadTable(conn = con,name = 'Movies')
## MovieId GenreTags GuidanceRating MovieName
## 1 1 SciFi, Adventure PG-13 Rogue One
## 2 2 Adventure R XXX : Xander Cage
## 3 3 Drama PG-13 Fences
## 4 4 SciFi, Drama PG-13 Arrival
## 5 5 Drama, Comedy U La La Land
## 6 6 Drama, Comedy U Manchester by the sea
## ReleaseDate Budget CumulativeEarnings
## 1 2016-12-12 150 400
## 2 2017-01-25 130 240
## 3 2017-01-17 90 300
## 4 2016-12-12 110 250
## 5 2016-12-05 95 350
## 6 2016-12-05 85 290
dbReadTable(conn = con,name = 'Users')
## UserId FirstName LastName State Age Gender UserType
## 1 1 Sebastian Vasquez NJ 33 M U
## 2 2 John Olsen NJ 46 M C
## 3 3 Gunjan Malhotra NJ 34 F U
## 4 4 Rebecca Smith NY 29 F C
## 5 5 Manish Gulati PA 31 M U
## 6 6 Aimee Tran ON 29 F U
## 7 7 Vincent <NA> NY 27 M U
## 8 8 Ryan Murphy NJ 28 M C
dbReadTable(conn = con,name = 'UserRatings')
## UserId MovieId Rating
## 1 1 1 5
## 2 1 2 5
## 3 1 3 4
## 4 1 4 2
## 5 1 5 2
## 6 1 6 4
## 7 2 1 1
## 8 2 2 5
## 9 2 3 1
## 10 2 4 3
## 11 2 5 1
## 12 2 6 3
## 13 3 1 4
## 14 3 2 4
## 15 3 3 4
## 16 3 4 1
## 17 3 5 3
## 18 3 6 1
## 19 4 1 1
## 20 4 2 4
## 21 4 3 5
## 22 4 4 3
## 23 4 5 4
## 24 4 6 1
## 25 5 1 1
## 26 5 2 2
## 27 5 3 2
## 28 5 4 2
## 29 5 5 2
## 30 5 6 3
## 31 6 1 3
## 32 6 2 1
## 33 6 3 1
## 34 6 4 1
## 35 6 5 2
## 36 6 6 4
## 37 7 1 4
## 38 7 2 2
## 39 7 3 4
## 40 7 4 1
## 41 7 5 2
## 42 7 6 4
## 43 8 1 4
## 44 8 2 1
## 45 8 3 5
## 46 8 4 3
## 47 8 5 3
## 48 8 6 1
SELECT USER RATINGS
ratings <- dbGetQuery(conn = con, statement = "SELECT Movies.MovieName As Movie , Users.FirstName As User, UserRatings.Rating As Rating FROM Movies, Users, UserRatings WHERE Movies.MovieId=UserRatings.MovieId and Users.UserId=UserRatings.UserId;")
head(ratings)
## Movie User Rating
## 1 Rogue One Sebastian 5
## 2 Rogue One John 1
## 3 Rogue One Gunjan 4
## 4 Rogue One Rebecca 1
## 5 Rogue One Manish 1
## 6 Rogue One Aimee 3