數(shù)據(jù)庫原理及應(yīng)用期末考試

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

2、 go  create  table  學(xué)生信息 （     學(xué)號(hào)  varchar(20)  NOT  NULL  PRIMARY  KEY,    姓名  varchar(20)  NOT  NULL ,    性別 

3、0;varchar(10)  NOT  NULL,    籍貫  varchar(20)  NOT  NULL,    班級(jí)編號(hào)  varchar(20)  NOT  NULL ) go  create  table  成績表 (     

4、  學(xué)號(hào)  varchar(20)  NOT  NULL  FOREIGN   KEY  REFERENCES  學(xué)生信息（學(xué)號(hào)）,     課程編號(hào) varchar(20)  NOT  NULL,     成績  numeric(5,2)  DEFAULT&

5、#160;(0)  CHECK(成績)=0  and  成績<=100) ,是否重修 varchar(8)  NOT  NULL ) go2寫出實(shí)現(xiàn)各查詢功能的SELECT語句。（1）統(tǒng)計(jì)班級(jí)編號(hào)為1002的學(xué)生人數(shù)。（2）查詢課程編號(hào)為“003”的課程成績高于該門課程平均分的學(xué)生信息。（3）在“學(xué)生信息”表中找出姓“王”的學(xué)生信息。（4）將“成績”表中的課程編號(hào)為“001”的成績增加10分。（5）列出每個(gè)同學(xué)的學(xué)號(hào)、姓名及選修課程的平均成績情況，沒

6、有選修的同學(xué)也列出。3創(chuàng)建一個(gè)名為stud_view2的視圖，該視圖可以用來查看每個(gè)學(xué)生的選修課程的門數(shù)和平均成績。create view stud_ view2 asselect count(*) as 課程門數(shù),avg(grade) as 平均成績 from成績表 group by 學(xué)號(hào)select * from stud_view2 4（1）在“教學(xué)管理”數(shù)據(jù)庫中創(chuàng)建一個(gè)帶參數(shù)的存儲(chǔ)過程proc_score。該存儲(chǔ)過程的作用是：當(dāng)輸入某門課程的課程名稱時(shí)，檢索該門課程成績，并將查

7、詢結(jié)果以“學(xué)號(hào)” ，“姓名” ，“課程名稱” ，“成績等級(jí)”輸出（成績劃分成四個(gè)等級(jí)：高于或等于85分者為優(yōu)秀，高于或等于75分者為良好，高于或等于60分者為及格，低于60分者為不及格）。（2）執(zhí)行存儲(chǔ)過程proc_score，查詢“003”課程的成績信息。二、 假設(shè)現(xiàn)有數(shù)據(jù)庫“學(xué)生信息”，其中包含“學(xué)生”、“課程”、“學(xué)期成績”三個(gè)表。表結(jié)構(gòu)如下：“學(xué)生”表結(jié)構(gòu)字段名數(shù)據(jù)類型長度是否為空約束學(xué)號(hào)int4否主鍵姓名char10否唯一約束性別char2是默認(rèn)約束專業(yè)char20是班級(jí)char12是備注text是“課程”表結(jié)構(gòu)課程（課號(hào)，課名，學(xué)分，先修課）“學(xué)期成績”表結(jié)構(gòu)選課（學(xué)號(hào)，課號(hào)，成績

8、）根據(jù)各題功能要求寫出相應(yīng)的T-SQL語句。1寫出在“學(xué)生信息”數(shù)據(jù)庫中創(chuàng)建“學(xué)生”表的T-SQL語句，并在建表的同時(shí)創(chuàng)建所需約束。約束要求如下： 將學(xué)號(hào)設(shè)置為主鍵，主鍵名為pk_xh；為姓名設(shè)置唯一約束，約束名為uk_xm；為性別添加默認(rèn)約束，約束名為df_xb,其值為“男” 。USE 學(xué)生信息                        &#

9、160;             GO CREATE TABLE 學(xué)生                               

10、;  (學(xué)號(hào) INT CONSTRAINT pk_xh PRIMARY KEY,               姓名 CHAR(10) NOT NULL CONSTRAINT uk_xm UNIQUE,        性別 CHAR

11、(2) NULL CONSTRAINT df_xb DEFAULT '男',       專業(yè) CHAR(20) NULL,      班級(jí) CHAR(12) NULL, 備注 TEXT NULL)2寫出實(shí)現(xiàn)各查詢功能的SELECT語句。（1）計(jì)算機(jī)專業(yè)不及格學(xué)生的學(xué)號(hào)和姓名。（2）選修了數(shù)據(jù)庫課程的學(xué)生的學(xué)號(hào)、姓名和成績，查詢結(jié)果

12、按分?jǐn)?shù)的降序排列。（3）在“課程”表中找出“課程名”中包含“計(jì)算機(jī)”三個(gè)字的課程。（4）將“課程”表中的課程編號(hào)為“001”的學(xué)分增加1學(xué)分；（5）列出每個(gè)同學(xué)的學(xué)號(hào)、姓名及選修課程的平均成績情況，沒有選修的同學(xué)也列出。3創(chuàng)建一個(gè)名為stud_view2的視圖，該視圖可以用來查看每個(gè)學(xué)生的選修課程的門數(shù)和平均成績。4（1）在“學(xué)生信息”數(shù)據(jù)庫中創(chuàng)建一個(gè)帶參數(shù)的存儲(chǔ)過程proc_score。該存儲(chǔ)過程的作用是：當(dāng)輸入某門課程的課程名稱時(shí)，檢索該門課程成績，并將查詢結(jié)果以“學(xué)號(hào)” ，“姓名” ，“課程名稱” ，“成績等級(jí)”輸出（成績劃分成四個(gè)等級(jí)：高于或等于85分者為優(yōu)秀，高于或等于75分者為良好

