數(shù)據(jù)庫課后習(xí)題解答

1、3-3 習(xí)題33.4 在SQL Server中，創(chuàng)建一個(gè)名為students且包含有下列幾個(gè)屬性的表。SNO char(10)；NAME varchar(10)；SEX char(1)；BDATE datetime；DEPT varchar(10)；DORMITORYvarchar(10).要求：1.采用兩種形式創(chuàng)建表，即用SQL語句和用圖形界面的形式來創(chuàng)建。2.定義必要的約束，包括主鍵SNO,NAME值不允許為空，且SEX取值為0或1。【解答】·進(jìn)入SQL查詢分析器建立查詢，創(chuàng)建students表的SQL語句如下，操作如圖3.17所示。use mydb /* 假設(shè)在mydb庫中建表

2、 */create table students(SNO char(10) not NULL primary key, NAME varchar(10) not NULL, SEX char(1) not NULL check(sex='0' or sex='1'), BDATE datetime, DEPT varchar(10), DORMITORY varchar(10)圖3.17 用SQL語句創(chuàng)建students表·進(jìn)入企業(yè)管理器用基本操作創(chuàng)建students表。用右鍵單擊“mydb”數(shù)據(jù)庫，從彈出的菜單中選擇“新建”,再從其下一級(jí)菜單中選擇“

3、表”。或者，用右鍵單擊“mydb”數(shù)據(jù)庫下一級(jí)的“表”，從彈出的菜單中選擇“新建表”。然后，在彈出的窗體中，把students表所包含的字段逐一輸入，每個(gè)字段都要指明列名、數(shù)據(jù)類型、長(zhǎng)度和是否允許空值、是否主鍵等內(nèi)容，如圖3.18所示。圖3.18 用基本操作創(chuàng)建students表其中，SEX字段取值為0或1，需要建立約束。操作是用右鍵單擊SEX字段，從彈出的菜單中選擇“CHECK約束”，再從彈出的“屬性”窗體中，選擇“CHECK約束”卡，在約束表達(dá)式框中輸入約束表達(dá)式，如圖3.19所示。圖3.19 輸入約束表達(dá)式最后，單擊“保存”圖標(biāo)，SQL Server將彈出一個(gè)“選擇名稱”對(duì)話框，輸入表名

4、“students”，單擊“確定”按鈕，新建的students表結(jié)構(gòu)將被保存起來。3.5 在Access中，完成習(xí)題3.4的要求?！窘獯稹縞reate table students( SNO text(10) not null primary key, NAME text(10) not null, SEX text(10) not null, BDATE datetime, DEPT text(10), DORMITORY text(10) )注意，J-SQL的CREATE TABLE語句沒有提供對(duì)字段的檢查約束?？梢栽趧?chuàng)建表后，使用基本操作方式，對(duì)SEX字段建立有效性規(guī)則。3.7 在SQL

5、 Server中，創(chuàng)建表Depts（DNO,DNAME,MGR）。用SQL語句在習(xí)題3.4中創(chuàng)建的Students表中將DEPT設(shè)置為外鍵，引用Depts表中的DNO列值。若某系還有學(xué)生時(shí)，不得在Depts表中刪除該系的記錄。use mydbcreate table Depts(DNO varchar(10) not NULL primary key, DNAME char(10) not NULL, MGR char(10) )alter table students add constraint c1 foreign key(Dept) references Depts(Dno) on d

6、elete cascade3.8 什么是視圖？視圖的作用是什么？在習(xí)題3.4所創(chuàng)建的Students表的基礎(chǔ)上，建立一個(gè)顯示所有計(jì)算機(jī)系學(xué)生的視圖，假設(shè)計(jì)算機(jī)系的代號(hào)為CS。use mydbgocreate view student_cs_view /*SQL SERVER約定：本語句必須為批處理的第一個(gè)語句*/asselect *from studentswhere students.DEPT='cs'3.11在SQL Server中完成下列操作： 用修改表結(jié)構(gòu)語句在Students表中添加整型的Height和Weight字段。 創(chuàng)建一個(gè)規(guī)則并綁定到Height列，用以限制插

