human
Schema:
book_club (book_club_id, Year, Author_or_Editor, Book_Title, Publisher, Category, Result)
movie (movie_id, Title, Year, Director, Budget_million, Gross_worldwide)
culture_company (Company_name, Type, Incorporated_in, Group_Equity_Shareholding, book_club_id, movie_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Show all director names who have a movie in both year 1999 and 2000.
assistant
SELECT director FROM movie WHERE YEAR = 2000 INTERSECT SELECT director FROM movie WHERE YEAR = 1999
human
Show all director names who have a movie in the year 1999 or 2000.
assistant
SELECT director FROM movie WHERE YEAR = 1999 OR YEAR = 2000
human
What is the title and director for the movie with highest worldwide gross in the year 2000 or before?
assistant
SELECT title , director FROM movie WHERE YEAR <= 2000 ORDER BY gross_worldwide DESC LIMIT 1
human
What is the average, maximum, and minimum budget for all movies before 2000.
assistant
SELECT avg(budget_million) , max(budget_million) , min(budget_million) FROM movie WHERE YEAR < 2000
human
How many movie directors are there?
assistant
SELECT COUNT (DISTINCT director) FROM movie
human
What are the titles, years, and directors of all movies, ordered by budget in millions?
assistant
SELECT title , YEAR , director FROM movie ORDER BY budget_million ;