data_607-week_02

week 2 assignment

Choose six recent popular movies. Ask at least five people that you know (friends, family, classmates, imaginary friends) to rate each of these movie that they have seen on a scale of 1 to 5. Take the results (observations) and store them in a SQL database. Load the information into an R dataframe. Your deliverables should include your SQL scripts and your R Markdown code, posted to GitHub.

Answer:

con <- dbConnect(RPostgres::Postgres()
     , host='amrepdev.ckxhi71v1dqf.us-east-1.rds.amazonaws.com'
     , port='5432'
     , dbname='postgres'
     , user='nichung'
     , password=Sys.getenv("pwd"))

dbExistsTable(con, "movie_ratings")

## [1] TRUE

df <- dbGetQuery(con, '
  SELECT * FROM movie_ratings
')
df

##   movieid                         title releasedate    budget score1
## 1   10121             Avengers: Endgame  2019-04-22 356000000      4
## 2   10122 Once Upon a Time in Hollywood  2019-07-26  96000000      5
## 3   10123                  The Farewell  2019-07-12   3000000      5
## 4   10124     Where'd You Go Bernadette  2019-08-16  18000000      4
## 5   10125                     Midsommar  2019-07-03  10000000      5
## 6   10126               The Plagiarists  2019-06-28        NA      3
##   score2 score3 score4 score5
## 1      3      5      5      4
## 2      4      3      4      4
## 3      5      5      4      5
## 4      3      3      4      2
## 5      5      4      5      4
## 6      4      3      4      4