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
How many movies about Persians were released after 1990 ?
assistant
SELECT COUNT ( DISTINCT t3.title ) FROM tags AS t2 JOIN keyword AS t1 ON t2.kid = t1.id JOIN movie AS t3 ON t2.msid = t3.mid WHERE t1.keyword = 'Persians' AND t3.release_year > 1990;
human
How many movies did ' Shahab Hosseini ' act in ?
assistant
SELECT COUNT ( DISTINCT 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 = 'Shahab Hosseini';
human
List all the directors of movies about nuclear weapons
assistant
SELECT t3.name FROM director AS t3 JOIN directed_by AS t2 ON t3.did = t2.did JOIN movie AS t4 ON t4.mid = t2.msid JOIN tags AS t5 ON t5.msid = t4.mid JOIN keyword AS t1 ON t5.kid = t1.id WHERE t1.keyword = 'nuclear weapons';
human
Find the movie which is classified in the most number of genres
assistant
SELECT t3.title FROM genre AS t2 JOIN classification AS t1 ON t2.gid = t1.gid JOIN movie AS t3 ON t3.mid = t1.msid GROUP BY t3.title ORDER BY COUNT ( DISTINCT t2.genre ) DESC LIMIT 1;
human
Find all movies directed by ' Asghar Farhadi ' and featuring ' Taraneh Alidoosti '
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 directed_by AS t2 ON t4.mid = t2.msid JOIN director AS t3 ON t3.did = t2.did WHERE t1.name = 'Taraneh Alidoosti' AND t3.name = 'Asghar Farhadi';
human
what are the genres of movies directed by ' Asghar Farhadi
assistant
SELECT t3.genre FROM director AS t5 JOIN directed_by AS t2 ON t5.did = t2.did JOIN movie AS t4 ON t4.mid = t2.msid JOIN classification AS t1 ON t4.mid = t1.msid JOIN genre AS t3 ON t3.gid = t1.gid WHERE t5.name = "Asghar Farhadi" ;