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

1、3-3 習(xí)題33.4 在sql server中，創(chuàng)建一個名為students且包含有下列幾個屬性的表。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?！窘獯稹窟M(jìn)入sql查詢分析器建立查詢，創(chuàng)建students表的sql語句如下，操作如圖3.17所示。use mydb /* 假設(shè)在mydb庫中建表 */cre

2、ate 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ù)庫，從彈出的菜單中選擇“新建”,再從其下一級菜單中選擇“表”?；蛘?，用右鍵單擊“mydb”數(shù)據(jù)庫下一級的“表”，從彈出的

3、菜單中選擇“新建表”。然后，在彈出的窗體中，把students表所包含的字段逐一輸入，每個字段都要指明列名、數(shù)據(jù)類型、長度和是否允許空值、是否主鍵等內(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將彈出一個“選擇名稱”對話框，輸入表名“students”，單擊“確定”按鈕，新建的students表

4、結(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語句沒有提供對字段的檢查約束?？梢栽趧?chuàng)建表后，使用基本操作方式，對sex字段建立有效性規(guī)則。3.7 在sql server中，創(chuàng)建表depts（dno,dname,mgr）

5、。用sql語句在習(xí)題3.4中創(chuàng)建的students表中將dept設(shè)置為外鍵，引用depts表中的dno列值。若某系還有學(xué)生時，不得在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 delete cascade3.8 什么是視圖？視圖的作用是什么？

6、在習(xí)題3.4所創(chuàng)建的students表的基礎(chǔ)上，建立一個顯示所有計算機系學(xué)生的視圖，假設(shè)計算機系的代號為cs。use mydbgocreate view student_cs_view /*sql server約定：本語句必須為批處理的第一個語句*/asselect *from studentswhere students.dept=cs3.11在sql server中完成下列操作： 用修改表結(jié)構(gòu)語句在students表中添加整型的height和weight字段。 創(chuàng)建一個規(guī)則并綁定到height列，用以限制插入到該列的整數(shù)范圍。 創(chuàng)建一個默認(rèn)值并綁定到weight列，插入記錄時，默認(rèn)值自動填

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

8、 r1 as range=145 and range=200goexec sp_bindrule rulename=r1, objname=students.height 創(chuàng)建一個默認(rèn)值并綁定到weight列，插入記錄時默認(rèn)值自動填充到該列中：use mydbgocreate default d1 as 50goexec sp_bindefault defname=d1,objname=students.weight 基于students表與depts表，分別建立惟一性約束、檢查約束、主鍵約束、外鍵約束和參照(引用)完整性約束：/* 假設(shè)在mydb庫中建表，若表students已存在，創(chuàng)建前先

9、把該表刪除 */use mydbcreate table students(sno char(10) not null primary key, /*主鍵約束*/ name varchar(10) not null unique, /*惟一性約束*/ sex char(1) not null check(sex=0or sex=1), /*檢查約束*/ bdate datetime, dept varchar(10) constraint c1 foreign key(dept) /*外鍵約束*/ references depts(dno) on delete cascade, /*參照完整性

10、約束*/ dormitory varchar(10)注：創(chuàng)建表后，有關(guān)驗證性的操作，請讀者自行完成。 創(chuàng)建一個帶select查詢語句的存儲過程p1，統(tǒng)計出每個系的學(xué)生平均身高：use mydbgocreate proc p1as select dept, avg(height) as avg_h from students group by deptreturn 執(zhí)行存儲過程p1的語句如下：use mydbgoexec p1 創(chuàng)建一個觸發(fā)器，其功能是當(dāng)試圖在depts表中修改數(shù)據(jù)時將發(fā)出警告消息：use mydbgocreate trigger t1 on deptsfor updateas

11、raiserror(警告！,10,1)go當(dāng)使用update語句修改depts表中數(shù)據(jù)時，將在消息欄顯示“警告！”消息。3.12 假設(shè)教學(xué)數(shù)據(jù)庫中有三個表，其數(shù)據(jù)結(jié)構(gòu)如下： 學(xué)生表s(學(xué)號sno,姓名sname,年齡age,性別sex)； 選修表sc(學(xué)號sno,課程號cno,成績grade)； 課程表c(課程號cno,課程名cname,任課教師teacher)； 試用基本的select語句表達(dá)下列操作： 檢索選修課程號為c06的學(xué)生學(xué)號與成績。 檢索選修課程號為c06的學(xué)生學(xué)號與姓名。 檢索選修課程名為english的學(xué)生學(xué)號與姓名。 檢索選修課程號為c08或c12的學(xué)生學(xué)號與成績。 檢索至

12、少選修課程號為c08和c12的學(xué)生學(xué)號與成績。 檢索沒有選修c02號課程的學(xué)生姓名與年齡。 檢索選修了全部課程的學(xué)生姓名。 檢索選修課程中包含了學(xué)生s05所學(xué)課程的學(xué)生學(xué)號。 求女學(xué)生的總?cè)藬?shù)和平均年齡。 統(tǒng)計選修了課程的學(xué)生人數(shù)?！窘獯稹?檢索選修課程號為c06的學(xué)生學(xué)號與成績。use mydbselect sno,gradefrom scwhere cno=c06 檢索選修課程號為c06的學(xué)生學(xué)號與姓名。第1種查詢方法連接查詢：use mydbselect s.sno, snamefrom s,scwhere s.sno=sc.sno and cno=c06;第2種查詢方法嵌套查詢：use

