數(shù)據(jù)庫原理與應(yīng)用習題集(11套)

-=============================================11章習題一、選擇題1．下列四項中，不屬于數(shù)據(jù)庫系統(tǒng)優(yōu)點的是（）。A．實現(xiàn)數(shù)據(jù)共享B．確保數(shù)據(jù)的安全性與**性C．控制數(shù)據(jù)冗余D．數(shù)據(jù)依賴程序2．數(shù)據(jù)庫系統(tǒng)中，對用戶使用的數(shù)據(jù)視圖的描述稱為（）。A．概念模式B．內(nèi)模式C．存儲模式D．外模式3．下列四項中，不屬于數(shù)據(jù)庫特點的是( )。A.數(shù)據(jù)共享 B.數(shù)據(jù)完整性C.數(shù)據(jù)冗余很高 D.數(shù)據(jù)獨立性高4．數(shù)據(jù)庫的三級模式之間存在著兩級映像，使數(shù)據(jù)庫系統(tǒng)具有較高的數(shù)據(jù)（）。A．相容性B．獨立性C．共享性D．一致性5．數(shù)據(jù)庫系統(tǒng)的三級模式結(jié)構(gòu)中，定義索引的組織方式屬于（ ）。A．概念模式B．外模式C．邏輯模式D．內(nèi)模式6.E-R模型屬于（ ）。A.概念模型B.層次模型C.網(wǎng)狀模型D.關(guān)系模型7．學生社團可以接納多名學生參加，但每個學生只能參加一個社團，從社團到學生之間的聯(lián)系類型是( )。A.多對多 B.一對一C.多對一 D.一對多8．反映現(xiàn)實世界中實體及實體間聯(lián)系的信息模型是( )。A.關(guān)系模型 B.層次模型C.網(wǎng)狀模型 D.E-R模型二、設(shè)計題1．*醫(yī)院病房計算機管理中需要如下信息：科室：科名，科地址，科，醫(yī)生**病房：病房號，床位號，所屬科室名醫(yī)生：**，職稱，所屬科室名，年齡，工作證號病人：病歷號，**，性別，診斷，主管醫(yī)生，病房號其中，一個科室有多個病房、多個醫(yī)生，一個病房只能屬于一個科室，一個醫(yī)生只屬于一個科室，但可負責多個病人的診治，一個病人的主管醫(yī)生只有一個。設(shè)計該計算機管理系統(tǒng)的E－R圖，并注明屬性和聯(lián)系類型。2．假設(shè)*公司的業(yè)務(wù)規(guī)則如下：（1）公司下設(shè)幾個部門，如技術(shù)部、財務(wù)部、市場部等。（2）每個部門承擔多個工程項目，每個工程項目屬于一個部門。（3）每個部門有多名職工，每一名職工只能屬于一個部門。（4）一個部門可能參與多個工程項目，且每個工程項目有多名職工參與施工。根據(jù)職工在工程項目中完成的情況發(fā)放酬金。（5）工程項目有工程號、工程名兩個屬性；部門有部門號、部門名稱兩個屬性；職工有職工號、**、性別屬性。. z.-請根據(jù)上述規(guī)則設(shè)計E-R模型。*公司的業(yè)務(wù)規(guī)則如下：（1）每位職工可以參加幾個不同的工程，且每個工程有多名職工參與；（2）每位職工有一個職位，且多名職工可能有相同的職位；（3）職位決定小時工資率，公司按職工在每一個工程中完成的工時，計算酬金；（4）職工的屬性有職工號、**、職位和小時工資率；（5）工程的屬性有工程號和工程名稱。試根據(jù)上述業(yè)務(wù)規(guī)則設(shè)計E-R模型。答案：一、選擇題 DDCBDADD二、設(shè)計題1.2.3.===================================================2第3章習題1．學校有若干個系，每個系有若干班級和教研室，每個教研室有若干教師，每名教師只教一門課，每門課可由多個教師教；每個班有若干學生，每個學生選修若干課程，每門課程可由若干學生選修。請用E-R圖畫出該學校的概念模型，注明聯(lián)系類型，再將E-R模型轉(zhuǎn)換為關(guān)系模型。2．工廠生產(chǎn)的每種產(chǎn)品由不同的零件組成，有的零件可用于不同的產(chǎn)品。這些零件由不同的原材料制成，不同的零件所用的材料可以相同。一個倉庫存放多種產(chǎn)品，一種產(chǎn)品存放在一個倉庫中。零件按所屬的不同產(chǎn)品分別放在倉庫中，原材料按照類別放在若干倉庫中（不跨倉庫存放）。請用E-R圖畫出此關(guān)于產(chǎn)品，零件，材料，倉庫的概念模型，注明聯(lián)系類型，再將E-R模型轉(zhuǎn)換為關(guān)系模型。3．一個圖書館管理系統(tǒng)中有如下信息：圖書：書號、書名、數(shù)量、位置借書人：借書證號、**、單位：名、郵編、地址、、其中約定：任何人可以借多種書，任何一種書可以被多個人借，借書和還書時，要登記相應(yīng)的借書日期和還書日期；一個可以出版多種書籍，同一本書僅為一個所出版，名具有唯一性。根據(jù)以上情況，完成如下設(shè)計：（1）設(shè)計系統(tǒng)的E-R圖；（2）將E-R圖轉(zhuǎn)換為關(guān)系模式；（3）指出轉(zhuǎn)換后的每個關(guān)系模式的關(guān)系鍵。答案假定：主碼用____表示外碼用表示該E-R圖可以轉(zhuǎn)換為以下關(guān)系模式：學院（院名，地址，成立日期，教師人數(shù)，學生人數(shù)）7個實體各自為一個關(guān)系系（系名，系址，系主任，院名）模式，6個1:N的聯(lián)系沒班級（班號，專業(yè)名，入學年份，學生數(shù)，班級導師，系名）有成為一個獨立的關(guān)系模學生（**，**，性別，出生日期，聯(lián)系，籍貫，班號）式，全部合并到多方實體. z.-教研室（教研室名稱，教師人數(shù)，，辦公室編號，系名）教師（教工號，**，性別，出生日期，聯(lián)系，教研室名稱,課號）課程（課號，課名，學時，學分，開課學期，必修課否）選修（**，課號，成績）2.1個M:N的聯(lián)系必須成為一個獨立的關(guān)系模該E-R圖可以轉(zhuǎn)換為以下關(guān)系模式：產(chǎn)品（產(chǎn)品名，型號，重量，倉庫號，數(shù)量c）4個實體各自為一個關(guān)系模式，2零件（零件名，規(guī)格）個1:N的聯(lián)系沒有成為一個獨立的材料（材料編號，名稱，倉庫號，數(shù)量a）關(guān)系模式，而是合并到多方實體，倉庫（倉庫號，地點，面積，保管員）合并時包括聯(lián)系得屬性產(chǎn)品-零件（產(chǎn)品名，零件名，組裝日期b）零件-材料（材料編號，零件名，生產(chǎn)日期a）零件-倉庫（零件名，倉庫號，數(shù)量b）3個M:N的聯(lián)系必須成為一個獨立的關(guān)系模3.E-R圖可以轉(zhuǎn)換為以下關(guān)系模式：圖書（書號，書名，數(shù)量，位置，名）借書人（借書證號，**，單位）（名，郵編，地址，，）借閱（借書證號，書號，借書日期，還書日期）===================================================3第5章習題1．試用T-SQL語言創(chuàng)建ZYGL(職員管理)數(shù)據(jù)庫，要求數(shù)據(jù)文件的初始大小為1MB，最大大小為50MB，增長方式按10%增長；日志文件的初始大小為3MB，按1MB增長。2．試用T-SQL語言修改ZYGL數(shù)據(jù)庫的主數(shù)據(jù)文件，將增長方式修改為按5MB增長。3．試用T-SQL語言修改ZYGL數(shù)據(jù)庫，為ZYGL增加一個數(shù)據(jù)文件ZYGLBAK。4．試用T-SQL語言刪除ZYGL數(shù)據(jù)庫。--1.CREATEDATABASEzyglON(NAME='zygl_Data',SIZE=1,MA*SIZE=30,FILEGROWTH=10%)LOGON(NAME='zygl_Log',SIZE=3,FILEGROWTH=1)GO--2ATLERDATABASEZYGLMODIFYFILENAME=ZYGL_DATA,FILEGROWTH=5)--3. z.-ATLERDATABASEZYGLADDFILE(NAME=ZYGLBAK,SIZE=1,MA*SIZE=50,FILEGROWTH=10%)--4DROPDATABASEZYGL===================================================46章習題設(shè)計題現(xiàn)有一個數(shù)據(jù)庫scd，庫中包含以下系、學生、班級各表：student（**，**，年齡，班號）class（班號，專業(yè)名，系名，入學年份）department（系號，系名）假設(shè)表中將有如下數(shù)據(jù)：student（2008101，*山，18，101；2008102，李斯，16，102；2008103，王玲，17，111；2008105，李飛，19，112）class（101，軟件，計算機，2005；102，微電子，計算機，2006；111，無機化學，化學，2004；112，高分子化學，化學，2006）department（001，數(shù)學；002，計算機；003，化學）1.用SQL語言創(chuàng)建以上各數(shù)據(jù)表，在定義中要求為每一字段（列）選擇合適的數(shù)據(jù)類型和長度，并設(shè)置適當?shù)募s束，即聲明：（1）每個表的主鍵和所有可能的外鍵。（2）學生**不能為空。（3）系部系名不能為空，且唯一。（4）學生的年齡介于15到40歲之間，默認為18。2．試用SQL語言完成下列功能：（1）學校又新增加了兩個系部：一個物理系，編號為006，一個經(jīng)濟系，編號為008。（2）將入學年份在2004年以前的班級刪除。（3）學生*山轉(zhuǎn)到111班，請更新相關(guān)的表。（4）為每個學生的年齡增加1歲，請更新相關(guān)的表。3．試用SQL語言修改表結(jié)構(gòu)：由于年齡可以通過出生日期計算而來，對student表刪除“年齡”字段，增加一個“出生. z.-日期”字段。（提示：必須先刪除對年齡的檢查約束，才能刪除年齡列）答案--1.createdatabasescdgoUsescdGoCREATETABLEdepartment(系號char(3)PRIMARYKEY,系名char(20)notnullunique)GoCREATETABLEclass(班號char(3)PRIMARYKEY,專業(yè)名char(20),系名char(20)referencesdepartment(系名),入學年份int)GoCREATETABLEstudent(**char(7)PRIMARYKEY,**char(8)NOTNULL,年齡intconstraintck_nlCHECK(年齡>=15and年齡<=40)constraintdf_nlDEFAULT18, --指定約束名ck_nl和df_nl,以便以后好刪除班號char(3)referencesclass(班號))Insertintodepartmentvalues('001','數(shù)學');Insertintodepartmentvalues('002','計算機');Insertintodepartmentvalues('003','化學')Insertintoclassvalues('101','軟件','計算機',2005);Insertintoclassvalues('102','微電子','計算機',2006);Insertintoclassvalues('111','無機化學','化學',2004);Insertintoclassvalues('112','高分子化學','化學',2006)Insertintostudentvalues('2008101','*山',18,'101');Insertintostudentvalues('2008102','李斯',16,'102');Insertintostudentvalues('2008103','王玲',17,'111')Insertintostudentvalues('2008104','李飛',19,'112')--2.--1)Insertintodepartmentvalues(‘006’,’物理系‘)Insertintodepartmentvalues(‘008’,’經(jīng)濟系‘)--2)Deleteclasswhere入學年份<2004--3)Updatestudentset班號='111'where**='*山'. z.---4）Updatestudentset年齡=年齡+1--3.--1)--要刪除年齡列，應(yīng)先刪除該列上的約束，再刪除Altertablestudentdropconstraintck_nl,df_nl--刪除年齡列Altertablestudentdropcolumn年齡Altertablestudentadd出生日期datetime--2)AltertableclassAdd班長**char(7)referencesstudent(**)===================================================57章習題設(shè)計題以下各題在CJGL數(shù)據(jù)庫中實現(xiàn)：查詢“數(shù)據(jù)庫原理”這門課程的學分和開課學期。查詢身高在175cm以上的男生信息。查詢所有姓“李”同學的基本情況。查詢至少選修了兩門課程的學生**。將所有學生信息按身高逆序輸出。查詢平均成績在75分以上的學生**、性別和專業(yè)。查詢“李林”同學沒學過的課程號。查詢有兩門課以上不及格的學生**。檢索選修課程包含“程明”同學所選課程之一的學生**。檢索同時選修了課程號為101和102這兩門課程的學生**。檢索選修課程名為“數(shù)據(jù)庫原理”的學生**和**。12*.查詢每門課得最高分的學生**。13*.檢索選修課程包含**為081103的學生所修全部課程的學生**。創(chuàng)建一個名為avg75的視圖，包含所有平均成績在75分以上的學生信息。答案1.查詢“數(shù)據(jù)庫原理”這門課程的學分和開課學期。Select課程號，開課學期FromkcWhere課程名=’數(shù)據(jù)庫原理’2.查詢身高在175cm以上的男生信息。Select*From*sWhere性別=’男’and身高>1753.查詢所有姓“李”同學的基本情況。Select*From*sWhere**like‘李%’4.查詢至少選修了兩門課程的學生**。Select**. z.-From*s_kcGroupby**Havingcount(課程號)>=25.將所有學生信息按身高逆序輸出。Select*From*sOrderby身高desc檢索平均成績在75分以上的學生**、性別和專業(yè)。方法一：Select**，性別，專業(yè)名From*sWhere**in(Select**From*s_kcGroupby**Havingavg(成績)>75)方法二：Select**，性別，專業(yè)名From*s_kcJOIN*sON*s.**=*s_kc.**Groupby*s.**,**，性別，專業(yè)名Havingavg(成績)>757.檢索“李林”同學不學的課程號。Select課程號FromkcWhere課程號notin(Select課程號from*s,*s_kcWhere*s.**=*s_kc.**and*s.**=’李林’)8.查詢只有兩門課不及格的學生**。Select**From*sWhere**in(Select**From*s_kcWhere成績<60Groupby**Havingcount(課程號)=2)9.檢索選修課程包含“程明”同學所選課程之一的學生**。Selectdistinct**From*s_kcWhere課程號in(Select課程號from*s,*s_kcWhere*s.**=*s_kc.**and*s.**=’程明’)10.檢索同時選修了課程號為101和102這兩門課程的學生**。select*from*s_kc------------------錯誤寫法：. z.-Select**From*s_kcWhere課程號='101'or課程號='102'Select**From*s_kcWhere課程號='101'and課程號='102'-------------------------------------------方法一，使用自連接：方法一，使用自連接：Selecta.**From*s_kca,*s_kcbWherea.**=b.**anda.課程號='101'andb.課程號='102'方法二，使用子查詢：Select**From*sWhere**in(select**From*s_kcwhere課程號='101')And**in(select**From*s_kcwhere課程號='102')方法三、Select**From*s_kcWhere課程號='101'or課程號='102'groupby**havingcount(**)=2檢索選修課程名為“數(shù)據(jù)庫原理”的學生**和**。Selecta.**,**From*sa,*s_kcb,kccWherea.**=b.**andb.課程號=c.課程號and課程名=’數(shù)據(jù)庫原理’查詢每門課的最高分的學生**。相關(guān)查詢法SELECTA.**,**,B.課程號,成績FROM*SA,*S_KCBWHEREA.**=B.**AND成績=(SELECTMA*(成績)FROM*S_KCCWHEREB.課程號=C.課程號)或派生表法SELECTA.**,B.課程號,B.成績,C.最高成績FROM*SA,*S_KCB,(SELECT課程號,MA*(成績)最高成績FROM*S_KCgroupby課程號)CWHEREA.**=B.**ANDB.課程號=C.課程號andB.成績=C.最高成績檢索選修課程包含**為081101的學生所修課程的學生**。方法一，selectdistinct**from*s_kcwhere課程號in(select課程號from*s_kcwhere**='081101')groupby**. z.-havingcount(課程號)=(selectcount(課程號)from*s_kcwhere**='081101')方法二，SELECT**，**FROM*sWHERENOTE*ISTS(SELECT*FROMkc,*s_kcaWHEREkc.課程號=a.課程號and**=’081103’andNOTE*ISTS(SELECT*FROM*s_kcbWHEREb.**=*s.**AND課程號=kc.課程號))創(chuàng)建一個名為avg75的視圖，包含所有平均成績在75分以上的學生信息。Createviewavg75AsSelect*From*sWhere**in(Select**From*s_kcGroupby**Havingavg(成績)>75)Createviewavg75AsSelect**，性別，專業(yè)名，avg(成績)From*s_kcJOIN*sON*s.**=*s_kc.**Groupby**，性別，專業(yè)名Havingavg(成績)>75===================================================6第8章習題對于數(shù)據(jù)庫scd，庫中包含以下系、學生、班級各表：student（**，**，年齡，班號）class（班號，專業(yè)名，系名，入學年份）department（系號，系名）請使用T-SQL語言完成以下各題。1．將student表的班號與**這兩列組合創(chuàng)建一個升序的非聚集索引。2．為department表的系名建立一個唯一索引，如果輸入了重復的鍵，將忽略該INSERT或UPDATE語句，并使用填充因子FILLFACTOR為50%。3．為數(shù)據(jù)庫scd建立一個默認對象，使其對應(yīng)于年齡為18，將其綁定到student表的年齡列上。4．為數(shù)據(jù)庫scd建立一個規(guī)則對象，并將其綁定到學生表的專業(yè)名列上，規(guī)定專業(yè). z.-名的取值只能為‘護理學’、‘地質(zhì)勘探’和‘考古學’之一。5．修改class表為其建立一個CHECK約束，檢查入學年份是否小于2009。答案1．將student表的班號與**這兩列組合創(chuàng)建一個升序的非聚集索引。Createnonclusteredinde*i*_bj_*monstudent(班號asc,**asc)2．為department表的系名建立一個唯一索引，如果輸入了重復的鍵，將忽略該INSERT或UPDATE語句，并使用填充因子FILLFACTOR為50%。Createuniqueinde*uqi*_*mondepartment(系名asc)WITHIGNORE_DUP_KEY，F(xiàn)ILLFACTOR=503．為數(shù)據(jù)庫scd建立一個默認對象，使其對應(yīng)于年齡為18，將其綁定到student表的年齡列上。Createdefaultd_ageas18GoE*ecsp_bindefault‘d_age’,‘student.年齡’4．為數(shù)據(jù)庫scd建立一個規(guī)則對象，并將其綁定到學生表的專業(yè)名列上，規(guī)定專業(yè)名的取值只能為‘護理學’、‘地質(zhì)勘探’和‘考古學’之一。Createruler_zymaszymin(‘護理學’,‘地質(zhì)勘探’,’考古學’)GoE*ecsp_bindrule‘r_zym’,‘student.專業(yè)名’5．修改class表為其建立一個CHECK約束，檢查入學年份是否小于2008。AltertableclassAddconstraintck_r*nfcheck(入學年份<2008)==================================================79章習題1習題九三、設(shè)計題：1．使用流程控制語句編寫程序：（1）在CJGL數(shù)據(jù)庫中，使用case函數(shù)處理：如果課程的學時在80以上顯示學習時間長,學時在54~80顯示學習時間一般,否則顯示學習時間短。（2）在CJGL數(shù)據(jù)庫中，使用case函數(shù)處理：如果學生的專業(yè)是“計算機”，顯示熱門，是“通信工程”顯示一般，否則顯示冷門。（3）計算18！=18*17*16…*2*1。（4）計算S=1！+2！+3！+……+10！2．以下各題在CJGL數(shù)據(jù)庫中實現(xiàn)：（1）創(chuàng)建一個自定義函數(shù)，可以求解任意數(shù)n的階乘，即n!。并用n=10調(diào)用該函數(shù)。（2）分別用函數(shù)或存儲過程實現(xiàn)以下功能：給定輸入?yún)?shù)課程名，統(tǒng)計該課程成績介于58~59分的學生人數(shù)。使用輸入?yún)?shù)“數(shù)據(jù)庫原理”調(diào)用該函數(shù)或該存儲過程。（3）創(chuàng)建存儲過程來完成求解給定*個學生的**返回該生的**和平均分的功能。（4）分別使用約束和觸發(fā)器實現(xiàn)以下功能：檢查在課程表中插入或修改的課程號是否在101~108之間。3．以下各題在第六章習題（設(shè)計題）給出的數(shù)據(jù)庫SCD中實現(xiàn)：（1）創(chuàng)建一個自定義函數(shù)用于統(tǒng)計各班級的人數(shù)，將class表增加一個人數(shù)字段，然后調(diào)用該函數(shù)將人數(shù)定義為計算列。. z.-（2）創(chuàng)建一個可以按給定**輸出學生的**、系名信息的存儲過程。（3）創(chuàng)建一個觸發(fā)器，當插入或更新*個學生的記錄時，檢查該班級的學生是否超過人，如果超過，就發(fā)出警告信息同時撤消所作的插入或更新操作。答案：三、設(shè)計題1．使用流程控制語句編寫程序：（1）在CJGL數(shù)據(jù)庫中，使用case函數(shù)處理：如果課程的學時在80以上顯示學習時間長,學時在54~80顯示學習時間一般,否則顯示學習時間短。SELECT課程號，課程名，學習時間=CASEWHEN學時>=80THEN'學習時間長'WHEN學時>=54THEN'學習時間一般'ELSE‘學習時間短’ENDFROMKC（2）在CJGL數(shù)據(jù)庫中，使用case函數(shù)處理：如果學生的專業(yè)是“計算機”，顯示熱門，是“通信工程”顯示一般，否則顯示冷門。SELECT**,**,專業(yè)熱度=CASE專業(yè)名WHEN‘計算機’THEN'熱門'WHEN‘通信工程’THEN'一般'ELSE'冷門'ENDFROM*s（3）計算18！=18*17*16…*2*1。DECLAREiint,pbigintSELECTi=1,p=1WHILEi<=18BEGINSETp=p*iSETi=i+1ENDPRINT'18*17*16…*2*1='+CAST(pASchar(20))（4）計算S=1！+2！+3！+……+10！DECLAREiint,pbigint,sbigintSELECTi=1,p=1,s=0WHILEi<=10BEGINSETp=p*iSETs=s+pSETi=i+1ENDPRINT'1！+2！+3！+……+10！='+CAST(sASchar(20))2．以下各題在CJGL數(shù)據(jù)庫中實現(xiàn)：（1）創(chuàng)建一個自定義函數(shù)，可以求解任意數(shù)n的階乘，即n!。并用n=10調(diào)用該函數(shù)。. z.-CREATEFUNCTIONF_C(nINT)RETURNSINTASBEGINDECLAREiint,pbigintSELECTi=1,p=1WHILEi<=nBEGINSETp=p*iSETi=i+1ENDReturnpENDGo--調(diào)用selectdbo.F_C(10)（2）分別用函數(shù)或存儲過程實現(xiàn)以下功能：給定輸入?yún)?shù)課程名，統(tǒng)計該課程成績介58~59分的學生人數(shù)。使用輸入?yún)?shù)“數(shù)據(jù)庫原理”調(diào)用該函數(shù)或該存儲過程。----函數(shù)：Createfunctionf_cj58(achar(20))returnsintAsBEGINDECLAREcintSELECTc=count(*)fromCJGL.dbo.*s_kca,CJGL.dbo.kcbwherea.課程號=b.課程號and課程名=aand(成績between58and59)ReturncENDGo以下函數(shù)調(diào)用：Selectdbo.f_cj58(‘數(shù)據(jù)庫原理’)as[58~59分人數(shù)]Go-----存儲過程：createprocedurep_cj58achar(20),cintoutputasSELECTc=count(*)fromCJGL.dbo.*s_kca,CJGL.dbo.kcbwherea.課程號=b.課程號and課程名=aand(成績between58and59)returnGo--存儲過程調(diào)用DECLAREc1intE*ecp_cj58‘數(shù)據(jù)庫原理’,c1output（3）創(chuàng)建存儲過程來完成求解給定*個學生的**返回該生的**和平均分的功能。Createprocedurep_pjsnochar(8),snachar(8)output,savgintoutputas. z.-SELECTsna=**,savg=avg(成績)fromCJGL.dbo.*sa,CJGL.dbo.*s_kcbwherea.**=b.**anda.**=snoGROUPBY**returnGo--調(diào)用DECLAREsnachar(8),savgintE*ecp_pj‘061101’,snaoutput,savgoutputSelectsna,savg（4）分別使用約束和觸發(fā)器實現(xiàn)以下功能：檢查在課程表中插入或修改的課程號是否101~108之間。---------------------------使用觸發(fā)器CreatetriggerT_KConkcForINSERT,UPDATEAsDeclareochar(4)Selecto=課程號frominsertedIfonotlike’10[1-8]’rollbacktransactiongo----------------------------使用約束AltertalbekcAddconstraintck_khcheck(課程號like’10[1-8]’)3．以下各題在第六章習題（設(shè)計題）給出的數(shù)據(jù)庫SCD中實現(xiàn)：（1）創(chuàng)建一個自定義函數(shù)用于統(tǒng)計各班級的人數(shù)，將class表增加一個人數(shù)字段，然后調(diào)用該函數(shù)將人數(shù)定義為計算列。USESCDGOCREATEFUNCTIONF_T(UMCHAR(8))RETURNSINTASBEGINDECLARETINTSELECTT=COUNT(*)FROMstudentWHERE班號=UMRETURNTENDGOUSESCD/*假定用戶函數(shù)F_T在此數(shù)據(jù)庫中已定義*/CREATETABLECLASS(班號CHAR(3)PRIMARYKEY,專業(yè)名CHAR(20),系名CHAR(20)REFERENCESDEPARTMENT(系名),入學年份INT人數(shù)ASDBO.F_T(班號). z.-)（2）創(chuàng)建一個可以按給定**輸出學生的**、系名信息的存儲過程。方法一：CREATEPROCEDURESTU_INFOSNOCHAR(7)ASSELECTA.**,**,系名FROMSTUDENTAINNERJOINCLASSBONA.班號=B.班號WHERE**=SNOGO調(diào)用：E*ECSTU_INFO'2008101'方法二：CREATEPROCEDURESTU_INFO_1SNOCHAR(6),SNACHAR(8)OUTPUT,DNACHAR(20)OUTPUTASSELECTSNA=**,DNA=系名FROMSTUDENTAINNERJOINCLASSBONA.班號=B.班號WHERE**=SNOGO調(diào)用：DECLARESNACHAR(8),DNACHAR(20)E*ECSTU_INFO'2008101',SNAOUTPUT,DNAOUTPUTSELECTSNA,DNA（3）創(chuàng)建一個觸發(fā)器，當插入或更新*個學生的記錄時，檢查該班級的學生是否超過人，如果超過，就發(fā)出警告信息同時撤消所作的插入或更新操作。CreatetriggerT_stuonstudentForINSERT,UPDATEAsDeclareochar(4)Selecto=班號frominsertedIf(selectcount(*) fromstudentwhere班號=o)>50beginRaiserror('該班人數(shù)超過人，插入或修改已撤銷.',16,1)RollbacktransactionendGO===================================================8第9章習題2在職員管理數(shù)據(jù)庫ZYGL中完成以下操作：1．創(chuàng)建一個自定義數(shù)據(jù)類型ID_type，用于描述員工號，執(zhí)行如下程序：. z.-USEZYGLE*ECsp_addtype'ID_type','char(3)','notnull'GO2．在SQLServer管理平臺中通過表設(shè)計器修改職員表和工資表，對它們的“員工號”的數(shù)據(jù)類型使用自定義數(shù)據(jù)類型ID_type。3．創(chuàng)建一個自定義函數(shù)用于統(tǒng)計*部門的人數(shù)，部門名為函數(shù)的輸入?yún)?shù)，如果沒有該部門，則返回-1值。USEZYGLCREATEFUNCTIONtotal_rs(name_bmchar(10))RETURNSintASBEGINDECLAREnumintIFnotE*ISTS(SELECT*FROM部門表WHERE部門名=name_bm)SELECTnum=-1ELSESELECTnum=count(*)FROM職員表,部門表WHERE職員表.部門號=部門表.部門號and部門名=name_bmRETURNnumEND4．分別以部門名“辦公室”和“銷售科”調(diào)用函數(shù)total_rs：USEZYGLSELECTdbo.total_rs('銷售科')AS部門人數(shù)記錄結(jié)果。SELECTdbo.total_rs('辦公室')AS部門人數(shù)記錄結(jié)果。5．創(chuàng)建一個可以按給定員工**，輸出其**、部門名及其工資信息的存儲過程。USEZYGLIFE*ISTS(SELECTnameFROMsysobjectsWHEREname='yg_info'ANDtype='P')DROPPROCEDUREyg_infoGO/*創(chuàng)建存儲過程*/CREATEPROCEDUREyg_infonamechar(8)ASSELECTa.員工號,**,部門名,應(yīng)發(fā)工資,實發(fā)工資FROM職員表aINNERJOIN部門表bONa.部門號=b.部門號INNERJOIN工資表tONa.員工號=t.員工號WHERE**=nameGO6．調(diào)用yg_info存儲過程查詢員工'岳亮'的信息。USEZYGLE*ECyg_info'岳亮'7．創(chuàng)建一個觸發(fā)器，當插入或更新*個員工的工資記錄時，檢查該員工的基本工資是否超過平均基本工資的三倍，如果超過，就發(fā)出警告信息同時撤消插入或更新。執(zhí)行如下程序：. z.-USEZYGLIFE*ISTS(SELECTnameFROMsysobjectsWHEREname='reminder_gz'ANDtype='TR')DROPTRIGGERreminder_gzGOCREATETRIGGERreminder_gzON工資表FORINSERT,UPDATEASDECLAREgzdecimal(7,2),pjgzdecimal(7,2)SELECTgz=基本工資FROMinsertedSELECTpjgz=avg(基本工資)FROM工資表IFgz>pjgz*3BEGINROLLBACKTRANSACTION--撤消插入操作RAISERROR('該工資數(shù)據(jù)有誤，超過平均值三倍，操作已經(jīng)撤消！',16,10)/*返回一個錯誤信息*/ENDGO8．在SQLServer管理平臺中打開ZYGL庫的工資表，將001號員工的基本工資分別修改為2600與8600，看觸發(fā)器的觸發(fā)反應(yīng)。===================================================910章習題1在職員管理數(shù)據(jù)庫ZYGL中完成以下操作：1．采用游標的方式將每位員工的津貼提高20％。注意津貼的變化對實發(fā)工資和應(yīng)發(fā)工資的影響。方法一：游標的非CURRENT形式的UPDATE語句USEZYGLGODECLAREidchar(3)DECLAREsalary_cursorCURSORFORSELECT員工號FROM工資表OPENsalary_cursorFETCHNE*TFROMsalary_cursorINTOidWHILEFETCH_STATUS=0BEGINUPDATE工資表SET津貼=津貼*1.2WHERE員工號=idFETCHNE*TFROMsalary_cursorINTOidENDCLOSEsalary_cursorDEALLOCATEsalary_cursorSELECT*FROM工資表GO方法二：游標的CURRENT形式的UPDATE語句USEZYGL. z.-GODECLAREsalary_cursorCURSORFORSELECT津貼FROM工資表OPENsalary_cursorFETCHNE*TFROMsalary_cursorWHILEFETCH_STATUS=0BEGINUPDATE工資表SET津貼=津貼*1.2WHERECURRENTOFsalary_cursorFETCHNE*TFROMsalary_cursorENDCLOSEsalary_cursorDEALLOCATEsalary_cursorSELECT*FROM工資表GO2．采用游標的方式查找每月實發(fā)工資最多的員工的**。USEZYGLGODECLAREidchar(3)DECLAREtemp_idchar(3)DECLAREsalarydecimal(9,2)DECLAREtemp_salarydecimal(9,2)DECLAREnamechar(8)DECLAREsalary_cursorCURSORFORSELECT員工號,實發(fā)工資FROM工資表OPENsalary_cursorFETCHNE*TFROMsalary_cursorINTOid,salarySETtemp_id=idSETtemp_salary=salaryWHILEFETCH_STATUS=0BEGINifsalary>temp_salaryBEGINSETtemp_salary=salarySETtemp_id=idENDFETCHNE*TFROMsalary_cursorINTOid,salaryENDCLOSEsalary_cursorDEALLOCATEsalary_cursorDECLAREstaff_cursorCURSORFORSELECT員工號,**FROM職員表OPENstaff_cursorFETCHNE*TFROMstaff_cursor. z.-INTOid,nameWHILEFETCH_STATUS=0BEGINifid=temp_idBEGINPRINT'實發(fā)工資最高的員工是'+nameBREAKENDFETCHNE*TFROMstaff_cursorINTOid,nameENDCLOSEstaff_cursorDEALLOCATEstaff_cursorGO================================================1010章習題21--事務(wù)回滾實驗啟動一個事務(wù)，向ZYGL數(shù)據(jù)庫中插入一名新的員工信息，然后回滾該事務(wù)，并驗證事務(wù)是否回滾。注意該事務(wù)涉及職員表和工資表這兩個表的操作，并注意這兩個表的操作順序。USEZYGLGOBEGINTRANSACTIONINSERTINTO職員表VALUES('009','李靖','男','1976-9-11INSERTINTO工資表(員工號,基本工資,津貼,三金扣款)VALUES('009',1800,720,490.5)SELECT*FROM職員表SELECT*FROM工資表ROLLBACKGOSELECT*FROM職員表SELECT*FROM工資表GO2--事務(wù)模式實驗--系統(tǒng)默認狀態(tài)：set*act_abortoff,setimplicit_transactionsoff--【1】-----顯式事務(wù)方式：usecjglcreatetable學生會(**nchar(4),性別nchar(1)check(性別in('男','女')),職務(wù)nchar(5))gobegintransactioninsert學生會(**,性別,職務(wù))values('王宏','男','主席')insert學生會(**,性別,職務(wù))values('*柳','女','副主席')insert學生會(**,性別,職務(wù))values('梅里','女','組織部長'). z.-rollback--回滾或MITgoselect*from學生會droptable學生會--【2】-----------隱式事務(wù)方式：setimplicit_transactionson --啟動隱性事務(wù)模式createtable學生會(**nchar(4),性別nchar(1)check(性別in('男','女')),職務(wù)nchar(5))goinsert學生會(**,性別,職務(wù))values('王宏','男','主席')insert學生會(**,性別,職務(wù))values('*柳','女','副主席')insert學生會(**,性別,職務(wù))values('梅里','女','組織部長')rollback--回滾或MITgoselect*from學生會droptable學生會--【3】----------自動提交事務(wù)方式：setimplicit_transactionsoffcreatetable學生會(**nchar(4),性別nchar(1)check(性別in('男','女')),職務(wù)nchar(5))goinsert學生會(**,性別,職務(wù)