大连理工数据库第三次上机

1.更新所有学生总学分tot_cred

update student
set tot_cred = (
		with a as(
			select takes.course_id,course.credits,takes.grade,takes.ID
			from course join takes on course.course_id = takes.course_id
			where grade != 'F' and grade is not null
		)
		select sum(credits)
		from a
		group by ID
		)

查询部分结果：

4	11795
11	11990
4	13779
8	15167
4	16352
18	16597
4	18553
8	20852
19	21065
14	25971
4	28763
4	28985
4	30131
7	32026
7	32433
7	33156
4	34229
10	34813
10	35627
7	39840
3	40006
4	42755
7	45876
7	47413
18	47957
10	52737
4	53418
10	57182
8	57662
9	60694
9	64840
6	66471
5	68183
5	69279

2.通过了所在学院所有课的学生

select *
from student
where not exists(
		select course_id
		from course
		where course.dept_name = student.dept_name
		except
		select course_id
		from takes
		where takes.ID = student.ID and grade != 'F' and grade is not null
)

结果：

16597	无崖子	逍遥	102
47957	萧峰	丐帮	26

3.学号排序6~10的学生，两种方法

--3.1
select ID,name
from (
	select top 5 ID,name
	from(
		select top 10 id,name
		from student
		order by ID
	) T(ID,name)
	order by ID desc
)T2(ID,name)
order by ID

--3.2
with a as (select rank() over (order by ID) as rank_id,ID,name
from student
)
select ID,name
from a
where rank_id >= 6 and rank_id <=10

结果：

16597	无崖子
18553	石清露
18909	王傀儡
20852	张万泉
20861	崔百泉

4.仍有未通过科目的学生

select student.ID,name,count(course_id) 未通过数
from(
	select ID,course_id
	from takes
	where grade = 'F'
	except
	select ID,course_id
	from takes
	where grade != 'F'
) T(ID,course_id) join student on T.ID = student.ID
group by student.name,student.ID;

结果:

16597	无崖子	1
20852	张万泉	1
42755	李秋来	1
52737	张三丰	2
57662	左子穆	1
66471	出尘子	1
68183	阿紫	1
69279	阿绿	1

5.多次选一个课的学生

select ID,(select name 
			from student
			where student.ID = takes.ID) 名字,
			(select title
			from course
			where course.course_id = takes.course_id) 课程名,count(course_id) 选课次数,
			(	
			select distinct count(course_id)
			from takes t
			group by ID,course_id,grade
			having grade = 'F'
			and takes.ID = t.ID
			and takes.course_id = t.course_id
			) 未通过次数
from takes
group by ID,course_id
having count(course_id)>1

结果：

11990	李秋水	江行初雪	2	1
16597	无崖子	凌波微步	2	1
20852	张万泉	初级内功	3	1
25971	吴光胜	初级内功	2	1
34813	本果	初级防御	2	1
52737	张三丰	葵花点穴手	2	2