數(shù)據(jù)庫(kù)系統(tǒng)原理第五章作業(yè)及其測(cè)驗(yàn)講評(píng)教材

第五章習(xí)題-5設(shè)職工-社團(tuán)數(shù)據(jù)庫(kù)有3個(gè)基本表：職工（職工號(hào)，姓名，年齡，性別）；社會(huì)團(tuán)體（編號(hào)，名稱(chēng)，負(fù)責(zé)人，活動(dòng)地點(diǎn)）；參加（職工號(hào)，編號(hào)，參加日期）。1.定義職工表、社會(huì)團(tuán)體表和參加表，并說(shuō)明其主碼和參照關(guān)系createtable職工(職工號(hào)char(5)primarykey,

姓名char(8)notnull,

年齡smallint,

性別char(2), constraintC1check(性別in('男','女')));createtable社會(huì)團(tuán)體(編號(hào)char(6)primarykey,

名稱(chēng)char(20)notnull,

負(fù)責(zé)人char(5),

活動(dòng)地點(diǎn)char(30), constraintC2foreignkey(負(fù)責(zé)人)references職工(職工號(hào)));createtable參加(職工號(hào)char(5),

編號(hào)char(6),

參加日期datetime, constraintC3primarykey(職工號(hào),編號(hào)), constraintC4foreignkey(職工號(hào))references職工(職工號(hào)), constraintC5foreignkey(編號(hào))references社會(huì)團(tuán)體(編號(hào)));…uniqueprimarykey,createtable職工(職工號(hào)char(5)notnullunique,

姓名char(8)notnull,

年齡smallint,

性別char(2), constraintCxprimarykey(職工號(hào)), constraintC1check(性別in('男','女')));createtable參加(職工號(hào)char(5)primarykey,

編號(hào)char(6)primarykey, constraintC4foreignkey(職工號(hào))references職工(職工號(hào)), constraintC5foreignkey(編號(hào))references社會(huì)團(tuán)體(編號(hào)));外碼的數(shù)據(jù)結(jié)構(gòu)要與主碼的相同！這道題出現(xiàn)的問(wèn)題有：缺少逗號(hào)、分號(hào)、引號(hào)，混淆主碼與唯一約束，忽略表之間數(shù)據(jù)結(jié)構(gòu)的一致。。。2.建立視圖社團(tuán)負(fù)責(zé)人(編號(hào),名稱(chēng),負(fù)責(zé)人職工號(hào),負(fù)責(zé)人姓名,負(fù)責(zé)人性別)createview社團(tuán)負(fù)責(zé)人(編號(hào),名稱(chēng),負(fù)責(zé)人職工號(hào),負(fù)責(zé)人姓名,負(fù)責(zé)人性別) asselect社會(huì)團(tuán)體.編號(hào),社會(huì)團(tuán)體.名稱(chēng),職工.職工號(hào),職工.姓名, 職工.性別

from職工,社會(huì)團(tuán)體

where職工.職工號(hào)=社會(huì)團(tuán)體.負(fù)責(zé)人;參加人情況(職工號(hào),姓名,社團(tuán)編號(hào),社團(tuán)名稱(chēng),參加日期)createview參加人情況(職工號(hào),姓名,社團(tuán)編號(hào),社團(tuán)名稱(chēng),參加日期) asselect參加.職工號(hào),姓名,參加.編號(hào),名稱(chēng),參加日期

from職工,社會(huì)團(tuán)體,參加

where職工.職工號(hào)=參加.職工號(hào)and社會(huì)團(tuán)體.編號(hào)=參加.編號(hào);可寫(xiě)成：社會(huì)團(tuán)體.負(fù)責(zé)人3.查找參加唱歌隊(duì)或籃球隊(duì)的職工號(hào)和姓名selectdistinct職工.職工號(hào),姓名from職工,社會(huì)團(tuán)體,參加where職工.職工號(hào)=參加.職工號(hào)and參加.編號(hào)=社會(huì)團(tuán)體.編號(hào) and(名稱(chēng)='籃球隊(duì)'or名稱(chēng)='唱歌隊(duì)');selectdistinct職工.職工號(hào),姓名from職工,社會(huì)團(tuán)體,參加where職工.職工號(hào)=參加.職工號(hào)and參加.編號(hào)=社會(huì)團(tuán)體.編號(hào) and名稱(chēng)in('籃球隊(duì)','唱歌隊(duì)');名稱(chēng)='籃球隊(duì)'or名稱(chēng)='唱歌隊(duì)';selectdistinct職工.職工號(hào),姓名from職工,社會(huì)團(tuán)體,參加where職工.職工號(hào)=參加.職工號(hào)and參加.編號(hào)=社會(huì)團(tuán)體.編號(hào) and

