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
What are all institutions in 'Japan'?
assistant
SELECT * FROM INST WHERE country = 'Japan';
human
Which authors belong to them?
assistant
SELECT 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.country = 'Japan';
human
Who has last name 'Ohori'?
assistant
SELECT 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.country = 'Japan' AND t1.lname = 'Ohori';
human
Give me the titles of papers he wrote as the first author.
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" ;