Write a query to find the film which grossed the highest revenue for the video renting organisation

0
votes

select title from film where film_id in ("highest revenue")

select film_id from inventory where inventory_id in ( select inventory_id from rental group by inventory_id order by count(inventory_id) desc ))

limit (highest revenue);

where im wrong?

3
sqldatabase
Thats a bad question. No information, no tables given. You just posted your answer. Try adding more information about the table structure and its data. - targhs
@shubhangi my answer below was based on the syntax. Should you need a better answer, please provide sample data and expected results. - Useme Alehosaini

3 Answers

2
votes
SELECT title
FROM film
WHERE film_id in (SELECT film_id
FROM inventory
WHERE inventory_id in (
SELECT inventory_id
FROM rental
GROUP BY inventory_id
ORDER BY count(inventory_id) DESC
        )) limit 1;

even this code with joins will work

select Title
from film
inner join inventory
using (film_id)
inner join rental
using (inventory_id)
inner join payment
using (rental_id)
group by title
order by sum(amount) desc
limit 1;
-1
votes
SELECT title
FROM film
WHERE film_id IN ("highest revenue")

SELECT film_id
FROM inventory
WHERE inventory_id IN (
        SELECT inventory_id
        FROM rental
        GROUP BY inventory_id
        ORDER BY count(inventory_id) DESC
        ) limit(highest revenue);

Remove the ) after DESC

-1
votes

Write a query to find the film which grossed the highest revenue for the video renting organization.

select TITLE from FILM f 
inner join INVENTORY i using (FILM_ID)
inner join RENTAL r using (INVENTORY_ID)
inner join PAYMENT p using (RENTAL_ID)
group by TITLE
order by sum(AMOUNT) desc
limit 1;