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
How many authors are there?
assistant
SELECT count(*) FROM authors
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
Who belong to the institution 'University of Oxford'? 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 = 'University of Oxford'
human
Find all the papers published by the institution 'Google'.
assistant
SELECT DISTINCT t1.title FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid = t2.paperid JOIN inst AS t3 ON t2.instid = t3.instid WHERE t3.name = 'Google'
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" ;