
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
*/
Sort the list of all the first and last names of authors in alphabetical order of the last names.
SELECT fname ,  lname FROM authors ORDER BY lname
[{'fname': 'Amal', 'lname': 'Ahmed'}, {'fname': 'Matthias', 'lname': 'Blume'}, {'fname': 'Jeremy', 'lname': 'Gibbons'}, {'fname': 'Ralf', 'lname': 'Hinze'}, {'fname': 'Daniel W. H.', 'lname': 'James'}, {'fname': 'Atsushi', 'lname': 'Ohori'}, {'fname': 'Nicolas', 'lname': 'Pouillard'}, {'fname': 'Tim', 'lname': 'Sheard'}, {'fname': 'Olin', 'lname': 'Shivers'}, {'fname': 'Aaron', 'lname': 'Turon'}, {'fname': 'Katsuhiro', 'lname': 'Ueno'}, {'fname': 'Stephanie', 'lname': 'Weirich'}, {'fname': 'Brent', 'lname': 'Yorgey'}]