數(shù)據(jù)庫(kù)系統(tǒng)原理與設(shè)計(jì)萬(wàn)常選設(shè)計(jì)試驗(yàn)參考

1、實(shí)驗(yàn)D02:單表查詢(xún)實(shí)驗(yàn)七、設(shè)計(jì)性實(shí)驗(yàn)1. 實(shí)驗(yàn)要求(1) 查詢(xún)計(jì)算機(jī)系學(xué)生的學(xué)號(hào)和姓名。Select sno,sname from s where dept= ' 計(jì)算機(jī)系 '(2) 查詢(xún)選修了課程的學(xué)生學(xué)號(hào)。Select distinct sno from sc(3) 查詢(xún)選修C01課程的學(xué)生學(xué)號(hào)和成績(jī)，并要求對(duì)查詢(xún)結(jié)果按成績(jī)的降序排列，如 果成績(jī)相同則按學(xué)號(hào)的升序排列。select sno,grade from sc where cno=' C01'order by grade desc, sno(4) 查詢(xún)每門(mén)課程的平均分。Select cno,avg(

2、grade) from sc group by cno(5) 查詢(xún)學(xué)校開(kāi)設(shè)的課程總數(shù)。Select count(distinct cno) from sc(6) 查詢(xún)選修兩門(mén)及兩門(mén)以上課程的學(xué)生學(xué)號(hào)。Select sno from sc group by snoHaving count(cno)=2(7) 查詢(xún)年齡在20-22之間的男生的學(xué)號(hào)、姓名、系部。Select sno,sname, dept from s where sex= '男'and age between 20 and 22(8) 查詢(xún)選修C01'課程的學(xué)生人數(shù)。Select count(sno) fr

3、om sc where cno= ' C01'3.思考題(1) 使用ORDER B肝句后SELECT的數(shù)據(jù)列有什么限制嗎？使用 GROUP BYY?答：使用ORDEfBU句后SELECT的數(shù)據(jù)列沒(méi)有限制，而使用GROUB行句后SELEC后的數(shù)據(jù)列只能是group by后跟的數(shù)據(jù)列和聚集函數(shù)。(2) 執(zhí)行 select max(grade) from sc, 寫(xiě)出執(zhí)行結(jié)果；執(zhí)行 select sno, max(grade) fromsc group by sno, 寫(xiě)出執(zhí)行結(jié)構(gòu)。思考：使用GROUP BY分組條件 子句后，語(yǔ)句中的統(tǒng)計(jì)函數(shù)的運(yùn)行結(jié)果有什么不同？執(zhí)行select m

4、ax(grade) from sc只有一個(gè)結(jié)果，整個(gè)選課表的平均成績(jī)， 而select sno, max(grade) from sc group by sno顯示的是每個(gè)學(xué)生的選課平均成績(jī)，使用 group by字句后，根據(jù)其后的字段對(duì)數(shù)據(jù)表進(jìn)行分組，再對(duì)每一個(gè)組進(jìn)行分組統(tǒng)計(jì)。實(shí)驗(yàn)D03:多表查詢(xún)實(shí)驗(yàn)七、設(shè)計(jì)性實(shí)驗(yàn)1. 實(shí)驗(yàn)要求(1) 查詢(xún)“計(jì)算機(jī)系“學(xué)生所選課程的成績(jī)表。Select sno,sname, cno,cname ,grade from s,sc,c where s.sno=sc.sno and o=o and dept='計(jì)算機(jī)系'(2) 查詢(xún)成績(jī)比該課程平均

5、成績(jī)低的同學(xué)的成績(jī)表。Select sno,cno,grade from sc sc1 where grade<(select avg(grade) from sc sc2 where o=o)(3) 查詢(xún)選修C01課程的學(xué)生學(xué)號(hào)、課程名、成績(jī)，并要求對(duì)查詢(xún)結(jié)果按學(xué)號(hào)的降序排列， 如果學(xué)號(hào)相同則按成績(jī)的升序排列。Select sno,cname,grade from sc,c where o=o and cno='C01'Order by grade desc, sno(4) 查詢(xún)選修兩門(mén)及兩門(mén)以上課程的學(xué)生學(xué)號(hào)及姓名。Select sno,sname from s,sc

6、 where s.sno=oGroup by sno,snameHaving count(cno)>=2(5) 查詢(xún)年齡在20-22之間的男生的選修的課程號(hào)。Select cno from sc where sno in(select sno from s where sex='男'and age between20 and 22)(6) 查詢(xún)選修數(shù)據(jù)結(jié)構(gòu)課程的學(xué)生人數(shù)。Select count(sno) from sc where cno in (select cno from c where cname=' 數(shù)據(jù)結(jié)構(gòu) ')(7) 查詢(xún)每門(mén)選課成績(jī)?cè)?8

