
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
*/
Who has written a paper that has the word "Functional" in its title? Return the first names of the authors.
SELECT t1.fname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title LIKE "%Functional%"
[{'fname': 'Olin'}, {'fname': 'Aaron'}]