This document can serve as a reasonable start to your work on HW 8. Note that you need to have all nine csv files in the same folder as this HTML file. If you connected to the Sakila database remotely (the intended way before connection issues), that’s fine.

Method 2: Create the database locally.

library(DBI)
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")
dbListTables(con)
## [1] "actor"         "category"      "customer"      "film"         
## [5] "film_actor"    "film_category" "inventory"     "payment"      
## [9] "rental"

Test Query

SQL

SELECT * 
FROM actor
Displaying records 1 - 10
actor_id first_name last_name last_update
1 PENELOPE GUINESS 2006-02-15 04:34:33
2 NICK WAHLBERG 2006-02-15 04:34:33
3 ED CHASE 2006-02-15 04:34:33
4 JENNIFER DAVIS 2006-02-15 04:34:33
5 JOHNNY LOLLOBRIGIDA 2006-02-15 04:34:33
6 BETTE NICHOLSON 2006-02-15 04:34:33
7 GRACE MOSTEL 2006-02-15 04:34:33
8 MATTHEW JOHANSSON 2006-02-15 04:34:33
9 JOE SWANK 2006-02-15 04:34:33
10 CHRISTIAN GABLE 2006-02-15 04:34:33

Q1

SELECT customer.first_name, customer.last_name, customer.email, count(*)
FROM customer JOIN rental on customer.customer_id = rental.customer_id
GROUP BY customer.customer_id
ORDER BY count(*) DESC
LIMIT 10;
Displaying records 1 - 10
first_name last_name email count(*)
ELEANOR HUNT 46
KARL SEAL 45
MARCIA DEAN 42
CLARA SHAW 42
TAMMY SANDERS 41
WESLEY BULL 40
SUE PETERS 40
TIM CARY 39
MARION SNYDER 39
RHONDA KENNEDY 39

Q2

SELECT film.title, film.description
FROM film
LEFT JOIN
  (SELECT * FROM rental JOIN inventory ON rental.inventory_id = inventory.inventory_id) AS new_table
ON new_table.film_id = film.film_id
WHERE rental_id IS NULL
ORDER BY film.title;
Displaying records 1 - 10
title description
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

Q3

SELECT name, AVG(length)
FROM film
JOIN 
  (SELECT * FROM film_category JOIN category ON film_category.category_id = category.category_id) AS new_table
ON film.film_id = new_table.film_id
GROUP BY category_id
ORDER BY AVG(length) DESC;
Displaying records 1 - 10
name AVG(length)
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

Q4

SELECT count(*), actor.first_name, actor.last_name
FROM actor
JOIN
  (SELECT * FROM film JOIN film_actor ON film.film_id = film_actor.film_id) AS new_table
ON actor.actor_id = new_table.actor_id
GROUP BY actor.actor_id
ORDER BY count(*) DESC
LIMIT 5;
5 records
count(*) first_name last_name
42 GINA DEGENERES
41 WALTER TORN
40 MARY KEITEL
39 MATTHEW CARREY
37 SANDRA KILMER

Q6

SELECT film.title, SUM(payment.amount) AS total_revenue
FROM payment
JOIN rental ON payment.rental_id = rental.rental_id
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film ON inventory.film_id = film.film_id
GROUP BY film.title
ORDER BY total_revenue DESC
LIMIT 10;
Displaying records 1 - 10
title total_revenue
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

Q1

library(plotly)
## Loading required package: ggplot2
## 
## 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(ggplot2)
data("diamonds")

# Create a plotly scatterplot
plot_ly(data = diamonds, x = ~carat, y = ~price, color = ~cut, 
        type = "scatter", mode = "markers", 
        text = ~paste("Carat:", carat, "<br>Price:", price, "<br>Cut:", cut),
        marker = list(size = 6)) %>%
  layout(title = "Diamonds Dataset - Carat vs Price",
         xaxis = list(title = "Carat"),
         yaxis = list(title = "Price"),
         hovermode = "closest",
         hoverlabel = list(bgcolor = "white", font = list(size = 12)))

Q2

GGplot2 is particularly strong in creating static visualizations with a defining characteristics of layers covering on layers. It is great for EDA, reports, and papers, where visualizations are on a traditional media instead of web. On the other wand, to embed visualizations on a website, plotly is strongly preferred for strong interactiveness. However, it may require more memory and cause slow loading.

Q3

library(gapminder)
data("gapminder")

plot_ly(data = gapminder, x = ~gdpPercap, y = ~lifeExp, color = ~continent,
        type = "scatter", mode = "markers", size = ~pop,
        text = ~paste("Country:", country, "<br>Year:", year, "<br>GDP per capita:", gdpPercap, "<br>Life Expectancy:", lifeExp),
        marker = list(sizemode = "diameter", opacity = 0.5)) %>%
  layout(title = "GDP per Capita vs Life Expectancy (1952-2007)",
         xaxis = list(title = "GDP per Capita"),
         yaxis = list(title = "Life Expectancy"),
         hovermode = "closest",
         hoverlabel = list(bgcolor = "white", font = list(size = 12)))
## Warning: `line.width` does not currently support multiple values.

## Warning: `line.width` does not currently support multiple values.

## Warning: `line.width` does not currently support multiple values.

## Warning: `line.width` does not currently support multiple values.

## Warning: `line.width` does not currently support multiple values.

This plot compares GDP per capita and life expectancy over time for different continents. Each point represents a country, and the size of the point is proportional to the population of the country. This comparison helps in understanding the relationship between economic development and life expectancy across different regions. It can also highlight trends and disparities in development between continents. Generally GDP per Capita and life expectancy are positively correlated from this visaulization. In this visualization, countries at different time are considered to be independent observations to have more datapoints. Regional dispariteis are also displayed, for example, European countries are more likely to have both high economic prosperity and high life expectancy.