
CREATE TABLE "Authors" (
	"authID" INTEGER, 
	lname TEXT, 
	fname TEXT, 
	PRIMARY KEY ("authID")
)


CREATE TABLE "Authorship" (
	"authID" INTEGER, 
	"instID" INTEGER, 
	"paperID" INTEGER, 
	"authOrder" INTEGER, 
	PRIMARY KEY ("authID", "instID", "paperID"), 
	FOREIGN KEY("authID") REFERENCES "Authors" ("authID"), 
	FOREIGN KEY("instID") REFERENCES "Inst" ("instID"), 
	FOREIGN KEY("paperID") REFERENCES "Papers" ("paperID")
)


CREATE TABLE "Inst" (
	"instID" INTEGER, 
	name TEXT, 
	country TEXT, 
	PRIMARY KEY ("instID")
)


CREATE TABLE "Papers" (
	"paperID" INTEGER, 
	title TEXT, 
	PRIMARY KEY ("paperID")
)

CREATE TABLE "Authors" (
	"authID" INTEGER, 
	lname TEXT, 
	fname TEXT, 
	PRIMARY KEY ("authID")
)

/*
2 rows from Authors table:
authID	lname	fname
50	Gibbons	Jeremy
51	Hinze	Ralf
*/


CREATE TABLE "Authorship" (
	"authID" INTEGER, 
	"instID" INTEGER, 
	"paperID" INTEGER, 
	"authOrder" INTEGER, 
	PRIMARY KEY ("authID", "instID", "paperID"), 
	FOREIGN KEY("authID") REFERENCES "Authors" ("authID"), 
	FOREIGN KEY("instID") REFERENCES "Inst" ("instID"), 
	FOREIGN KEY("paperID") REFERENCES "Papers" ("paperID")
)

/*
2 rows from Authorship table:
authID	instID	paperID	authOrder
50	1000	200	1
51	1000	200	2
*/


CREATE TABLE "Inst" (
	"instID" INTEGER, 
	name TEXT, 
	country TEXT, 
	PRIMARY KEY ("instID")
)

/*
2 rows from Inst table:
instID	name	country
1000	University of Oxford	UK
1010	Northeastern University	USA
*/


CREATE TABLE "Papers" (
	"paperID" INTEGER, 
	title TEXT, 
	PRIMARY KEY ("paperID")
)

/*
2 rows from Papers table:
paperID	title
200	Just do it: Simple Monadic Equational Reasoning
201	Proving the Unique Fixed-Point Principle Correct: An Adventure with Category Theory
*/
Find the titles of papers whose first author is affiliated with an institution in the country "Japan" and has last name "Ohori"?
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"
[{'title': 'Making Standard ML a Practical Database Programming Language'}]