human
Schema:
actor (aid, gender, name, nationality, birth_city, birth_year)
copyright (id, msid, cid)
cast (id, msid, aid, role)
genre (gid, genre)
classification (id, msid, gid)
company (id, name, country_code)
director (did, gender, name, nationality, birth_city, birth_year)
producer (pid, gender, name, nationality, birth_city, birth_year)
directed_by (id, msid, did)
keyword (id, keyword)
made_by (id, msid, pid)
movie (mid, title, release_year, title_aka, budget)
tags (id, msid, kid)
tv_series (sid, title, release_year, num_of_seasons, num_of_episodes, title_aka, budget)
writer (wid, gender, name, nationality, num_of_episodes, birth_city, birth_year)
written_by (id, msid, wid)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the directors of all movies featuring ' Kate Winslet '
assistant
SELECT t3.name FROM CAST AS t4 JOIN actor AS t1 ON t4.aid = t1.aid JOIN movie AS t5 ON t5.mid = t4.msid JOIN directed_by AS t2 ON t5.mid = t2.msid JOIN director AS t3 ON t3.did = t2.did WHERE t1.name = 'Kate Winslet';
human
Find all movies directed by ' Steven Spielberg ' after 2006
assistant
SELECT t3.title FROM director AS t2 JOIN directed_by AS t1 ON t2.did = t1.did JOIN movie AS t3 ON t3.mid = t1.msid WHERE t2.name = 'Steven Spielberg' AND t3.release_year > 2006;
human
What is the number of movies directed by ' Woody Allen ' per year ?
assistant
SELECT COUNT ( DISTINCT t3.title ) , t3.release_year FROM director AS t2 JOIN directed_by AS t1 ON t2.did = t1.did JOIN movie AS t3 ON t3.mid = t1.msid WHERE t2.name = 'Woody Allen' GROUP BY t3.release_year;
human
Find all movies featuring ' Robin Wright '
assistant
SELECT t2.title FROM CAST AS t3 JOIN actor AS t1 ON t3.aid = t1.aid JOIN movie AS t2 ON t2.mid = t3.msid WHERE t1.name = 'Robin Wright';
human
Find all movies written and produced by ' Woody Allen '
assistant
SELECT t2.title FROM movie AS t2 JOIN made_by AS t3 ON t2.mid = t3.msid JOIN producer AS t1 ON t1.pid = t3.pid JOIN written_by AS t5 ON t5.msid = t2.mid JOIN writer AS t4 ON t5.wid = t4.wid WHERE t1.name = 'Woody Allen' AND t4.name = 'Woody Allen';
human
Find all movies that star both ' Woody Strode ' and ' Jason Robards
assistant
SELECT t4.title FROM CAST AS t5 JOIN actor AS t1 ON t5.aid = t1.aid JOIN movie AS t4 ON t4.mid = t5.msid JOIN CAST AS t3 ON t4.mid = t3.msid JOIN actor AS t2 ON t3.aid = t2.aid WHERE t1.name = "Woody Strode" AND t2.name = "Jason Robards" ;