1.找到没有过不及格记录的课程，显示课程名，开过的班数（要求五种方法）
--1 select title,( select count(*) from section S group by course_id having S.course_id = course.course_id ) from course where course_id not in ( select course_id from takes where grade = 'F' ) --2 select title,( select count(*) from section S group by course_id having S.course_id = course.course_id ) from course where course_id in ( select course_id from course except select course_id from takes where grade = 'F' ) --3 select title,( select count(*) from section S group by course_id having S.course_id = course.course_id ) from course where not exists ( select course_id from takes where grade = 'F' and course.course_id = takes.course_id ) --4 select distinct title,( select count(*) from section S group by course_id having S.course_id = course.course_id ) from course left join takes on takes.course_id=course.course_id where course.course_id not in ( select course_id from takes t where t.grade = 'F' ) --5 select distinct title,( select count(*) from section S group by course_id having S.course_id = course.course_id ) from course,takes where course.course_id not in ( select course_id from takes t where t.grade = 'F' )结果：
金钟罩 3 铁布衫 1 金刚不坏体 1 礼敬如来 NULL 千里横行 1 棒打狗头 1 天下无狗 1 八卦掌 1 仙人指路 1 天外飞仙 1 初级轻功 4 步步生花 2 云体风身 1 初级制毒 4 化骨绵掌 1 天地同寿 1 初级掌法 3 推心掌 1 无中生有 NULL2.教学班成绩分布（A-也算A）
with a as ( select course_id,year,semester,sec_id, case grade when 'A' then 1 when 'A-' then 1 end A, case grade when 'B' then 1 when 'B-' then 1 end B, case grade when 'C' then 1 when 'C-' then 1 end C, case grade when 'D' then 1 when 'D-' then 1 end D, case grade when 'F' then 1 end F, case when grade is null then 1 end nograde from takes ) select course_id,year,semester,sec_id,isnull(sum(A),0) A,isnull(sum(B),0) B,isnull(sum(C),0) C,isnull(sum(D),0) D,isnull(sum(F),0) F,isnull(sum(nograde),0) nograde from a group by course_id,year,semester,sec_id;结果：
fy1 2016 Summer 1 3 0 0 0 1 0 fy1 2016 Summer 2 2 0 0 0 0 0 fy1 2017 Summer 1 3 0 0 0 0 0 fy2 2016 Fall 1 3 3 0 0 0 0 fy3 2017 Summer 1 2 0 0 0 0 0 gf1 2016 Summer 1 2 0 1 0 1 1 gf1 2016 Summer 2 0 1 1 0 0 0 gf2 2016 Fall 1 1 0 0 1 1 0 gf2 2016 Fall 2 0 1 1 0 0 0 gf3 2017 Spring 1 1 0 0 0 0 0 gf4 2017 Summer 1 1 0 0 0 0 0 gf5 2017 Fall 1 1 0 0 0 0 0 ng1 2016 Spring 1 0 0 0 0 1 0 ng1 2016 Summer 1 0 0 0 1 1 0 ng1 2016 Summer 2 0 0 0 0 0 0 ng1 2016 Summer 3 1 0 0 0 0 0 ng1 2017 Spring 1 2 0 1 0 0 0 ng2 2016 Fall 1 2 0 0 0 0 0 ng3 2016 Fall 1 2 0 0 0 0 0 ng4 2017 Summer 1 0 1 0 0 0 0 ng5 2017 Fall 1 0 0 0 0 1 0 qg1 2016 Summer 1 3 1 0 1 0 0 qg1 2016 Summer 2 1 0 0 0 0 0 qg1 2017 Summer 1 1 0 0 1 0 0 qg2 2016 Fall 1 2 0 0 0 0 0 qg2 2016 Fall 2 0 1 0 0 0 0 qg3 2016 Spring 1 0 0 0 0 1 0 qg3 2017 Spring 1 1 1 0 0 0 0 qg4 2017 Summer 1 1 0 0 0 0 0 qg5 2016 Fall 1 0 0 0 0 1 0 qg5 2017 Fall 1 0 0 1 0 0 0 zd1 2016 Summer 1 3 0 0 0 0 0 zd1 2016 Summer 2 3 0 0 0 0 0 zd2 2016 Fall 1 2 1 0 0 0 0 zd3 2017 Spring 1 0 0 0 0 2 0 zd4 2017 Summer 1 1 0 0 1 1 0 zd5 2017 Fall 1 0 0 0 0 0 1 zf1 2016 Summer 1 3 0 0 0 0 0 zf1 2017 Summer 1 0 0 0 1 0 0 zf2 2016 Fall 1 1 2 0 0 0 0 zf3 2016 Winter 1 0 0 0 1 2 0 zf3 2017 Winter 1 0 0 0 0 1 0 zf4 2017 Spring 1 0 0 0 0 1 03.每个课程每个学期的每周总课时数
with a as ( select time_slot_id,sum(((end_hr-start_hr)*60 + (end_min-start_min))/50) credit from time_slot group by time_slot_id ) select title,year,semester,count(sec_id) count_of_course ,sum(a.credit) Total_Credit_hours from section join course on course.course_id = section.course_id join a on a.time_slot_id = section.time_slot_id group by section.course_id,year,semester,title结果：
初级防御 2016 Summer 2 6.000000 初级防御 2017 Summer 1 3.000000 金钟罩 2016 Fall 1 3.000000 金钟罩 2016 Winter 1 3.000000 金钟罩 2017 Spring 1 3.000000 铁布衫 2017 Summer 1 3.000000 金刚不坏体 2017 Fall 1 3.600000 初级棍法 2016 Summer 2 12.000000 初级棍法 2017 Summer 1 3.000000 冲锋斩将 2016 Fall 2 6.000000 千里横行 2017 Spring 1 3.000000 棒打狗头 2017 Summer 1 3.000000 天下无狗 2017 Fall 1 3.000000 初级内功 2016 Spring 1 3.000000 初级内功 2016 Summer 3 9.000000 初级内功 2017 Spring 1 3.000000 八卦掌 2016 Fall 1 3.000000 仙人指路 2016 Fall 1 3.000000 天外飞仙 2017 Summer 1 3.000000 真武七截阵 2017 Fall 1 3.000000 初级轻功 2016 Spring 1 3.000000 初级轻功 2016 Summer 2 6.000000 初级轻功 2017 Summer 1 3.000000 步步生花 2016 Fall 2 6.000000 江行初雪 2016 Spring 1 3.000000 江行初雪 2017 Spring 1 3.000000 云体风身 2017 Summer 1 3.000000 凌波微步 2016 Fall 1 3.000000 凌波微步 2017 Fall 1 3.000000 初级制毒 2016 Summer 2 12.600000 初级制毒 2017 Summer 2 12.600000 化骨绵掌 2016 Fall 1 3.600000 含笑半步颠 2017 Spring 1 3.600000 一日丧命散 2017 Summer 1 3.600000 天地同寿 2017 Fall 1 3.000000 初级掌法 2016 Spring 1 3.000000 初级掌法 2016 Summer 1 3.000000 初级掌法 2017 Summer 1 3.000000 推心掌 2016 Fall 1 3.000000 葵花点穴手 2016 Winter 1 3.000000 葵花点穴手 2017 Winter 1 3.000000 斗转星移 2017 Spring 1 3.0000004.找到既是学生的导师，又教学生课的老师和学生组合
select distinct student.name,instructor.name from course join takes on takes.course_id=course.course_id join student on student.ID=takes.ID join advisor on advisor.s_ID=student.ID join teaches on teaches.course_id=takes.course_id and teaches.sec_id=takes.sec_id and teaches.semester=takes.semester and teaches.year=takes.year join instructor on instructor.ID=teaches.ID where advisor.i_ID = instructor.ID结果：
出尘子 施全 李春来 洪通 李秋水 秦观 容子矩 林岩 无崖子 康广陵 吴光胜 俞远山 辛双清 吕师襄 郁光标 张中行 止澈 慧芳 止渊 玄寂注意：有的课好几个老师教，学生可能没选他导师的那一节
