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
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
How many movie directors are there?
assistant
SELECT COUNT (DISTINCT director) FROM movie
human
Show all movie titles, years, and directors, ordered by budget.
assistant
SELECT title , YEAR , director FROM movie ORDER BY budget_million
human
Show all company names with a movie directed in year 1999.
assistant
SELECT T2.company_name FROM movie AS T1 JOIN culture_company AS T2 ON T1.movie_id = T2.movie_id WHERE T1.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
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 ;