Question

MYSQL (SAKILLA DATABASE)

1. How many stores are in Lethbridge?
2. How many active customers are they?
3. How many rentals did our customer Mary Smith purchase through our staff member Mike Hillyer?
4. How many actors were in the film “Casablanca Super”?

Answer 1

Dear Student ,

As per requirement submitted above kindly find below solution.

This demonstration is using MySQL Workbench,

Question 1:

SQL Query :

select count(store_id) as 'Number of stores' from store,address,city where store.address_id=address.address_id and address.city_id=city.city_id and city='Lethbridge'

Screen in MySQL Workbench:

*************************************

Question 2:

SQL Query :

select count(*) as 'Number of active customers' from customer where active=1;

Screen in MySQL Workbench:

*************************************

Question 3:

SQL Query :

select count(rental_id) as 'Number of rentals' from rental,customer,staff
where
rental.customer_id=customer.customer_id and
rental.staff_id=staff.staff_id and customer.first_name='Mary' and customer.last_name='Smith'
and staff.first_name='Mike' and staff.last_name='Hillyer';

Screen in MySQL Workbench:

*************************************

Question 4:

SQL Query :

select count(actor_id) as 'Number of actors' from film_actor,film
where
film_actor.film_id=film.film_id and title='Casablanca Super';

Screen in MySQL Workbench:

*************************************

NOTE :PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.