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 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
How many institutions are there?
assistant
SELECT count(*) FROM inst
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'
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
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
What is the name of the institution that 'Matthias Blume' belongs 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 = "Matthias" AND t1.lname = "Blume" ;