How many students live in each dorm?
SELECT count ( * )  ,  T3.dorm_name FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid   =   T2.stuid JOIN dorm AS T3 ON T3.dormid   =   T2.dormid GROUP BY T3.dorm_name
counting students per dorm refers to COUNT(*); each dorm refers to GROUP BY dorm_name; students live in dorms refers to JOIN between student and dorm tables on dorm_id;