
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
*/
How many dorms have amenities?
SELECT count(DISTINCT dormid) FROM has_amenity
[{'count(DISTINCT dormid)': 7}]