
CREATE TABLE "Course" (
	"CID" VARCHAR(7), 
	"CName" VARCHAR(40), 
	"Credits" INTEGER, 
	"Instructor" INTEGER, 
	"Days" VARCHAR(5), 
	"Hours" VARCHAR(11), 
	"DNO" INTEGER, 
	PRIMARY KEY ("CID"), 
	FOREIGN KEY("Instructor") REFERENCES "Faculty" ("FacID"), 
	FOREIGN KEY("DNO") REFERENCES "Department" ("DNO")
)


CREATE TABLE "Department" (
	"DNO" INTEGER, 
	"Division" VARCHAR(2), 
	"DName" VARCHAR(25), 
	"Room" VARCHAR(5), 
	"Building" VARCHAR(13), 
	"DPhone" INTEGER, 
	PRIMARY KEY ("DNO")
)


CREATE TABLE "Enrolled_in" (
	"StuID" INTEGER, 
	"CID" VARCHAR(7), 
	"Grade" VARCHAR(2), 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("CID") REFERENCES "Course" ("CID"), 
	FOREIGN KEY("Grade") REFERENCES "Gradeconversion" (lettergrade)
)


CREATE TABLE "Faculty" (
	"FacID" INTEGER, 
	"Lname" VARCHAR(15), 
	"Fname" VARCHAR(15), 
	"Rank" VARCHAR(15), 
	"Sex" VARCHAR(1), 
	"Phone" INTEGER, 
	"Room" VARCHAR(5), 
	"Building" VARCHAR(13), 
	PRIMARY KEY ("FacID")
)


CREATE TABLE "Gradeconversion" (
	lettergrade VARCHAR(2), 
	gradepoint FLOAT, 
	PRIMARY KEY (lettergrade)
)


CREATE TABLE "Member_of" (
	"FacID" INTEGER, 
	"DNO" INTEGER, 
	"Appt_Type" VARCHAR(15), 
	FOREIGN KEY("FacID") REFERENCES "Faculty" ("FacID"), 
	FOREIGN KEY("DNO") REFERENCES "Department" ("DNO")
)


CREATE TABLE "Minor_in" (
	"StuID" INTEGER, 
	"DNO" INTEGER, 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("DNO") REFERENCES "Department" ("DNO")
)


CREATE TABLE "Student" (
	"StuID" INTEGER, 
	"LName" VARCHAR(12), 
	"Fname" VARCHAR(12), 
	"Age" INTEGER, 
	"Sex" VARCHAR(1), 
	"Major" INTEGER, 
	"Advisor" INTEGER, 
	city_code VARCHAR(3), 
	PRIMARY KEY ("StuID")
)

CREATE TABLE "Course" (
	"CID" VARCHAR(7), 
	"CName" VARCHAR(40), 
	"Credits" INTEGER, 
	"Instructor" INTEGER, 
	"Days" VARCHAR(5), 
	"Hours" VARCHAR(11), 
	"DNO" INTEGER, 
	PRIMARY KEY ("CID"), 
	FOREIGN KEY("Instructor") REFERENCES "Faculty" ("FacID"), 
	FOREIGN KEY("DNO") REFERENCES "Department" ("DNO")
)

/*
2 rows from Course table:
CID	CName	Credits	Instructor	Days	Hours	DNO
600.101	COMPUTER LITERACY	3	6112	MTW	3	600
600.103	INTRODUCTION TO COMPUTER SCIENCE	1	4230	Th	4	600
*/


CREATE TABLE "Department" (
	"DNO" INTEGER, 
	"Division" VARCHAR(2), 
	"DName" VARCHAR(25), 
	"Room" VARCHAR(5), 
	"Building" VARCHAR(13), 
	"DPhone" INTEGER, 
	PRIMARY KEY ("DNO")
)

/*
2 rows from Department table:
DNO	Division	DName	Room	Building	DPhone
10	AS	History of Art	268	Mergenthaler	7117
20	AS	Biology	144	Mudd	7330
*/


CREATE TABLE "Enrolled_in" (
	"StuID" INTEGER, 
	"CID" VARCHAR(7), 
	"Grade" VARCHAR(2), 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("CID") REFERENCES "Course" ("CID"), 
	FOREIGN KEY("Grade") REFERENCES "Gradeconversion" (lettergrade)
)

/*
2 rows from Enrolled_in table:
StuID	CID	Grade
1001	550.681	A-
1001	600.303	B
*/


CREATE TABLE "Faculty" (
	"FacID" INTEGER, 
	"Lname" VARCHAR(15), 
	"Fname" VARCHAR(15), 
	"Rank" VARCHAR(15), 
	"Sex" VARCHAR(1), 
	"Phone" INTEGER, 
	"Room" VARCHAR(5), 
	"Building" VARCHAR(13), 
	PRIMARY KEY ("FacID")
)

/*
2 rows from Faculty table:
FacID	Lname	Fname	Rank	Sex	Phone	Room	Building
1082	Giuliano	Mark	Instructor	M	2424	224	NEB
1121	Goodrich	Michael	Professor	M	3593	219	NEB
*/


CREATE TABLE "Gradeconversion" (
	lettergrade VARCHAR(2), 
	gradepoint FLOAT, 
	PRIMARY KEY (lettergrade)
)

/*
2 rows from Gradeconversion table:
lettergrade	gradepoint
A+	4.0
A	4.0
*/


CREATE TABLE "Member_of" (
	"FacID" INTEGER, 
	"DNO" INTEGER, 
	"Appt_Type" VARCHAR(15), 
	FOREIGN KEY("FacID") REFERENCES "Faculty" ("FacID"), 
	FOREIGN KEY("DNO") REFERENCES "Department" ("DNO")
)

/*
2 rows from Member_of table:
FacID	DNO	Appt_Type
7792	600	Primary
9210	520	Primary
*/


CREATE TABLE "Minor_in" (
	"StuID" INTEGER, 
	"DNO" INTEGER, 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("DNO") REFERENCES "Department" ("DNO")
)

/*
2 rows from Minor_in table:
StuID	DNO
1004	520
1005	550
*/


CREATE TABLE "Student" (
	"StuID" INTEGER, 
	"LName" VARCHAR(12), 
	"Fname" VARCHAR(12), 
	"Age" INTEGER, 
	"Sex" VARCHAR(1), 
	"Major" INTEGER, 
	"Advisor" INTEGER, 
	city_code VARCHAR(3), 
	PRIMARY KEY ("StuID")
)

/*
2 rows from Student table:
StuID	LName	Fname	Age	Sex	Major	Advisor	city_code
1001	Smith	Linda	18	F	600	1121	BAL
1002	Kim	Tracy	19	F	600	7712	HKG
*/
Find the last names of faculties in building Barton in alphabetic order.
SELECT Lname FROM FACULTY WHERE Building  =  "Barton" ORDER BY Lname
[{'Lname': 'Andreou'}, {'Lname': 'Brody'}, {'Lname': 'Byrne'}, {'Lname': 'Cauwenberghs'}, {'Lname': 'Collins'}, {'Lname': 'Davidson'}, {'Lname': 'Glaser'}, {'Lname': 'Goutsias'}, {'Lname': 'Hughes'}, {'Lname': 'Iglesias'}, {'Lname': 'Jelinek'}, {'Lname': 'Joseph'}, {'Lname': 'Kaplan'}, {'Lname': 'Khurgin'}, {'Lname': 'Meyer'}, {'Lname': 'Prince'}, {'Lname': 'Rugh'}, {'Lname': 'Runolfsson'}, {'Lname': 'Weinert'}, {'Lname': 'Westgate'}]