human
Schema:
venue (venueId, venueName)
author (authorId, authorName)
dataset (datasetId, datasetName)
journal (journalId, journalName)
keyphrase (keyphraseId, keyphraseName)
paper (paperId, title, venueId, year, numCiting, numCitedBy, journalId)
cite (citingPaperId, citedPaperId)
paperDataset (paperId, datasetId)
paperKeyphrase (paperId, keyphraseId)
writes (paperId, authorId)
Please answer with SQL.
assistant
Sure, I understand.
human
What are all the publication titles by Donald E Knuth ?
assistant
SELECT DISTINCT t3.title FROM writes AS t2 JOIN author AS t1 ON t2.authorid = t1.authorid JOIN paper AS t3 ON t2.paperid = t3.paperid WHERE t1.authorname = 'Donald E Knuth';
human
Who has published more papers in chi ?
assistant
SELECT DISTINCT COUNT ( DISTINCT t2.paperid ) , t1.authorid FROM venue AS t3 JOIN paper AS t2 ON t3.venueid = t2.venueid JOIN writes AS t1 ON t1.paperid = t2.paperid WHERE t3.venuename = 'chi' GROUP BY t1.authorid ORDER BY COUNT ( DISTINCT t2.paperid ) DESC;
human
which journal did Donald E Knuth publish his last paper ?
assistant
SELECT DISTINCT t3.journalid , t3.year FROM writes AS t2 JOIN author AS t1 ON t2.authorid = t1.authorid JOIN paper AS t3 ON t2.paperid = t3.paperid WHERE t1.authorname = 'Donald E Knuth' GROUP BY t3.journalid , t3.year ORDER BY t3.year DESC;
human
How many papers does jamie callan publish each year ?
assistant
SELECT DISTINCT COUNT ( t3.paperid ) , t3.year FROM writes AS t2 JOIN author AS t1 ON t2.authorid = t1.authorid JOIN paper AS t3 ON t2.paperid = t3.paperid WHERE t1.authorname = 'jamie callan' GROUP BY t3.year;
human
How many papers does Samuel Madden publish outside of PVLDB area ?
assistant
SELECT DISTINCT COUNT ( DISTINCT t3.paperid ) FROM venue AS t4 JOIN paper AS t3 ON t4.venueid = t3.venueid JOIN writes AS t2 ON t2.paperid = t3.paperid JOIN author AS t1 ON t2.authorid = t1.authorid WHERE t1.authorname = 'Samuel Madden' AND t4.venuename != 'PVLDB';
human
how many papers has Christopher D. Manning published ?
assistant
SELECT DISTINCT COUNT( DISTINCT t2.paperid ) FROM writes AS t2 JOIN author AS t1 ON t2.authorid = t1.authorid WHERE t1.authorname = "Christopher D. Manning" ;