《數(shù)據(jù)庫技術(shù)與應用-SQLSERVER2005》課后答案

1、數(shù)據(jù)庫技術(shù)與應用-SQL SERVER2005劉衛(wèi)國 熊擁軍主編習題參考答案目錄第一章 數(shù)據(jù)庫系統(tǒng)概論一、選擇題ABDAD二、填空題載體、意義；數(shù)據(jù)集合；數(shù)據(jù)庫管理系統(tǒng)；數(shù)據(jù)庫系統(tǒng)；層次、網(wǎng)狀、關(guān)系、關(guān)系模型第二章 關(guān)系數(shù)據(jù)庫基本原理一、選擇題D DACBD二、填空題1. 實體完整性、參照完整性、用戶自定義完整性。2. （A,B） R1(A,D)和R2(A,B,C).3. 元子特性4. 外鍵5. F1（AB，AC，AD）6. 選擇第三章 SQL SERVER2005系統(tǒng)基礎(chǔ)一、選擇題ACBCB二、填空題1. 企業(yè)版，標準版，開發(fā)版，工作組版，精簡版2. WINDOWs身份驗證模式和混合模式3.

2、 SQL Server管理平臺、SQL Server配置管理器4. 已注冊服務器窗口、對象資源管理器，文檔窗口5. 數(shù)據(jù)查詢語言，數(shù)據(jù)定義語言，數(shù)據(jù)操縱語言和數(shù)據(jù)控制語言第四章 數(shù)據(jù)庫的管理一、選擇題ABDAC二、填空題1、數(shù)據(jù)文件和事務日志文件2、master , tempdb , model ,msdb和mssqlsystemresource3、數(shù)據(jù)文件，事務日志文件和文件組4、55、create database, alter database, drop database四、應用題1create database saleson primary(name=saldat01,filena

3、me=c:dbsaldat01.mdf),(name=saldat02,filename=c:dbsaldat02.ndf),filegroup filegrp1(name=saldat11,filename=d:dbsaldat11.ndf),(name=saldat12,filename=d:dbsaldat12.ndf),(name=saldat13,filename=d:dbsaldat13.ndf)2alter database salesadd log file(name=sallog2,filename=c:dbsallog2.ldf)3alter database salesa

4、dd file(name=saldat03,filename=c:dbsaldat03.ndf,size=5,filegrowth=20%)擴展alter database salesadd file(name=saldat14,filename=d:dbsaldat14.ndf,size=5,filegrowth=20%) to filegroup filegrp14alter database sales set single_user5drop database sales第五章 表的管理一、選擇題CACCC二、填空題1、-2 -2-1， 0-255.2、輸入存儲字段小于100時按原字段

5、存，大于100時截取100位。8個字節(jié)。3、日期時間數(shù)據(jù)類型，數(shù)字數(shù)據(jù)類型4、表名和字段名稱5、insert，update， truncate或delete。四、應用題-創(chuàng)建數(shù)據(jù)庫create database sales-4.1-標識符列（自動增長的列）-identity(種子，增量)create table sell_order(order_id1 int identity(1,2),goods_id char(6) not null,employee_id char(4) not null,custmer_id char(4) not null,transporter_id char(4

6、) not null,order_num float,discount float,order_date datetime,send_date datetime,arrival_date datetime,cost money)-刪除標識符列alter table sell_orderdrop column order_id1-添加標識符列alter table sell_orderadd order_id1 int identity(1,2)-4.2alter table sell_order-drop column send_dateadd 發(fā)貨日期 datetime-4.3-標識列自動增

7、長insert sell_order (goods_id,employee_id,custmer_id,order_num,discount,order_date)values(135,16,99,30,9.5,2009-2-26)insert sell_order values(135,16,99,30,9.5,2009-2-26,)-為空和null不同insert sell_order values(135,16,99,null,30,9.5,2009-2-26,null,null,null)-允許手動增長（顯示聲明）set identity_insert sales.dbo.sell_o

