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
Please find all the papers published by 'Aaron Turon'?
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 = 'Aaron' AND t1.lname = 'Turon';
human
Please find all the papers published by 'Ralf Hinze'?
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 = 'Ralf' AND t1.lname = 'Hinze';
human
Can you add the paper IDs of those to the table please?
assistant
SELECT t3.title , t3.paperID 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 = 'Ralf' AND t1.lname = 'Hinze';
human
What is the name of the author who wrote the most papers? | Do you mean the fname or lname? | Both please
assistant
SELECT t1.lname , t1.fname 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.authID ORDER by COUNT( * ) desc LIMIT 1 ;