human
Schema:
Inst (instID, name, country)
Authors (authID, lname, fname)
Papers (paperID, title)
Authorship (authID, instID, paperID, authOrder)
Please answer with SQL.
assistant
Sure, I understand.
human
Retrieve the country that has published the most papers.
assistant
SELECT t1.country FROM inst AS t1 JOIN authorship AS t2 ON t1.instid = t2.instid JOIN papers AS t3 ON t2.paperid = t3.paperid GROUP BY t1.country ORDER BY count(*) DESC LIMIT 1
human
Find the titles of the papers that contain the word 'ML'.
assistant
SELECT title FROM papers WHERE title LIKE '%ML%'
human
Retrieve the title of the paper that has the largest number of authors.
assistant
SELECT t2.title FROM authorship AS t1 JOIN papers AS t2 ON t1.paperid = t2.paperid WHERE t1.authorder = (SELECT max(authorder) FROM authorship)
human
Find the name of the organization that has published the largest number of papers.
assistant
SELECT t1.name FROM inst AS t1 JOIN authorship AS t2 ON t1.instid = t2.instid JOIN papers AS t3 ON t2.paperid = t3.paperid GROUP BY t1.name ORDER BY count(*) DESC LIMIT 1
human
Find the number of papers published by the institution 'University of Pennsylvania'.
assistant
SELECT count(DISTINCT t1.title) FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid = t2.paperid JOIN inst AS t3 ON t2.instid = t3.instid WHERE t3.name = 'University of Pennsylvania'
human
Count the number of total papers.
assistant
SELECT COUNT( * ) FROM papers ;