名稱(chēng)=('籃球隊(duì)'or'唱歌隊(duì)');這樣會(huì)選擇出只要滿(mǎn)足了職工.職工號(hào)=參加.職工號(hào)and參加.編號(hào)=社會(huì)團(tuán)體.編號(hào)and名稱(chēng)=‘籃球隊(duì)’

或者名稱(chēng)=‘唱歌隊(duì)’的結(jié)果。語(yǔ)法錯(cuò)誤??！4.查找沒(méi)有參加任何社會(huì)團(tuán)體的職工情況select*from職工wherenotexists(select* from參加

where職工.職工號(hào)=職工號(hào));select*from職工where職工號(hào)notin(select職工號(hào)

from參加);select職工.*from職工,參加where職工.職工號(hào)=參加.職工號(hào)and編號(hào)isnull;編號(hào)是參加表的主碼，不可能為空，所以這這個(gè)查詢(xún)的結(jié)果肯定為空。5.查找參加了全部社會(huì)團(tuán)體的職工情況select*from職工wherenotexists(select* from社會(huì)團(tuán)體

wherenotexists(select* from參加

where職工.職工號(hào)=參加.職工號(hào)and 社會(huì)團(tuán)體.編號(hào)=參加.編號(hào)));6.查找參加了職工號(hào)為“1001”的職工所參加的全部社會(huì)團(tuán)體的職工號(hào)select職工號(hào)from職工wherenotexists(select* from參加參加1 where參加1.職工號(hào)=‘1001' andnotexists(select* from參加參加2 where職工.職工號(hào)=參加2.職工號(hào) and參加2.編號(hào)=參加1.編號(hào)));select職工號(hào)from參加where編號(hào)in(select編號(hào)

from參加

where職工號(hào)='1001');這個(gè)查詢(xún)所求的是：參加了職工號(hào)為“1001”的職工所參加團(tuán)體中的一個(gè)的職工號(hào)。select職工號(hào)from參加where編號(hào)=(select編號(hào)

from參加

where職工號(hào)='1001');這是一個(gè)集合，所以前面不能用等號(hào)，要用IN。7.求每個(gè)社團(tuán)的參加人數(shù)select編號(hào),count(職工號(hào))from參加groupby編號(hào);select名稱(chēng),count(職工號(hào))from社會(huì)團(tuán)體,參加where參加.編號(hào)=社會(huì)團(tuán)體.編號(hào)groupby名稱(chēng);社會(huì)團(tuán)體.編號(hào);服務(wù)器:消息8120，級(jí)別16，狀態(tài)1，行1列‘社會(huì)團(tuán)體.名稱(chēng)’在選擇列表中無(wú)效，因?yàn)樵摿屑炔话诰酆虾瘮?shù)中，也不包含在GROUPBY子句中。編號(hào)名稱(chēng)…00001A…00002B…00002C…00003D…00004D………...8.求參加人數(shù)最多的社會(huì)團(tuán)體的名稱(chēng)和參加人數(shù)select名稱(chēng),count(參加.職工號(hào))as參加人數(shù)from社會(huì)團(tuán)體,參加where參加.編號(hào)=社會(huì)團(tuán)體.編號(hào)groupby名稱(chēng)havingcount(*)>=all(selectcount(*) from參加,社會(huì)團(tuán)體

where參加.編號(hào)=社會(huì)團(tuán)體.編號(hào)

groupby名稱(chēng));groupby名稱(chēng)havingcount(職工號(hào))>=(allcount(職工號(hào)));count(職工號(hào))返回的只是一個(gè)數(shù)，不是一個(gè)集合，所以不能對(duì)其使用all函數(shù)，即語(yǔ)法上會(huì)出錯(cuò)。9.求參加人數(shù)超過(guò)100人的社會(huì)團(tuán)體的名稱(chēng)和負(fù)責(zé)人selectdistinct名稱(chēng),負(fù)責(zé)人from社會(huì)團(tuán)體where編號(hào)in(select編號(hào)

from參加

groupby編號(hào)havingcount(*)>=100);selectdistinct名稱(chēng),負(fù)責(zé)人,職工號(hào)from社會(huì)團(tuán)體,參加where社會(huì)團(tuán)體.編號(hào)=參加.編號(hào)andexists(select編號(hào)

from參加

groupby編號(hào)havingcount(*)>=100);因?yàn)橹挥写嬖谟幸粋€(gè)社會(huì)團(tuán)體的參加人數(shù)超過(guò)100，那exists(select編號(hào)

from參加

groupby編號(hào)havingcount(*)>=100的值一定是真，這樣所查的結(jié)果必定是所有社會(huì)團(tuán)體的名稱(chēng)及其負(fù)責(zé)人！?。〉谖逭铝?xí)題-6設(shè)工程-零件數(shù)據(jù)庫(kù)中有4個(gè)基本表：供應(yīng)商（供應(yīng)商代碼，姓名，所在城市，聯(lián)系電話(huà)）；工程（工程代碼，工程名，負(fù)責(zé)人，預(yù)算）；零件（零件代碼，零件名，規(guī)格，產(chǎn)地，顏色）；供應(yīng)零件（供應(yīng)商代碼，工程代碼，零件代碼，數(shù)量）第五章習(xí)題6：解答1）找出天津供應(yīng)商的姓名和電話(huà)Select姓名,聯(lián)系電話(huà)

