Week 2 Assignment: SQL and R
- Load data from MySQL into an R dataframe
#install.packages("RMySQL")
library(RMySQL)
## Loading required package: DBI
SQLConnection <- dbConnect(MySQL(),user="root",password="",dbname="movies",host="localhost")
movies <- dbGetQuery(SQLConnection,"SELECT * from movies;")
head(movies)
## name judge rate
## 1 The Post a 1
## 2 Call Me by Your Name a 3
## 3 Mission: Impossible - Fallout a 3
## 4 Star Wars: The Last Jedi a 4
## 5 Lady Bird a 2
## 6 Murder on the Orient Express a 5
- Manipulate the dataset with SQL
B_rating <- dbGetQuery(SQLConnection,"SELECT name, rate FROM movies WHERE judge = 'b';")
B_rating
## name rate
## 1 The Post 4
## 2 Call Me by Your Name 5
## 3 Mission: Impossible - Fallout 3
## 4 Star Wars: The Last Jedi 3
## 5 Lady Bird 3
## 6 Murder on the Orient Express 5
- Manipulate the dataset with R
A_rating <- subset(movies,judge == 'b',select = c(name,rate))
A_rating
## name rate
## 7 The Post 4
## 8 Call Me by Your Name 5
## 9 Mission: Impossible - Fallout 3
## 10 Star Wars: The Last Jedi 3
## 11 Lady Bird 3
## 12 Murder on the Orient Express 5
- Update one record in the database
dbSendQuery(SQLConnection,"UPDATE movies SET rate = 1 WHERE judge = 'a' AND name = 'The Post';")
## <MySQLResult:2,0,2>
updated_movies <- dbGetQuery(SQLConnection,"SELECT * from movies;")
updated_movies
## name judge rate
## 1 The Post a 1
## 2 Call Me by Your Name a 3
## 3 Mission: Impossible - Fallout a 3
## 4 Star Wars: The Last Jedi a 4
## 5 Lady Bird a 2
## 6 Murder on the Orient Express a 5
## 7 The Post b 4
## 8 Call Me by Your Name b 5
## 9 Mission: Impossible - Fallout b 3
## 10 Star Wars: The Last Jedi b 3
## 11 Lady Bird b 3
## 12 Murder on the Orient Express b 5
## 13 The Post c 4
## 14 Call Me by Your Name c 5
## 15 Mission: Impossible - Fallout c 4
## 16 Star Wars: The Last Jedi c 4
## 17 Lady Bird c 2
## 18 Murder on the Orient Express c 3
## 19 The Post d 3
## 20 Call Me by Your Name d 5
## 21 Mission: Impossible - Fallout d 4
## 22 Star Wars: The Last Jedi d 3
## 23 Lady Bird d 5
## 24 Murder on the Orient Express d 3
## 25 The Post e 4
## 26 Call Me by Your Name e 3
## 27 Mission: Impossible - Fallout e 4
## 28 Star Wars: The Last Jedi e 3
## 29 Lady Bird e 3
## 30 Murder on the Orient Express e 3