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
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 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
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
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" ;