query assignmennt1

create view film_category_name as (select film_id,film_category.category_id,category.name from film_category inner join category on film_category.category_id=category.category_id);

select * from (select film_id from film_category where category_id = (select category_id from category where name='Comedy'))A Inner Join (SELECT film_id FROM film  where rating='PG-13')B on A.film_id=B.film_id;
select film_text.*,no_of_rentals from film_text join (select count(rental_id)as no_of_rentals ,film_id from rental Join  (select inventory_id,film_id from inventory where film_id in (select film_id from film_category where category_id=(select category_id from category where name='Horror')))as horror_inventory on rental.inventory_id=horror_inventory.inventory_id group by film_id order by no_of_rentals desc limit 3)as top_3_film_id on top_3_film_id.film_id=film_text.film_id; 
select customers.* from (select rental.* from inventory join rental on inventory.inventory_id=rental.inventory_id where film_id in(select film_id from film_category_name where name='Sports'))as inventory_rental join (select customer.* from customer 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 where country='India')as customers on customers.customer_id=inventory_rental.customer_id group by customer_id;
select customer_list.* from customer_list join (select rental.* from inventory join rental on inventory.inventory_id=rental.inventory_id where film_id in(select film_id from film_actor where actor_id=(select actor_id from actor where first_name='NICK' and last_name='WAHLBERG')))rental_nick_movies on customer_list.ID=rental_nick_movies.customer_id where country='Canada' group by id;
select count(*) from film_list where 0< locate('SEAN WILLIAMS',actors);