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 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 distinct director of all films.
assistant
SELECT DISTINCT Director FROM film
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 names of studios that have at least two films.
assistant
SELECT Studio FROM film GROUP BY Studio HAVING COUNT(*) >= 2
human
Show the studios that have produced films with director 'Nicholas Meyer' and 'Walter Hill'.
assistant
SELECT Studio FROM film WHERE Director = 'Nicholas Meyer' INTERSECT SELECT Studio FROM film WHERE Director = 'Walter Hill'
human
Which studios have never worked with the director Walter Hill?
assistant
SELECT Studio FROM film EXCEPT SELECT Studio FROM film WHERE Director = "Walter Hill" ;