7、入到該列的整數(shù)范圍。 創(chuàng)建一個(gè)默認(rèn)值并綁定到Weight列，插入記錄時(shí)，默認(rèn)值自動(dòng)填充到該列中。 基于Students表與Depts表，分別建立惟一性約束、檢查約束、主鍵約束、外鍵約束和參照(引用)完整性約束，并輔以其他操作予以驗(yàn)證。 創(chuàng)建并執(zhí)行一個(gè)帶SELECT查詢語句的存儲(chǔ)過程,統(tǒng)計(jì)出每個(gè)系的學(xué)生平均身高。 創(chuàng)建一個(gè)觸發(fā)器，其功能是：當(dāng)試圖在Depts表中修改數(shù)據(jù)時(shí)將發(fā)出警告消息。【解答】 在Students表中添加整型的Height和Weight字段：use mydbgoalter table students add Height int,Weight int 創(chuàng)建一個(gè)規(guī)則并綁定到He

8、ight列，用以限制插入到該列的整數(shù)范圍：use mydbgocreate rule R1 as range>=145 and range<=200goexec sp_bindrule rulename='R1', objname='students.Height' 創(chuàng)建一個(gè)默認(rèn)值并綁定到Weight列，插入記錄時(shí)默認(rèn)值自動(dòng)填充到該列中：use mydbgocreate default D1 as 50goexec sp_bindefault defname='D1',objname='students.Weight'

9、 基于Students表與Depts表，分別建立惟一性約束、檢查約束、主鍵約束、外鍵約束和參照(引用)完整性約束：/* 假設(shè)在mydb庫中建表，若表students已存在，創(chuàng)建前先把該表刪除 */use mydbcreate table students(SNO char(10) not NULL primary key, /*主鍵約束*/ NAME varchar(10) not NULL unique, /*惟一性約束*/ SEX char(1) not NULL check(sex='0'or sex='1'), /*檢查約束*/ BDATE dateti

10、me, DEPT varchar(10) constraint c1 foreign key(Dept) /*外鍵約束*/ references Depts(Dno) on delete cascade, /*參照完整性約束*/ DORMITORY varchar(10)注：創(chuàng)建表后，有關(guān)驗(yàn)證性的操作，請(qǐng)讀者自行完成。 創(chuàng)建一個(gè)帶SELECT查詢語句的存儲(chǔ)過程p1，統(tǒng)計(jì)出每個(gè)系的學(xué)生平均身高：use mydbgocreate proc p1as select dept, avg(Height) as avg_h from students group by deptreturn 執(zhí)行存儲(chǔ)過程p

11、1的語句如下：use mydbgoexec p1 創(chuàng)建一個(gè)觸發(fā)器，其功能是當(dāng)試圖在Depts表中修改數(shù)據(jù)時(shí)將發(fā)出警告消息：use mydbgocreate trigger t1 on deptsfor updateas raiserror('警告！',10,1)go當(dāng)使用update語句修改Depts表中數(shù)據(jù)時(shí)，將在消息欄顯示“警告！”消息。3.12 假設(shè)教學(xué)數(shù)據(jù)庫中有三個(gè)表，其數(shù)據(jù)結(jié)構(gòu)如下： 學(xué)生表S(學(xué)號(hào)SNO,姓名SNAME,年齡AGE,性別SEX)； 選修表SC(學(xué)號(hào)SNO,課程號(hào)CNO,成績(jī)GRADE)； 課程表C(課程號(hào)CNO,課程名CNAME,任課教師TEACHE

12、R)； 試用基本的SELECT語句表達(dá)下列操作： 檢索選修課程號(hào)為C06的學(xué)生學(xué)號(hào)與成績(jī)。 檢索選修課程號(hào)為C06的學(xué)生學(xué)號(hào)與姓名。 檢索選修課程名為ENGLISH的學(xué)生學(xué)號(hào)與姓名。 檢索選修課程號(hào)為C08或C12的學(xué)生學(xué)號(hào)與成績(jī)。 檢索至少選修課程號(hào)為C08和C12的學(xué)生學(xué)號(hào)與成績(jī)。 檢索沒有選修C02號(hào)課程的學(xué)生姓名與年齡。 檢索選修了全部課程的學(xué)生姓名。 檢索選修課程中包含了學(xué)生S05所學(xué)課程的學(xué)生學(xué)號(hào)。 求女學(xué)生的總?cè)藬?shù)和平均年齡。 統(tǒng)計(jì)選修了課程的學(xué)生人數(shù)?！窘獯稹?檢索選修課程號(hào)為C06的學(xué)生學(xué)號(hào)與成績(jī)。use mydbselect sno,gradefrom scwhere cn

