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

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

2


SELECT f.title, f.description
FROM film f
LEFT JOIN inventory i ON f.film_id = i.film_id
LEFT JOIN rental r ON i.inventory_id = r.inventory_id
LEFT JOIN customer c ON r.customer_id = c.customer_id
WHERE r.rental_id IS NULL
ORDER BY f.title ASC;
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

3

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.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

4

SELECT a.first_name,
       a.last_name,
       COUNT(*) 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

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

It appears there are no movies with Johnny Depp.

6

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

1

data("diamonds")

plot <- ggplot(diamonds, aes(x = carat, y = price, color = cut, size = clarity)) +
  geom_point(alpha = 0.7) +
  labs(
    title = "Scatterplot of Diamond Price vs. Carat",
    subtitle = "Colored by Cut, Sized by Clarity",
    x = "Carat",
    y = "Price",
    color = "Cut",
    size = "Clarity"
  ) +
  theme_minimal()

ggplotly(plot)

2

##What is an example of a time when you would definitely want to use each one over the other? ggplot is great to use for visualizing data and being able to customize the layout of the plots and plotly is great for doing the same thing but making it interactive. I would use ggplot for making a report or presentation because it would only display the data I want the vieweer to focus on. I would use plotly for something web-based where I want the viewer to be able to actually interact with the data and explore it on their own.

3

library(gapminder)

country_colors <- rainbow(length(unique(gapminder$country)))

gapminder %>%
  plot_ly(x = ~gdpPercap, y = ~lifeExp, text = ~country, frame = ~year, type = 'scatter', mode = 'markers',
          marker = list(size = sqrt(gapminder$pop) / 1000, opacity = 0.7, color = country_colors)) %>%
  layout(title = "Life Expectancy vs. GDP per Capita over Time",
         xaxis = list(title = "GDP per Capita"),
         yaxis = list(title = "Life Expectancy"),
         hovermode = "closest",
         colorbar = list(title = "Country"))
## Warning: 'layout' objects don't have these attributes: 'colorbar'
## 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'

Comparing life expectancy to GDP per capita over time reveals how better GDP impacts population health. Generally, higher GDP per capita correlates with longer life expectancy.