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 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 ordered in alphabetical order.
assistant
SELECT fname FROM authors ORDER BY fname
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
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 authors have first name 'Amal'? List their last names.
assistant
SELECT lname FROM authors WHERE fname = "Amal" ;