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 titles of papers whose first author is affiliated with an institution in the country 'Japan' and has last name 'Ohori'?
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 JOIN inst AS t4 ON t2.instid = t4.instid WHERE t4.country = 'Japan' AND t2.authorder = 1 AND t1.lname = 'Ohori'
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
Find the last name of the author with first name 'Amal'.
assistant
SELECT lname FROM authors WHERE fname = 'Amal'
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
What is the first name of the author with last name 'Ueno'?
assistant
SELECT fname FROM authors WHERE lname = "Ueno" ;