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
How many film are there?
assistant
SELECT count(*) FROM film
human
Find the titles and studios of the films that are produced by some film studios that contained the word 'Universal'.
assistant
SELECT title , Studio FROM film WHERE Studio LIKE '%Universal%'
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 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 studios of each film and the number of films produced by that studio.
assistant
SELECT Studio , COUNT( * ) FROM film GROUP BY Studio ;