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"
dbListFields(con, "film")
## [1] "film_id" "title" "description"
## [4] "release_year" "language_id" "original_language_id"
## [7] "rental_duration" "rental_rate" "length"
## [10] "replacement_cost" "rating" "special_features"
## [13] "last_update"
dbListFields(con, "rental")
## [1] "rental_id" "rental_date" "inventory_id" "customer_id" "return_date"
## [6] "staff_id" "last_update"
dbListFields(con, "inventory")
## [1] "inventory_id" "film_id" "store_id" "last_update"
dbListFields(con, "customer")
## [1] "customer_id" "store_id" "first_name" "last_name" "email"
## [6] "address_id" "active" "create_date" "last_update"
dbListFields(con, "film_actor")
## [1] "actor_id" "film_id" "last_update"
dbListFields(con, "actor")
## [1] "actor_id" "first_name" "last_name" "last_update"
Q1
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;
Q2
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
WHERE r.rental_id IS NULL
ORDER BY f.title ASC;
Displaying records 1 - 10
| 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 |
Q3
SELECT c.name AS category_name,
AVG(f.length) AS average_length
FROM film_category fc
JOIN film f ON fc.film_id = f.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
| 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 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
| GINA |
DEGENERES |
42 |
| WALTER |
TORN |
41 |
| MARY |
KEITEL |
40 |
| MATTHEW |
CARREY |
39 |
| SANDRA |
KILMER |
37 |
Q5
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
JOIN film f ON i.film_id = f.film_id
WHERE a.first_name = 'Johnny' AND a.last_name = 'Depp';
Q6
SELECT f.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.title
ORDER BY total_revenue DESC
LIMIT 10;
Displaying records 1 - 10
| 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
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")
plot <- plot_ly(data = diamonds, x = ~carat, y = ~price, type = 'scatter', mode = 'markers',
marker = list(size = 5, opacity = 0.5))
plot <- plot %>% layout(title = list(text = "Diamonds: Carat vs Price", x = 0.5),
xaxis = list(title = "Carat"),
yaxis = list(title = "Price"),
# Add subtitle
annotations = list(
list(
x = 0.5,
y = 1.02, # Adjusted vertical position
xref = "paper",
yref = "paper",
text = "Using the Diamonds Dataset in the Ggplot2 Package",
showarrow = FALSE
)
))
plot
2
Ggplot2 and plotly are similar in that they both are data
visualization tools that can integrate with various tools like Python,
MATLAB, Jupyter, and React. Plotly is naturally interactive, whereas
ggplot2 requires a tooltip add-on to become interactive, so if the focus
of my visualization was making it interactive, I would probably want to
use plotly over ggplot2. In ggplot2, users can progressively build more
intricate visualizations, utilizing a single system for crafting layered
plots, storing plots as objects, and accessing aesthetically pleasing
default color palettes and settings. These features make it more
customaizable than plotly in some ways. So if I wanted to make a
non-interactive, static visualization with impressive aesthetics like
color, margins, points, axis titles, and tickmarks, I would use
ggplot2.
3
library(plotly)
library(gapminder)
df <- gapminder
fig <- df %>%
plot_ly(
x = ~pop, y = ~gdpPercap,color = ~continent,
size = ~lifeExp, frame = ~year, text = ~country,
hoverinfo = "text", type = 'scatter', mode = 'markers'
)%>%
layout(title = "Population vs. GDP Per Capita",
xaxis = list(title = "Population"),
yaxis = list(title = "GDP Per Capita"))
fig
## 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.
## 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.
## 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.
## 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.
## 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.
## 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.
## 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.
## 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.
## 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.
## 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.
## 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.
## 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 comparison shows that from 1952-2007, many countries in Asia in
particular have experienced population growth without any increase in
their GDP Per Capita. The comparison also shows that there is a
prominent trend involving European and Oceanic countries increasing
their GDP per capita over time, but keeping their population’s steady.
This advances the narrative that there are disparities in global
development over the period from 1952 to 2007. The observation that many
Asian countries experienced population growth without a significant
increase in GDP per capita suggests that these nations may have faced
challenges in translating their population growth into economic
prosperity. The trend of European and Oceanic countries increasing their
GDP per capita while maintaining steady populations suggests a different
trajectory of development compared to Asian countries. This pattern
could indicate successful economic policies, investments in education
and technology, robust social welfare systems, and political stability
that facilitated sustained economic growth without the need for
significant population expansion.