How many detentions are there?
select count(*) from detention
What about detentions with type code "AFTER"?
select count(*) from detention where detention_type_code = "AFTER"
How many does each teacher have?
select t1.teacher_id, t2.last_name, count(*) from detention as t1 join teachers as t2 on t1.teacher_id = t2.teacher_id where t1.detention_type_code = "AFTER" group by t1.teacher_id
Give me the id and last name of the teacher with the largest number.
select t1.teacher_id, t2.last_name from detention as t1 join teachers as t2 on t1.teacher_id = t2.teacher_id where t1.detention_type_code = "AFTER" group by t1.teacher_id order by count(*) desc limit 1