13、，高于或等于60分者為及格，低于60分者為不及格）。CREATE PROCEDURE proc_score KCMC CHAR(20) AS SELECT 學(xué)生.學(xué)號(hào),姓名,課程名稱,成績等級(jí)=CASE   WHEN 成績>=85 THEN '優(yōu)秀' WHEN 成績>=75 THEN '良好' WHEN 成績>=60 THEN '及格' WHEN 成績<60

14、0; THEN '不及格' （或：ELSE '不及格'） END FROM 學(xué)生 JOIN 學(xué)期成績 ON 學(xué)生.學(xué)號(hào)=學(xué)期成績.學(xué)號(hào)  JOIN 課程注冊 ON 課程注冊.課程代碼=學(xué)期成績.課程代碼 WHERE 課程名稱=KCMC  GO （2）執(zhí)行存儲(chǔ)過程proc_score，查詢“計(jì)算機(jī)網(wǎng)絡(luò)”課程的成績信息。EXEC  proc_scor

15、e 計(jì)算機(jī)網(wǎng)絡(luò)5定義一事務(wù)tran_stu，在學(xué)生“學(xué)期成績”表中，為所有成績高于50分的同學(xué)的成績增加10分，并提交事務(wù)。三、假設(shè)現(xiàn)有數(shù)據(jù)庫“學(xué)生信息”，其中包含“學(xué)生”、“課程注冊”、“學(xué)期成績”三個(gè)表。表結(jié)構(gòu)如下：“學(xué)生”表結(jié)構(gòu)：字段名數(shù)據(jù)類型長度是否為空約束學(xué)號(hào)int4否主鍵姓名char10否唯一約束性別char2是默認(rèn)約束專業(yè)char20是班級(jí)char12是備注text是裝訂線“課程注冊”表結(jié)構(gòu)：字段名數(shù)據(jù)類型長度是否為空約束課程代碼char4否主鍵課程名稱char20否“學(xué)期成績”表結(jié)構(gòu)：字段名數(shù)據(jù)類型長度是否為空約束學(xué)號(hào)int4否外鍵課程代碼char4否外鍵成績floa

16、t8是 根據(jù)各題功能要求寫出相應(yīng)的T-SQL語句。1寫出在“學(xué)生信息”數(shù)據(jù)庫中創(chuàng)建“學(xué)生”表的T-SQL語句，并在建表的同時(shí)創(chuàng)建所需約束。約束要求如下：將學(xué)號(hào)設(shè)置為主鍵，主鍵名為pk_xh；為姓名設(shè)置唯一約束，約束名為uk_xm；為性別添加默認(rèn)約束，約束名為df_xb,其值為“男”。USE 學(xué)生信息                        &

