《數(shù)據(jù)庫原理及應(yīng)用期末考試》由會員分享，可在線閱讀，更多相關(guān)《數(shù)據(jù)庫原理及應(yīng)用期末考試（13頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、綜合題一、1使用TransactSQL語言創(chuàng)建教學(xué)管理數(shù)據(jù)庫，創(chuàng)建兩個(gè)表學(xué)生信息（學(xué)號，姓名，性別，籍貫，班級編號）和成績表（學(xué)號，課程編號，成績，是否重修）。其中，學(xué)生信息表中學(xué)號為主鍵，其他信息都不允許為空。通過學(xué)號與成績表形成一個(gè)一對多的關(guān)系，成績表中的成績的默認(rèn)值為0，但必須在0100之間。注：將學(xué)號設(shè)置為主鍵，主鍵名為pk_xh；為成績添加默認(rèn)值約束，約束名為df_xb,其值為“0”；為成績添加check約束，約束名為誠懇ck_xb, 在0100之間。CREATEDATABASE教學(xué)管理use教學(xué)管理go createtable學(xué)生信息（ 學(xué)號varchar(20)NOTNULLPR

2、IMARYKEY,姓名varchar(20)NOTNULL,性別varchar(10)NOTNULL,籍貫varchar(20)NOTNULL,班級編號varchar(20)NOTNULL)go createtable成績表( 學(xué)號varchar(20)NOTNULLFOREIGNKEYREFERENCES學(xué)生信息（學(xué)號）, 課程編號varchar(20)NOTNULL, 成績numeric(5,2)DEFAULT(0)CHECK(成績)=0and成績=85THEN優(yōu)秀WHEN成績=75THEN良好WHEN成績=60THEN及格WHEN成績(SELECTAVG(成績)FROM學(xué)期成績JOIN課

3、程注冊O(shè)N學(xué)期成績.課程代碼=課程注冊.課程代碼WHERE課程名稱=軟件工程)(3)查詢男同學(xué)選修的課程號。(4)查詢至少選修課程號為“C01”和“C02”的學(xué)生學(xué)號。將所有學(xué)生的“01”課程的成績增加10分。3在“學(xué)生信息”數(shù)據(jù)庫中創(chuàng)建一個(gè)帶參數(shù)的存儲過程st_xsxx。該存儲過程的作用是：當(dāng)輸入某門課程的課程名稱時(shí)，檢索該門課程成績，并將查詢結(jié)果以“學(xué)號” ，“姓名” ，“課程名稱” ，“成績等級”輸出（成績劃分成四個(gè)等級：高于或等于85分者為優(yōu)秀，高于或等于75分者為良好，高于或等于60分者為及格，低于60分者為不及格）。CREATEPROCEDUREst_xsxx（1分）KCMCCHA

4、R(20)（1分）ASSELECT學(xué)生.學(xué)號,姓名,課程名稱,成績等級=CASEWHEN成績=85THEN優(yōu)秀WHEN成績=75THEN良好WHEN成績=60THEN及格WHEN成績=90)7修改教師表結(jié)構(gòu)：教師表的職稱列上，規(guī)定取值為（教授，副教授，講師， 助教）之一。createrulezhicheng_ruleaszhichengin(教授,副教授,講師,助教)gosp_bindrulezhicheng_rule,教師表.職稱8計(jì)算某教師代課總課時(shí)，并將值返回的存儲過程以及執(zhí)行代碼。執(zhí)行：計(jì)算“郭老師”的總課時(shí)。createprocedure統(tǒng)計(jì)課時(shí)教師名nchar(16),asbegi

5、ndeclare總課時(shí)intselect總課時(shí)=sum(課時(shí))from任課表視圖where姓名=教師名endgoexecute統(tǒng)計(jì)課時(shí)郭老師五、現(xiàn)在要為學(xué)校建立一個(gè)數(shù)據(jù)庫，設(shè)學(xué)校的環(huán)境如下：一個(gè)系有若干個(gè)專業(yè)，每個(gè)專業(yè)一年只招一個(gè)班，每個(gè)班有若干個(gè)學(xué)生，現(xiàn)要建立關(guān)于系、學(xué)生、班級的數(shù)據(jù)庫，關(guān)系模式為：學(xué)生表student（學(xué)號，姓名，性別，班級，生源地，高考成績，出生日期）班級表class（班級名，專業(yè)名，系號，入學(xué)年份）系表 department（系號，系名）使用SQL語句完成一下功能：1. 建立數(shù)據(jù)庫名為school并激活當(dāng)前新建的數(shù)據(jù)庫，要求主數(shù)據(jù)文件的邏輯文件名為school，實(shí)際文件

6、名為school.mdf，事務(wù)日志文件的邏輯文件名為school,，實(shí)際文件名分別為school.ldf，上述文件的初始容量均為5MB，最大容量均為20MB，遞增量均為1MB。createdatabaseschoolonprimary(name=school,filename=SCHOOL.mdf,SIZE=5MB,MAXSIZE=20MB,filegrowth=1MB)logon(name=school_log,filename= SCHOOL_log.ldf,SIZE=2MB,maxsize=20MB,filegrowth=10%)go2建立表student（學(xué)號，姓名，性別，班級，生源地

