Ashish_Investigate_Relational_Database.txt

Question 1: We want to understand more about the movies that families are watching. The following categories are considered family movies: Animation, Children, Classics, Comedy, Family and Music.

Create a query that lists each movie, the film category it is classified in, and the number of times it has been rented out.

For this query, you will need 5 tables: Category, Film_Category, Inventory, Rental and Film. Your solution should have three columns: Film title, Category name and Count of Rentals and order by category name followed by the film title.

/* Qyery 1 */
SELECT film_title, category_name, COUNT(rental_id) rental_count
FROM
(SELECT c.name category_name, f.title film_title,r.rental_id rental_id
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
JOIN inventory i ON i.film_id = fc.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
)sub
GROUP BY 1,2
ORDER BY 2,1;


Question 2: Now we need to know how the length of rental duration of these family-friendly movies compares to the duration that all movies are rented for. Can you provide a table with the movie titles and divide them into 4 levels (first_quarter, second_quarter, third_quarter, and final_quarter) based on the quartiles (25%, 50%, 75%) of the rental duration for movies across all categories? Make sure to also indicate the category that these family-friendly movies fall into. You should only need the category, film_category, and film tables to answer this

/* Query 2 */
SELECT f.title, c.name, f.rental_duration, NTILE(4) OVER (ORDER BY rental_duration) as standard_quartile
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
ORDER BY 3;


Question 3: Finally, provide a table with the family-friendly film category, each of the quartiles, and the corresponding count of movies within each combination of film category for each corresponding rental duration category. The resulting table should have three columns: 1) Category, 2) Rental length category and 3)Count. The Count column should be sorted first by Category and then by Rental Duration category.

/* Query 3 */
SELECT category_name, standard_quartile, COUNT(standard_quartile)
FROM(
SELECT f.title, c.name category_name, f.rental_duration, NTILE(4) OVER (ORDER BY rental_duration) as standard_quartile
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music'))t1
GROUP BY 1,2
ORDER BY 1,2;


Question 4: We want to find out how the two stores compare in their count of rental orders during every month for all the years we have data for. Write a query that returns the store ID for the store, the year and month and the number of rental orders each store has fulfilled for that month. Your table should include a column for each of the following: year, month, store ID and count of rental orders fulfilled during that month. The count of rental orders is sorted in descending order.

/* Query 4 */
SELECT rental_month, rental_year, store_id, COUNT(rental_id) as count_rental
FROM
(SELECT r.rental_id, s.store_id,
DATE_PART('month', r.rental_date) as rental_month,
DATE_PART('year', r.rental_date) as rental_year
FROM rental r
JOIN staff st ON st.staff_id = r.staff_id
JOIN store s ON s.store_id = st.store_id)sub
GROUP BY 1,2,3
ORDER BY 4 DESC;