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 journals, which have more than 60 papers containing keyword ' Relational Database ' .
assistant
SELECT t3.name FROM publication_keyword AS t4 JOIN keyword AS t1 ON t4.kid = t1.kid JOIN publication AS t2 ON t2.pid = t4.pid JOIN journal AS t3 ON t2.jid = t3.jid WHERE t1.keyword = 'Relational Database' GROUP BY t3.name HAVING COUNT ( DISTINCT t2.title ) > 60;
human
return me the number of authors who have papers containing keyword ' Relational Database ' .
assistant
SELECT COUNT ( DISTINCT t2.name ) FROM publication_keyword AS t5 JOIN keyword AS t1 ON t5.kid = t1.kid JOIN publication AS t3 ON t3.pid = t5.pid JOIN writes AS t4 ON t4.pid = t3.pid JOIN author AS t2 ON t4.aid = t2.aid WHERE t1.keyword = 'Relational Database';
human
return me the authors who have papers containing keyword ' Relational Database ' .
assistant
SELECT t2.name FROM publication_keyword AS t5 JOIN keyword AS t1 ON t5.kid = t1.kid JOIN publication AS t3 ON t3.pid = t5.pid JOIN writes AS t4 ON t4.pid = t3.pid JOIN author AS t2 ON t4.aid = t2.aid WHERE t1.keyword = 'Relational Database';
human
return me the journal that has the most number of papers containing keyword ' Relational Database ' .
assistant
SELECT t3.name FROM publication_keyword AS t4 JOIN keyword AS t1 ON t4.kid = t1.kid JOIN publication AS t2 ON t2.pid = t4.pid JOIN journal AS t3 ON t2.jid = t3.jid WHERE t1.keyword = 'Relational Database' GROUP BY t3.name ORDER BY COUNT ( DISTINCT t2.title ) DESC LIMIT 1;
human
return me the authors who have more than 10 papers containing keyword ' Relational Database ' .
assistant
SELECT t2.name FROM publication_keyword AS t5 JOIN keyword AS t1 ON t5.kid = t1.kid JOIN publication AS t3 ON t3.pid = t5.pid JOIN writes AS t4 ON t4.pid = t3.pid JOIN author AS t2 ON t4.aid = t2.aid WHERE t1.keyword = 'Relational Database' GROUP BY t2.name HAVING COUNT ( DISTINCT t3.title ) > 10;
human
return me the author who has the most number of papers containing keyword ' Relational Database ' .
assistant
SELECT t2.name FROM publication_keyword AS t5 JOIN keyword AS t1 ON t5.kid = t1.kid JOIN publication AS t3 ON t3.pid = t5.pid JOIN writes AS t4 ON t4.pid = t3.pid JOIN author AS t2 ON t4.aid = t2.aid WHERE t1.keyword = "Relational Database" GROUP BY t2.name ORDER BY COUNT( DISTINCT t3.title ) DESC LIMIT 1 ;