Test solutions :

Test solutions :

Tables :
teachers(teacher_id,teacher_name)
courses(course_id,teacher_id,num_of_students)
students(roll_no,stu_name)
stu_courses(roll_no,course_id)

(a) Teachers not teaching any courses :
Select teacher_name from teachers where teacher_id not in (select distinct(teacher_id) from courses);
OR
Select teacher_name from teachers left join courses ON(teachers.teacher_id = courses.teacher_id) where course_id is NULL;
 
(b) Students not taking any courses :
Similar to part (a).

(c) Teachers taking maximum courses :

select a.teacher_name as 'Teacher', a.teacher_id as 'Teacher Id', count(b.course_id) as 'Num of Courses' 
from teachers a 
inner join courses b on a.teacher_id = b.teacher_id 
group by b.teacher_id 
order by count(b.course_id) desc 
limit 1
OR
mysql> select teachers.teacher_name,tmp1.teacher_id,tmp1.cnt from (select max(tm
p.cnt) as tmpMax from (select teacher_id,count(teacher_id) as cnt from courses g
roup by teacher_id) as tmp) as tmp2,(select teacher_id,count(teacher_id) as cnt
from courses group by teacher_id) as tmp1,teachers where tmp1.cnt = tmp2.tmpMax
and teachers.teacher_id = tmp1.teacher_id;

(d) Students taking maximum courses :
similar to part (c)

(e) names of teachers teaching maximum number of students :

mysql> select max(tmp1.sum1) from (select teacher_id,sum(num_students) as sum1 f
rom courses group by teacher_id) as tmp1;