
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 first name of students who are living in the dorm that has most number of amenities.
SELECT T1.fname FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid WHERE T2.dormid IN (SELECT T2.dormid FROM dorm AS T3 JOIN has_amenity AS T4 ON T3.dormid  =  T4.dormid JOIN dorm_amenity AS T5 ON T4.amenid  =  T5.amenid GROUP BY T3.dormid ORDER BY count(*) DESC LIMIT 1)
[{'Fname': 'Linda'}, {'Fname': 'Tracy'}, {'Fname': 'Shiela'}, {'Fname': 'Dinesh'}, {'Fname': 'Paul'}, {'Fname': 'Lisa'}, {'Fname': 'Jandy'}, {'Fname': 'Eric'}, {'Fname': 'Derek'}, {'Fname': 'David'}, {'Fname': 'Steven'}, {'Fname': 'Charles'}, {'Fname': 'Susan'}, {'Fname': 'Mark'}, {'Fname': 'Bruce'}, {'Fname': 'Michael'}, {'Fname': 'Ian'}, {'Fname': 'George'}, {'Fname': 'Michael'}, {'Fname': 'David'}, {'Fname': 'Stacy'}, {'Fname': 'Mark'}, {'Fname': 'Paul'}, {'Fname': 'Eric'}, {'Fname': 'Jun'}, {'Fname': 'Lisa'}, {'Fname': 'Sarah'}, {'Fname': 'Eric'}, {'Fname': 'William'}, {'Fname': 'Eric'}, {'Fname': 'Sarah'}]