what are the names of all dorms?
select dorm_name from dorm
what are all of the different amenities?
select amenity_name from dorm_amenity
Find the names of dorms that have a TV Lounge.
select t1.dorm_name from dorm as t1 join has_amenity as t2 on t1.dormid = t2.dormid join dorm_amenity as t3 on t2.amenid = t3.amenid where t3.amenity_name = 'TV Lounge'
how about those that also have a Study Room?
select t1.dorm_name from dorm as t1 join has_amenity as t2 on t1.dormid = t2.dormid join dorm_amenity as t3 on t2.amenid = t3.amenid where t3.amenity_name = 'TV Lounge' intersect select t1.dorm_name from dorm as t1 join has_amenity as t2 on t1.dormid = t2.dormid join dorm_amenity as t3 on t2.amenid = t3.amenid where t3.amenity_name = 'Study Room'