13、o='c06' 檢索選修課程號(hào)為C06的學(xué)生學(xué)號(hào)與姓名。·第1種查詢方法連接查詢：use mydbSELECT s.sno, snameFROM s,scWHERE s.sno=sc.sno and cno='c06'·第2種查詢方法嵌套查詢：use mydbSELECT sno, snameFROM sWHERE sno in ( SELECT sno FROM sc WHERE cno= 'c06' );·嵌套查詢(使用相關(guān)查詢)： use mydbSELECT sno, snameFROM sWHERE 

14、9;c06' in ( SELECT cno FROM sc WHERE sno=s.sno);·第3種查詢方法使用存在量詞的嵌套查詢：use mydbSELECT sno,snameFROM sWHERE exists (SELECT * FROM sc WHERE s.sno=sc.sno and cno='c06' ); 檢索選修課程名為ENGLISH的學(xué)生學(xué)號(hào)與姓名。·嵌套查詢：use mydbSELECT sno, snameFROM sWHERE sno in ( SELECT sno FROM sc WHERE cno in ( SEL

15、ECT cno FROM cWHERE cname= 'ENGLISH' );·連接查詢：use mydbSELECT s.sno, snameFROM s,c,scWHERE s.sno = sc.sno and o = o and cname = 'english' ; 檢索選修課程號(hào)為C08或C12的學(xué)生學(xué)號(hào)與成績(jī)。use mydbSELECT *FROM scWHERE cno= 'C08' or cno= 'C12' ; 注：這里輸出選修表sc的所有列，除學(xué)生學(xué)號(hào)與成績(jī)外，還有選課的課程號(hào)。若某個(gè)學(xué)生同時(shí)選修了

16、C08和C12兩門課程，可通過選課的課程號(hào)予以區(qū)分。 檢索至少選修課程號(hào)為C08和C12的學(xué)生學(xué)號(hào)與成績(jī)。use mydbSELECT A.sno, A.grade, B.gradeFROM sc AS A, sc AS BWHERE A.sno = B.sno and A.cno= 'C08' and B.cno= 'C12' ; 檢索沒有選修C02號(hào)課程的學(xué)生姓名與年齡。use mydbSELECT sname, ageFROM sWHERE sno not in ( SELECT sno FROM sc WHERE cno in ( SELECT cno

17、FROM c WHERE cno= 'C02' ) ;若把最外層的WHERE子句由“not in”改為“not exists”，則代碼如下：use mydbSELECT sname, ageFROM sWHERE not exists ( SELECT sno FROM sc WHERE s.sno=sc.sno and cno in ( SELECT cno FROM c WHERE cno= 'C02' ) ; 檢索選修了全部課程的學(xué)生姓名。use mydbSELECT sno, snameFROM sWHERE not exists ( SELECT *

18、FROM c WHERE not exists ( SELECT * FROM sc WHERE s.sno=sc.sno and o=o ) ; 檢索選修課程中包含了學(xué)生S05所學(xué)課程的學(xué)生學(xué)號(hào)。use mydbSELECT DISTINCT snoFROM scWHERE sno<>'S05' and cno in ( SELECT cno FROM sc WHERE sno= 'S05' ) ; 求女學(xué)生的總?cè)藬?shù)和平均年齡。use mydbSELECT count(*) as 總?cè)藬?shù), avg(age) as 平均年齡FROM sWHERE s

