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
Find the papers which have 'Olin Shivers' as an author.
assistant
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid = t2.authid JOIN papers AS t3 ON t2.paperid = t3.paperid WHERE t1.fname = 'Olin' AND t1.lname = 'Shivers'
human
What is the first name of the author with last name 'Ueno'?
assistant
SELECT fname FROM authors WHERE lname = 'Ueno'
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
What are the last names of the author of the paper titled 'Binders Unbound'?
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 WHERE t3.title = 'Binders Unbound'
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
What are the first and last name of the author who published the paper titled 'Nameless, Painless'?
assistant
SELECT t1.fname , t1.lname 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 = "Nameless , Painless" ;