sqlconnecting_week2
Vijaya Cherukuri
September 9, 2018
R Markdown
This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com.
When you click the Knit button a document will be generated that includes both content as well as the output of any embedded R code chunks within the document. You can embed an R code chunk like this:
# Load necessary packages
library(DBI)
library(RMySQL)
# Establish DB connection
mydb = dbConnect(MySQL(), user='root', password='lakshmi564', dbname ='cuny', host='localhost', port = 3306)
# Test connection
mydb## <MySQLConnection:0,0># Query to fetch usertable data and assign to query1
query1 <- dbGetQuery(conn = mydb, statement = "SELECT * from usertable")
query1## user_id Firstname Lastname
## 1 1 Tom Racony
## 2 2 Nick Torento
## 3 3 Eric Pike
## 4 4 Scott Limback
## 5 5 Greg Gerardin
## 6 6 James Hope# Query to fetch movie_ratings data and assign to query2
query2 <- dbGetQuery(conn = mydb, statement = "SELECT * from movie_ratings")
query2## user_id movie_id movie_name movie_rating
## 1 1 1 The Dark Knight Rises 4.0
## 2 1 2 Mission: Impossible â\200“ Fallout 4.5
## 3 1 3 Star Wars: The Last Jedi 4.0
## 4 1 4 Black Panther 4.0
## 5 1 5 Incredibles 2 4.5
## 6 1 6 Avengers: Infinity War 5.0
## 7 2 1 The Dark Knight Rises 3.0
## 8 2 2 Mission: Impossible â\200“ Fallout 4.0
## 9 2 3 Star Wars: The Last Jedi 4.0
## 10 2 4 Black Panther 5.0
## 11 2 5 Incredibles 2 5.0
## 12 2 6 Avengers: Infinity War 5.0
## 13 3 1 The Dark Knight Rises 4.5
## 14 3 2 Mission: Impossible â\200“ Fallout 5.0
## 15 3 3 Star Wars: The Last Jedi 4.0
## 16 3 4 Black Panther 4.0
## 17 3 5 Incredibles 2 5.0
## 18 3 6 Avengers: Infinity War 5.0
## 19 4 1 The Dark Knight Rises 3.0
## 20 4 2 Mission: Impossible â\200“ Fallout 4.0
## 21 4 3 Star Wars: The Last Jedi 3.0
## 22 4 4 Black Panther 4.0
## 23 4 5 Incredibles 2 5.0
## 24 4 6 Avengers: Infinity War 5.0
## 25 5 1 The Dark Knight Rises 4.0
## 26 5 2 Mission: Impossible â\200“ Fallout 5.0
## 27 5 3 Star Wars: The Last Jedi 5.0
## 28 5 4 Black Panther 3.0
## 29 5 5 Incredibles 2 4.0
## 30 5 6 Avengers: Infinity War 3.0
## 31 6 1 The Dark Knight Rises 5.0
## 32 6 2 Mission: Impossible â\200“ Fallout 4.8
## 33 6 3 Star Wars: The Last Jedi 4.5
## 34 6 4 Black Panther 4.5
## 35 6 5 Incredibles 2 4.8
## 36 6 6 Avengers: Infinity War 4.5# Query to join usertable and movie_rating and get the resultset
query3 <- dbGetQuery(conn = mydb, statement = "select A.user_id, A.Firstname, A.Lastname, B.movie_id, B.movie_name, B.movie_rating
from usertable A, movie_ratings B where A.user_id = B.user_id ")
query3## user_id Firstname Lastname movie_id movie_name
## 1 1 Tom Racony 1 The Dark Knight Rises
## 2 1 Tom Racony 2 Mission: Impossible â\200“ Fallout
## 3 1 Tom Racony 3 Star Wars: The Last Jedi
## 4 1 Tom Racony 4 Black Panther
## 5 1 Tom Racony 5 Incredibles 2
## 6 1 Tom Racony 6 Avengers: Infinity War
## 7 2 Nick Torento 1 The Dark Knight Rises
## 8 2 Nick Torento 2 Mission: Impossible â\200“ Fallout
## 9 2 Nick Torento 3 Star Wars: The Last Jedi
## 10 2 Nick Torento 4 Black Panther
## 11 2 Nick Torento 5 Incredibles 2
## 12 2 Nick Torento 6 Avengers: Infinity War
## 13 3 Eric Pike 1 The Dark Knight Rises
## 14 3 Eric Pike 2 Mission: Impossible â\200“ Fallout
## 15 3 Eric Pike 3 Star Wars: The Last Jedi
## 16 3 Eric Pike 4 Black Panther
## 17 3 Eric Pike 5 Incredibles 2
## 18 3 Eric Pike 6 Avengers: Infinity War
## 19 4 Scott Limback 1 The Dark Knight Rises
## 20 4 Scott Limback 2 Mission: Impossible â\200“ Fallout
## 21 4 Scott Limback 3 Star Wars: The Last Jedi
## 22 4 Scott Limback 4 Black Panther
## 23 4 Scott Limback 5 Incredibles 2
## 24 4 Scott Limback 6 Avengers: Infinity War
## 25 5 Greg Gerardin 1 The Dark Knight Rises
## 26 5 Greg Gerardin 2 Mission: Impossible â\200“ Fallout
## 27 5 Greg Gerardin 3 Star Wars: The Last Jedi
## 28 5 Greg Gerardin 4 Black Panther
## 29 5 Greg Gerardin 5 Incredibles 2
## 30 5 Greg Gerardin 6 Avengers: Infinity War
## 31 6 James Hope 1 The Dark Knight Rises
## 32 6 James Hope 2 Mission: Impossible â\200“ Fallout
## 33 6 James Hope 3 Star Wars: The Last Jedi
## 34 6 James Hope 4 Black Panther
## 35 6 James Hope 5 Incredibles 2
## 36 6 James Hope 6 Avengers: Infinity War
## movie_rating
## 1 4.0
## 2 4.5
## 3 4.0
## 4 4.0
## 5 4.5
## 6 5.0
## 7 3.0
## 8 4.0
## 9 4.0
## 10 5.0
## 11 5.0
## 12 5.0
## 13 4.5
## 14 5.0
## 15 4.0
## 16 4.0
## 17 5.0
## 18 5.0
## 19 3.0
## 20 4.0
## 21 3.0
## 22 4.0
## 23 5.0
## 24 5.0
## 25 4.0
## 26 5.0
## 27 5.0
## 28 3.0
## 29 4.0
## 30 3.0
## 31 5.0
## 32 4.8
## 33 4.5
## 34 4.5
## 35 4.8
## 36 4.5# Query to use database functions and get average ratings for movies
query4 <- dbGetQuery(conn = mydb, statement = "select movie_id, round(avg(movie_rating),2) from cuny.movie_ratings group by movie_id")
query4## movie_id round(avg(movie_rating),2)
## 1 1 3.92
## 2 2 4.55
## 3 3 4.08
## 4 4 4.08
## 5 5 4.72
## 6 6 4.58Including Plots
You can also embed plots, for example:
Note that the echo = FALSE parameter was added to the code chunk to prevent printing of the R code that generated the plot.