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
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
How many institutions are there?
assistant
SELECT count(*) FROM inst
human
What are the papers published under the institution 'Indiana University'?
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 = 'Indiana University'
human
What is the first name of the author with last name 'Ueno'?
assistant
SELECT fname FROM authors WHERE lname = 'Ueno'
human
Which institution does 'Katsuhiro Ueno' belong to?
assistant
SELECT DISTINCT t3.name FROM authors AS t1 JOIN authorship AS t2 ON t1.authid = t2.authid JOIN inst AS t3 ON t2.instid = t3.instid WHERE t1.fname = "Katsuhiro" AND t1.lname = "Ueno" ;