How many students are living in the dorms?
select count ( * ) from student
Yes.
select distinct dormid from has_amenity
How many dorms do not have a TV lounge?
select t3.dormid from has_amenity as t3 join dorm_amenity as t4 on t3.amenid = t4.amenid where t4.amenity_name = 'TV Lounge'
How many students are in those four dorms?
select count ( * ) from student as t1 join lives_in as t2 on t1.stuid = t2.stuid where t2.dormid not 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' )
What are the first names of those nine students?
select t1.fname from student as t1 join lives_in as t2 on t1.stuid = t2.stuid where t2.dormid not 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' )
What are their ages?
select t1.age from student as t1 join lives_in as t2 on t1.stuid = t2.stuid where t2.dormid not 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' )