School

/* Considering any course has just one author */

select course.name as course_name, 
       author.name as author, 
       count(distinct section.id) as section_cnt, 
       count(*) as class_cnt
from course
left join author on author.id = course.author_id
left join section on section.course_id = course.id
left join class on class.section_id = section.id
where subject_id = 1
GROUP BY course.id
order by course.name;

select class.name as class_name, 
       section.name as section_name
from class
inner join section on class.section_id = section.id
where course_id = 1
order by section.name;

select subject.name as subject_name,
       course.name as course_name,
       section.name as section_name,
       class.name as class_name,
       task.name as task_name
from task
inner join class on class.id = task.class_id
inner join section on section.id = class.section_id
inner join course on course.id = section.course_id
inner join subject on subject.id = course.subject_id
where course_id = 1;