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 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
What are the low and high estimates of film markets?
assistant
SELECT Low_Estimate , High_Estimate FROM film_market_estimation
human
What is the average number of cities of markets with low film market estimate bigger than 10000?
assistant
SELECT avg(T2.Number_cities) FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID = T2.Market_ID WHERE T1.Low_Estimate > 10000
human
What is the average ticket sales gross in dollars of films?
assistant
SELECT avg(Gross_in_dollar) FROM film
human
Which studios have an average gross of over 4500000?
assistant
SELECT Studio FROM film GROUP BY Studio HAVING avg ( Gross_in_dollar ) >= 4500000 ;