19、ex='女' 統(tǒng)計(jì)選修了課程的學(xué)生人數(shù)。use mydbselect count(distinct sno) as 選課人數(shù)from sc3.13 對(duì)習(xí)題3.12給出的表，用完整的SELECT語句或使用限定等方式表達(dá)下列操作： 統(tǒng)計(jì)每一年齡選修課程的學(xué)生人數(shù)。 求S表中男學(xué)生的每一年齡組(超過3人)的人數(shù)；查詢結(jié)果按人數(shù)升序排列，若人數(shù)相同按年齡降序排列。 檢索女學(xué)生選修的所有課程號(hào)。 檢索每個(gè)學(xué)生的出生年份,輸出學(xué)生姓名和出生年份分別用新列名: XM,CSNF。 檢索1820歲且姓名以字符L打頭的學(xué)生姓名。 檢索至少?zèng)]有選修C02和C03兩門課程的學(xué)生學(xué)號(hào)。 檢索選修表SC中

20、平均成績(jī)最高的學(xué)生學(xué)號(hào)。 檢索出每門課程的最高分和最低分。 使用COMPUTE子句生成Students表中Weight列的和及平均值。 建立另一個(gè)Students1表，求Students表與Students1表的并集、差集、交集?！窘獯稹?統(tǒng)計(jì)每一年齡選修課程的學(xué)生人數(shù)。use mydbselect age as 年齡, count(distinct s.sno) as 人數(shù)from s,scwhere s.sno=sc.snogroup by age 求S表中男學(xué)生的每一年齡組(超過3人)的人數(shù)；查詢結(jié)果按人數(shù)升序排列，若人數(shù)相同按年齡降序排列。use mydbselect age as 年

21、齡, count(distinct s.sno) as 人數(shù)from s,scwhere s.sno=sc.sno and sex='男'group by agehaving count(distinct s.sno)>3order by 2, age desc 檢索女學(xué)生選修的所有課程號(hào)。use mydbselect distinct o as 課程號(hào)from s,scwhere s.sno=sc.sno and sex='女' 檢索每個(gè)學(xué)生的出生年份,輸出學(xué)生姓名和出生年份分別用新列名:XM,CSNF。use mydbselect sname as X

