getting "Out of memory error"

Hi Anwar,
There seems to be a typo (you are joining on a 1=1 condition, which creates cartesian joins)
SELECT c.name, sum(p.amount)
FROM rental r
inner join payment p
on r.rental_id = p.rental_id
inner join inventory i
on r.inventory_id = i.inventory_id
inner join film_category fc
-----------------------------------------
on i.film_id = i.film_id
----------------------------------------
inner join film f
on f.film_id= fc.film_id
inner join category c
on c.category_id = fc.category_id
group by c.name
order by sum(p.amount) desc
limit 3;
Switching
on i.film_id = i.film_id
to
on i.film_id = fc.film_id
Solves the problem.
Thanks,
Leon