数据库第二次上机SSDUT

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
无中生有	NULL

2.教学班成绩分布（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	0

3.每个课程每个学期的每周总课时数

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.000000

4.找到既是学生的导师，又教学生课的老师和学生组合

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

结果：

出尘子	施全
李春来	洪通
李秋水	秦观
容子矩	林岩
无崖子	康广陵
吴光胜	俞远山
辛双清	吕师襄
郁光标	张中行
止澈	慧芳
止渊	玄寂

注意：有的课好几个老师教，学生可能没选他导师的那一节