library(DBI)
library(dbplyr)
con <- dbConnect(drv = RSQLite::SQLite(),
                 dbname = ":memory:")

rental<- read.csv("/Users/jiawendavis/Documents/SP24 STAT/04:05 datasets/rental.csv")
actor<- read.csv("/Users/jiawendavis/Documents/SP24 STAT/04:05 datasets/actor.csv")
customer<- read.csv("/Users/jiawendavis/Documents/SP24 STAT/04:05 datasets/customer.csv")
film<- read.csv("/Users/jiawendavis/Documents/SP24 STAT/04:05 datasets/film.csv")
inventory<- read.csv("/Users/jiawendavis/Documents/SP24 STAT/04:05 datasets/inventory.csv")
category<- read.csv("/Users/jiawendavis/Documents/SP24 STAT/04:05 datasets/category.csv")
film_category <- read.csv("/Users/jiawendavis/Documents/SP24 STAT/04:05 datasets/film_category.csv")
film_actor<- read.csv("/Users/jiawendavis/Documents/SP24 STAT/04:05 datasets/film_actor.csv")
payment<- read.csv("/Users/jiawendavis/Documents/SP24 STAT/04:05 datasets/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"

SQL

Test Query

SELECT *
FROM film
Displaying records 1 - 10
film_id title description release_year language_id original_language_id rental_duration rental_rate length replacement_cost rating special_features last_update
1 ACADEMY DINOSAUR A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies 2006 1 NULL 6 0.99 86 20.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
2 ACE GOLDFINGER A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China 2006 1 NULL 3 4.99 48 12.99 G Trailers,Deleted Scenes 2006-02-15 05:03:42
3 ADAPTATION HOLES A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory 2006 1 NULL 7 2.99 50 18.99 NC-17 Trailers,Deleted Scenes 2006-02-15 05:03:42
4 AFFAIR PREJUDICE A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank 2006 1 NULL 5 2.99 117 26.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
5 AFRICAN EGG A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico 2006 1 NULL 6 2.99 130 22.99 G Deleted Scenes 2006-02-15 05:03:42
6 AGENT TRUMAN A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China 2006 1 NULL 3 2.99 169 17.99 PG Deleted Scenes 2006-02-15 05:03:42
7 AIRPLANE SIERRA A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat 2006 1 NULL 6 4.99 62 28.99 PG-13 Trailers,Deleted Scenes 2006-02-15 05:03:42
8 AIRPORT POLLOCK A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India 2006 1 NULL 6 4.99 54 15.99 R Trailers 2006-02-15 05:03:42
9 ALABAMA DEVIL A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat 2006 1 NULL 3 2.99 114 21.99 PG-13 Trailers,Deleted Scenes 2006-02-15 05:03:42
10 ALADDIN CALENDAR A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China 2006 1 NULL 6 4.99 63 24.99 NC-17 Trailers,Deleted Scenes 2006-02-15 05:03:42

Problem 1

SELECT customer.first_name, customer.last_name, customer.email, COUNT(rental.rental_id) AS total_rentals
FROM customer
JOIN rental ON customer.customer_id = rental.customer_id
GROUP BY customer.customer_id
ORDER BY total_rentals DESC
LIMIT 10;
Displaying records 1 - 10
first_name last_name email total_rentals
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

Problem 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
WHERE rental.rental_id IS NULL
ORDER BY 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

Problem 3

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

Problem 4

SELECT actor.first_name, actor.last_name, COUNT(film_actor.film_id) AS film_count
FROM actor
JOIN film_actor ON actor.actor_id = film_actor.actor_id
GROUP BY actor.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

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

Problem 6

SELECT film.title, SUM(payment.amount) AS total_revenue
FROM film
JOIN inventory ON film.film_id = inventory.film_id
JOIN rental ON inventory.inventory_id = rental.inventory_id
JOIN payment ON rental.rental_id = payment.rental_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

Problem 1

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)

dia_plot <- plot_ly(data = diamonds, x= ~carat, y= ~price, mode = 'markers', 
                    type= 'scatter', marker= list(color='purple',size = 4, opacity = 0.5))

dia_plot <-dia_plot %>% layout(
  title = "<b>Carat vs Price from Diamonds Dataset<b>",
  xaxis = list(title = "Carat"),
  yaxis = list(title = "Price"),
  hoverinfo = "text",
  hovertext = paste("Carat: ", diamonds$carat, "<br>Price: ", diamonds$price)
)

