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
Please show the titles of films and the types of market estimations.
assistant
SELECT T1.Title , T2.Type FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID = T2.Film_ID
human
How many film are there?
assistant
SELECT count(*) FROM film
human
List the distinct director of all films.
assistant
SELECT DISTINCT Director FROM film
human
List the names of studios that have at least two films.
assistant
SELECT Studio FROM film GROUP BY Studio HAVING COUNT(*) >= 2
human
List the studios of each film and the number of films produced by that studio.
assistant
SELECT Studio , COUNT(*) FROM film GROUP BY Studio
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 ;