From供應(yīng)商

Where所在城市=‘天津市’;2）查找預(yù)算在50000~100000元之間的工程信息，并將結(jié)果按預(yù)算降序排列Select*

From工程

Where預(yù)算Between50000and100000

Orderby預(yù)算DESC;第五章習(xí)題6：解答3）找出使用供應(yīng)商S1所供零件的工程號(hào)碼Select工程代碼

From供應(yīng)零件

Where供應(yīng)商代碼=‘S1’;4）找出工程項(xiàng)目J2使用的各種零件名稱(chēng)及其數(shù)量Select零件名,數(shù)量

From供應(yīng)零件,零件

Where供應(yīng)零件.零件代碼=零件.零件代碼

and工程代碼=‘J2’;第五章習(xí)題6：解答5）找出上海廠商供應(yīng)的所有零件號(hào)碼Select零件代碼

From供應(yīng)零件,供應(yīng)商

Where供應(yīng)零價(jià).供應(yīng)商代碼=供應(yīng)商.供應(yīng)商代碼

and供應(yīng)商.所在城市=‘上?！?6）找出使用上海產(chǎn)的零件的工程名稱(chēng)Select工程名稱(chēng)

From供應(yīng)零件,工程,零件

Where供應(yīng)零件.工程代碼=工程商.工程代碼

and供應(yīng)零件.零件代碼=零件.零件代碼

and產(chǎn)地=‘上?！?第五章習(xí)題6：解答7）找出沒(méi)有使用天津產(chǎn)零件的工程號(hào)碼Select工程代碼from工程

Wherenotexists

(Select*

From供應(yīng)零件,零件

Where供應(yīng)零件.零件代碼=零件.零件代碼

and供應(yīng)零件.工程代碼=工程.工程代碼

and產(chǎn)地=‘天津’);8）把全部紅色零件的顏色改成藍(lán)色Update零件Set顏色=‘藍(lán)色’Where顏色=‘紅色’;第五章習(xí)題6：解答9）將由供應(yīng)商S5供給工程代碼為J4的零件P6改為由S3供應(yīng)，并作其他必要的修改Update供應(yīng)零件

Set供應(yīng)商代碼=‘S3’

Where供應(yīng)商代碼=‘S5’

and工程代碼=‘J4’

and零件代碼=‘P6’;10）從供應(yīng)商關(guān)系中刪除S2的紀(jì)錄，并從供應(yīng)零件關(guān)系中刪除相應(yīng)的紀(jì)錄DeleteFrom供應(yīng)零件Where供應(yīng)商代碼=‘S2’;DeleteFrom供應(yīng)商Where供應(yīng)商代碼=‘S2’;關(guān)鍵1:

供應(yīng)商(供應(yīng)商代碼，姓名，所在城市，聯(lián)系電話(huà))；工程（工程代碼，工程名，負(fù)責(zé)人，預(yù)算）零件（零件代碼，零件名，規(guī)格，產(chǎn)地，顏色）供應(yīng)零件（供應(yīng)商代碼，工程代碼，零件代碼，數(shù)量）4.找出工程項(xiàng)目J2使用的各種零件名稱(chēng)及其數(shù)量Select零件名稱(chēng),數(shù)量From零件,供應(yīng)零件Where零件.零件代碼=供應(yīng)零件.零件代碼and工程代碼=‘J2’Select零件名稱(chēng),sum(數(shù)量)From零件,供應(yīng)零件Where零件.零件代碼=供應(yīng)零件.零件代碼and工程代碼=‘J2’Groupby零件名稱(chēng)