7、，高考成績，出生日期）要求：（1）表要有主鍵（2）學(xué)生的性別只能取男或者女 （3）學(xué)生姓名不能為空useSCHOOLcreatetablestudent-學(xué)生表定義(學(xué)號char(10)primarykey,-主鍵為學(xué)號姓名varchar(20)notnull,-姓名不能為空值性別char(4)default男,-性別默認(rèn)為男性生源地varchar(12),-生源地出生日期datetime,check(性別=男)or(性別=女) -性別必須為男性或女性)3.在student表中插入一條記錄：（學(xué)號：8101，姓名:張三，性別：男，生源地：山東）insertintostudentvalues(8

8、101,張三,男,山東)4.根據(jù)student表完成以下的查詢功能（1）通過對學(xué)生信息表的查詢，查詢出所有學(xué)生的學(xué)號，姓名和性別。select學(xué)號,姓名,性別fromstudent（2）通過對學(xué)生信息表的查詢，查詢來自福建的學(xué)生的記錄。select*fromstudentwhere生源地=福建（3）在查詢語句中使用聚合函數(shù)計(jì)算出所有學(xué)生總數(shù)（起別名為學(xué)生人數(shù)）和平均高考成績（起別名為平均成績）。selectcount(*)as學(xué)生人數(shù)fromstudent（4）更新學(xué)號為8103的這位學(xué)生的姓名為張玲。updatestudentset姓名=張玲where學(xué)號=8103（5）查詢高考成績最高的前

9、5條學(xué)生記錄。Selecttop5*fromstudentorderby高考成績desc（6）在學(xué)生表中統(tǒng)計(jì)班級列表及各班級的人數(shù)。Select班級,count(*)fromstudentgroupby班級（7）查詢高考成績在90到100之間的學(xué)生的學(xué)號、姓名、專業(yè)名。Select學(xué)號,姓名,專業(yè)名from學(xué)生表,班級表where學(xué)生表.班級=班級表.班級名and高考成績between90and100（8）刪除姓名為李飛的學(xué)生記錄；刪除數(shù)據(jù)表student。deletefromstudentwhere姓名=李飛六、已知邏輯結(jié)構(gòu)見下表，寫出各題的T-SQL語句。表readers（讀者信息表）結(jié)構(gòu)

10、屬性名數(shù)據(jù)類型寬度小數(shù)位數(shù)編號（主碼）char8姓名char8讀者類型int2已借數(shù)量int2books表（圖書信息表）結(jié)構(gòu)屬性名數(shù)據(jù)類型寬度編號（主鍵）char15書名char42作者char8出版社char28出版日期datetime8定價(jià)（0）decimal8borrowinf表（借閱信息表）結(jié)構(gòu)屬性名數(shù)據(jù)類型寬度讀者編號（外鍵）char8圖書編號（外鍵）char15借期datetime8還期datetime8主鍵（讀者編號，圖書編號，借期）視圖overdue（超期信息）列名對應(yīng)基本表的列名讀者編號Reaers.編號讀者姓名Readers.姓名圖書編號Books.書名書名Books.應(yīng)還

11、日期Borrwinf.借期+readrtype期限1. 創(chuàng)建基本表books。CREATETABLEbooks(圖書編號CHAR(15)PRIMARYKEY,書名CHAR(42),作者CHAR(8),出版社CHAR(28),出版日期DATETIME,價(jià)格float(8);2. 創(chuàng)建基本表borrowinf。CREATETABLEborrowinf(讀者編號CHAR(10),圖書編號CHAR(15),借期DATETIME,還期DATETIME,PRIMARYKEY(讀者編號,圖書編號),FOREIGNKEY(讀者編號)REFERENCESreaders(讀者編號),FOREIGNKEY(圖書編號

12、)REFERENCESbooks(圖書編號);3. 創(chuàng)建視圖overdue（超期信息）。Create view overdue as SELECT 圖書編號 FROM borrowinf WHERE 還期04. 查詢姓名為王立群的讀者的借閱信息。SELECT圖書編號,借期,還期FROMborrowinfWHERE讀者編號IN(SELECT讀者編號FROMreadersWHERE姓名=王立5. 查詢書名中包含文字程序設(shè)計(jì)的圖書信息。SELECT * FROM books WHERE 書名 LIKE%程序設(shè)計(jì)6. 查詢各出版社的館藏書數(shù)量。SELECT COUNT(DISTINCT 圖書編號) F

13、ROM books7. 查詢2004-1-1和2004-12-31之間作者為梁曉峰的圖書借閱情況。SELECT * FROM borrowinf WHERE 借期2007-1-1AND 還期 2007-12-31 AND 圖書編號 IN (SELECT 圖書編號 FROM books WHERE 作者=梁曉峰)8.創(chuàng)建存儲過程計(jì)算用戶指定圖書的價(jià)格，并將價(jià)格作為返回值。再執(zhí)行調(diào)用該存儲過程的命令。USE圖書管理GoCREATEPROCEDUREproc_BooksPricesmchar(42),djfloatoutputAsSELECTdj=定價(jià)FROMbooksWHERE書名=smGo調(diào)用存儲過程：DeclarepricefloatEXECproc_BooksPrice計(jì)算機(jī)文化基礎(chǔ),priceoutput SELECTpriceGO