Assignment 2

Create a connection from RStudio to your MySQL instance

library(RMySQL)

## Warning: package 'RMySQL' was built under R version 3.5.3

## Loading required package: DBI

## Warning: package 'DBI' was built under R version 3.5.3

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

connection <- dbConnect(MySQL(),
                        user="root",
                        password="123456",
                        dbname="movie",
                        host="localhost"
                        )

Store the data access query in a variable

query <- "SELECT Movie_Name,
                 min(Movie_Rating) as LowestRating,
                 max(Movie_Rating) as HighestRating
          FROM movie
          GROUP BY Movie_Name
          ORDER BY Movie_Name ASC;"

Fetch data from your MySQL instance

resultset <- dbGetQuery(connection, query)

Display the records

resultset

##       Movie_Name LowestRating HighestRating
## 1   Don't Let Go            1             2
## 2      Good Boys            2             5
## 3 IT Chapter Two            1             3
## 4          NeZha            3             4
## 5     Spider-Man            3             5
## 6  The Lion King            2             5

resultset2 <- resultset
summary(resultset2)

##   Movie_Name        LowestRating       HighestRating     
##  Length:6           Length:6           Length:6          
##  Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character