class 28.sql

CREATE DATABASE REGEX2;
USE REGEX2;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    city VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
);



INSERT INTO customers (customer_id, customer_name, city) VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Los Angeles'),
(3, 'Charlie', 'Chicago'),
(4, 'David', 'Houston'),
(5, 'Eva', 'Phoenix'),
(6, 'Frank', 'Philadelphia'),
(7, 'Grace', 'San Antonio'),
(8, 'Henry', 'San Diego'),
(9, 'Ivy', 'Dallas'),
(10, 'Jack', 'San Jose');


INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
(101, 1, '2024-01-05', 250.00),
(102, 2, '2024-01-06', 150.00),
(103, 3, '2024-01-07', 300.00),
(104, 1, '2024-01-10', 120.00),
(105, 5, '2024-01-12', 450.00),
(106, 6, '2024-01-15', 200.00),
(107, 2, '2024-01-18', 175.00),
(108, 8, '2024-01-20', 500.00),
(109, 11, '2024-01-22', 90.00),
(110, 12, '2024-01-25', 60.00);

select * from customers ;
select * from orders;

select c.customer_id , c.customer_name , c.city , o.order_id , o.customer_id , o.order_date from customers as c join orders as o on
c.customer_id = o.order_id ;

select c.customer_id ,c.customer_name , o.customer_id , o.amount from customers as c join orders as o using (customer_id);

select c.customer_id ,c.customer_name , o.customer_id , o.order_id , o.amount from customers as c left join orders as o 
using (customer_id);


select c.customer_id ,c.customer_name , o.customer_id , o.order_id , o.amount from customers as c right join orders as o
 using (customer_id);


select c.customer_id , c.customer_name , c.customer_id , o.order_id, o.order_date from customers as c join orders as o 
using (customer_id )  where customer_id = '1';

select c.customer_id , c.customer_name , c.customer_id , o.order_id, o.order_date from customers as c join orders as o 
using (customer_id) where customer_id = '2'; 

select c.customer_id , c.customer_name , c.customer_id , o.order_id, o.order_date , o.amount from customers as c join orders as o 
using (customer_id) where amount > '250';

select c.customer_id , c.customer_name , c.customer_id , o.order_id, o.order_date , o.amount from customers as c join orders as o 
using (customer_id) group by c.customer_name;


use sakila ;
select * from actor ;
select * from film_actor;

-- List actor names and the number of films they have acted in. Show only actors with more than 20 films.
SELECT a.first_name,a.last_name,COUNT(fa.film_id) AS film_count
FROM actor AS a JOIN film_actor AS fa ON a.actor_id = fa.actor_id
GROUP BY a.actor_id, a.first_name, a.last_name
HAVING COUNT(fa.film_id) > 20; 

-- Find the top 5 film categories with the highest number of rentals.

select * from category ;
select * from inventory ;
select * from rental ;
select * from film_category ;

select c.name , count(r.rental_id) as rental_count from category as c join film_category as fc on c.category_id = fc.category_id
 join inventory as i on i.film_id = fc.film_id 
 join rental as r on i.inventory_id = r.inventory_id group by c.name 
 order by rental_count desc 
 limit 5 ;

-- Find customers who have rented more than the average number of films.
select * from customer ;
select * from film;
select * from rental ;

select ;

-- Calculate total revenue generated by each store.

select * from payment ;
select * from store ;
select * from staff ;

SELECT s.store_id , sum(p.payment_id ) as payment from store as s join staff as st on s.store_id = st.store_id 
join payment as p on p.staff_id = st.staff_id group by s.store_id;

-- Find the actor whose films have been rented the maximum number of times.
select * from actor;
select * from film_actor ;
select * from inventory ;
select * from rental ;

select concat( a.first_name ,'', a.last_name ) as actor_name, count(r.rental_id) as rental_count  from actor as a join film_actor as fa on a.actor_id = fa.actor_id 
join inventory as i on i.film_id = fa.film_id join rental as r on r.inventory_id = i.inventory_id  group by actor_name order by 
rental_count desc limit 1 ;

-- Find total payment amount made by each customer and classify them as:
select * from payment ;
select * from customer ;

select