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)

## 
## Attaching package: 'dbplyr'

## The following objects are masked from 'package:dplyr':
## 
##     ident, sql

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)


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")

dbListTables(con)

## character(0)

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")


#con1 <- dbConnect(SQLite(), dbname = "my_local_database.db")

###SQL Part

##Problem 1 Part 1:

--Problem 1

SELECT 
    c.first_name,
    c.last_name,
    c.email,
    COUNT(r.rental_id) AS total_rentals
FROM 
    customer c
JOIN 
    rental r
ON 
    c.customer_id = r.customer_id
GROUP BY 
    c.customer_id
ORDER BY 
    total_rentals DESC
LIMIT 10;

Displaying records 1 - 10
first_name	last_name	email	total_rentals
ELEANOR	HUNT	ELEANOR.HUNT@sakilacustomer.org	46
KARL	SEAL	KARL.SEAL@sakilacustomer.org	45
MARCIA	DEAN	MARCIA.DEAN@sakilacustomer.org	42
CLARA	SHAW	CLARA.SHAW@sakilacustomer.org	42
TAMMY	SANDERS	TAMMY.SANDERS@sakilacustomer.org	41
WESLEY	BULL	WESLEY.BULL@sakilacustomer.org	40
SUE	PETERS	SUE.PETERS@sakilacustomer.org	40
TIM	CARY	TIM.CARY@sakilacustomer.org	39
MARION	SNYDER	MARION.SNYDER@sakilacustomer.org	39
RHONDA	KENNEDY	RHONDA.KENNEDY@sakilacustomer.org	39

##Problem 2 Part 1:


SELECT f.title, f.description
FROM film f
WHERE f.film_id NOT IN (
    SELECT DISTINCT inv.film_id
    FROM inventory inv
    INNER JOIN rental r ON inv.inventory_id = r.inventory_id
)
ORDER BY f.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

##Problem 3 Part 1:



SELECT 
    c.name AS category_name,
    AVG(f.length) AS average_length
FROM 
    film f
JOIN 
    film_category fc ON f.film_id = fc.film_id
JOIN 
    category c ON fc.category_id = c.category_id
GROUP BY 
    c.category_id, c.name
ORDER BY 
    average_length DESC;

Displaying records 1 - 10
category_name	average_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

##Problem 4 Part 1:

--Problem 4

SELECT 
    a.first_name,
    a.last_name,
    COUNT(fa.film_id) AS film_count
FROM 
    actor a
JOIN 
    film_actor fa ON a.actor_id = fa.actor_id
GROUP BY 
    a.actor_id
ORDER BY 
    film_count DESC
LIMIT 5;

5 records
first_name	last_name	film_count
GINA	DEGENERES	42
WALTER	TORN	41
MARY	KEITEL	40
MATTHEW	CARREY	39
SANDRA	KILMER	37

##Problem 5 Part 1:


SELECT actor_id 
FROM actor 
WHERE first_name = 'JOHNNY' AND last_name = 'DEPP';

SELECT film_id 
FROM film_actor 
WHERE actor_id = X;

SELECT inventory_id 
FROM inventory 
WHERE film_id IN (SELECT film_id FROM film_actor WHERE actor_id = X);

SELECT rental_id 
FROM rental 
WHERE inventory_id IN (SELECT inventory_id FROM inventory WHERE film_id IN (SELECT film_id FROM film_actor WHERE actor_id = X));

SELECT DISTINCT c.first_name, c.last_name 
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
WHERE r.inventory_id IN (
    SELECT inventory_id 
    FROM inventory 
    WHERE film_id IN (
        SELECT film_id 
        FROM film_actor 
        WHERE actor_id = X
    )
);

0 records
actor_id

##Problem 6 Part 1:


SELECT f.title, SUM(p.amount) AS total_revenue
FROM payment p
INNER JOIN rental r ON p.rental_id = r.rental_id
INNER JOIN inventory inv ON r.inventory_id = inv.inventory_id
INNER JOIN film f ON inv.film_id = f.film_id
GROUP BY f.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 Part

##Question 1:

fig <- plot_ly(data = diamonds, x = ~carat, y = ~price, type = 'scatter', mode = 'markers',
               hoverinfo = 'text', text = ~paste('Cut:', cut, '<br>X:', x, '<br>Y:', y,'<br>Z:', z))

fig <- fig %>% layout(title = 'Diamond Price vs Carat',
                      xaxis = list(title = 'Carat'),
                      yaxis = list(title = 'Price (USD)'),
                      hovermode = 'closest')

fig

##Question 2:

The ggplot2 package is primarily used for creating detailed and aesthetically customizable plots that have static elements. A crucial aspect of the package is its layering structure that allows for multiple components to build complex graphics, making it ideal for producing high-quality plots that do not revolve around being able to interact with the data being displayed. Conversely, plotly specializes in interactive, web-based graphics that are usually catered towards more experienced data analysts. Its ability to turn plots into dynamic visualizations with features like zoom and hover greatly enhance the user’s ability to interact with the data. Lastly, making graphics with plotly is a bit easier to learn due to the more straight-forward syntax compared to ggplot2, but this does not mean that it is easier to use effectively.

The choice between creating graphics with ggplot2 or plotly is mainly dependent on the medium of display and the level of interactivity required. ggplot2 is much better for static, printed graphics where the representation of data is fixed, whereas plotly is preferred when the goal is to enable viewers to engage with different facets of the data in a visualization through various interactive elements. Lastly, ggplot2 offers more customization and thematic options which can be preferable when wanting to display static graphs in accordance with a theme or pattern.

##Question 3:

gapminder <- gapminder

plot <- gapminder %>%
  plot_ly(
    x = ~year, 
    y = ~lifeExp,
    text = ~country, 
    color = ~continent,
    colors = "Set1", 
    mode = 'markers'
  ) %>%
  layout(
    title = 'Life Expectancy Over Years by Continent',
    xaxis = list(title = 'Year'),
    yaxis = list(title = 'Life Expectancy')
  )

# Print the plot
plot

## No trace type specified:
##   Based on info supplied, a 'scatter' trace seems appropriate.
##   Read more about this trace type -> https://plotly.com/r/reference/#scatter

This plot displays life expectancy by country recorded every 5 years and separates continents by color. It helps to visualize how life expectancy has changed over the years across different continents and reveals patterns such as whether certain continents have experienced more significant increases in life expectancy, which continents and countries have higher life expectancies at different points in time, as well if there are outliers that may warrant further investigation.

The main narrative that is advanced by this comparison is that countries in Europe have been leading life expectancy averages since the 1950s. This makes sense as these countries have historically been the most powerful and innovative, hence the most capable in medicinal technology and discoveries which lead to increase life expectancy. A secondary narrative that is supported here is that the Oceanic countries have been near the top of the life expectancy averages for the past half century, which also makes sense for the same reason as the European countries but to a lesser extent.

sakila_hw

Method 1: Connect to Sakila remotely (preferred)

Method 2: Create the database locally.