【Advanced SQL-1 】大连理工软件学院_数据库第四次上机答案

这次还是之前的数据库，以下五道题对应教材第三章结尾部分

Using the university schema that you have write the following queries. In some cases you might need to insert extra data to show the effect of a particular feature. 

//有些时候你需要修改数据库

第一题：

Insert each student as an instructor of department ‘拳脚学院’, with salary=40000 

插入操作，没什么好说的，注意插之前判断一下教师表里是否已经存在这个人了

insert into instructor
select S.ID, S.name, '拳脚学院', 40000
from student S
where S.ID not in ( select ID from instructor );

第二题：

Now delete all the newly added "instructors" above (note: already existing instructors who happened to have salary=40000 should not get deleted) 

删掉第一个问插入的数据

delete from instructor
where ID in ( select ID from student ) and
	dept_name = '拳脚学院' and
	salary = 40000;

第三题：

Update the salary of each instructor to 10000 times the number of course sections they have taught. 

将每个讲师的工资更新为：他所教section数 * 10000

update instructor
set salary = 10000 * (
		select COUNT(*)
		from teaches
		where teaches.ID = instructor.ID
	)

直接执行代码，会发生错误：“UPDATE语句与***约束冲突”，原因是讲师表里对salary属性设置了CHECK约束，必须是numeric(8, 2)，10000可能是默认int型并不符合规范，查阅微软MSDN提供的官方说明：

https://msdn.microsoft.com/zh-cn/library/aa292216(VS.71).aspx

我们可以获得解决这一问题的方法，如下：

右键数据库设计中发生CHECK冲突的列，选择CHECK约束

将下图所示项设为“否”

再次执行代码，操作成功！

第四题：

The university rules allow an F grade to be overridden by any pass grade (for example, A). Now, lists students who have fail grades that have not been overridden. For each student as such, information displayed (in one row) should involve: 

·Identifier of student 

·Name of student 

·Count of F grades that have not been overridden. 

找出那些挂科了，并且补考也没过或者还没参加补考的人，以及他们各自挂了几科。

统计出通过课程表，那些得了F并且不在通过课程表里的，这些就是补考仍然没过或者还没补考的。最后再COUNT统计数目即可。

with pass(ID, course_id) as
(
	select distinct S1.ID, T1.course_id
	from student S1, takes T1
	where S1.ID = T1.ID and
		T1.grade != 'F'
),
not_pass(ID, name, course_id) as
(
	select S.ID, S.name, T.course_id
	from student S, takes T
	where S.ID = T.ID and
		T.grade = 'F' and
		S.ID not in (
			select pass.ID from pass
			where T.course_id = pass.course_id
		)
)
select ID, name, COUNT(*) as '未通过科目数'
from not_pass
group by ID, name;

第五题：

In one result, list the instructors who have never taught any courses and the students who have never registered for any courses. For each person, information displayed (in one row) should involve: 

·Id of the person 

·Name of the person 

·Role of the person. The value of role should be ‘student’ or ‘instructor’. 

找出那些一门课都没有选的学生，和一门课都不教的讲师，显示在同一个表里。

各自找出来后，使用集合并运算“union”即可。

select S.ID, S.name, 'student' as 'Role'
from student S
where S.ID not in ( select ID from takes)
union
select I.ID, I.name, 'instructor' as 'Role'
from instructor I
where I.ID not in ( select ID from teaches)