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("~/Desktop/customer.csv")
rental <- read.csv("~/Desktop/rental.csv")
film <- read.csv("~/Desktop/film.csv")
inventory <- read.csv("~/Desktop/inventory.csv")
category <- read.csv("~/Desktop/category.csv")
film_category <- read.csv("~/Desktop/film_category.csv")
actor <- read.csv("~/Desktop/actor.csv")
film_actor <- read.csv("~/Desktop/film_actor.csv")
payment <- read.csv("~/Desktop/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"

#Query 1

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

#Query 2

SELECT
  film.title,
  film.description
FROM
  film film
LEFT JOIN
  inventory i ON film.film_id = i.film_id
LEFT JOIN
  rental r ON i.inventory_id = r.inventory_id
WHERE
  r.rental_id IS NULL
ORDER BY
  film.title
Displaying records 1 - 10
title description
ACADEMY DINOSAUR A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
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

#Query 3

SELECT 
    c.name AS category_name,
    AVG(f.length) AS average_length
FROM 
    category c
JOIN 
    film_category fc ON c.category_id = fc.category_id
JOIN 
    film f ON fc.film_id = f.film_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

#Query 4

SELECT 
    a.actor_id,
    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, a.first_name, a.last_name
ORDER BY 
    film_count DESC
LIMIT 5;
5 records
actor_id first_name last_name film_count
107 GINA DEGENERES 42
102 WALTER TORN 41
198 MARY KEITEL 40
181 MATTHEW CARREY 39
23 SANDRA KILMER 37

#Query 5

SELECT DISTINCT
    c.first_name,
    c.last_name
FROM
    customer c
JOIN
    rental r ON c.customer_id = r.customer_id
JOIN
    inventory i ON r.inventory_id = i.inventory_id
JOIN
    film_actor fa ON i.film_id = fa.film_id
JOIN
    actor a ON fa.actor_id = a.actor_id
WHERE
    a.first_name = 'Johnny' AND a.last_name = 'Depp';
0 records
first_name last_name

#Query 6

SELECT
  film.title AS film_title,
  COUNT(rent.rental_id) * film.rental_rate AS Total_Revenue
FROM
  film film
JOIN
  inventory invent ON film.film_id = invent.film_id
JOIN
  rental rent ON invent.inventory_id = rent.inventory_id
GROUP BY
  film.film_id, film.title, film.rental_rate
ORDER BY
  Total_Revenue DESC
LIMIT 10
Displaying records 1 - 10
film_title Total_Revenue
BUCKET BROTHERHOOD 169.66
SCALAWAG DUCK 159.68
APACHE DIVINE 154.69
GOODFELLAS SALUTE 154.69
WIFE TURN 154.69
ZORRO ARK 154.69
CAT CONEHEADS 149.70
DOGMA FAMILY 149.70
HARRY IDAHO 149.70
MASSACRE USUAL 149.70

#Plotly 1

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
diamonds_plot <- plot_ly(
  diamonds, 
  x = ~carat, 
  y = ~price, 
  type = "scatter", 
  mode = "markers", 
  text = ~paste("Carat: ", carat, "<br>Price: $", price)
)

diamonds_plot <- diamonds_plot %>% 
  layout(
    title = "Price vs Carat Size of Diamonds",
    xaxis = list(title = "Carat Size"),
    yaxis = list(title = "Price in $$"),
    showlegend = FALSE,
    annotations = list(
      text = "The plot indicates a clear trend: as the carat size of the diamond
      increases, so does its price. Interestingly, there's a noticeable clustering
      of data points around whole number carat sizes, suggesting that these sizes
      are commonly sought after by buyers.",
      x=5,
      y=900
    )
  )

diamonds_plot

Plotly #2 ggplot2 and Plotly cater to different needs and contexts.ggplot2 is useful in creating more static plots, whereas plotly is great for creating more interactive and dynamic plots. ggplot2 creates graphics layer by layer, so as you want more you continue to add. On the other hand, plotly specializes in interactive graphics that can be placed in web applications. This is specifically useful for projects that need interactice aspects and user engagement.

In essence, ggplot2 is the tool of choice when you require static, highly-customized visuals for clear data interpretation and thorough analysis. On the other hand, Plotly is great when you need to use interactivity, explore additional dimensions, or present information in a captivating manner. An example of a use case of plotly is an interactive dashboard that allows users to explore traffic congestion levels by day of the week and time of day. An example for ggplot2, is a static graph of CO2 emissions over a few years.

#Plotly 3

library(gapminder)
library(plotly)

gapminder_india <- gapminder[gapminder$country == "India", ]


plot_india <- plot_ly(gapminder_india, x = ~year, type = "scatter", mode = "lines+markers") %>%
  add_trace(
    y = ~gdpPercap,
    name = "GDP per Capita",
    text = ~paste("Year: ", year, "<br>GDP per Capita: $", gdpPercap)
  ) %>%
  add_trace(
    y = ~lifeExp,
    name = "Life Expectancy",
    text = ~paste("Year: ", year, "<br>Life Expectancy: ", lifeExp, " years"),
    yaxis = "y2"
  ) %>%
  layout(
    title = "GDP per Capita vs Life Expectancy Over Time in India",
    xaxis = list(title = "Year"),
    yaxis = list(
      title = "GDP per Capita",
      side = "left",
      zeroline = FALSE
    ),
    yaxis2 = list(
      title = "Life Expectancy",
      overlaying = "y",
      side = "right",
      zeroline = FALSE
    ),
    showlegend = TRUE
  ) %>%
  config(displayModeBar = FALSE)


plot_india

This code creates a Plotly plot with GDP per capita and life expectancy plotted against the year for India. The plot includes both lines and markers for better visualization, and the y-axes are scaled appropriately to fit the different ranges of the two variables. The plot includes text annotations to provide additional information when hovering over data points. Finally, the mode bar is hidden to avoid cluttering the plot with unnecessary buttons.