SQLize | PHPize | SQLtest

create table address like sakila.address; insert into address select * from sakila.address; create table customer like sakila.customer; insert into customer select * from sakila.customer; create table city like sakila.city; insert into city select * from sakila.city; create table country like sakila.country; insert into country select * from sakila.country;; 1. add new customer insert into customer ( store_id, first_name, last_name, email, address_id, activebool,active, create_date, last_update) values ( 1, 'kaleb', 'abebaw', 'kalebabebaw@gmail.com', 397, true,1, now(), now()); --2. add new actor to actor table insert into actor (first_name, last_name, last_update) values ('paul', 'mescal', now()); -- 3. add new film insert into film ( title, description, release_year, language_id, rental_duration, rental_rate, length, replacement_cost, rating,last_update) values ( 'gladiator 2', 'epic historical action film', 2025, 1, 5, 4.99, 140, 19.99, 'r', now()); --select * from film where title = 'gladiator 2' -- 4. add actor-film relationship (film_actor) insert into film_actor (actor_id, film_id, last_update) values ( (select actor_id from actor where first_name = 'paul' and last_name = 'mescal'), (select film_id from film where title = 'gladiator 2'), now()); -- 5. add film category (assume category_id = 1 for action) insert into film_category (film_id, category_id, last_update) values ( (select film_id from film where title = 'gladiator 2'), 1, now()); select * from film_category ca join film f on ca.film_id=f.film_id join inventory i on f.film_id =i.film_id where title ='gladiator 2' -- 6. add film to inventory insert into inventory (film_id, store_id, last_update) values ( (select film_id from film where title = 'gladiator 2'), 1, now()); -- 8. add new rental insert into rental ( rental_date, inventory_id, customer_id, return_date, staff_id, last_update) values ( now(), (select inventory_id from inventory order by inventory_id desc limit 1), (select customer_id from customer where first_name = 'kaleb' and last_name = 'abebaw'), null, 1, now()); -- 9. record payment insert into payment ( customer_id, staff_id, rental_id, amount, payment_date) values ( (select customer_id from customer where first_name = 'kaleb' and last_name = 'abebaw'), 1, (select rental_id from rental order by rental_id desc limit 1), 4.99, now()); -- General Analysis -- 1. List the top 10 longest movies along with their length and title. select title,length from film order by length desc limit 10 -- 2. Find all customers who have rented more than 10 movies. select c.first_name|| ' ' ||c.last_name customer_name,count(r.rental_id) rental_count from rental r left join customer c on r.customer_id=c.customer_id group by c.first_name,last_name having count(r.rental_id)>10 order by rental_count desc -- 3. Get the average rental rate for each movie rating (G, PG, R, etc.). select rating,round (avg(rental_rate),2) as rental_rate from film group by rating -- 4. Find the top 5 cities with the most customers. select c.city,count(cu.customer_id) customer_count from customer cu join address ad on ad.address_id=cu.address_id join city c on c.city_id=ad.city_id group by c.city order by customer_count desc limit 5; -- 5. Show the total revenue (payment amount) collected by each staff member. select s.first_name || '-'||s.last_name staff_member ,sum(p.amount) payment_collected from staff s join payment p on s.staff_id =p.staff_id group by s.first_name,s.last_name -- 6. Retrieve the 10 most rented films along with how many times each was rented. select f.title,count(r.rental_id) most_rented from film f join inventory i on f.film_id=i.film_id join rental r on i.inventory_id =r.inventory_id group by f.title order by most_rented desc limit 10 -- 7. Find the customer who has spent the most in total payments. SELECT c.first_name|| ' ' ||c.last_name customer_name,sum(p.amount) total_spent from customer c join payment p on c.customer_id=p.customer_id group by c.first_name ,c.last_name order by total_spent desc limit 1; -- 8. List all movies that have never been rented. select f.title from film f where f.film_id not in ( select i.film_id from rental r left join inventory i on i.inventory_id = r.inventory_id); -- 9. Show each rental, including customer full name, film title, and rental date. select c.first_name || ' ' || c.last_name full_name,f.title film_title,date(r.rental_date) rental_date from rental r join customer c on r.customer_id = c.customer_id join inventory i on r.inventory_id =i.inventory_id join film f on i.film_id = f.film_id -- 10. Display the customers who have rented a film categorized as "Comedy". select distinct c.customer_id,ca.name,c.first_name ||' '||c.last_name customer_name from customer c join rental r on r.customer_id = c.customer_id join inventory i on r.inventory_id = i.inventory_id join film f on i.film_id = f.film_id join film_category fc on f.film_id=fc.film_id join category ca on fc.category_id=ca.category_id where ca.name = 'Comedy' -- 11. Find the names of staff members who processed rentals in 2006. select distinct s.first_name|| '-' ||s.last_name staf_member ,date(r.rental_date) rental_year from staff s join rental r on s.staff_id=r.staff_id where extract (year from r.rental_date)=2006 -- 12. Retrieve all customers who rented a film with a rental rate greater than the average rental rate. select distinct c.customer_id,c.first_name,c.last_name from customer c left join rental r on c.customer_id =r.customer_id join inventory i on r.inventory_id=r.inventory_id join film f on i.film_id =f.film_id where f.rental_rate >(SELECT AVG(rental_rate)from film); -- 13. List all actors who have appeared in more than 20 films. select a.first_name ,a.last_name,count(fa.film_id) film_count FROM actor a join film_actor fa on a.actor_id=fa.actor_id group by a.actor_id having count(fa.film_id)>20 order by film_count desc -- case study -- 1. Customer Behavior Analysis: -- A marketing team wants to identify loyal customers to send special discount offers. -- o Write a query to find customers who rented more than 20 movies and spent more than $100 in total. -- o Return their full name, email, total rentals, and total amount paid. -- o Sort the results by the total amount spent, highest first. select c.first_name || ' '|| c.last_name as customer_name, c.email, count(r.rental_id) total_rentals, sum(p.amount) total_amount_spent from customer c join rental r on c.customer_id = r.customer_id join payment p on r.rental_id = p.rental_id group by c.customer_id having count(r.rental_id)>20 and sum(p.amount)>100 order by total_amount_spent desc; -- 2. Case Study 2: Film Performance Review: The store manager wants to know which movies are -- underperforming and might be removed from inventory. -- o Find all films that have been rented fewer than 5 times. -- o Return the film title, rental count, and average rental rate for each. -- o Sort the result by rental count, lowest first, and limit to 20 films. --underproforming movies avialable for rent in the inventory --store manager’s goal: find films that were available in inventory for rent but performed poorly. select f.title, count(r.rental_id) rental_count, Round (avg(f.rental_rate),2) avg_rental_rate from film f join inventory i on f.film_id =i.film_id left join rental r on i.inventory_id =r.inventory_id group by f.film_id having count(r.rental_id)<5 ORDER BY rental_count asc limit 20; --underproforming movies avialable for rent and also films that are not avialable or never stocked --store manager’s goal: find films that were available but performed poorly. --if we include store inventory select s.store_id, f.title as film_title, count(r.rental_id) as rental_count, round (avg(f.rental_rate),2) as avg_rental_rate from film f join inventory i on f.film_id = i.film_id join store s on i.store_id = s.store_id left join rental r on i.inventory_id = r.inventory_id group by s.store_id, f.film_id, f.title, f.rental_rate having count(r.rental_id) < 5 order by s.store_id, rental_count asc limit 20; ; select * from customer_address order by customer_id;