Following Packages are to be installed for this assignment
RMySQL
htmlTable
mydb = dbConnect(MySQL(), user='data607', password='testpassword', dbname='movies', host='localhost')
mr <- dbGetQuery(mydb, "select Movie_name ,Rated_by ,Rating ,Creation_date_time from movie_rating")
mr<- as.data.frame(mr)
htmlTable(mr, caption = 'Movie Rating By Individual')| Movie Rating By Individual | ||||
| Movie_name | Rated_by | Rating | Creation_date_time | |
|---|---|---|---|---|
| 1 | Home Again | Person1 | 3 | 2017-09-09 |
| 2 | Home Again | Person2 | 4 | 2017-09-09 |
| 3 | Home Again | Person3 | 2 | 2017-09-09 |
| 4 | Home Again | Person4 | 3 | 2017-09-09 |
| 5 | Home Again | Person5 | 3 | 2017-09-09 |
| 6 | Beyond the mask | Person1 | 4 | 2017-09-09 |
| 7 | Beyond the mask | Person2 | 4 | 2017-09-09 |
| 8 | Beyond the mask | Person3 | 4 | 2017-09-09 |
| 9 | Beyond the mask | Person4 | 3 | 2017-09-09 |
| 10 | Beyond the mask | Person5 | 3 | 2017-09-09 |
| 11 | Leep | Person1 | 2 | 2017-09-09 |
| 12 | Leep | Person2 | 3 | 2017-09-09 |
| 13 | Leep | Person3 | 3 | 2017-09-09 |
| 14 | Leep | Person4 | 4 | 2017-09-09 |
| 15 | Leep | Person5 | 4 | 2017-09-09 |
| 16 | Dancer and the Dame | Person1 | 4 | 2017-09-09 |
| 17 | Dancer and the Dame | Person2 | 4 | 2017-09-09 |
| 18 | Dancer and the Dame | Person3 | 3 | 2017-09-09 |
| 19 | Dancer and the Dame | Person4 | 2 | 2017-09-09 |
| 20 | Dancer and the Dame | Person5 | 3 | 2017-09-09 |
| 21 | Daddy's Home | Person1 | 4 | 2017-09-09 |
| 22 | Daddy's Home | Person2 | 3 | 2017-09-09 |
| 23 | Daddy's Home | Person3 | 4 | 2017-09-09 |
| 24 | Daddy's Home | Person4 | 4 | 2017-09-09 |
| 25 | Daddy's Home | Person5 | 4 | 2017-09-09 |
| 26 | I am not ashamed | Person1 | 3 | 2017-09-09 |
| 27 | I am not ashamed | Person2 | 4 | 2017-09-09 |
| 28 | I am not ashamed | Person3 | 4 | 2017-09-09 |
| 29 | I am not ashamed | Person4 | 3 | 2017-09-09 |
| 30 | I am not ashamed | Person5 | 3 | 2017-09-09 |
mr_avg <- dbGetQuery(mydb, "select movie_name,avg(rating) Rating from movie_rating group by movie_name")
#mr_avg<-sqldf("select movie_name,avg(creation_date_time) Rating from mr group by movie_name")htmlTable(mr_avg, caption = "Avg Movie Rating")| Avg Movie Rating | ||
| movie_name | Rating | |
|---|---|---|
| 1 | Beyond the mask | 3.6 |
| 2 | Daddy's Home | 3.8 |
| 3 | Dancer and the Dame | 3.2 |
| 4 | Home Again | 3 |
| 5 | I am not ashamed | 3.4 |
| 6 | Leep | 3.2 |
write.csv(mr, file = "C:\\Users\\james\\movie_rating.csv", row.names = FALSE)