17、#160;             GO CREATE TABLE 學(xué)生                              

18、0;  (學(xué)號(hào) INT CONSTRAINT pk_xh PRIMARY KEY,           姓名 CHAR(10) NOT NULL CONSTRAINT uk_xm UNIQUE,        性別 CHAR(2) NULL CONS

19、TRAINT df_xb DEFAULT '男',       專業(yè) CHAR(20) NULL,      班級(jí) CHAR(12) NULL, 備注 TEXT NULL) 2完成以下查詢。(1)統(tǒng)計(jì)學(xué)生表中各班級(jí)的學(xué)生人數(shù)。SELECT 班級(jí),COUNT(*) AS 人數(shù)  FROM

20、0; 學(xué)生  GROUP  BY  班級(jí) (2) 查詢“軟件工程”課程成績高于該門課程平均分的學(xué)生信息。SELECT 學(xué)號(hào),課程名稱,成績    FROM 學(xué)期成績 JOIN 課程注冊  ON 學(xué)期成績.課程代碼=課程注冊.課程代碼 WHERE 課程名稱='軟件工程' AND 成績>  (SELECT AVG(成績)   &#

21、160; FROM 學(xué)期成績 JOIN 課程注冊  ON 學(xué)期成績.課程代碼=課程注冊.課程代碼      WHERE 課程名稱='軟件工程')(3)查詢男同學(xué)選修的課程號(hào)。(4)查詢至少選修課程號(hào)為“C01”和“C02”的學(xué)生學(xué)號(hào)。將所有學(xué)生的“01”課程的成績增加10分。3在“學(xué)生信息”數(shù)據(jù)庫中創(chuàng)建一個(gè)帶參數(shù)的存儲(chǔ)過程st_xsxx。該存儲(chǔ)過程的作用是：當(dāng)輸入某門課程的課程名稱時(shí)，檢索該門課程成績，并將查詢結(jié)果以“學(xué)號(hào)” ，“姓名” ，

22、“課程名稱” ，“成績等級(jí)”輸出（成績劃分成四個(gè)等級(jí)：高于或等于85分者為優(yōu)秀，高于或等于75分者為良好，高于或等于60分者為及格，低于60分者為不及格）。CREATE PROCEDURE st_xsxx  （1分） KCMC CHAR(20)   （1分） AS SELECT 學(xué)生.學(xué)號(hào),姓名,課程名稱,成績等級(jí)=CASE     WHEN 成績>=85 THEN '優(yōu)秀'

23、 WHEN 成績>=75 THEN '良好' WHEN 成績>=60 THEN '及格' WHEN 成績<60  THEN '不及格' （或：ELSE '不及格'） END FROM 學(xué)生 JOIN 學(xué)期成績 ON 學(xué)生.學(xué)號(hào)=學(xué)期成績.學(xué)號(hào)  JOIN 課程注冊&#

24、160;ON 課程注冊.課程代碼=學(xué)期成績.課程代碼 WHERE 課程名稱=KCMC （1分） GO四、現(xiàn)有關(guān)系數(shù)據(jù)庫如下： 數(shù)據(jù)庫名：教師數(shù)據(jù)庫教師表(編號(hào) char(6)，姓名，性別，民族，職稱，身份證號(hào)) 課程表(課號(hào) char(6)，名稱) 任課表(ID，教師編號(hào),課號(hào)，課時(shí)數(shù)) 用SQL語言實(shí)現(xiàn)下列功能的sql語句代碼：1 寫出創(chuàng)建數(shù)據(jù)庫代碼，創(chuàng)建教師表代碼； 要求使用：編號(hào)(主鍵)、默認(rèn)(民族“漢”)、民族和姓名 (非空)、身份證號(hào) (唯一)、性別(檢查約束：只能是男或女)。create database 教師數(shù)

25、據(jù)庫                 -（2分） use 教師數(shù)據(jù)庫 go create table 教師表                      