22、M, year(getdate()-age as CSNFfrom s 檢索1820歲且姓名以字符L打頭的學(xué)生姓名。use mydbselect snamefrom swhere age between 18 and 20 and sname like 'L%' 檢索至少?zèng)]有選修C02和C03兩門課程的學(xué)生學(xué)號(hào)。use mydbSELECT snoFROM sWHERE sno not in ( SELECT A.sno FROM sc AS A, sc AS B WHERE A.sno = B.sno and A.cno= 'C02' and B.cno= &

23、#39;C03' ) ; 檢索選修表SC中平均成績(jī)最高的學(xué)生學(xué)號(hào)。use mydbselect sno as 學(xué)號(hào), m as 最高平均分from (SELECT sno,m=avg(grade) FROM sc GROUP BY sno) as A where m>=all (SELECT avg(grade) FROM sc GROUP BY sno) 檢索出每門課程的最高分和最低分。use mydbselect cno as 課程號(hào), min(grade) as 最高分, max(grade) as 最低分from scgroup by cno 使用COMPUTE子句生成S

24、tudents表中Weight列的和及平均值。use mydbselect *from studentscompute sum(weight), avg(weight) 建立另一個(gè)Students1表，求Students表與Students1表的并集、差集、交集。use mydb/*并集*/select *from studentsunionselect *from student1/*差集*/select *from studentswhere sno not in (select sno from student1)/*交集*/select students.*from students

25、INNER JOIN student1 ON students.sno=student1.sno ;3.14 對(duì)于如下關(guān)系模式：雇員表 EMP(雇員編號(hào)EID,姓名ENAME,出生年月BDATE,性別SEX,居住城市CITY)；公司表 COMP(公司編號(hào)CID,公司名稱CNAME,公司所在城市CITY)；工作表 WORKS(雇員編號(hào)EID,公司編號(hào)CID,加入公司日期STARTDATE，薪酬SALARY)； 試用SQL完成下列操作： 檢索出所有為“IBM公司”工作的雇員名字。 檢索出所有年齡超過50歲的女性雇員的姓名和所在公司的名稱。 檢索出所有居住城市與公司所在城市相同的雇員。 檢索出“IB

26、M公司”雇員的人數(shù)，平均工資，最高工資和最低工資，并且分別用E#，AVG_SAL，MAX_SAL，MIN_SAL作為列標(biāo)題。 檢索同時(shí)在“IBM公司”和“SAP公司”兼職的雇員名字。 檢索出工資高于其所在公司雇員平均工資的所有雇員。 檢索雇員最多的公司。 為工齡超出10年的雇員加薪10%。 年齡大于60歲的雇員應(yīng)辦理退休手續(xù)，刪除退休雇員的所有相關(guān)記錄?！癐BM公司”增加某新雇員，將該雇員有關(guān)的記錄插入到EMP表和WORKS表中，假設(shè)新進(jìn)雇員薪酬未定，暫以空值表示?！窘獯稹?檢索出所有為“IBM公司”工作的雇員名字。use mydbselect ENAMEfrom EMPwhere EID i

27、n (select EID from WORKS where CID in (select CID from COMP where CNAME= 'IBM公司' ) 本題的檢索可以使用多種不同的形式，例如：use mydbselect ENAMEfrom EMP,WORKS,COMPwhere COMP.CNAME='IBM公司' AND COMP.CID=WORKS.CID AND WORKS.EID=EMP.EID 檢索出所有年齡超過50歲的女性雇員的姓名和所在公司的名稱。use mydbselect ENAME, COMP.CNAMEfrom EMP,WO

28、RKS,COMPwhere year(getdate()-year(EMP.BDATE)>50 AND EMP.SEX='女' AND EMP.EID= WORKS.EID AND WORKS.CID =COMP.CID 檢索出所有居住城市與公司所在城市相同的雇員。use mydbselect ENAME,EMP.CITY,COMP.CITYfrom EMP,WORKS,COMPwhere EMP.CITY=COMP.CITY AND EMP.EID= WORKS.EID AND WORKS.CID =COMP.CID 檢索出“IBM公司”雇員的人數(shù)，平均工資，最高工資和

29、最低工資，并且分別用E#，AVG_SAL，MAX_SAL，MIN_SAL作為列標(biāo)題。use mydbselect COUNT(EID) AS E#, AVG(SALARY) AS AVG_SAL, MAX(SALARY) AS MAX_SAL, MIN(SALARY) AS MIN_SALfrom WORKS, COMPwhere COMP.CNAME= 'IBM公司' AND COMP.CID=WORKS.CID 檢索同時(shí)在“IBM公司”和“SAP公司”兼職的雇員名字。use mydbselect EID, ENAMEfrom EMPwhere EID IN (select

30、EID from WORKS where CID IN (select CID from COMP where CNAME = 'IBM公司' or CNAME = 'SAP公司' ) 檢索出工資高于其所在公司雇員平均工資的所有雇員。use mydbselect EID as 雇員編號(hào), ENAME as 姓名from EMPwhere EID in (select B.EID from (select CID,M=avg(SALARY) from WORKS group by CID) as A ,WORKS as B where A.CID=B.CID an

31、d B.SALARY>A.M) 檢索雇員最多的公司。use mydbselect CID as 公司編號(hào), CNAME as 公司名稱from COMPwhere CID in (select CID from (select CID, M=COUNT(EID) from WORKS group by CID) as A where M>=all (select COUNT(EID) from WORKS group by CID ) 為工齡超出10年的雇員加薪10%。use mydbupdate WORKSset SALARY=SALARY*1.1where year(getda

32、te()-year(STARTDATE)>10 年齡大于60歲的雇員應(yīng)辦理退休手續(xù)，刪除退休雇員的所有相關(guān)記錄。use mydbdelete EMPwhere year(getdate()-year(BDATE)>60“IBM公司”增加某新雇員，將該雇員有關(guān)的記錄插入到EMP表和WORKS表中，假設(shè)新進(jìn)雇員薪酬未定，暫以空值表示。use mydbinsert into EMP(EID,ENAME,BDATE,SEX,CITY) values('E07','andy','1970-3-8','男','廣州'

33、;)goinsert into WORKS(EID,CID,STARTDATE,SALARY) values('E07','C01', getdate(),null)3.16 根據(jù)習(xí)題3.14給出的關(guān)系模式，創(chuàng)建一個(gè)視圖，按照公司順序來顯示其所有雇員的有關(guān)信息?！窘獯稹縰se mydbgocreate view C_E_VIEWasselect COMP.CID,CNAME,CITY,EID,STARTDATE,SALARYfrom COMP left join WORKS on COMP.CID=WORKS.CIDgoselect *from C_E_VIEW

34、3.19 對(duì)習(xí)題3.12給出的三個(gè)表，試用T-SQL更新語句表達(dá)下列更新操作： 在S表中插入一行：(S06,WANG,20 )。 在S表中檢索出每一門成績(jī)都大于等于85分的學(xué)生學(xué)號(hào)、姓名和性別，并把檢索結(jié)果存入一個(gè)已存在的表STUDENT(SNO,SNAME,SEX)中。 刪除SC表中尚沒有成績(jī)的所有行。 把姓名為L(zhǎng)ILI的學(xué)生的所有成績(jī)刪去。 把選修ENGLISH課程的不及格成績(jī)(<60)全改為空值。 把低于MATHS課平均成績(jī)的外語系女生的MATHS成績(jī)提高5%。 修改SC表中C09課程的成績(jī)，若成績(jī)小于等于70分則提高4%,若成績(jī)大于70分則提高3%。 使用INSTEAD OF觸發(fā)

35、器，在SC表中插入一行(CHEN)。注: 設(shè)CNO為C03【解答】 在S表中插入一行：(S06,WANG,20 )。use mydbinsert into S(SNO,SNAME,AGE) values('S06','WANG',20) 在S表中檢索出每一門成績(jī)都大于等于85分的學(xué)生學(xué)號(hào)、姓名和性別，并把檢索結(jié)果存入一個(gè)已存在的表STUDENT(SNO,SNAME,SEX)中。use mydbinsert into STUDENT(SNO,SNAME,SEX)select distinct S.SNO,SNAME,SEXfrom S inner join SC

36、on S.SNO=SC.SNOwhere 85<all ( select GRADE from SC where S.SNO=SNO ) 刪除SC表中尚沒有成績(jī)的所有行。use mydbdelete from SCwhere GRADE is null 把姓名為L(zhǎng)ILI的學(xué)生的所有成績(jī)刪去。use mydbupdate SCset GRADE=nullwhere SNO in ( select SNO from S where SNAME='LILI') 把選修ENGLISH課程的不及格成績(jī)(<60)全改為空值。use mydbupdate SCset GRADE=nullwhere GRADE<60 and CNO in ( select CNO from C where CNAME='ENGLISH') 把低于MATHS課平均成績(jī)的外語系女生的MATHS成績(jī)提高5%。/* 首先在S表中添加一個(gè)字段DEPARTMENT(系) */use myd