CUNY Data 607 - Week 2 Assignment SQL and R

Assignment

Choose six recent popular movies. Ask at least five people that you know (friends, family, classmates, imaginary friends) to rate each of these movie that they have seen on a scale of 1 to 5. Take the results (observations) and store them in a SQL database. Load the information into an R dataframe. Your deliverables should include your SQL scripts and your R Markdown code, posted to GitHub. This is by design a very open ended assignment. A variety of reasonable approaches are acceptable. You can (and should) blank out your SQL password if your solution requires it; otherwise, full credit requires that your code is “reproducible,” with the assumption that I have the same database server and R software.

R Code Performed to Upload Results from a SQL Database in a R Dataframe

Load RMySQL Package & Library

library(RMySQL)

## Loading required package: DBI

Establish Connection

con <- dbConnect(MySQL(),
                 user="Test", password="Data607",
                 dbname="observations2", host="localhost")

Show table in plain SQL

sql <- "SELECT * from observations"
res <- dbGetQuery(con, sql)
res

##   idName First_Name Moana Frozen Beauty_and_the_Beast Brave Star_Wars
## 1      1     Emilie     5      4                    4     4         3
## 2      2      Helen     5      5                    3     5         5
## 3      3      Chuck     5      5                    2     3         5
## 4      4        Max     4      3                    1     1         5
## 5      5       Levi     3      3                    1     1         5
##   Willy_Wonka
## 1           3
## 2           3
## 3           5
## 4           2
## 5           4

Disconnect from MySQL observations2 database

dbDisconnect(con)

## [1] TRUE