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
Show the distinct director of films with market estimation in the year of 1995.
assistant
SELECT DISTINCT T1.Director FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID = T2.Film_ID WHERE T2.Year = 1995
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
How many film are there?
assistant
SELECT count(*) 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 names of studios that have at least two films.
assistant
SELECT Studio FROM film GROUP BY Studio HAVING COUNT(*) >= 2
human
What are the different film Directors?
assistant
SELECT DISTINCT Director FROM film ;