13、 mydbselect sno, snamefrom swhere sno in ( select sno from sc where cno= c06 );嵌套查詢(使用相關(guān)查詢)： use mydbselect sno, snamefrom swhere 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 ); 檢索選修課程名為eng

14、lish的學(xué)生學(xué)號與姓名。嵌套查詢：use mydbselect sno, snamefrom swhere sno in ( select sno from sc where cno in ( select cno from cwhere cname= english );連接查詢：use mydbselect s.sno, snamefrom s,c,scwhere s.sno = sc.sno and o = o and cname = english ; 檢索選修課程號為c08或c12的學(xué)生學(xué)號與成績。use mydbselect *from scwhere cno= c08 or c

15、no= c12 ; 注：這里輸出選修表sc的所有列，除學(xué)生學(xué)號與成績外，還有選課的課程號。若某個學(xué)生同時選修了c08和c12兩門課程，可通過選課的課程號予以區(qū)分。 檢索至少選修課程號為c08和c12的學(xué)生學(xué)號與成績。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號課程的學(xué)生姓名與年齡。use mydbselect sname, agefrom swhere sno not in ( select sno

16、 from sc where cno in ( select cno 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 exi

17、sts ( select * from c where not exists ( select * from sc where s.sno=sc.sno and o=o ) ; 檢索選修課程中包含了學(xué)生s05所學(xué)課程的學(xué)生學(xué)號。use mydbselect distinct snofrom scwhere snos05 and cno in ( select cno from sc where sno= s05 ) ; 求女學(xué)生的總?cè)藬?shù)和平均年齡。use mydbselect count(*) as 總?cè)藬?shù), avg(age) as 平均年齡from swhere sex=女 統(tǒng)計選修了課程的

18、學(xué)生人數(shù)。use mydbselect count(distinct sno) as 選課人數(shù)from sc3.13 對習(xí)題3.12給出的表，用完整的select語句或使用限定等方式表達(dá)下列操作： 統(tǒng)計每一年齡選修課程的學(xué)生人數(shù)。 求s表中男學(xué)生的每一年齡組(超過3人)的人數(shù)；查詢結(jié)果按人數(shù)升序排列，若人數(shù)相同按年齡降序排列。 檢索女學(xué)生選修的所有課程號。 檢索每個學(xué)生的出生年份,輸出學(xué)生姓名和出生年份分別用新列名: xm,csnf。 檢索1820歲且姓名以字符l打頭的學(xué)生姓名。 檢索至少沒有選修c02和c03兩門課程的學(xué)生學(xué)號。 檢索選修表sc中平均成績最高的學(xué)生學(xué)號。 檢索出每門課程的最高

