
CREATE TABLE "Dorm" (
	dormid INTEGER, 
	dorm_name VARCHAR(20), 
	student_capacity INTEGER, 
	gender VARCHAR(1)
)


CREATE TABLE "Dorm_amenity" (
	amenid INTEGER, 
	amenity_name VARCHAR(25)
)


CREATE TABLE "Has_amenity" (
	dormid INTEGER, 
	amenid INTEGER, 
	FOREIGN KEY(amenid) REFERENCES "Dorm_amenity" (amenid), 
	FOREIGN KEY(dormid) REFERENCES "Dorm" (dormid)
)


CREATE TABLE "Lives_in" (
	stuid INTEGER, 
	dormid INTEGER, 
	room_number INTEGER, 
	FOREIGN KEY(dormid) REFERENCES "Dorm" (dormid), 
	FOREIGN KEY(stuid) REFERENCES "Student" ("StuID")
)


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 "Dorm" (
	dormid INTEGER, 
	dorm_name VARCHAR(20), 
	student_capacity INTEGER, 
	gender VARCHAR(1)
)

/*
2 rows from Dorm table:
dormid	dorm_name	student_capacity	gender
100	Smith Hall	85	X
110	Bud Jones Hall	116	M
*/


CREATE TABLE "Dorm_amenity" (
	amenid INTEGER, 
	amenity_name VARCHAR(25)
)

/*
2 rows from Dorm_amenity table:
amenid	amenity_name
900	TV Lounge
901	Study Room
*/


CREATE TABLE "Has_amenity" (
	dormid INTEGER, 
	amenid INTEGER, 
	FOREIGN KEY(amenid) REFERENCES "Dorm_amenity" (amenid), 
	FOREIGN KEY(dormid) REFERENCES "Dorm" (dormid)
)

/*
2 rows from Has_amenity table:
dormid	amenid
109	900
109	901
*/


CREATE TABLE "Lives_in" (
	stuid INTEGER, 
	dormid INTEGER, 
	room_number INTEGER, 
	FOREIGN KEY(dormid) REFERENCES "Dorm" (dormid), 
	FOREIGN KEY(stuid) REFERENCES "Student" ("StuID")
)

/*
2 rows from Lives_in table:
stuid	dormid	room_number
1001	109	105
1002	100	112
*/


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 name of students who is either female (sex is F) and living in the city of code BAL or male (sex is M) and in age of below 20.
SELECT lname FROM student WHERE sex  =  'F' AND city_code  =  'BAL' UNION SELECT lname FROM student WHERE sex  =  'M' AND age  <  20
[{'LName': 'Andreou'}, {'LName': 'Brody'}, {'LName': 'Epp'}, {'LName': 'Goldman'}, {'LName': 'Han'}, {'LName': 'Lee'}, {'LName': 'Nelson'}, {'LName': 'Norris'}, {'LName': 'Pang'}, {'LName': 'Prater'}, {'LName': 'Schultz'}, {'LName': 'Schwartz'}, {'LName': 'Simms'}, {'LName': 'Smith'}, {'LName': 'Tai'}, {'LName': 'Woods'}]