Sakila (MySQL) – SQL & VIEW Assignment
Student Name: Đinh Quốc Việt
Student ID: 11247245
Date: 28/01/2026
A. Basic SQL (SELECT / WHERE / ORDER BY / LIMIT)
Task 1: List 10 newest films by release_year
1) SQL Answer
2) Result Screenshot
Task 2: Find films with rating = 'PG-13' and length > 120 1) SQL Answer
2) Result Screenshot
Task 3: List customers living in one specific city
1) SQL Answer
2) Result Screenshot
Task 4: Show top 20 films with the highest rental_rate
1) SQL Answer
2) Result Screenshot
Task 5: Find customers whose email contains a specific domain pattern
1) SQL Answer
2) Result Screenshot
B. Functions (String / Date)
Task 6: Display each customer’s full name as one column
1) SQL Answer
2) Result Screenshot
Task 7: Show customer emails in lowercase
1) SQL Answer
2) Result Screenshot
Task 8: Compute rental duration in days
1) SQL Answer
2) Result Screenshot
Task 9: List rentals that are not returned yet
1) SQL Answer
2) Result Screenshot
Task 10: Count rentals by month
1) SQL Answer
2) Result Screenshot
C. JOIN
Task 11: List films with their language
1) SQL Answer
2) Result Screenshot
Task 12: List films and their actors 1) SQL Answer
2) Result Screenshot
Task 13: List customers with total number of rentals
1) SQL Answer
2) Result Screenshot
Task 14: Show each store with total inventory items
1) SQL Answer
2) Result Screenshot
Task 15: Show each staff member with total revenue they handled
1) SQL Answer
2) Result Screenshot
D. GROUP BY / HAVING
Task 16: Top 10 customers by number of rentals
1) SQL Answer
2) Result Screenshot
Task 17: Top 10 most-rented films
1) SQL Answer
2) Result Screenshot
Task 18: Count films by rating 1) SQL Answer
2) Result Screenshot
Task 19: Total revenue by store
1) SQL Answer
2) Result Screenshot
Task 20: Actors who acted in more than 20 films
1) SQL Answer
2) Result Screenshot
E. Subqueries
Task 21: Films with rental count higher than the average film rental count
1) SQL Answer
2) Result Screenshot
Task 22: Customers with total payments higher than the average customer payment
1) SQL Answer
2) Result Screenshot
Task 23: Categories with the highest revenue
1) SQL Answer
2) Result Screenshot
Task 24: Highest-revenue staff member for each store
1) SQL Answer
2) Result Screenshot
Task 25: Films that have never been rented
1) SQL Answer
2) Result Screenshot
F. Basic Views
Task 26: Create view v_customer_fullinfo
1) SQL Answer
2) Result Screenshot
Task 27: Create view v_film_fullinfo
1) SQL Answer
2) Result Screenshot
Task 28: Create view v_actor_film_count
1) SQL Answer
2) Result Screenshot
Task 29: Create view v_rental_status
1) SQL Answer
2) Result Screenshot
G. Revenue Reporting Views
Task 30: Create view v_revenue_daily
1) SQL Answer
2) Result Screenshot
Task 31: Create view v_revenue_by_store
1) SQL Answer
2) Result Screenshot
Task 32: Create view v_revenue_by_staff
1) SQL Answer
2) Result Screenshot
Task 33: Create view v_top_customers
1) SQL Answer
2) Result Screenshot
H. Advanced Views (Aggregations)
Task 34: Create view v_film_rental_stats
1) SQL Answer
2) Result Screenshot
Task 35: Create view v_category_performance
1) SQL Answer
2) Result Screenshot
Task 36: Create view v_customer_activity_monthly
1) SQL Answer
2) Result Screenshot