19、分和最低分。 使用compute子句生成students表中weight列的和及平均值。 建立另一個students1表，求students表與students1表的并集、差集、交集?！窘獯稹?統(tǒng)計每一年齡選修課程的學(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 年齡, count(distinct s.sno

20、) as 人數(shù)from s,scwhere s.sno=sc.sno and sex=男g(shù)roup by agehaving count(distinct s.sno)3order by 2, age desc 檢索女學(xué)生選修的所有課程號。use mydbselect distinct o as 課程號from s,scwhere s.sno=sc.sno and sex=女 檢索每個學(xué)生的出生年份,輸出學(xué)生姓名和出生年份分別用新列名:xm,csnf。use mydbselect sname as xm, year(getdate()-age as csnffrom s 檢索1820歲且姓名以

21、字符l打頭的學(xué)生姓名。use mydbselect snamefrom swhere age between 18 and 20 and sname like l% 檢索至少沒有選修c02和c03兩門課程的學(xué)生學(xué)號。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= c03 ) ; 檢索選修表sc中平均成績最高的學(xué)生學(xué)號。use mydbselect sno as 學(xué)號, m as 最高平均分from

22、 (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 課程號, min(grade) as 最高分, max(grade) as 最低分from scgroup by cno 使用compute子句生成students表中weight列的和及平均值。use mydbselect *from studentscompute sum(weight), avg(weig

23、ht) 建立另一個students1表，求students表與students1表的并集、差集、交集。use mydb/*并集*/select *from studentsunionselect *from student1/*差集*/select *from studentswhere sno not in (select sno from student1)/*交集*/select students.*from students inner join student1 on students.sno=student1.sno ;3.14 對于如下關(guān)系模式：雇員表 emp(雇員編號eid,姓

24、名ename,出生年月bdate,性別sex,居住城市city)；公司表 comp(公司編號cid,公司名稱cname,公司所在城市city)；工作表 works(雇員編號eid,公司編號cid,加入公司日期startdate，薪酬salary)； 試用sql完成下列操作： 檢索出所有為“ibm公司”工作的雇員名字。 檢索出所有年齡超過50歲的女性雇員的姓名和所在公司的名稱。 檢索出所有居住城市與公司所在城市相同的雇員。 檢索出“ibm公司”雇員的人數(shù)，平均工資，最高工資和最低工資，并且分別用e#，avg_sal，max_sal，min_sal作為列標(biāo)題。 檢索同時在“ibm公司”和“sap公

25、司”兼職的雇員名字。 檢索出工資高于其所在公司雇員平均工資的所有雇員。 檢索雇員最多的公司。 為工齡超出10年的雇員加薪10%。 年齡大于60歲的雇員應(yīng)辦理退休手續(xù)，刪除退休雇員的所有相關(guān)記錄。“ibm公司”增加某新雇員，將該雇員有關(guān)的記錄插入到emp表和works表中，假設(shè)新進(jìn)雇員薪酬未定，暫以空值表示?！窘獯稹?檢索出所有為“ibm公司”工作的雇員名字。use mydbselect enamefrom empwhere eid in (select eid from works where cid in (select cid from comp where cname= ibm公司 )

26、本題的檢索可以使用多種不同的形式，例如：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,works,compwhere year(getdate()-year(emp.bdate)50 and emp.sex=女 and emp.eid= works.eid and works.cid =co

27、mp.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ù)，平均工資，最高工資和最低工資，并且分別用e#，avg_sal，max_sal，min_sal作為列標(biāo)題。use mydbselect count(eid) as e#, avg(salary) as avg_sal, max(salary) as m

28、ax_sal, min(salary) as min_salfrom works, compwhere comp.cname= ibm公司 and comp.cid=works.cid 檢索同時在“ibm公司”和“sap公司”兼職的雇員名字。use mydbselect eid, enamefrom empwhere eid in (select eid from works where cid in (select cid from comp where cname = ibm公司 or cname = sap公司 ) 檢索出工資高于其所在公司雇員平均工資的所有雇員。use mydbsele

29、ct eid as 雇員編號, 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 and b.salarya.m) 檢索雇員最多的公司。use mydbselect cid as 公司編號, cname as 公司名稱from compwhere cid in (select cid from (select cid, m=count(eid) from works gro

30、up 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(getdate()-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)雇員薪

31、酬未定，暫以空值表示。use mydbinsert into emp(eid,ename,bdate,sex,city) values(e07,andy,1970-3-8,男,廣州)goinsert into works(eid,cid,startdate,salary) values(e07,c01, getdate(),null)3.16 根據(jù)習(xí)題3.14給出的關(guān)系模式，創(chuàng)建一個視圖，按照公司順序來顯示其所有雇員的有關(guān)信息?！窘獯稹縰se mydbgocreate view c_e_viewasselect comp.cid,cname,city,eid,startdate,salaryf

32、rom comp left join works on comp.cid=works.cidgoselect *from c_e_view3.19 對習(xí)題3.12給出的三個表，試用t-sql更新語句表達(dá)下列更新操作： 在s表中插入一行：(s06,wang,20 )。 在s表中檢索出每一門成績都大于等于85分的學(xué)生學(xué)號、姓名和性別，并把檢索結(jié)果存入一個已存在的表student(sno,sname,sex)中。 刪除sc表中尚沒有成績的所有行。 把姓名為lili的學(xué)生的所有成績刪去。 把選修english課程的不及格成績(60)全改為空值。 把低于maths課平均成績的外語系女生的maths成績提

33、高5%。 修改sc表中c09課程的成績，若成績小于等于70分則提高4%,若成績大于70分則提高3%。 使用instead of觸發(fā)器，在sc表中插入一行(chen)。注: 設(shè)cno為c03【解答】 在s表中插入一行：(s06,wang,20 )。use mydbinsert into s(sno,sname,age) values(s06,wang,20) 在s表中檢索出每一門成績都大于等于85分的學(xué)生學(xué)號、姓名和性別，并把檢索結(jié)果存入一個已存在的表student(sno,sname,sex)中。use mydbinsert into student(sno,sname,sex)select

34、distinct s.sno,sname,sexfrom s inner join sc on s.sno=sc.snowhere 85all ( select grade from sc where s.sno=sno ) 刪除sc表中尚沒有成績的所有行。use mydbdelete from scwhere grade is null 把姓名為lili的學(xué)生的所有成績刪去。use mydbupdate scset grade=nullwhere sno in ( select sno from s where sname=lili) 把選修english課程的不及格成績(60)全改為空值。use mydbupdate scset grade=nullwhere grade60 and cno in ( select cno from c where cname=englis