SQl Home Assignment
#importing the libraries
library(RSQLite)## Warning: package 'RSQLite' was built under R version 4.3.3library(DBI)## Warning: package 'DBI' was built under R version 4.3.3#connecting Database
drv <- dbDriver("SQLite")
mydta <- dbConnect(drv, dbname = "rent-a-movie.db")
#testing connection
dbListTables(mydta)## [1] "actor" "address" "category"
## [4] "city" "country" "customer"
## [7] "customer_list" "film" "film_actor"
## [10] "film_category" "film_list" "film_text"
## [13] "inventory" "language" "payment"
## [16] "rental" "sales_by_film_category" "sales_by_store"
## [19] "sqlite_sequence" "staff" "staff_list"
## [22] "store"#Q#1: Code
q1 <- dbGetQuery(mydta ,"SELECT payment.customer_id ,
sum(payment.amount) AS total_spending,
country.country AS country
FROM payment
JOIN customer
ON customer.customer_id = payment.customer_id
JOIN address
ON customer.address_id = address.address_id
JOIN city
ON city.city_id = address.city_id
JOIN country
ON country.country_id = city.country_id
GROUP BY 3
HAVING total_spending >= 11
ORDER BY 2 DESC
Limit 5;")q1## customer_id total_spending country
## 1 12 6630.27 India
## 2 37 5802.73 China
## 3 2 4110.32 United States
## 4 1 3471.74 Japan
## 5 84 3307.04 MexicoTop 5 targeted countries are India, China, United States, Japan and Mexico respectively.
#Q#2 Code is as below
q2 <- dbGetQuery(mydta,'SELECT cat.name AS genre,SUM(p.amount) AS Amount_spent
FROM payment AS p
JOIN rental AS r ON p.rental_id = r.rental_id
JOIN inventory AS inv ON r.inventory_id = inv.inventory_id
JOIN film AS f ON inv.film_id = f.film_id
JOIN film_category AS fc ON f.film_id = fc.film_id
JOIN category AS cat ON fc.category_id = cat.category_id
GROUP BY cat.name
HAVING SUM(p.amount) >= 11
ORDER BY Amount_spent DESC
limit 5;')q2## genre Amount_spent
## 1 Sports 5314.21
## 2 Sci-Fi 4756.98
## 3 Animation 4656.30
## 4 Drama 4587.39
## 5 Comedy 4383.58Top targeted gendres are Sports, Sci-Fi, Animation, Drama and Comedy Respectively.