DATA 607 Week 2 Assignment

# Week 2 Assignment - R and SQL
# Student: Kalyan (Kalyanaraman Parthasarathy)

# Read the DB password for "ruser" from the file
password = read.table("/Users/vidyakalyan/Data/ruser-password.txt")

# Install and Load RMySQL package that helps to connect to MySQL DB
# install.packages("RMySQL")
library(RMySQL)

## Loading required package: DBI

# Connect to MySQL DB
mySQLConnection = dbConnect(MySQL(), user='ruser', password=toString(password[1,1]), dbname='entertainment', host='localhost')

# Just for verification - List the tables in the DB
dbListTables(mySQLConnection)

## [1] "movies"  "ratings"

# Get the records from the "movies" table
moviesResultset = dbSendQuery(mySQLConnection, 'select * from movies order by movie_name')

# Reads the results as Dataframe
moviesResultsAsDataframe = fetch(moviesResultset, n=-1)

# Display the query results
moviesResultsAsDataframe

##               movie_name release_date running_time     genre
## 1   Beauty and the Beast      3/17/17          129    Family
## 2        Despicable Me 3      6/30/17           90 Animation
## 3                Dunkirk      7/21/17          106    Action
## 4                  Logan       3/3/17          129    Action
## 5 Spider-Man: Homecoming       7/7/17          133    Action
## 6           Wonder Woman       6/2/17          141    Action
##                    director
## 1               Bill Condon
## 2 Kyle Balda, Pierre Coffin
## 3         Christopher Nolan
## 4             James Mangold
## 5                 Jon Watts
## 6             Patty Jenkins

# Get the records from the "ratings" table
ratingsResultset = dbSendQuery(mySQLConnection, 'select * from ratings order by movie_name')

# Reads the results as Dataframe
ratingsResultsAsDataframe = fetch(ratingsResultset, n=-1)

# Display the query results
ratingsResultsAsDataframe

##                movie_name person_rated rating
## 1    Beauty and the Beast        Shiva      4
## 2    Beauty and the Beast       Mukund      4
## 3    Beauty and the Beast          Anu      5
## 4    Beauty and the Beast        Vidya      5
## 5    Beauty and the Beast       Shreya      5
## 6         Despicable Me 3        Shiva      4
## 7         Despicable Me 3       Mukund      5
## 8         Despicable Me 3          Anu      5
## 9         Despicable Me 3        Vidya      3
## 10        Despicable Me 3       Shreya      5
## 11                Dunkirk        Shiva      5
## 12                Dunkirk       Mukund      3
## 13                Dunkirk          Anu      2
## 14                Dunkirk        Vidya      5
## 15                Dunkirk       Shreya      2
## 16                  Logan        Shiva      4
## 17                  Logan       Mukund      3
## 18                  Logan          Anu      2
## 19                  Logan        Vidya      4
## 20                  Logan       Shreya      2
## 21 Spider-Man: Homecoming        Shiva      5
## 22 Spider-Man: Homecoming       Mukund      5
## 23 Spider-Man: Homecoming          Anu      2
## 24 Spider-Man: Homecoming        Vidya      3
## 25 Spider-Man: Homecoming       Shreya      2
## 26           Wonder Woman        Shiva      5
## 27           Wonder Woman       Mukund      3
## 28           Wonder Woman          Anu      4
## 29           Wonder Woman        Vidya      5
## 30           Wonder Woman       Shreya      5

# Get the average user rating for the movies
avgUserRatingsResultset = dbSendQuery(mySQLConnection, 'SELECT mov.movie_name "Movie Name", CAST(AVG(ra.rating) AS DECIMAL(4,1)) "Average User Rating" FROM movies mov, ratings ra WHERE mov.movie_name = ra.movie_name GROUP BY mov.movie_name ORDER BY mov.movie_name')

## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numeric

# Reads the results as Dataframe
avgUserRatingsResultsAsDataframe = fetch(avgUserRatingsResultset, n=-1)

# Display the query results
avgUserRatingsResultsAsDataframe

##               Movie Name Average User Rating
## 1   Beauty and the Beast                 4.6
## 2        Despicable Me 3                 4.4
## 3                Dunkirk                 3.4
## 4                  Logan                 3.0
## 5 Spider-Man: Homecoming                 3.4
## 6           Wonder Woman                 4.4

# Disconnect the DB connection
dbDisconnect(mySQLConnection)

## Warning: Closing open result sets

## [1] TRUE