大連理工軟件學院_數據庫第四次上機答案

這次還是之前的數據庫，以下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)

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