Question

USING SQL

4. Show me the maximum rental duration and minimum rental duration from the film table. Name the columns MaxRentalDuration and MinRentalDuration respectively.

5. List the actor ID, last name, and first name of each actor and the count of films (name column FilmCount) each is in for those actors who acted in more than 35 films. Order results by FilmCount. (Hint: You will need to use GROUP BY and HAVING in your query.)

Answer 1

Solution :

Query 1:

SELECT MAX(rental_duration) as MaxRentalDuration,MIN(rental_duration) as MinRentalDuration from film;

Explanation:

Here we are retrieving maximum and minimum rental_durations from film table by using Aggregate functions.

Query 2:

select a.actor_id,a.first_name,a.last_name,b.FilmCount from actor a join (select actor_id,count(film_id) as FilmCount from film_actor group by actor_id having count(film_id)>35)b on a.actor_id=b.actor_id order by b.FilmCount;

Explanatoin:

Here we are counting the film_ids by grouping actor_ids and then we are filtering only the count(film_id)s greater than 35 by using having clause.

After that we are joining that result with actor table to get the first and last names of actor.

Finally sorting or arranging the records based on count(film_id)

Code and Output Screenshots:

Note : While displaying records in the table displayed only top 10 rows since the table contains huge data it is not possible to show entire one.if we want to see the data then check the film,actor,film_actor tables in sakila database in MySQL. Sakila database is a default database comes with MySQL Software.

Note : if you have any queries please post a comment thanks a lot..always available to help you