Get data from MySQL database
This is the Markdown file for the my second homework assignment. The problem statement is as follows:
"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.
This is by design a very open ended assignment. A variety of reasonable approaches are acceptable. You can (and should) blank out your SQL password if your solution requires it; otherwise, full credit requires that your code is “reproducible,” with the assumption that I have the same database server and R software.
You may work in a small group on this assignment. If you work in a group, each group member should indicate who they worked with, and all group members should individually submit their week 2 assignment.
Please start early, and do work that you would want to include in a “presentations portfolio” that you might share in a job interview with a potential employer! You are encouraged to share thoughts, ask, and answer clarifying questions in the “Week 2: R and SQL” forum."
Below step sets the url pointer (just a variable) to the location:
## Warning: package 'RMySQL' was built under R version 3.6.3## Loading required package: DBI## Warning: package 'DBI' was built under R version 3.6.3## [1] "movies" "responders" "responses"s <- paste0("SELECT m.movie_name, r.resp_name, mr.response, r.city, r.state, r.ethnicity
FROM movies m JOIN responses mr
ON (m.movie_Id = mr.movie_Id)
JOIN responders r
ON (mr.resp_Id = r.resp_Id)
ORDER BY mr.resp_Id")
rs2 <- dbSendQuery(mydb, s)
df2 <- fetch(rs2, n=-1)
df2## movie_name resp_name response city state ethnicity
## 1 Shakespeare in Love John 3 New York New York White
## 2 Solaris John NA New York New York White
## 3 Enter the Dragon John 5 New York New York White
## 4 Star Wars John 3 New York New York White
## 5 Rumble in the Bronx John NA New York New York White
## 6 Autumn Sonata John 5 New York New York White
## 7 Autumn Sonata Ali 1 Columbus Ohio Asian
## 8 Shakespeare in Love Ali 2 Columbus Ohio Asian
## 9 Solaris Ali 4 Columbus Ohio Asian
## 10 Enter the Dragon Ali 5 Columbus Ohio Asian
## 11 Star Wars Ali NA Columbus Ohio Asian
## 12 Rumble in the Bronx Ali NA Columbus Ohio Asian
## 13 Rumble in the Bronx Jose 4 Gainesville Florida Hisp
## 14 Autumn Sonata Jose NA Gainesville Florida Hisp
## 15 Shakespeare in Love Jose NA Gainesville Florida Hisp
## 16 Solaris Jose 4 Gainesville Florida Hisp
## 17 Enter the Dragon Jose 5 Gainesville Florida Hisp
## 18 Star Wars Jose 4 Gainesville Florida Hisp
## 19 Star Wars Bergman 2 New York New York White
## 20 Rumble in the Bronx Bergman NA New York New York White
## 21 Autumn Sonata Bergman NA New York New York White
## 22 Shakespeare in Love Bergman 4 New York New York White
## 23 Solaris Bergman 5 New York New York White
## 24 Enter the Dragon Bergman 1 New York New York White
## 25 Enter the Dragon Peter 5 New York New York Black
## 26 Star Wars Peter 5 New York New York Black
## 27 Rumble in the Bronx Peter 5 New York New York Black
## 28 Autumn Sonata Peter 5 New York New York Black
## 29 Shakespeare in Love Peter 5 New York New York Black
## 30 Solaris Peter 5 New York New York BlackMarker: 607-02