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 authors who have papers in VLDB conference before 2002 .
assistant
SELECT t1.name FROM publication AS t4 JOIN conference AS t2 ON t4.cid = t2.cid JOIN writes AS t3 ON t3.pid = t4.pid JOIN author AS t1 ON t3.aid = t1.aid WHERE t2.name = 'VLDB' AND t4.year < 2002;
human
return me the authors who have papers in VLDB conference in 2002 .
assistant
SELECT t1.name FROM publication AS t4 JOIN conference AS t2 ON t4.cid = t2.cid JOIN writes AS t3 ON t3.pid = t4.pid JOIN author AS t1 ON t3.aid = t1.aid WHERE t2.name = 'VLDB' AND t4.year = 2002;
human
return me the number of authors who have papers in the VLDB conference .
assistant
SELECT COUNT ( DISTINCT t1.name ) FROM publication AS t4 JOIN conference AS t2 ON t4.cid = t2.cid JOIN writes AS t3 ON t3.pid = t4.pid JOIN author AS t1 ON t3.aid = t1.aid WHERE t2.name = 'VLDB';
human
return me the author who has the most number of papers in the VLDB conference .
assistant
SELECT t1.name FROM publication AS t4 JOIN conference AS t2 ON t4.cid = t2.cid JOIN writes AS t3 ON t3.pid = t4.pid JOIN author AS t1 ON t3.aid = t1.aid WHERE t2.name = 'VLDB' GROUP BY t1.name ORDER BY COUNT ( DISTINCT t4.title ) DESC LIMIT 1;
human
return me the authors who have papers in the VLDB conference .
assistant
SELECT t1.name FROM publication AS t4 JOIN conference AS t2 ON t4.cid = t2.cid JOIN writes AS t3 ON t3.pid = t4.pid JOIN author AS t1 ON t3.aid = t1.aid WHERE t2.name = 'VLDB';
human
return me the authors who have more than 10 papers in the VLDB conference .
assistant
SELECT t1.name FROM publication AS t4 JOIN conference AS t2 ON t4.cid = t2.cid JOIN writes AS t3 ON t3.pid = t4.pid JOIN author AS t1 ON t3.aid = t1.aid WHERE t2.name = "VLDB" GROUP BY t1.name HAVING COUNT( DISTINCT t4.title ) > 10 ;