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 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 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
Find the first names of all the authors ordered in alphabetical order.
assistant
SELECT fname FROM authors ORDER BY fname
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
Which authors belong to the institution 'Google'? Show the first names and last names.
assistant
SELECT DISTINCT t1.fname , t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid = t2.authid JOIN inst AS t3 ON t2.instid = t3.instid WHERE t3.name = 'Google'
human
Find the first names and last names of the authors whose institution affiliation is 'University of Oxford'.
assistant
SELECT DISTINCT t1.fname , t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid = t2.authid JOIN inst AS t3 ON t2.instid = t3.instid WHERE t3.name = "University of Oxford" ;