不同供應(yīng)商提供相同的零件關(guān)鍵2:

供應(yīng)商(供應(yīng)商代碼，姓名，所在城市，聯(lián)系電話(huà))；工程（工程代碼，工程名，負(fù)責(zé)人，預(yù)算）零件（零件代碼，零件名，規(guī)格，產(chǎn)地，顏色）供應(yīng)零件（供應(yīng)商代碼，工程代碼，零件代碼，數(shù)量）7.找出沒(méi)有使用天津產(chǎn)零件的工程號(hào)碼Select工程號(hào)碼From工程Where工程號(hào)碼notexists(select工程號(hào)碼from供應(yīng)零件,零件Where零件.零件代碼=供應(yīng)零件.零件代碼And供應(yīng)零件.工程代碼=工程.工程代碼And產(chǎn)地=‘天津’)如果有些零件的產(chǎn)地名稱(chēng)是‘天津市’，怎么辦？第五章習(xí)題8:

S(S#，SN，SEX，AGE,DEPT)；C（C#，CN，TEARCHER）SC(S#，C#，GRADE）1.找出選修了”程軍”老師教的所有課程的學(xué)生姓名SelectSNFromSWherenotexists(select*fromCwhereTEARCHER=“程軍”Wherenotexists(select*fromSCwhereS.S#=SC.S#and

C.C#=SC.C#))第五章習(xí)題8:

S(S#，SN，SEX，AGE,DEPT)；C（C#，CN，TEARCHER）SC(S#，C#，GRADE）6.檢索選修了四門(mén)以上課程的學(xué)生總成績(jī)(不統(tǒng)計(jì)不及格的課程)，并要求按照成績(jī)降序排列selectS#,count(*)as課程門(mén)數(shù),sum(Grade)as總成績(jī)fromscwheregrade>=60groupbyS#havingcount(*)>=4orderby總成績(jī)desc第五章習(xí)題8:

S(S#，SN，SEX，AGE,DEPT)；C（C#，CN，TEARCHER）SC(S#，C#，GRADE）8.檢索不學(xué)C6課程的男學(xué)生的學(xué)號(hào)和姓名selectS#,SN,SEXfromstudentwhereSEX='男'andS#notin(selectS#fromSCwhereC#='C6')selectstu_id,Name,SEXfromstudentwhereSEX='男'andnotexists

(select*fromSCwhereSC.S#=S.S#andC#='C6');第五章習(xí)題8:

S(S#，SN，SEX，AGE,DEPT)；C（C#，CN，TEARCHER）SC(S#，C#，GRADE）9.將SC表中每門(mén)課程的平均成績(jī)插入SC_C(CNO,CNAME,AVG_GRADE)CreatetableSC_C(CNOchar(8),CNAMEchar(20),AVG_GRADEnumeric(8,2))InsertintoSC_CselectC.C_ID,CN,AVG(GRADE)fromSC,CwhereSC.C#=C.C#groupbyC.C#,CN第五章習(xí)題8:S(S#，SN，SEX，AGE,DEPT)；C（C#，CN，TEARCHER）SC(S#，C#，GRADE）11.從SC表中把數(shù)學(xué)課程中低于數(shù)學(xué)平均分的元組刪除DeletefromSCwheregrade<(selectavg(grade)fromSCwhereC#in(selectC#fromCwhereCN='數(shù)學(xué)'))andC#in(selectC#fromCwhereCN='數(shù)學(xué)')10—22選擇題答案：10—15：B;C;A;C;C;B16--22:A;C;B;C;B;D;C課堂測(cè)驗(yàn)題:設(shè)有下列四個(gè)表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)；其中學(xué)生表S由學(xué)號(hào)(Sno)、學(xué)生姓名(Sname)和學(xué)生住址(Saddress)和學(xué)生所在系(Sdept)組成；課程表C由課程編號(hào)(Cno)、課程名稱(chēng)(Cname)組成；教師表T由教師職工號(hào)(Tno)、教師姓名(Tname)和教師電話(huà)(Phone)組成；表示學(xué)生選課的選課表SCT由學(xué)號(hào)(Sno)、課程編號(hào)(Cno)、教師職工號(hào)(Tno)、考試成績(jī)(Grade)、學(xué)年(Tyear)組成。用SQL語(yǔ)言完成下列各項(xiàng)操作：創(chuàng)建表SCT(Sno,Cno,Tno,Grade,Tyear)；查詢(xún)學(xué)號(hào)(Sno)為‘03287512’的學(xué)生姓名Sname和地址Saddress；查詢(xún)電話(huà)號(hào)碼中含有‘3762’的教師姓名Tname；將所有電話(huà)號(hào)碼中含有‘3762’的教師的電話(huà)號(hào)碼前加‘8’，形成新的電話(huà)號(hào)碼；查詢(xún)2006學(xué)年(Tyear=2006)選修了課程號(hào)Cno為‘C012’，且考試成績(jī)(Grade)不及格(<60)的學(xué)生人數(shù)；課堂測(cè)驗(yàn)題:設(shè)有下列四個(gè)表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)；用SQL語(yǔ)言完成下列各項(xiàng)操作：查詢(xún)2006學(xué)年(Tyear=2006)選修過(guò)的全部課程的課程編號(hào)Cno、及其相應(yīng)每門(mén)課的選修人數(shù)、平均成績(jī)；查詢(xún)選修了所有課程的學(xué)生學(xué)號(hào)、姓名；查詢(xún)沒(méi)有選修張強(qiáng)老師(Tname=’張強(qiáng)’)所授課程的學(xué)生學(xué)號(hào)和學(xué)生姓名；求2006學(xué)年(Tyear=2006)選課人數(shù)最多的課程編號(hào)、選課人數(shù)；建立視圖View_SCT，它記錄了教師編號(hào)為“T01”授課情況，包括：課程編號(hào)、課程名稱(chēng)、選修人數(shù)、平均成績(jī)、最高分；將機(jī)電工程系的學(xué)生，選修了課程編號(hào)Cno為‘C100’的所有學(xué)生的成績(jī)加5分。課堂測(cè)驗(yàn)題:

部分解答設(shè)有下列四個(gè)表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)

4.將所有電話(huà)號(hào)碼中含有‘3762’的教師的電話(huà)號(hào)碼前加‘8’，形成新的電話(huà)號(hào)碼；UpdateTSetPhone=‘8’+PhoneWherePhonelike‘%3762%’測(cè)驗(yàn)題:

設(shè)有下列四個(gè)表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)

5.查詢(xún)2006學(xué)年(Tyear=2006)選修了課程號(hào)Cno為‘C012’，且考試成績(jī)(Grade)不及格(<60)的學(xué)生人數(shù)；Selectcount(Sno)FromSCTWhereTyear=2006andGrade<60andCno=‘C012’錯(cuò)例分析:GroupbySnoSelectcount(Sno)FromSCTWhereTyear=2006andCno=‘C012’andGrade<60本題是統(tǒng)計(jì)選修了課程號(hào)為Cno=‘C012’的所有學(xué)生的人數(shù),直接在表SCT中查詢(xún)即可,不能以學(xué)號(hào)來(lái)分組.如果以Sno分組則每元組的count(Sno)列始終為１．測(cè)驗(yàn)題:

設(shè)有下列四個(gè)表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)

6.查詢(xún)2006學(xué)年(Tyear=2006)選修過(guò)的全部課程的編號(hào)Cno、及其相應(yīng)每門(mén)課的選修人數(shù)、平均成績(jī)；SelectCno,count(Sno)選修人數(shù),Avg(Grade)平均成績(jī)FromSCTWhereTyear=2006GroupByCno如果沒(méi)寫(xiě),查詢(xún)結(jié)果中就無(wú)列名了啊!!測(cè)驗(yàn)題:

設(shè)有下列四個(gè)表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)

7.查詢(xún)選修了所有課程的學(xué)生學(xué)號(hào)、姓名；selectSno,NamefromSwhereSno

in(selectSnofromSCTwherenotexists(select*fromCwherenotexists(select*fromSCTwhereSCT.Sno=S.SnoandC.Cno=SCT.Cno)))測(cè)驗(yàn)題:

設(shè)有下列四個(gè)表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)

8.查詢(xún)沒(méi)有選修張強(qiáng)老師所授課程的學(xué)生學(xué)號(hào)和學(xué)生姓名

selectSno,NamefromswhereSnonotin(selectSnofromSCTwhereTnoin(selectT.TnofromTwhereTname=‘張強(qiáng)’))selectSno,Namefromswherenotexists(select*fromSCTwhereexists(select*fromTwhereTname=‘張強(qiáng)’andT.Tno=SCT.TnoandS.Sno=SCT.