7、0分以上的學(xué)生學(xué)號(hào)、姓名。Select sno,sname from s where sno in (select sno from sc group by sno having min(grade)>=80)2. 思考題(1) 多表的連接查詢(xún)是否都可以轉(zhuǎn)換成嵌套查詢(xún)？什么情況下連接查詢(xún)不能用嵌套查詢(xún)表示？答：當(dāng)查詢(xún)結(jié)果涉及多張表中的字段時(shí)，多表連接查詢(xún)不能轉(zhuǎn)換成嵌套查詢(xún)；當(dāng)涉及左外連接或者右外連接時(shí)多表連接查詢(xún)也不能轉(zhuǎn)換成嵌套查詢(xún)。(2) 多表連接查詢(xún)和IN嵌套子查詢(xún)從查詢(xún)效率上來(lái)說(shuō)，哪種查詢(xún)的效果更好？為什么答：若嵌套子查詢(xún)?yōu)闊o(wú)關(guān)嵌套查詢(xún)時(shí)，IN嵌套子查詢(xún)的查詢(xún)效率比多表連接查詢(xún)查詢(xún)

8、要高。因?yàn)闊o(wú)關(guān)嵌套查詢(xún)只對(duì)子查詢(xún)涉及的表掃描1次，而多表連接要做笛卡兒乘積運(yùn)算，子表要查詢(xún)多次。若嵌套子查詢(xún)?yōu)橄嚓P(guān)嵌套查詢(xún)時(shí)，則和多表連接查詢(xún)的查詢(xún)效率差不多。實(shí)驗(yàn)D04參考答案七、設(shè)計(jì)性實(shí)驗(yàn)1. 實(shí)驗(yàn)要求(1) 在S表中添加完整性約束：SNO設(shè)置為主鍵，SEX的卻省值為女，AGE的有效值為16-25。Alter table s add primary key(sno), default 女'for sex, check (age between 16 and 25)(2) 創(chuàng)建1張與S相同的表S1,在創(chuàng)建的同時(shí)將SN破置為主鍵，SEX的卻省值為女,AGE 的有效值為16-25，并將S

9、表中的數(shù)據(jù)插入到S1中，插入不同的記錄來(lái)嚴(yán)整設(shè)置的完整性。Create table s1(SNO VARCHAR (10) primary key ,SNAME VARCHAR(20),DEPA VARCHAR(20),AGE INT check (age between 16 and 25), SEX CHAR(4) default女')Insert into s1 select sno,sname, depa, age, sex from sInsert into s1 (sno,sname) values(200512 ', 'zxx ')Insert i

10、nto s1(sno,sname,age) values(200618', 'zxx',26)Insert into s1(sno,sname,age) values(200618', 'zxx',18)(3) 創(chuàng)建1張與SC同的表SC1,將(SNO, CNO設(shè)置為主鍵，SN#口CN改置為外鍵，并將SC表 中的數(shù)據(jù)復(fù)制到SC1中，插入不同的記錄來(lái)嚴(yán)整設(shè)置的完整性。create table SC1(SNO VARCHAR(10) NOT NULL, Cno VARCHAR(10) NOT NULL, GRADE DECIMAL)alter tabl

11、e sc1add primary key(sno,cno), foreign key(sno) references s(sno), foreignkey(cno)references c(cno) insert into sc1 values( 200620 ', 'c01',90)insert into sc1 values(null,null,80)insert into sc1 values( 200512 ', 'c06',80)(4)創(chuàng)建1張與C表相同的表C1,將C中數(shù)據(jù)插入到C1后，在C1上添加完整性約束：將( CNO設(shè) 置為主鍵，

12、CPN。弓|用CNO, CREDIT©為1-6。create table C1(CNO VARCHAR(10) NOT NULL, CNAME VARCHAR(20),cpno varchar(10),credit int)alter table cladd primary key (cno), foreign key (cpno) references c(cno), check (credit between 1and 6)2.思考題SQL Server中有哪些完整性功能？保證數(shù)據(jù)完整性還需要注意哪些方面的問(wèn)題？答：SQL Server 有 primary key , forei

13、gn key, unique, check, default 等完整性約束功能。在定 義完整性約束要考慮表之間的聯(lián)系，特別是定義foreign key時(shí)要確保外鍵在別的表中已經(jīng)定義主鍵。實(shí)驗(yàn)D05:數(shù)據(jù)操作與索引實(shí)驗(yàn)、實(shí)驗(yàn)名稱(chēng)和性質(zhì)所屬課程數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)名稱(chēng)二數(shù)據(jù)操作與索引實(shí)驗(yàn)學(xué)時(shí)2實(shí)驗(yàn)性質(zhì)? 驗(yàn)證 綜合？ 口設(shè)計(jì)必做/選做?必做選做二、實(shí)驗(yàn)?zāi)康? .掌握數(shù)據(jù)操作-插入、刪除、修改；2. 掌握數(shù)據(jù)表的索引方法；3. 掌握視圖的創(chuàng)建機(jī)制及其上的操作。七、設(shè)計(jì)性實(shí)驗(yàn)1. 實(shí)驗(yàn)要求(1) 將s表系部為“計(jì)算機(jī)系”學(xué)生對(duì)應(yīng)的系部改為 cs。Update sSet depa= 'CS where

