Sakila BD Exercise

Question

I am trying to develop my SQL (MySQL) capabilities in the Sakila database. I have questions on the following question: What is the average movie rental time per category and per year?

Here's my query so far:

select avg(r.return_date - r.rental_date) as avgDate, c.name
from 
    rental r, category c
    inner join inventory i
        on i.inventory_id = r.inventory_id 
    inner join film f
        on f.film_id=i.film_id 
group by c.name
order by avgDate desc;

The error is as follows:

Error Code: 1054. Unknown column 'r.inventory_id' in 'on clause'.

I know the part of the year is still missing, but I am trying to solve this problem. Someone can help me?

Thanks!!

GMB · Accepted Answer · 2019-12-10T23:15:27.210

Don't mix implicit and explicit joins. Explicit joins have higher prescedence than implicit joins so they are evaluated first, and this results in the error that you are getting. Matter of fact, always use explicit joins.

Also, you need to bring in junction table film_category to relate a film to a category.

This is your new from clause:

from 
    rental r
    inner join inventory i on i.inventory_id = r.inventory_id 
    inner join film f on f.film_id = i.film_id
    inner join film_category fc on fc.fim_id = f.fim_id
    inner join category c on c.category_id = fc.category_id

Another problem in your query is date arithmetics. You cannot substract two dates in MySQL. You could use timestampdiff() for that purpose. Say you want the difference between dates in minutes, then: timestampdiff(minute, r.rental_date, r.return_date).

Your final query:

select 
    c.name, 
    avg(timestampdiff(minute, r.rental_date, r.return_date)) avg_rental_duration
from 
    rental r
    inner join inventory i on i.inventory_id = r.inventory_id 
    inner join film f on f.film_id = i.film_id
    inner join film_category fc on fc.fim_id = f.fim_id
    inner join category c on c.category_id = fc.category_id
group by c.category_id, c.name

Sakila BD Exercise

1 Answers1