大连理工软件学院_数据库第四次上机答案
这次还是之前的数据库，以下5道题对应教材第3章结尾部份
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.
//有些时候你需要修改数据库
第1题：
Insert each student as an instructor of department ‘拳脚学院’, with salary=40000
插入操作，没甚么好说的，注意插之前判断1下教师表里是不是已存在这个人了
insert into instructor
select S.ID, S.name, 拳脚学院, 40000
from student S
where S.ID not in ( select ID from instructor );
第2题：
Now delete all the newly added "instructors" above (note: already existing instructors who happened to have salary=40000 should not get deleted)
删掉第1个问插入的数据
delete from instructor
where ID in ( select ID from student ) and
dept_name = 拳脚学院 and
salary = 40000;
第3题：
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
我们可以取得解决这1问题的方法，以下：
右键数据库设计中产生CHECK冲突的列，选择CHECK束缚
将下图所示项设为“否”
再次履行代码，操作成功！
第4题：
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;
第5题：
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’.
找出那些1门课都没有选的学生，和1门课都不教的讲师，显示在同1个表里。
各自找出来后，使用集合并运算“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)