大連理工軟件學(xué)院_數(shù)據(jù)庫(kù)第四次上機(jī)答案

這次還是之前的數(shù)據(jù)庫(kù)，以下5道題對(duì)應(yīng)教材第3章結(jié)尾部份

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. 

//有些時(shí)候你需要修改數(shù)據(jù)庫(kù)

第1題：

Insert each student as an instructor of department ‘拳腳學(xué)院’, with salary=40000 

插入操作，沒甚么好說(shuō)的，注意插之前判斷1下教師表里是不是已存在這個(gè)人了

insert into instructor select S.ID, S.name, 拳腳學(xué)院, 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個(gè)問插入的數(shù)據(jù)

delete from instructor where ID in ( select ID from student ) and dept_name = 拳腳學(xué)院 and salary = 40000;

第3題：

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

將每一個(gè)講師的工資更新為：他所教section數(shù) * 10000

update instructor set salary = 10000 * ( select COUNT(*) from teaches where teaches.ID = instructor.ID )
直接履行代碼，會(huì)產(chǎn)生毛病：“UPDATE語(yǔ)句與***束縛沖突”，緣由是講師表里對(duì)salary屬性設(shè)置了CHECK束縛，必須是numeric(8, 2)，10000多是默許int型其實(shí)不符合規(guī)范，查閱微軟MSDN提供的官方說(shuō)明：

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

我們可以取得解決這1問題的方法，以下：

右鍵數(shù)據(jù)庫(kù)設(shè)計(jì)中產(chǎn)生CHECK沖突的列，選擇CHECK束縛

將下圖所示項(xiàng)設(shè)為“否”

再次履行代碼，操作成功！

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

找出那些掛科了，并且補(bǔ)考也沒過或還沒參加補(bǔ)考的人，和他們各自掛了幾科。

統(tǒng)計(jì)出通過課程表，那些得了F并且不在通過課程表里的，這些就是補(bǔ)考依然沒過或還沒補(bǔ)考的。最后再COUNT統(tǒng)計(jì)數(shù)目便可。

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 未通過科目數(shù) 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門課都沒有選的學(xué)生，和1門課都不教的講師，顯示在同1個(gè)表里。

各自找出來(lái)后，使用集合并運(yùn)算“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)

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)