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
What are the titles and directors of the films were never presented in China?
assistant
SELECT title , director FROM film WHERE film_id NOT IN (SELECT film_id FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.market_id = T2.Market_ID WHERE country = 'China')
human
List the distinct director of all films.
assistant
SELECT DISTINCT Director FROM film
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
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 many film are there?
assistant
SELECT COUNT( * ) FROM film ;