14、 depa='計(jì)算機(jī)系'(2) 創(chuàng)建一個(gè)與SC表相同新數(shù)據(jù)表SCNEW查詢(xún)SC表中成績(jī)?yōu)榭盏挠涗洸⑵鋸?fù)制到SCNEW 表中。Select * into SCNEW from sc where grade is not null(3) 刪除SC中成績(jī)?yōu)榭盏倪x課記錄。Delete from sc where grade is null(4) 根據(jù)S表中姓名字段建立唯一性索引。Create unique index ui_s_sname on s(sname) 提示：因?yàn)镾中有重名學(xué)生，所以不能建立姓名字段的唯一索引(5) 建立S表上SNO勺聚族索引。Create clustere

15、d index ci_s_sno on s(sno)(6) 建立S決上根據(jù)SN(序，CN(W序的復(fù)合索引。Create index i_sc_snocno on sc(sno, cno desc)(7) 建立每門(mén)課程的課程號(hào)、課程名、選課人數(shù)、平均分的視圖。Create view vCourseAsSelect o, cname, count(sno) selectNum, avg(grade) avgGrade from sc, cWhere o=oGroup by o,cname2.思考題(1) 是否任何可以對(duì)任意的視圖進(jìn)行修改？什么樣的視圖上不能進(jìn)行修改操作？答：不是任何試圖都可以進(jìn)行修

16、改，當(dāng)視圖中存在聚集函數(shù)或者其他函數(shù)計(jì)算時(shí)不能修改視圖。(2) 如果表中某個(gè)字段重復(fù)值比較多，要不要在該字段上建立索引？為什么？答：若表中某個(gè)字段的重復(fù)值比較多，則不要在該字段上建立索引。因?yàn)?，重?fù)值多時(shí)建立索引后根據(jù)索引找到該字段值還有多個(gè)，則要到表中查找多次記錄，影響查找速度。實(shí)驗(yàn)D06: SQL編程及存儲(chǔ)過(guò)程實(shí)驗(yàn)所屬課程數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)名稱(chēng)SQL編程及存儲(chǔ)過(guò)程實(shí)驗(yàn)學(xué)時(shí)2實(shí)驗(yàn)性質(zhì)? 驗(yàn)證 綜合？ 口設(shè)計(jì)必做/選做?必做選做二、實(shí)驗(yàn)?zāi)康? .了解T-SQL的基本數(shù)據(jù)類(lèi)型、函數(shù);2. 掌握局部變量的定義和賦值；3. 掌握存儲(chǔ)過(guò)程的定義及調(diào)用七、設(shè)計(jì)性實(shí)驗(yàn)1.實(shí)驗(yàn)要求(1) 調(diào)用幫助系統(tǒng)來(lái)查找系統(tǒng)

17、存儲(chǔ)過(guò)程或函數(shù)來(lái)顯示SQL SERVER勺版本號(hào)及當(dāng)前系統(tǒng)時(shí)間。(提示：到幫助中根據(jù)關(guān)鍵字等查詢(xún)到對(duì)應(yīng)的函數(shù)或存儲(chǔ)過(guò)程來(lái)完成任務(wù))Print Getdate()Print version或者 xp_msver(2) 編寫(xiě)存儲(chǔ)過(guò)程：完成1 ! +2! +, +n!的計(jì)算。(提示：傳入?yún)?shù)n,根據(jù)NR循環(huán)控制來(lái)求對(duì)應(yīng)的值)Create proc SumFactorn intAsDeclare sum, i,factor intSet sum=0Set factor=1Set i=1While (i<=n)Beginfactor*=isum+=factori+=1endprint 1!+2!+

18、 +'+cast(n as varchar(4)+'='+cast(sum as varchar(10)(3) 將，表中的DEP序段內(nèi)容改為中文：IS-信息系，CS-計(jì)算機(jī)系，PH-物理系，MA數(shù)學(xué)系。(提示：用UPDATE句來(lái)修改DEP序段，DEPA勺值根據(jù)不同英文縮寫(xiě)要修改為不同的中文系名，用CAS®數(shù)來(lái)完成)Update sSet depa=case depaWhen信息系'thenIS'When計(jì)算機(jī)系'then CSWhen物理系'thenPHWhen數(shù)學(xué)系'thenMAendFrom s(4) 將C表上增加一個(gè)字段seleNUM，并編寫(xiě)存儲(chǔ)過(guò)程：傳入學(xué)號(hào)和課程號(hào)后，完成在SC!中插入相應(yīng)的選課記錄，并在C表中對(duì)應(yīng)課程的seleNUM加1。Alter table c add seleNUM intUpdate cSet selenium=(select count(sno) from sc where cno=o)Create proc addSeleNumsno varchar(10), cno varchar(10)AsInsert int