human
Schema:
film (Film_ID, Title, Studio, Director, Gross_in_dollar)
market (Market_ID, Country, Number_cities)
film_market_estimation (Estimation_ID, Low_Estimate, High_Estimate, Film_ID, Type, Market_ID, Year)
Please answer with SQL.
assistant
Sure, I understand.
human
List the studios which average gross is above 4500000.
assistant
SELECT Studio FROM film GROUP BY Studio HAVING avg(Gross_in_dollar) >= 4500000
human
Show the studios that have produced films with director 'Nicholas Meyer' and 'Walter Hill'.
assistant
SELECT Studio FROM film WHERE Director = 'Nicholas Meyer' INTERSECT SELECT Studio FROM film WHERE Director = 'Walter Hill'
human
How many film are there?
assistant
SELECT count(*) FROM film
human
List the name of film studio that have the most number of films.
assistant
SELECT Studio FROM film GROUP BY Studio ORDER BY COUNT(*) DESC LIMIT 1
human
List the names of studios that have at least two films.
assistant
SELECT Studio FROM film GROUP BY Studio HAVING COUNT(*) >= 2
human
How films are produced by each studio?
assistant
SELECT Studio , COUNT( * ) FROM film GROUP BY Studio ;