綜合題一、1使用TransactSQL語言創(chuàng)建教學(xué)管理數(shù)據(jù)庫，創(chuàng)建兩個表學(xué)生信息（學(xué)號，姓名，性別，籍貫，班級編號）和成績表（學(xué)號，課程編號，成績，是否重修）。其中，學(xué)生信息表中學(xué)號為主鍵，其他信息都不允許為空。通過學(xué)號與成績表形成一個一對多的關(guān)系，成績表中的成績的默認值為0，但必須在0100之間。注：將學(xué)號設(shè)置為主鍵，主鍵名為pk_xh；為成績添加默認值約束，約束名為df_xb,其值為“0”；為成績添加check約束，約束名為誠懇ck_xb, 在0100之間。CREATEDATABASE教學(xué)管理use教學(xué)管理go createtable學(xué)生信息（ 學(xué)號varchar(20)NOTNULLPRIMARYKEY,姓名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成績<=100),是否重修varchar(8)NOTNULL)go2寫出實現(xiàn)各查詢功能的SELECT語句。（1）統(tǒng)計班級編號為1002的學(xué)生人數(shù)。（2）查詢課程編號為“003”的課程成績高于該門課程平均分的學(xué)生信息。（3）在“學(xué)生信息”表中找出姓“王”的學(xué)生信息。（4）將“成績”表中的課程編號為“001”的成績增加10分。（5）列出每個同學(xué)的學(xué)號、姓名及選修課程的平均成績情況，沒有選修的同學(xué)也列出。3創(chuàng)建一個名為stud_view2的視圖，該視圖可以用來查看每個學(xué)生的選修課程的門數(shù)和平均成績。createviewstud_ view2 asselectcount(*) as 課程門數(shù),avg(grade) as 平均成績from成績表groupby學(xué)號select*fromstud_view24（1）在“教學(xué)管理”數(shù)據(jù)庫中創(chuàng)建一個帶參數(shù)的存儲過程proc_score。該存儲過程的作用是：當輸入某門課程的課程名稱時，檢索該門課程成績，并將查詢結(jié)果以“學(xué)號” ，“姓名” ，“課程名稱” ，“成績等級”輸出（成績劃分成四個等級：高于或等于85分者為優(yōu)秀，高于或等于75分者為良好，高于或等于60分者為及格，低于60分者為不及格）。（2）執(zhí)行存儲過程proc_score，查詢“003”課程的成績信息。二、 假設(shè)現(xiàn)有數(shù)據(jù)庫“學(xué)生信息”，其中包含“學(xué)生”、“課程”、“學(xué)期成績”三個表。表結(jié)構(gòu)如下：“學(xué)生”表結(jié)構(gòu)字段名數(shù)據(jù)類型長度是否為空約束學(xué)號int4否主鍵姓名char10否唯一約束性別char2是默認約束專業(yè)char20是班級char12是備注text是“課程”表結(jié)構(gòu)課程（課號，課名，學(xué)分，先修課）“學(xué)期成績”表結(jié)構(gòu)選課（學(xué)號，課號，成績）根據(jù)各題功能要求寫出相應(yīng)的T-SQL語句。1寫出在“學(xué)生信息”數(shù)據(jù)庫中創(chuàng)建“學(xué)生”表的T-SQL語句，并在建表的同時創(chuàng)建所需約束。約束要求如下： 將學(xué)號設(shè)置為主鍵，主鍵名為pk_xh；為姓名設(shè)置唯一約束，約束名為uk_xm；為性別添加默認約束，約束名為df_xb,其值為“男” 。USE學(xué)生信息GOCREATETABLE學(xué)生(學(xué)號INTCONSTRAINTpk_xhPRIMARYKEY,姓名CHAR(10)NOTNULLCONSTRAINTuk_xmUNIQUE,性別CHAR(2)NULLCONSTRAINTdf_xbDEFAULT男,專業(yè)CHAR(20)NULL,班級CHAR(12)NULL,備注TEXTNULL)2寫出實現(xiàn)各查詢功能的SELECT語句。（1）計算機專業(yè)不及格學(xué)生的學(xué)號和姓名。（2）選修了數(shù)據(jù)庫課程的學(xué)生的學(xué)號、姓名和成績，查詢結(jié)果按分數(shù)的降序排列。（3）在“課程”表中找出“課程名”中包含“計算機”三個字的課程。（4）將“課程”表中的課程編號為“001”的學(xué)分增加1學(xué)分；（5）列出每個同學(xué)的學(xué)號、姓名及選修課程的平均成績情況，沒有選修的同學(xué)也列出。3創(chuàng)建一個名為stud_view2的視圖，該視圖可以用來查看每個學(xué)生的選修課程的門數(shù)和平均成績。4（1）在“學(xué)生信息”數(shù)據(jù)庫中創(chuàng)建一個帶參數(shù)的存儲過程proc_score。該存儲過程的作用是：當輸入某門課程的課程名稱時，檢索該門課程成績，并將查詢結(jié)果以“學(xué)號” ，“姓名” ，“課程名稱” ，“成績等級”輸出（成績劃分成四個等級：高于或等于85分者為優(yōu)秀，高于或等于75分者為良好，高于或等于60分者為及格，低于60分者為不及格）。CREATE PROCEDURE proc_score KCMC CHAR(20) AS SELECT 學(xué)生.學(xué)號,姓名,課程名稱,成績等級=CASEWHEN成績>=85THEN優(yōu)秀WHEN成績>=75THEN良好WHEN成績>=60THEN及格WHEN成績<60THEN不及格（或：ELSE不及格）ENDFROM學(xué)生JOIN學(xué)期成績ON學(xué)生.學(xué)號=學(xué)期成績.學(xué)號JOIN課程注冊O(shè)N課程注冊.課程代碼=學(xué)期成績.課程代碼WHERE課程名稱=KCMCGO（2）執(zhí)行存儲過程proc_score，查詢“計算機網(wǎng)絡(luò)”課程的成績信息。EXEC proc_score計算機網(wǎng)絡(luò)5定義一事務(wù)tran_stu，在學(xué)生“學(xué)期成績”表中，為所有成績高于50分的同學(xué)的成績增加10分，并提交事務(wù)。三、假設(shè)現(xiàn)有數(shù)據(jù)庫“學(xué)生信息”，其中包含“學(xué)生”、“課程注冊”、“學(xué)期成績”三個表。表結(jié)構(gòu)如下：“學(xué)生”表結(jié)構(gòu)：字段名數(shù)據(jù)類型長度是否為空約束學(xué)號int4否主鍵姓名char10否唯一約束性別char2是默認約束專業(yè)char20是班級char12是備注text是裝訂線“課程注冊”表結(jié)構(gòu)：字段名數(shù)據(jù)類型長度是否為空約束課程代碼char4否主鍵課程名稱char20否“學(xué)期成績”表結(jié)構(gòu)：字段名數(shù)據(jù)類型長度是否為空約束學(xué)號int4否外鍵課程代碼char4否外鍵成績float8是 根據(jù)各題功能要求寫出相應(yīng)的T-SQL語句。1寫出在“學(xué)生信息”數(shù)據(jù)庫中創(chuàng)建“學(xué)生”表的T-SQL語句，并在建表的同時創(chuàng)建所需約束。約束要求如下：將學(xué)號設(shè)置為主鍵，主鍵名為pk_xh；為姓名設(shè)置唯一約束，約束名為uk_xm；為性別添加默認約束，約束名為df_xb,其值為“男”。USE學(xué)生信息GOCREATETABLE學(xué)生(學(xué)號INTCONSTRAINTpk_xhPRIMARYKEY,姓名CHAR(10)NOTNULLCONSTRAINTuk_xmUNIQUE,性別CHAR(2)NULLCONSTRAINTdf_xbDEFAULT男,專業(yè)CHAR(20)NULL,班級CHAR(12)NULL,備注TEXTNULL)2完成以下查詢。(1)統(tǒng)計學(xué)生表中各班級的學(xué)生人數(shù)。SELECT班級,COUNT(*)AS人數(shù)FROM 學(xué)生 GROUP BY 班級(2) 查詢“軟件工程”課程成績高于該門課程平均分的學(xué)生信息。SELECT學(xué)號,課程名稱,成績FROM學(xué)期成績JOIN課程注冊O(shè)N學(xué)期成績.課程代碼=課程注冊.課程代碼WHERE課程名稱=軟件工程AND成績>(SELECTAVG(成績)FROM學(xué)期成績JOIN課程注冊O(shè)N學(xué)期成績.課程代碼=課程注冊.課程代碼WHERE課程名稱=軟件工程)(3)查詢男同學(xué)選修的課程號。(4)查詢至少選修課程號為“C01”和“C02”的學(xué)生學(xué)號。將所有學(xué)生的“01”課程的成績增加10分。3在“學(xué)生信息”數(shù)據(jù)庫中創(chuàng)建一個帶參數(shù)的存儲過程st_xsxx。該存儲過程的作用是：當輸入某門課程的課程名稱時，檢索該門課程成績，并將查詢結(jié)果以“學(xué)號” ，“姓名” ，“課程名稱” ，“成績等級”輸出（成績劃分成四個等級：高于或等于85分者為優(yōu)秀，高于或等于75分者為良好，高于或等于60分者為及格，低于60分者為不及格）。CREATEPROCEDUREst_xsxx（1分）KCMCCHAR(20)（1分）ASSELECT學(xué)生.學(xué)號,姓名,課程名稱,成績等級=CASEWHEN成績>=85THEN優(yōu)秀WHEN成績>=75THEN良好WHEN成績>=60THEN及格WHEN成績<60THEN不及格（或：ELSE不及格）ENDFROM學(xué)生JOIN學(xué)期成績ON學(xué)生.學(xué)號=學(xué)期成績.學(xué)號JOIN課程注冊O(shè)N課程注冊.課程代碼=學(xué)期成績.課程代碼WHERE課程名稱=KCMC（1分）GO四、現(xiàn)有關(guān)系數(shù)據(jù)庫如下： 數(shù)據(jù)庫名：教師數(shù)據(jù)庫教師表(編號 char(6)，姓名，性別，民族，職稱，身份證號) 課程表(課號 char(6)，名稱) 任課表(ID，教師編號,課號，課時數(shù)) 用SQL語言實現(xiàn)下列功能的sql語句代碼：1 寫出創(chuàng)建數(shù)據(jù)庫代碼，創(chuàng)建教師表代碼； 要求使用：編號(主鍵)、默認(民族“漢”)、民族和姓名 (非空)、身份證號 (唯一)、性別(檢查約束：只能是男或女)。createdatabase教師數(shù)據(jù)庫-（2分）use教師數(shù)據(jù)庫gocreatetable教師表-（6分）(編號char(6)primarykey,姓名nchar(4)notnull,性別nchar(1)check(性別in(男,女),民族nchar(8)default漢族notnull,職稱nchar(12),身份證號char(18)unique)createtable課程表-（2分）(課號char(6)primarykey,名稱char(40)notnull)createtable任課表-（4分）(IDIDENTITY(1,1),教師編號char(6)references學(xué)生表(學(xué)號),課號char(6)references課程表(課號),課時數(shù)integercheck(課時數(shù)between0and200)2寫出下列課程記錄添加到課程表的代碼 課號 課程名稱 100001 SQL Server數(shù)據(jù)庫 100002 數(shù)據(jù)結(jié)構(gòu) 100003 VB程序設(shè)計 修改：把課號為100003的課程名稱改為“Visual Basic程序設(shè)計”；刪除：課號為100003的課程信息 insert課程表values(100001,SQLServer數(shù)據(jù)庫)insert課程表values(100002,數(shù)據(jù)結(jié)構(gòu))insert課程表values(100003,VB程序設(shè)計)update課程表set名稱=VisualBasic程序設(shè)計where課號=100003delete課程表where課號=1000033創(chuàng)建視圖：任課表 (教師編號，姓名，課號，課程名稱，課時數(shù))；createview任課表視圖asselect教師編號,姓名,課號,課程名稱,課時數(shù)from教師表,任課表where教師表.編號=任課表.教師編號4查詢：所有教“SQL Server數(shù)據(jù)庫”這門課程的老師姓名；createfunction某門課任課教師 (課程名varchar(15)returnstableasreturn(select課程名稱,課時數(shù),教師姓名=姓名from任課表視圖where課程名=課程名)goselect*from某門課任課教師(SQLServer數(shù)據(jù)庫)5查詢：最大課時數(shù)、最低課時數(shù)、平均課時的；createprocedure統(tǒng)計課時數(shù)asselect最大課時數(shù)=max(課時),最小課時數(shù)=min(課時),平均課時數(shù)=avg(課時)from任課表go6檢索有一門或一門以上課程課時數(shù)大于90的所有教師的信息，包括編號、姓名；select編號,姓名from教師表where編號in(selectdistinct教師編號from任課表where課時數(shù)>=90)7修改教師表結(jié)構(gòu)：教師表的職稱列上，規(guī)定取值為（教授，副教授，講師， 助教）之一。createrulezhicheng_ruleaszhichengin(教授,副教授,講師,助教)gosp_bindrulezhicheng_rule,教師表.職稱8計算某教師代課總課時，并將值返回的存儲過程以及執(zhí)行代碼。執(zhí)行：計算“郭老師”的總課時。createprocedure統(tǒng)計課時教師名nchar(16),asbegindeclare總課時intselect總課時=sum(課時)from任課表視圖where姓名=教師名endgoexecute統(tǒng)計課時郭老師五、現(xiàn)在要為學(xué)校建立一個數(shù)據(jù)庫，設(shè)學(xué)校的環(huán)境如下：一個系有若干個專業(yè)，每個專業(yè)一年只招一個班，每個班有若干個學(xué)生，現(xiàn)要建立關(guān)于系、學(xué)生、班級的數(shù)據(jù)庫，關(guān)系模式為：學(xué)生表student（學(xué)號，姓名，性別，班級，生源地，高考成績，出生日期）班級表class（班級名，專業(yè)名，系號，入學(xué)年份）系表 department（系號，系名）使用SQL語句完成一下功能：1. 建立數(shù)據(jù)庫名為school并激活當前新建的數(shù)據(jù)庫，要求主數(shù)據(jù)文件的邏輯文件名為school，實際文件名為school.mdf，事務(wù)日志文件的邏輯文件名為school,，實際文件名分別為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é)號，姓名，性別，班級，生源地，高考成績，出生日期）要求：（1）表要有主鍵（2）學(xué)生的性別只能取男或者女 （3）學(xué)生姓名不能為空useSCHOOLcreatetablestudent-學(xué)生表定義(學(xué)號char(10)primarykey,-主鍵為學(xué)號姓名varchar(20)notnull,-姓名不能為空值性別char(4)default男,-性別默認為男性生源地varchar(12),-生源地出生日期datetime,check(性別=男)or(性別=女) -性別必須為男性或女性)3.在student表中插入一條記錄：（學(xué)號：8101，姓名:張三，性別：男，生源地：山東）insertintostudentvalues(8101,張三,男,山東)4.根據(jù)student表完成以下的查詢功能（1）通過對學(xué)生信息表的查詢，查詢出所有學(xué)生的學(xué)號，姓名和性別。select學(xué)號,姓名,性別fromstudent（2）通過對學(xué)生信息表的查詢，查詢來自福建的學(xué)生的記錄。select*fromstudentwhere生源地=福建（3）在查詢語句中使用聚合函數(shù)計算出所有學(xué)生總數(shù)（起別名為學(xué)生人數(shù)）和平均高考成績（起別名為平均成績）。selectcount(*)as學(xué)生人數(shù)fromstudent（4）更新學(xué)號為8103的這位學(xué)生的姓名為張玲。updatestudentset姓名=張玲where學(xué)號=8103（5）查詢高考成績最高的前5條學(xué)生記錄。Selecttop5*fromstudentorderby高考成績desc（6）在學(xué)生表中統(tǒng)計班級列表及各班級的人數(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)屬性名數(shù)據(jù)類型寬度小數(shù)位數(shù)編號（主碼）char8姓名char8讀者類型int2已借數(shù)量int2books表（圖書信息表）結(jié)構(gòu)屬性名數(shù)據(jù)類型寬度編號（主鍵）char15書名char42作者char8出版社char28出版日期datetime8定價（>0）decimal8borrowinf表（借閱信息表）結(jié)構(gòu)屬性名數(shù)據(jù)類型寬度讀者編號（外鍵）char8圖書編號（外鍵）char15借期datetime8還期datetime8主鍵（讀者編號，圖書編號，借期）視圖overdue（超期信息）列名對應(yīng)基本表的列名讀者編號Reaers.編號讀者姓名Readers.姓名圖書編號Books.書名書名Books.應(yīng)還日期Borrwinf.借期+readrtype期限1. 創(chuàng)建基本表books。CREATETABLEbooks(圖書編號CHAR(15)PRIMARYKEY,書名CHAR(42),作者CHAR(8),出版社CHAR(28),出版日期DATETIME,價格float(8);2. 創(chuàng)建基本表borrowinf。CREATETABLEborrowinf(讀者編號CHAR(10),圖書編號CHAR(15),借期DATETIME,還期DATETIME,PRIMARYKEY(讀者編號,圖書編號),FOREIGNKEY(讀者編號)REFERENCESreaders(讀者編號),FOREIGNKEY(圖書編號)REFERENCESbooks(圖書編號);3. 創(chuàng)建視圖overdue（超期信息）。Create view overdue as SELECT 圖書編號 FROM borrowinf WHERE 還期>04. 查詢姓名為王立群的讀者的借閱信息。SELECT圖書編號,借期,還期FROMborrowinfWHERE讀者編號IN(SELECT讀者編號FROMreadersWHERE姓名=王立5. 查詢書名中包含文字程序設(shè)計的圖書信息。SELECT * FROM books WHERE 書名 LIKE%程序設(shè)計6. 查詢各出版社的館藏書數(shù)量。SELECT COUNT(DISTINCT 圖書編號) FROM 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)建存儲過程計算用戶指定圖書的價格，并將價格作為返回值。再執(zhí)行調(diào)用該存儲過程的命令。USE圖書管理GoCREATEPROCEDUREproc_BooksPricesmchar(42),djfloatoutputAsSELECTdj=定價FROMbooksWHERE書名=smGo調(diào)用存儲過程：DeclarepricefloatEXECproc_BooksPrice計算機文化基礎(chǔ),priceoutput SELECTpriceGO