
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
*/
What is the first and last name of all students who are younger than average?
SELECT fname ,  lname FROM student WHERE age  <  (SELECT avg(age) FROM student)
[{'Fname': 'Linda', 'LName': 'Smith'}, {'Fname': 'Tracy', 'LName': 'Kim'}, {'Fname': 'Andy', 'LName': 'Schultz'}, {'Fname': 'Lisa', 'LName': 'Apap'}, {'Fname': 'Eric', 'LName': 'Tai'}, {'Fname': 'Derek', 'LName': 'Lee'}, {'Fname': 'Charles', 'LName': 'Norris'}, {'Fname': 'Susan', 'LName': 'Lee'}, {'Fname': 'Mark', 'LName': 'Schwartz'}, {'Fname': 'Arthur', 'LName': 'Pang'}, {'Fname': 'George', 'LName': 'Andreou'}, {'Fname': 'Michael', 'LName': 'Woods'}, {'Fname': 'Stacy', 'LName': 'Prater'}, {'Fname': 'Mark', 'LName': 'Goldman'}, {'Fname': 'Eric', 'LName': 'Pang'}, {'Fname': 'Paul', 'LName': 'Brody'}, {'Fname': 'Jun', 'LName': 'Han'}, {'Fname': 'William', 'LName': 'Simms'}, {'Fname': 'Eric', 'LName': 'Epp'}]