Week 2 lab 607

Week 2 Assignment R and SQL

Part 1 : Build a table

• Choose six recent popular movies.
• Ask at least five people that you know (friends, family, classmates, imaginary friends if necessary) to rate each of these movies that they have seen on a scale of 1 to 5.

Part 2: Store data in SQL database

• Take the results (observations) and store them in the class MySQL database:
  -Server name: cunydata607sql.mysql.database.azure.com
  -Username / password: will be given to you in an email
  Note: it is good practice to change your password. To do so, use this SQL command: SET PASSWORD = ‘’;

Part 3: Transfer data from SQL database to R dataframe

• Load the information from the SQL database into an R dataframe.

Build Table and Store Data in SQL Database

A table has been created and stored in the cunydata607sql.mysql.database.azure.com called “movieratings”.

library (tidyverse)

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.4.4     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.1
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(openintro)

## Loading required package: airports
## Loading required package: cherryblossom
## Loading required package: usdata

library (RMySQL)

## Loading required package: DBI

library(DBI)

#Import from SQL reference to Part 3

mysqldb = dbConnect(RMySQL::MySQL(),
                            dbname= 'blessing.abraham-anoroh65',
                            host= 'cunydata607sql.mysql.database.azure.com',
                            port=3306,
                            user='blessing.abraham-anoroh65',
                            password='blessing.abraham-anoroh65')

dbListTables(mysqldb)

## [1] "movieratings"

#fetching results from SQL

result = dbSendQuery(mysqldb, "select * from movieratings")

movieratings <- fetch(result)

print(movieratings)

##   Pop_Movie_Name Person1 Person2 Person3 Person4 Person5
## 1     Mean Girls       2       3       3       4       4
## 2           Lift       4       2       2       5       5
## 3    The Kitchen       5       2       1       3       2
## 4         Barbie       3       3       4       1       5
## 5        Fighter       4       2       3       2       4

Part 4: Missing data strategy

• Implement an approach to missing data
• Explain why you decided to take the chosen approach
  Note: consider that later in the course you will revisit this information you have collected and will use it to implement a Recommend-er.

For missing data I would identify where the missing data is using WHERE command to find “NULL” - which is considered to empty perhaps. I can also remove or delete where there is missing data. But in this exercise I would input the missing data using the number “0” as the NULL because the rating is from 1 to 5. And I would not want the missing data to cause an issue in running the SQL.