Data 607 HW2

Make Movies Tabe in MySQL:

DROP TABLE IF EXISTS movies;

CREATE TABLE movies
(
id int PRIMARY KEY,
title varchar(100) NOT NULL
);

INSERT INTO movies(id,title) VALUES (1,‘It’);
INSERT INTO movies(id,title) VALUES (2,‘Spiderman Homecoming’);
INSERT INTO movies(id,title) VALUES (3,‘Emoji Movie’);
INSERT INTO movies(id,title) VALUES (4,‘Dunkirk’);
INSERT INTO movies(id,title) VALUES (5,‘Wonder Woman’);
INSERT INTO movies(id,title) VALUES (6,‘Baby Driver’);

Make the Reviews Table in MySQL:

DROP TABLE IF EXISTS reviews;

CREATE TABLE reviews
(
id int PRIMARY KEY,
movie_id int,
username varchar(50),
rating int
);

INSERT INTO reviews(id,movie_id,username,rating) VALUES (1,1,‘David’,5);
INSERT INTO reviews(id,movie_id,username,rating) VALUES (2,4,‘Dawn’,4);
INSERT INTO reviews(id,movie_id,username,rating) VALUES (3,2,‘Lauren’,0);
INSERT INTO reviews(id,movie_id,username,rating) VALUES (4,6,‘Luke’, 5);
INSERT INTO reviews(id,movie_id,username,rating) VALUES (5,3,‘Brooke’,1);
INSERT INTO reviews(id,movie_id,username,rating) VALUES (6,6,‘Blake’,3);

Make R connection to MySQL and the Videos DB

library(RMySQL)

## Loading required package: DBI

con <- dbConnect(MySQL(),    user='root',    password='password',   dbname='videos', host='localhost')

Look at the movies in the Movies Table

movies <- dbSendQuery(con, "SELECT * FROM movies")
dbFetch(movies)

##   id                title
## 1  1                   It
## 2  2 Spiderman Homecoming
## 3  3          Emoji Movie
## 4  4              Dunkirk
## 5  5         Wonder Woman
## 6  6          Baby Driver

Look at the reviews in the Reviews Table

reviews <- dbSendQuery(con, "SELECT * FROM reviews")
dbFetch(reviews)

##   id movie_id username rating
## 1  1        1    David      5
## 2  2        4     Dawn      4
## 3  3        2   Lauren      0
## 4  4        6     Luke      5
## 5  5        3   Brooke      1
## 6  6        6    Blake      3

Find the rating for each movie and their reviewers

movie_reviews <- dbSendQuery(con, "SELECT 
                              m.title,
                              r.username,
                              r.rating
                             FROM movies m 
                             LEFT JOIN reviews r
                             on m.id = r.movie_id")
dbFetch(movie_reviews)

##                  title username rating
## 1                   It    David      5
## 2              Dunkirk     Dawn      4
## 3 Spiderman Homecoming   Lauren      0
## 4          Baby Driver     Luke      5
## 5          Emoji Movie   Brooke      1
## 6          Baby Driver    Blake      3
## 7         Wonder Woman     <NA>     NA