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
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 publications were added to the Cell journal this year ?
assistant
SELECT DISTINCT COUNT ( t3.paperid ) FROM paper AS t3 JOIN journal AS t2 ON t3.journalid = t2.journalid JOIN paperkeyphrase AS t1 ON t3.paperid = t1.paperid WHERE t2.journalname = 'Cell' AND t3.year = 2015;
human
List the popular publications on dependent types
assistant
SELECT DISTINCT t3.paperid FROM paperkeyphrase AS t2 JOIN keyphrase AS t1 ON t2.keyphraseid = t1.keyphraseid JOIN paper AS t3 ON t3.paperid = t2.paperid WHERE t1.keyphrasename = 'dependent types';
human
Find journals about Temporal Data .
assistant
SELECT DISTINCT t3.journalid FROM paperkeyphrase AS t2 JOIN keyphrase AS t1 ON t2.keyphraseid = t1.keyphraseid JOIN paper AS t3 ON t3.paperid = t2.paperid WHERE t1.keyphrasename = 'Temporal Data' GROUP BY t3.journalid;
human
What journals are Takashi Matsumoto 's articles published in ?
assistant
SELECT DISTINCT t3.journalid 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 = 'Takashi Matsumoto' GROUP BY t3.journalid;
human
List today 's journals
assistant
SELECT DISTINCT journalid FROM paper WHERE YEAR = 2011 GROUP BY journalid ;