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
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
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 first names of all the authors ordered in alphabetical order.
assistant
SELECT fname FROM authors ORDER BY fname
human
Retrieve all the first and last names of authors in the alphabetical order of last names.
assistant
SELECT fname , lname FROM authors ORDER BY lname
human
Retrieve all the last names of authors in alphabetical order.
assistant
SELECT lname FROM authors ORDER BY lname ;