sql1.txt

1.Which actors have the first name ‘Scarlett’?
	select * from actor where first_name like 'SCARLETT';

2.Which actors have the last name ‘Johansson’?
	select * from actor where last_name like 'JOHANSSON';
	
3.How many distinct actors last names are there?
	select count(distinct(last_name))as 'Count of Distinct actors last names' from actor

4.Which last names are not repeated?
	select last_name from actor group by last_name having count(last_name)=1;

5.Which last names appear more than once?
	select last_name from actor group by last_name having count(last_name)>1;

6.Which actor has appeared in the most films?
	select concat(first_name,' ', last_name) as 'Actor Name', count(*) as 'No of Films' from film_actor, actor_info where film_actor.actor_id=actor_info.actor_id group by film_actor.actor_id order by count(*) desc limit 1;
	

7.Is ‘Academy Dinosaur’ available for rent from Store 1?
	

8.Insert a record to represent Mary Smith renting ‘Academy Dinosaur’ from Mike Hillyer at Store 1 today .
	insert into rental(rental_date, inventory_id, customer_id, return_date, staff_id) values(now(), 1, 1, date_add(now(), interval 6 day), 1);

9.When is ‘Academy Dinosaur’ due?
	select rental.rental_id, concat(customer.first_name,' ',customer.last_name) as 'Rented Customer Name', datediff(rental.return_date, rental.rental_date) as 'Due in Days' from rental, inventory, customer where rental.inventory_id=inventory.inventory_id and inventory.film_id=1 and rental.customer_id=customer.customer_id;


10.What is that average running time of all the films in the sakila DB?
	select avg(length) as 'Avg running time of all films' from film

11.What is the average running time of films by category?
	select avg(length) as 'Avg running time', category.name as 'Category' from film, film_category, category where  film.film_id=film_category.film_id and film_category.category_id=category.category_id group by category.category_id;

12.Why does this query return the empty set? 
select * from film natural join inventory;

	Since, there are no matching columns except film_id, it returns empty set.