##Assignment 2 In this assignment we were requested to create a database and uploaded to the R console. I created a simple database in MySQL 8 with two tables with names of the movies and viewers, including the rating of each one. ##First we need to install the RMySQl package into R studio.

library(RMySQL)
## Loading required package: DBI
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

##We need to create an R object (mysqlconnection) to access to the database. ##with username, password, DBname, host, port,

mysqlconnection = dbConnect(RMySQL::MySQL(),
                            dbname='movie_rating',
                            host='localhost',
                            port=3306,
                            user='root',
                            password='Viviana10!')

##this command is to display the tables in the database(movie_rating)

dbListTables(mysqlconnection)
## [1] "movies"          "viewers ratings"

I created an object (mov_dat) to access to the table information.

mov_dat <- dbGetQuery(mysqlconnection, "select * from movies")
glimpse(mov_dat)
## Rows: 6
## Columns: 5
## $ id          <int> 1, 2, 3, 4, 5, 6
## $ name        <chr> "poor things", "a haunting in venice", "five nights at fre…
## $ genre       <chr> "drama", "suspense", "horror", "drama", "comedy", "suspens…
## $ viewer_name <chr> "viviana", "diego", "fatima", "argento", "hugo", "fausto"
## $ rating      <chr> "4", "3", "2", "4", "1", "3"