dia_plot
## Warning: 'layout' objects don't have these attributes: 'hoverinfo', 'hovertext'
## Valid attributes include:
## '_deprecated', 'activeshape', 'annotations', 'autosize', 'autotypenumbers', 'calendar', 'clickmode', 'coloraxis', 'colorscale', 'colorway', 'computed', 'datarevision', 'dragmode', 'editrevision', 'editType', 'font', 'geo', 'grid', 'height', 'hidesources', 'hoverdistance', 'hoverlabel', 'hovermode', 'images', 'legend', 'mapbox', 'margin', 'meta', 'metasrc', 'modebar', 'newshape', 'paper_bgcolor', 'plot_bgcolor', 'polar', 'scene', 'selectdirection', 'selectionrevision', 'separators', 'shapes', 'showlegend', 'sliders', 'smith', 'spikedistance', 'template', 'ternary', 'title', 'transition', 'uirevision', 'uniformtext', 'updatemenus', 'width', 'xaxis', 'yaxis', 'barmode', 'bargap', 'mapType'

Problem 2

I would use plotly over ggplot2 when I want to make more interactive plots. ggplot2 primarily creates static plots that is good for embedding in documents and presentations, but plotly is more efficient for web-based viewing where users can explore and manipulate data. If I was making a static plot for a report or paper, I would use ggplot2. If I want to share an interactive plot online, I would use plotly.

Problem 3

gap_plot <- gapminder %>%
  plot_ly(x = ~year, y = ~gdpPercap, color = ~continent, mode = 'lines',
          colors = c('blue', 'darkgreen', 'purple3', 'orange', 'red', 'yellow2')) %>%
  layout(
    title = "<b>GDP per capita over time by continent<b>",
    xaxis = list(title = "Year", tickmode= "linear", dtick = 5),
    yaxis = list(title = "GDP per capita"),
    hoverinfo = "text",
    hovertext = ~paste("Country: ", country,"<br>GDP per Capita: ", gdpPercap)
  ) %>%
  config(line = list(width = 2)) # makes the lines bolder
gap_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
## Warning: 'layout' objects don't have these attributes: 'hoverinfo', 'hovertext'
## Valid attributes include:
## '_deprecated', 'activeshape', 'annotations', 'autosize', 'autotypenumbers', 'calendar', 'clickmode', 'coloraxis', 'colorscale', 'colorway', 'computed', 'datarevision', 'dragmode', 'editrevision', 'editType', 'font', 'geo', 'grid', 'height', 'hidesources', 'hoverdistance', 'hoverlabel', 'hovermode', 'images', 'legend', 'mapbox', 'margin', 'meta', 'metasrc', 'modebar', 'newshape', 'paper_bgcolor', 'plot_bgcolor', 'polar', 'scene', 'selectdirection', 'selectionrevision', 'separators', 'shapes', 'showlegend', 'sliders', 'smith', 'spikedistance', 'template', 'ternary', 'title', 'transition', 'uirevision', 'uniformtext', 'updatemenus', 'width', 'xaxis', 'yaxis', 'barmode', 'bargap', 'mapType'
## Warning: 'config' objects don't have these attributes: 'line'
## Valid attributes include:
## 'autosizable', 'displaylogo', 'displayModeBar', 'doubleClick', 'doubleClickDelay', 'editable', 'edits', 'fillFrame', 'frameMargins', 'globalTransforms', 'linkText', 'locale', 'locales', 'logging', 'mapboxAccessToken', 'modeBarButtons', 'modeBarButtonsToAdd', 'modeBarButtonsToRemove', 'notifyOnLogging', 'plotGlPixelRatio', 'plotlyServerURL', 'queueLength', 'responsive', 'scrollZoom', 'sendData', 'setBackground', 'showAxisDragHandles', 'showAxisRangeEntryBoxes', 'showEditInChartStudio', 'showLink', 'showSendToCloud', 'showSources', 'showTips', 'staticPlot', 'toImageButtonOptions', 'topojsonURL', 'typesetMath', 'watermark'

This line graph shows the GDP per capita over time, showing the differences between continents through color. It is easier to see this comparison when we hover over a point on a line because then we can see the exact year and GDP per capita value. This plot adequately shows us how different continents have progressed in there GDP per capita over time. Through this analysis of GDP per capita, we get a better idea of the economic conditions of each continent and can notice potential disparities in economic development. We see that overall most countries have had a steady rise in GDP per capita, with the very noticeable exception of two countries in Asia.