Oh, sorry. I meant to ask what dorm does the student with the last name of Smith live in?
select t1.dorm_name from dorm as t1 join lives_in as t4 on t4.dormid = t1.dormid join student as t5 on t5.stuid = t4.stuid where t5.lname = 'Smith'
What are the amenities of those dorms?
select t3.amenity_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 join lives_in as t4 on t4.dormid = t1.dormid join student as t5 on t5.stuid = t4.stuid where t5.lname = 'Smith'
What are the amenities of the dorm that the student with the last name of Jones lives in?
select t3.amenity_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 join lives_in as t4 on t4.dormid = t1.dormid join student as t5 on t5.stuid = t4.stuid where t5.lname = 'Jones'
What are the amenities of the dorm that the oldest student lives in?
select t3.amenity_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 join lives_in as t4 on t4.dormid = t1.dormid join student as t5 on t5.stuid = t4.stuid where t5.age = ( select max ( age ) from student )
What are the amenities of the dorm that has the most female students living in it?
select t3.amenity_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 join lives_in as t4 on t4.dormid = t1.dormid join student as t5 on t5.stuid = t4.stuid where t1.dormid = ( select t1.dormid from dorm as t1 join lives_in as t4 on t1.dormid = t4.dormid join student as t5 on t4.stuid = t5.stuid where t5.sex = "F" group by t1.dormid order by count ( * ) desc limit 1 )
What are the amenities of the dorm that has the smallest student capacity?
select t3.amenity_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 t1.student_capacity = ( select min ( student_capacity ) from dorm )