26、;    -（6分） (編號(hào) char(6) primary key, 姓名 nchar(4) not null, 性別 nchar(1) check(性別 in ('男', '女'), 民族 nchar(8) default '漢族' not null, 職稱 nchar(12),

27、0;身份證號(hào) char(18) unique )  create table 課程表                          -（2分） (課號(hào) char(6) primary key, 名稱 cha

28、r(40) not null )  create table 任課表                          -（4分） (ID IDENTITY(1, 1),  教師編號(hào) char(6) 

29、references 學(xué)生表(學(xué)號(hào)), 課號(hào) char(6) references 課程表(課號(hào)), 課時(shí)數(shù) integer check(課時(shí)數(shù) between 0 and 200) ) 2寫出下列課程記錄添加到課程表的代碼 課號(hào) 課程名稱 100001 SQL Server數(shù)據(jù)庫 100002 數(shù)據(jù)結(jié)構(gòu) 100003 VB程序設(shè)計(jì) 修改：把課號(hào)為100003的課程名稱改為“Visual Basic程序設(shè)計(jì)”；刪除：課號(hào)為100003的課程信息 inser

30、t 課程表 values('100001', 'SQL Server數(shù)據(jù)庫')  insert 課程表 values('100002', '數(shù)據(jù)結(jié)構(gòu)') insert 課程表 values('100003', 'VB程序設(shè)計(jì)') update 課程表 set 名稱='Visual Basic程序設(shè)計(jì)'

31、60;where 課號(hào)='100003' delete 課程表 where 課號(hào)='100003'3創(chuàng)建視圖：任課表 (教師編號(hào)，姓名，課號(hào)，課程名稱，課時(shí)數(shù))；create view 任課表視圖 as select 教師編號(hào),姓名,課號(hào), 課程名稱,課時(shí)數(shù) from 教師表,任課表 where 教師表.編號(hào)=任課表.教師編號(hào) 4查詢：所有教“SQL Server數(shù)據(jù)庫”這門課程的老師姓名；create&

32、#160;function 某門課任課教師 (課程名 varchar(15) returns table as return (select 課程名稱, 課時(shí)數(shù), 教師姓名=姓名 from 任課表視圖 where 課程名=課程名) go select * from 某門課任課教師('SQL Server數(shù)據(jù)庫')5查詢：最大課時(shí)數(shù)、最低課時(shí)數(shù)、平均課時(shí)的；create p

33、rocedure 統(tǒng)計(jì)課時(shí)數(shù) as select  最大課時(shí)數(shù)=max(課時(shí)) ,最小課時(shí)數(shù)=min(課時(shí)),平均課時(shí)數(shù)=avg(課時(shí)) from 任課表 go 6檢索有一門或一門以上課程課時(shí)數(shù)大于90的所有教師的信息，包括編號(hào)、姓名；select 編號(hào), 姓名 from 教師表 where編號(hào) in (select distinct 教師編號(hào) from 任課表 where課時(shí)

34、數(shù)>=90) 7修改教師表結(jié)構(gòu)：教師表的職稱列上，規(guī)定取值為（'教授'，'副教授'，'講師'， '助教'）之一。create rule zhicheng _rule as zhicheng  in ('教授','副教授','講師', '助教') go sp_bindrule zhicheng_rule, '教師表.職

35、稱' 8計(jì)算某教師代課總課時(shí)，并將值返回的存儲(chǔ)過程以及執(zhí)行代碼。執(zhí)行：計(jì)算“郭老師”的總課時(shí)。create procedure 統(tǒng)計(jì)課時(shí)  教師名 nchar(16), as begin    declare 總課時(shí) int     select 總課時(shí)=sum (課時(shí)) from 任課表視圖    where 

36、;姓名 = 教師名  end go execute 統(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é)生、班級(jí)的數(shù)據(jù)庫，關(guān)系模式為：學(xué)生表student（學(xué)號(hào)，姓名，性別，班級(jí)，生源地，高考成績，出生日期）班級(jí)表class（班級(jí)名，專業(yè)名，系號(hào)，入學(xué)年份）系表 department（系號(hào)，系名）使用SQL語句完成一下功能：1. 建立數(shù)據(jù)庫名為school并激活當(dāng)前新建的數(shù)據(jù)庫，要求主數(shù)據(jù)文件的邏輯文

37、件名為school，實(shí)際文件名為school.mdf，事務(wù)日志文件的邏輯文件名為school,，實(shí)際文件名分別為school.ldf，上述文件的初始容量均為5MB，最大容量均為20MB，遞增量均為1MB。create database school on primary ( name = school, filename = 'SCHOOL.mdf', SIZE = 5MB, MAXSIZE = 20MB,&#

38、160;filegrowth = 1MB) log on( name= school_log, filename =  SCHOOL_log.ldf', SIZE = 2MB, maxsize = 20MB, filegrowth = 10%) go2建立表student（學(xué)號(hào)，姓名，性別，班級(jí)，生源地，高考成績，出生日期）要求：（1）表要有主鍵（2）學(xué)生的性別只能取男或者女 （3）學(xué)生姓名不能為空

39、use SCHOOL create table student - 學(xué)生表定義 ( 學(xué)號(hào)char(10) primary key , - 主鍵為學(xué)號(hào) 姓名varchar(20) not null , - 姓名不能為空值 性別char(4) default '男' ,   - 性別默認(rèn)為男性 生源地var

40、char(12) , -生源地出生日期datetime , check(性別= '男') or (性別= '女')  - 性別必須為男性或女性 )3.在student表中插入一條記錄：（學(xué)號(hào)：8101，姓名:張三，性別：男，生源地：山東）insert into student values(8101,'張三','男','山東')4.根據(jù)student表完成以下的查詢功能（1）通

41、過對學(xué)生信息表的查詢，查詢出所有學(xué)生的學(xué)號(hào)，姓名和性別。select 學(xué)號(hào),姓名,性別 from student （2）通過對學(xué)生信息表的查詢，查詢來自福建的學(xué)生的記錄。select * from student where 生源地= '福建'（3）在查詢語句中使用聚合函數(shù)計(jì)算出所有學(xué)生總數(shù)（起別名為學(xué)生人數(shù)）和平均高考成績（起別名為平均成績）。select count(*) as '學(xué)生人數(shù)' from stud

42、ent （4）更新學(xué)號(hào)為8103的這位學(xué)生的姓名為張玲。update student set 姓名= '張玲'  where 學(xué)號(hào)= '8103'（5）查詢高考成績最高的前5條學(xué)生記錄。Select top 5 * from student order by 高考成績 desc（6）在學(xué)生表中統(tǒng)計(jì)班級(jí)列表及各班級(jí)的人數(shù)。Select 班級(jí),count(*) from

43、 student group by 班級(jí)（7）查詢高考成績在90到100之間的學(xué)生的學(xué)號(hào)、姓名、專業(yè)名。Select 學(xué)號(hào),姓名,專業(yè)名 from 學(xué)生表,班級(jí)表 where 學(xué)生表.班級(jí)=班級(jí)表.班級(jí)名 and 高考成績 between 90 and 100（8）刪除姓名為李飛的學(xué)生記錄；刪除數(shù)據(jù)表student。delete from student where 姓名= '李飛'

44、;六、已知邏輯結(jié)構(gòu)見下表，寫出各題的T-SQL語句。表readers（讀者信息表）結(jié)構(gòu)屬性名數(shù)據(jù)類型寬度小數(shù)位數(shù)編號(hào)（主碼）char8姓名char8讀者類型int2已借數(shù)量int2books表（圖書信息表）結(jié)構(gòu)屬性名數(shù)據(jù)類型寬度編號(hào)（主鍵）char15書名char42作者char8出版社char28出版日期datetime8定價(jià)（>0）decimal8borrowinf表（借閱信息表）結(jié)構(gòu)屬性名數(shù)據(jù)類型寬度讀者編號(hào)（外鍵）char8圖書編號(hào)（外鍵）char15借期datetime8還期datetime8主鍵（讀者編號(hào)，圖書編號(hào)，借期）視圖overdue（超期信息）列名對應(yīng)基本表的列名讀者編號(hào)Reaers.編號(hào)讀者姓名Readers.姓名圖書編號(hào)Books.書名書名Books.應(yīng)還日期Borrwinf.借期+readrtype期限1. 創(chuàng)建基本表books。CREATE TABLE books (  圖書編號(hào) CHA