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 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
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
Show the studios that have not produced films with director 'Walter Hill'.
assistant
SELECT Studio FROM film EXCEPT SELECT Studio FROM film WHERE Director = 'Walter Hill'
human
List the title of films that do not have any market estimation.
assistant
SELECT Title FROM film WHERE Film_ID NOT IN (SELECT Film_ID FROM film_market_estimation)
human
Return the titles and directors of films that were never in the market of 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" ) ;