Method 2: Create the database locally.
library(DBI)
## Warning: package 'DBI' was built under R version 4.3.2
library(dbplyr)
con <- dbConnect(drv = RSQLite::SQLite(),
dbname = ":memory:")
customer <- read.csv("customer.csv")
rental <- read.csv("rental.csv")
film <- read.csv("film.csv")
inventory <- read.csv("inventory.csv")
category <- read.csv("category.csv")
film_category <- read.csv("film_category.csv")
actor <- read.csv("actor.csv")
film_actor <- read.csv("film_actor.csv")
payment <- read.csv("payment.csv")
dbWriteTable(conn = con, name = "customer", value = customer)
dbWriteTable(conn = con, name = "rental", value = rental)
dbWriteTable(conn = con, name = "film", value = film)
dbWriteTable(conn = con, name = "inventory", value = inventory)
dbWriteTable(conn = con, name = "category", value = category)
dbWriteTable(conn = con, name = "film_category", value = film_category)
dbWriteTable(conn = con, name = "actor", value = actor)
dbWriteTable(conn = con, name = "film_actor", value = film_actor)
dbWriteTable(conn = con, name = "payment", value = payment)
knitr::opts_chunk$set(connection = "con")
SQL
Question 1
SELECT
first_name, last_name, email, COUNT(rental_date)
FROM
customer
JOIN
rental USING (customer_id)
GROUP BY
customer_id
ORDER BY
COUNT(rental_date) DESC
LIMIT 10;
Question 2
SELECT
title, description
FROM
film
LEFT JOIN
inventory on film.film_id=inventory.film_id
LEFT JOIN
rental on inventory.inventory_id=rental.inventory_id
GROUP BY
film.film_id
HAVING
COUNT(rental_id) = 0
ORDER BY
film.title ASC;
Displaying records 1 - 10
| ALICE FANTASIA |
A Emotional Drama of a A Shark And a Database
Administrator who must Vanquish a Pioneer in Soviet Georgia |
| APOLLO TEEN |
A Action-Packed Reflection of a Crocodile And a
Explorer who must Find a Sumo Wrestler in An Abandoned Mine Shaft |
| ARGONAUTS TOWN |
A Emotional Epistle of a Forensic Psychologist And a
Butler who must Challenge a Waitress in An Abandoned Mine Shaft |
| ARK RIDGEMONT |
A Beautiful Yarn of a Pioneer And a Monkey who must
Pursue a Explorer in The Sahara Desert |
| ARSENIC INDEPENDENCE |
A Fanciful Documentary of a Mad Cow And a Womanizer who
must Find a Dentist in Berlin |
| BOONDOCK BALLROOM |
A Fateful Panorama of a Crocodile And a Boy who must
Defeat a Monkey in The Gulf of Mexico |
| BUTCH PANTHER |
A Lacklusture Yarn of a Feminist And a Database
Administrator who must Face a Hunter in New Orleans |
| CATCH AMISTAD |
A Boring Reflection of a Lumberjack And a Feminist who
must Discover a Woman in Nigeria |
| CHINATOWN GLADIATOR |
A Brilliant Panorama of a Technical Writer And a
Lumberjack who must Escape a Butler in Ancient India |
| CHOCOLATE DUCK |
A Unbelieveable Story of a Mad Scientist And a
Technical Writer who must Discover a Composer in Ancient China |
Question 3
SELECT
category.name, AVG(length)
FROM
film
JOIN
film_category USING (film_id)
JOIN
category USING (category_id)
GROUP BY
category.name
ORDER BY
AVG(length) DESC;
Displaying records 1 - 10
| Sports |
128.2027 |
| Games |
127.8361 |
| Foreign |
121.6986 |
| Drama |
120.8387 |
| Comedy |
115.8276 |
| Family |
114.7826 |
| Music |
113.6471 |
| Travel |
113.3158 |
| Horror |
112.4821 |
| Classics |
111.6667 |
Question 4
SELECT
first_name, last_name, COUNT(film_id)
FROM
actor
JOIN
film_actor USING (actor_id)
GROUP BY
actor.actor_id
ORDER BY
COUNT(film_id) DESC
LIMIT 5;
5 records
| GINA |
DEGENERES |
42 |
| WALTER |
TORN |
41 |
| MARY |
KEITEL |
40 |
| MATTHEW |
CARREY |
39 |
| SANDRA |
KILMER |
37 |
Question 5
SELECT
customer.first_name, customer.last_name
FROM
customer
JOIN
rental USING (customer_id)
JOIN
inventory USING (inventory_id)
JOIN
film_actor USING (film_id)
JOIN
actor USING (actor_ID)
WHERE
actor.first_name="Johnny" AND actor.last_name="Depp";
Question 6
SELECT
title, SUM(amount)
FROM
film
JOIN
inventory USING (film_id)
JOIN
rental USING (inventory_id)
JOIN
payment USING (rental_id)
GROUP BY
film.film_id
ORDER BY
SUM(amount) DESC
LIMIT 10;
Displaying records 1 - 10
| TELEGRAPH VOYAGE |
231.73 |
| WIFE TURN |
223.69 |
| ZORRO ARK |
214.69 |
| GOODFELLAS SALUTE |
209.69 |
| SATURDAY LAMBS |
204.72 |
| TITANS JERK |
201.71 |
| TORQUE BOUND |
198.72 |
| HARRY IDAHO |
195.70 |
| INNOCENT USUAL |
191.74 |
| HUSTLER PARTY |
190.78 |
Plotly
library(ggplot2)
library(plotly)
##
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
##
## last_plot
## The following object is masked from 'package:stats':
##
## filter
## The following object is masked from 'package:graphics':
##
## layout
library(gapminder)
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.4 ✔ readr 2.1.5
## ✔ forcats 1.0.0 ✔ stringr 1.5.1
## ✔ lubridate 1.9.2 ✔ tibble 3.2.1
## ✔ purrr 1.0.2 ✔ tidyr 1.3.0
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks plotly::filter(), stats::filter()
## ✖ dplyr::ident() masks dbplyr::ident()
## ✖ dplyr::lag() masks stats::lag()
## ✖ dplyr::sql() masks dbplyr::sql()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
Question 1
plotly1 <- plot_ly(diamonds, x=~depth, y=~price, color=~cut, type="scatter", mode="markers", text=~carat, hoverinfo="text") %>%
layout(xaxis=list(title="Depth"), yaxis=list(title="Price"),
title="Diamond Depth vs Price<br><sub>Hover for Carat</sub>")
plotly1
Question 2
Ggplot2 and plotly can both be helpful when making graphs, but they
each have their own advantages and disadvantages. For example, both of
them are useful in customizing things like titles, axis labels, and the
size and colors of points. However, plotly takes a much longer time to
run, and the information can sometimes be delayed when hovering over
points. Therefore, if you are just trying to make a simple graph very
quickly, ggplot2 is probably a better option because it tends to run
much faster. One advantage of plotly over ggplot2 is that you can
include hover information, which can be very useful if you want the
viewer to be able to see information about specific points.
Question 3
plotly3 <- plot_ly(x=gapminder$gdpPercap, y=gapminder$lifeExp, type="scatter", mode="markers", frame=gapminder$year) %>%
layout(xaxis=list(title="GDP Per Capita"), yaxis=list(title=
"Life Expectancy"), title="GDP Per Capita and Life Expectancy Over Time")
plotly3
This comparison shows that over time, both GDP per capita and life
expectancy have increased. The shape of this growth is logarithmic,
meaning that life expectancy peaks around 85 years old, but GDP per
capita continues to increase. This advances the narrative that there is
a cap on life expectancy, but not on GDP per capita.