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 last name of the author with first name 'Amal'.
assistant
SELECT lname FROM authors WHERE fname = 'Amal'
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 last names of all the authors that have written a paper with title containing the word 'Monadic'.
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 LIKE '%Monadic%'
human
Find the first and last name of the author(s) who wrote the paper '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'
human
How many authors are there?
assistant
SELECT count(*) FROM authors
human
Which author has written the most papers? Find his or her last name.
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 ;