human
Schema:
author (aid, homepage, name, oid)
conference (cid, homepage, name)
domain (did, name)
domain_author (aid, did)
domain_conference (cid, did)
journal (homepage, jid, name)
domain_journal (did, jid)
keyword (keyword, kid)
domain_keyword (did, kid)
publication (abstract, cid, citation_num, jid, pid, reference_num, title, year)
domain_publication (did, pid)
organization (continent, homepage, name, oid)
publication_keyword (pid, kid)
writes (aid, pid)
cite (cited, citing)
Please answer with SQL.
assistant
Sure, I understand.
human
return me the papers by ' H. V. Jagadish ' .
assistant
SELECT t3.title FROM writes AS t2 JOIN author AS t1 ON t2.aid = t1.aid JOIN publication AS t3 ON t2.pid = t3.pid WHERE t1.name = 'H. V. Jagadish';
human
return me the authors who have cited the papers by ' H. V. Jagadish ' .
assistant
SELECT t2.name FROM publication AS t7 JOIN cite AS t5 ON t7.pid = t5.citing JOIN publication AS t6 ON t6.pid = t5.cited JOIN writes AS t3 ON t3.pid = t7.pid JOIN writes AS t4 ON t4.pid = t6.pid JOIN author AS t2 ON t3.aid = t2.aid JOIN author AS t1 ON t4.aid = t1.aid WHERE t1.name = 'H. V. Jagadish';
human
return me the authors who have cooperated both with ' H. V. Jagadish ' and ' Divesh Srivastava ' .
assistant
SELECT t2.name FROM writes AS t4 JOIN author AS t2 ON t4.aid = t2.aid JOIN publication AS t7 ON t4.pid = t7.pid JOIN writes AS t5 ON t5.pid = t7.pid JOIN writes AS t6 ON t6.pid = t7.pid JOIN author AS t1 ON t5.aid = t1.aid JOIN author AS t3 ON t6.aid = t3.aid WHERE t1.name = 'H. V. Jagadish' AND t3.name = 'Divesh Srivastava';
human
return me the number of authors who have cooperated with ' H. V. Jagadish ' .
assistant
SELECT COUNT ( DISTINCT t2.name ) FROM writes AS t3 JOIN author AS t2 ON t3.aid = t2.aid JOIN publication AS t5 ON t3.pid = t5.pid JOIN writes AS t4 ON t4.pid = t5.pid JOIN author AS t1 ON t4.aid = t1.aid WHERE t1.name = 'H. V. Jagadish';
human
return me the authors who have cooperated with ' H. V. Jagadish ' after 2000 .
assistant
SELECT t2.name FROM writes AS t3 JOIN author AS t2 ON t3.aid = t2.aid JOIN publication AS t5 ON t3.pid = t5.pid JOIN writes AS t4 ON t4.pid = t5.pid JOIN author AS t1 ON t4.aid = t1.aid WHERE t1.name = 'H. V. Jagadish' AND t5.year > 2000;
human
return me the authors who have cooperated with ' H. V. Jagadish ' .
assistant
SELECT t2.name FROM writes AS t3 JOIN author AS t2 ON t3.aid = t2.aid JOIN publication AS t5 ON t3.pid = t5.pid JOIN writes AS t4 ON t4.pid = t5.pid JOIN author AS t1 ON t4.aid = t1.aid WHERE t1.name = "H. V. Jagadish" ;