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
What is the last name of the author that has published the most papers?
assistant
SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid = t2.authid JOIN papers AS t3 ON t2.paperid = t3.paperid GROUP BY t1.fname , t1.lname ORDER BY count(*) DESC LIMIT 1
human
Which paper's title contains the word 'Database'?
assistant
SELECT title FROM papers WHERE title LIKE '%Database%'
human
Find the first names of all the authors who have written a paper with title containing the word 'Functional'.
assistant
SELECT t1.fname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid = t2.authid JOIN papers AS t3 ON t2.paperid = t3.paperid WHERE t3.title LIKE '%Functional%'
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
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
Which papers have the substring 'ML' in their titles? Return the titles of the papers.
assistant
SELECT title FROM papers WHERE title LIKE "%ML%" ;