
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 are the first and last names of all students who are living in a dorm with a TV Lounge?
SELECT T1.fname ,  T1.lname FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid WHERE T2.dormid IN (SELECT T3.dormid FROM has_amenity AS T3 JOIN dorm_amenity AS T4 ON T3.amenid  =  T4.amenid WHERE T4.amenity_name  =  'TV Lounge')
[{'Fname': 'Linda', 'LName': 'Smith'}, {'Fname': 'Dinesh', 'LName': 'Kumar'}, {'Fname': 'Paul', 'LName': 'Gompers'}, {'Fname': 'Lisa', 'LName': 'Apap'}, {'Fname': 'Jandy', 'LName': 'Nelson'}, {'Fname': 'Eric', 'LName': 'Tai'}, {'Fname': 'Derek', 'LName': 'Lee'}, {'Fname': 'David', 'LName': 'Adams'}, {'Fname': 'Steven', 'LName': 'Davis'}, {'Fname': 'Susan', 'LName': 'Lee'}, {'Fname': 'Mark', 'LName': 'Schwartz'}, {'Fname': 'Bruce', 'LName': 'Wilson'}, {'Fname': 'Michael', 'LName': 'Leighton'}, {'Fname': 'Ian', 'LName': 'Thornton'}, {'Fname': 'George', 'LName': 'Andreou'}, {'Fname': 'David', 'LName': 'Shieber'}, {'Fname': 'Mark', 'LName': 'Goldman'}, {'Fname': 'Paul', 'LName': 'Brody'}, {'Fname': 'Jun', 'LName': 'Han'}, {'Fname': 'Lisa', 'LName': 'Cheng'}, {'Fname': 'Eric', 'LName': 'Brown'}, {'Fname': 'Eric', 'LName': 'Epp'}]