8、rder on insert sell_order (order_id1,goods_id,employee_id,custmer_id,order_num,discount,order_date) values(8,135,16,99,30,9.5,2009-2-26)set identity_insert sales.dbo.sell_order off -4.4insert sell_order values(26,02,6,10,200,8,2008-10-10,2008-12-12,2008-12-1)-4.5insert sell_order values(26,29,100,10

9、,200,8,2009-1-1,2008-12-12,null,2008-12-1)update sell_order set employee_id=16 where (employee_id=29 and cost is null)-4.6update sell_order set discount=discount*0.9 where(custmer_id=100 and cost is null)-4.7delete from sell_order where order_date=2009-1-1第六章 數(shù)據(jù)查詢一、選擇題CABBC二、填空題1、TOP/PERCENT2、UNION，

10、查詢數(shù)據(jù)/結(jié)果集3、嵌套查詢/子查詢4、內(nèi)鏈接，外連接5、等值連接，自然連接6、into四、應用題-128頁應用題第一題create database student2create table student( s_no char(8), s_name char(10), s_sex char(2), birthday datetime, polity char(8)insert into student values(s003,江魚,女,2003-01-01,國民黨)create table sco( s_no char(8), c_no char(8), score float)inser

11、t into sco values(s003,數(shù)據(jù)結(jié)構(gòu),85)-1-1select * from student order by s_no-1-2select * from student where s_sex=女 order by s_sex compute count(s_sex)-1-3select s_name,birthday,year(getdate()-year(birthday) as 年齡 from student where s_sex=男select s_name,convert(varchar,birthday,23),year(getdate()-year(bir

12、thday) as 年齡 from student where s_sex=男-select convert(varchar,getdate(),23) 只截取系統(tǒng)當前日期-select convert(varchar,getdate(),8) 只截取系統(tǒng)當前時間 -1-4select s_name,birthday,year(getdate()-year(birthday) as 年齡,c_no,score from student,sco where student.s_no=sco.s_noselect s_name,birthday,year(getdate()-year(birthd

13、ay) as 年齡,c_no,score from student inner join sco on student.s_no=sco.s_no-1-5select score from sco where s_no=(select s_no from student where s_name=江魚)select sco.score,student.s-name from sco inner join student on sco.s_no=student.s_no where student.s_name=江于-1-6select s_name from student where s_n

14、o in(select s_no from sco where score35-2-5select 部門名,count(職工號) as 職工人數(shù) from 職工,部門 where 部門.部門號=職工.部門號 group by 部門名 having count(*)=2 order by count(*) descselect 部門名,count(*) as 職工人數(shù) from 職工 inner join 部門 on 部門.部門號=職工.部門號 group by 部門.部門名 having count(*)=1 order by count(*) desc第七章 索引與視圖一、選擇題CCADB二

15、、填空題1、alter table, drop index2、primary key, unique3、視圖4、定義5、計算列6、修改，刪除第八章 數(shù)據(jù)完整性一、選擇題BCCAD二、填空題1、行2、create rule和create default3、唯一性4、primary key ，多5、檢查，唯一性6、constraint，約束名四、應用題-第八章應用題-1alter table sell_orderadd order_id1 int identity(1,2)alter table sell_orderdrop column order_id1-2alter table sell_o

16、rderadd primary key(order_id1)-3use salesalter table customeradd primary key(customer_id)alter table sell_orderadd foreign key(custom_id) references customer(customer_id) on delete cascade on update cascade-4alter table customeradd constraint kk unique(customer_name)-5alter table sell_orderadd const

17、raint k3 default (0)for discount-6alter table sell_orderadd constraint k4 check (order_datesend_date),check (send_datearriver_date)第九章 T-SQL程序設計一、選擇題BCCDD二、填空題1、局部2、44,82,1263、gf4、/* */5、單條，begin-end6、break, continue7、declare cursor, select四、應用題-4.1.1SELECT goods_name as 商品名稱, CASE WHEN classificati

18、on_id=P001 THEN 筆記本計算機 WHEN classification_id=P002 THEN 激光打印機 WHEN classification_id=P003 THEN 噴墨打印機 WHEN classification_id=P004 THEN 交換機 END AS 商品類別, unit_price AS 單價,stock_quantity as 庫存FROM goods-4.1.2declare value realset value=-1while value15000open employee_cursorFETCH NEXT FROM employee_curso

19、rWHILE fetch_status = 0 BEGIN FETCH NEXT FROM employee_cursorEND-4-2select employee_name, case (datediff(yy,birth_date,getdate()-1)/10 when 2 then 20到30歲 when 3 then 30到40歲 when 4 then 40到50歲 else 50歲以上 end as 年齡段,department_name as 所屬部門from department,employee where department.department_id=employe

20、e.department_id order by 所屬部門 ascselect nld as 年齡段,count(*) as 人數(shù) from (select case (datediff(yy,birth_date,getdate()-1)/10 when 2 then 20到30歲 when 3 then 30到40歲 when 4 then 40到50歲 else 50歲以上 end as nld,birth_date from employee) a GROUP BY nld-4-3create function fact2(x int)returns intasbegin if x=1

21、 return 1 return x*dbo.fact2(x-1)endselect dbo.fact2(6)-4-4 declare i int,j int,sum int select i = 1, j = 0,sum=0 while(i100) begin if(i%7=0) begin set j=j+1 set sum=sum+i end set i=i+1 end select j,sum-4-5declare cur_employee cursorforselect sex,count(*) from employee group by sexdeclare sex char(2

22、),sex_num intopen cur_employeefetch next from cur_employee into sex,sex_numwhile fetch_status=0 begin select sex as 性別,sex_num as 人數(shù) fetch next from cur_employee into sex,sex_num endclose cur_employeedeallocate cur_employee第十章 存儲過程和觸發(fā)器一、選擇題CAADA二、填空題1、sp_2、存儲過程3、commit transaction, rollback transact

23、ion4、數(shù)據(jù)封鎖機制四、應用題-1CREATE PROC prStoreOrderIDOrder_id1 char(6),goods_id char(6),employee_id char(4),Custom_ID char(5),Transporter_ID char(4),order_date datetime,order_id2 char(6) OUTPUTASBEGIN INSERT INTO sell_order(Order_ID1,goods_id,employee_id,Custom_ID,Transporter_ID,order_date) VALUES(Order_id1,

24、goods_id,employee_id,Custom_ID,Transporter_ID,order_date) SELECT order_id2=Order_ID1 FROM sell_order WHERE Order_ID1=Order_id1 ENDDECLARE order_id2 char(6)EXEC prStoreOrderID S00009,G00005,e010,C0009,T010,2013.01.01,order_id2 OUTPUTSELECT order_id2-2create proc chapter10_4_2as begin create table #te

25、mp (id int) declare n int select n=1 while (n=100) begin insert #temp values(n) select n=n+1 end select * from #tempendexec chapter10_4_2-3create proc prupdatename g_id char(6),g_name varchar(50)asbegin update goods set goods_name=g_name where goods_id=g_idendexec prupdatename G0008,IBM T51-4CREATE

26、PROC prTest ASSELECT * FROM goodsIF EXISTS(SELECT * FROM sysobjects WHERE name=prTest) print 存在此存儲過程ELSE print 不存在此存儲過程-5EXEC sp_helptext prTest-6EXEC sp_depends prTest-7EXEC sp_rename prTest,npr_Test-8DROP PROC npr_Test第十一章 數(shù)據(jù)庫的安全管理一、選擇題CDBC二、填空題1、登錄賬號，角色2、window身份驗證模式和混合模式3、對象、語句、隱含4、grant, revoke5、創(chuàng)建新的數(shù)據(jù)庫角色、分配權(quán)限給創(chuàng)建的角色、將這個角色授予某個用戶四、應用題sp_addlogin kwh,salesuse salessp_grantdbaccess kwh,kwh2sp_addrolemember db_datareader,kwh2sp_revokedbaccess kwh2sp_droplogin kwh第十二章 數(shù)據(jù)庫的備份與還原一、選擇題ABAA二、填空題1、磁盤備份設備、磁帶設備、命名通道設備2、