數(shù)據(jù)庫上機習題及答案

1、數(shù)據(jù)庫及應用復習題一、設計題有一個 學生課程 數(shù)據(jù)庫,數(shù)據(jù)庫中包括三個表:學生表 Student 由學號 (Sno、姓名 (Sname、性別 (Ssex、年齡 (Sage、所 在系 (Sdept五個屬性組成,記為: Student(Sno,Sname,Ssex,Sage,Sdept , Sno 為關鍵字。課程表 Course 由課程號 (Cno、課程名 (Cname、先修課號 (Cpno、學分 (Ccredit四個屬性組成,記為:Course(Cno,Cname,Cpno,Ccredit Cno 為關鍵 字。成績表 SG 由學號 (Sno、課程號 (Cno、成績 (Grade三個屬性組成,記為

2、: SG(Sno,Cno,Grade (SNO, CNO為關鍵字。用 SQL 語言實現(xiàn)下列功能:1.建立學生表 Student ,其中學號屬性不能為空,并且其值是唯一的。2.向 Student 表增加“入學時間 (Scome”列,其數(shù)據(jù)類型為日期型。3.查詢選修了 3號課程的學生的學號及其成績,查詢結果按分數(shù)的降序排列。4.查詢學習 1號課程的學生最高分數(shù)、平均成績。5.查詢與“李洋”在同一個系學習的學生。6.將計算機系全體學生的成績置零。7.刪除學號為 05019的學生記錄。8.刪除計算機系所有學生的成績記錄。1.CREATETABLE Student(Sno CHAR(5 NOT NULL

3、 UNIQUE,Sname CHAR(20,Ssex CHAR(2,Sage INT,Sdept CHAR(152.ALTER TABLE Student ADD Scome DATETIME3.SELECT Sno, GradeFROM SGWHERE Cno='3'ORDER BY Grade DESC4.SELECT MAX(Grade, AVG(GradeFROM SCWHERE Cno='1'5.SELECT Sno, Sname, SdeptFROM StudentWHERE Sdept IN(SELECT Sdept FROM StudentWHE

4、RE Sname='李洋 '6. UPDATE SGSET Grade=0WHERE Sno in( SELECT Sno FROM StudentWHERE Sdept = '計算機系 '7. DELETE FROM StudentWHERE Sno='05019'8. DELETE FROM SGWHERE Sno in( SELECT Sno FROM StudentWHERE Sdept = '計算機系 '二、設計題現(xiàn)有關系數(shù)據(jù)庫如下:數(shù)據(jù)庫名:教師數(shù)據(jù)庫教師表 (編號 char(6,姓名,性別,民族,職稱,身份證號 課程

5、表 (課號 char(6,名稱 任課表 (ID,教師編號 , 課號,課時數(shù) 用 SQL 語言實現(xiàn)下列功能的 sql 語句代碼:1. 創(chuàng)建上述三表的建庫、建表代碼 (14分 ;要求使用:主鍵 (教師表 . 編號,課程表 . 課號 、外鍵 (任課表 . 教師編號, 任課表 . 課號 、 默認 (民族 、 非空 (民族, 姓名 、 唯一 (身份證號 、 檢查 (性 別、課時數(shù) , 自動編號 (ID2. 將下列課程信息添加到課程表的代碼 (6分 課號 課程名稱100001 SQL Server數(shù)據(jù)庫100002 數(shù)據(jù)結構100003 VB程序設計修改 課號為 100003的課程名稱:Visual Ba

6、sic程序設計刪除 課號為 100003的課程信息3. 寫出創(chuàng)建 任課表視圖 (教師編號,姓名,課號,課程名稱,課時數(shù) 的代 碼; (4分 4. 寫出創(chuàng)建 某門課任課教師 內嵌表值函數(shù)以及檢索的代碼; (6分 檢索:所有代 'SQL Server數(shù)據(jù)庫 ' 這門課程的老師姓名;5. 寫出創(chuàng)建 統(tǒng)計課時數(shù) :輸出最大課時數(shù)、最低課時數(shù)、平均課時的存儲 過程以及執(zhí)行代碼; (6分 6. 寫出創(chuàng)建:計算某教師代課總課時, 并將值返回的存儲過程以及執(zhí)行代碼。 (6分 執(zhí)行:計算“郭老師”的總課時。 (6分 7. 檢索有一門或一門以上課程課時數(shù)大于 90的所有教師的信息,包括編號、 姓名

7、。 (4分 8. 建一個規(guī)則,并將其綁定到教師表的職稱列上,規(guī)定取值為(' 教授 ',' 副教授 ',' 講師 ', '助教 ' 之一。 (4分 1.create database 教師數(shù)據(jù)庫 -(2分use 教師數(shù)據(jù)庫 gocreate table 教師表 -(6分(編號 char(6 primary key,姓名 nchar(4 not null,性別 nchar(1 check(性別 in ('男 ', '女 ',民族 nchar(8 default '漢族 ' not nul

8、l,職稱 nchar(12,身份證號 char(18 uniquecreate table 課程表 -(2分(課號 char(6 primary key,名稱 char(40 not nullcreate table 任課表 -(4分(ID IDENTITY(1, 1,教師編號 char(6 references 學生表 (學號 ,課號 char(6 references 課程表 (課號 ,課時數(shù) integer check(課時數(shù) between 0 and 2002.insert 課程表 values('100001', 'SQL Server數(shù)據(jù)庫 'in

9、sert 課程表 values('100002', '數(shù)據(jù)結構 'insert 課程表 values('100003', 'VB程序設計 'update 課 程 表 set 名 稱 ='Visual Basic 程 序 設 計 ' where 課 號 ='100003'delete 課程表 where 課號 ='100003'3.create view 任課表視圖 asselect 教師編號 , 姓名 , 課號 , 課程名稱 , 課時數(shù) from 教師表 , 任課表 where 教師

10、表 . 編號 =任課表 . 教師編號4.create function 某門課任課教師 (課程名 varchar(15returns table asreturn (select 課程名稱 , 課時數(shù) , 教師姓名 =姓名 from 任課表視圖 where 課程名 =課程名 goselect * from 某門課任課教師 ('SQL Server數(shù)據(jù)庫 '5.create procedure 統(tǒng)計課時數(shù) asselect 最大課時數(shù) =max(課時 ,最小課時數(shù) =min(課時 , 平均課時數(shù) =avg(課時 from 任課表goexecute 統(tǒng)計課時 6.create pr

11、ocedure 統(tǒng)計課時 教師名 nchar(16,asbegindeclare 總課時 intselect 總課時 =sum (課時 from 任課表視圖where 姓名 = 教師名endgoexecute 統(tǒng)計課時 '郭老師 '7.select 編號 , 姓名 from 教師表where 編號 in (select distinct 教師編號 from 任課表 where課時 數(shù) >=908.create rule zhicheng _ruleas zhicheng in ('教授 ',' 副教授 ',' 講師 ', &

12、#39;助教 'gosp_bindrule zhicheng_rule, '教師表 . 職稱 '三、設計題 (共 50分 現(xiàn)有關系數(shù)據(jù)庫如下:數(shù)據(jù)庫名:醫(yī)院數(shù)據(jù)庫醫(yī)生表 (編號,姓名,性別,出生日期,職稱 病人表 (編號,姓名,性別,民族,身份證號 病歷表 (ID,病人編號 , 醫(yī)生編號,病歷描述 用 SQL 語言實現(xiàn)下列功能的 sql 語句代碼:1. 創(chuàng)建上述三表的建庫、建表代碼;要求使用:主鍵 (師醫(yī)生表 . 編號,病人表 . 課號 、外鍵 (病歷表 . 醫(yī)生編號, 病歷表 . 病人課號 、非空 (職稱,姓名 、檢查 (性別 , 自動編號 (ID (16分 2. 將

13、下列醫(yī)生信息添加到醫(yī)生表的代碼編號 姓名 性別 出生日期 職稱100001 杜醫(yī)生 男 1963-5-18 副主任醫(yī)師100002 郭醫(yī)生 女 1950-7-26 副主任醫(yī)師100003 劉醫(yī)生 男 1973-9-18 醫(yī)師修改 編號為 100002的醫(yī)生職稱為主任醫(yī)師刪除 編號為 100003的醫(yī)生信息 (8分 3. 寫出創(chuàng)建:醫(yī)療表視圖 (醫(yī)生編號,姓名,病人姓名,病歷 的代碼; (4分 4. 寫出所有病人編號、姓名、病歷、以及病人所對應的醫(yī)生編號的查詢語句; (4分 7. 寫出創(chuàng)建:輸出某醫(yī)生(根據(jù)醫(yī)生編號即可看病人數(shù)存儲過程以及執(zhí) 行過程(要求輸入醫(yī)生姓名的參數(shù),輸出病人數(shù) (6分 。

14、8. 寫出查詢 1970年以前出生的醫(yī)生 (4分 。7. 檢索有病人的醫(yī)生信息。 (4分 8. 創(chuàng)建一個默認,并將其綁定到醫(yī)生表的成績職稱列上,默認值為“醫(yī)師” 。 (4分 1.create database 醫(yī)院數(shù)據(jù)庫 use 醫(yī)院數(shù)據(jù)庫 gocreate table 醫(yī)生表(編號 char(6 primary key,姓名 nchar(4 not null,性別 nchar(1 check(性別 in ('男 ', '女 ' ,出生日期 datetime ,職稱 char(18 not nullcreate table病人表(課號 char(6 primar

15、y key,姓名 nchar(4 not null,性別 nchar(1 check(性別 in ('男 ', '女 ' ,民族 nchar(10,身份證號 char(18 not nullcreate table病歷表(ID IDENTITY(1, 1,病人編號 char(6 references 病人表 (學號 ,醫(yī)生編號 char(6 references 醫(yī)生表 (編號 ,病歷描述 varchar(10002.insert 醫(yī)生表 values('100001', '杜醫(yī)生 ', '男 ', '19

16、63-5-18', '副 主任醫(yī)師 'insert 醫(yī)生表 values('100002', '郭醫(yī)生 ', '女 ', '1950-7-26', '副 主任醫(yī)師 'insert 醫(yī)生表 values('100003', '劉醫(yī)生 ', '男 ', '1973-9-18', '醫(yī) 師 'update 醫(yī)生表 set 職稱 ='主任醫(yī)師 ' where 編號 ='100002'del

17、ete 醫(yī)生表 where 編號 ='100003'3.create view 醫(yī)療表視圖 as select br.姓名,ys.姓名,yl.病歷 from 病人表 as br join 醫(yī)療表 as yl on br.編號 yl.病人編號 join 醫(yī)生表 as ys on yl.醫(yī)生編號=ys.編號 4 Select br.編號, br.姓名, br.病歷, yl.醫(yī)生編號 from 病人表 br left join 醫(yī)療表 yl on br.編號 = yl.病人編號 5 create procedure 某醫(yī)生看病人數(shù) 醫(yī)生編號 nchar(16, 人數(shù) int outp

18、ut as select 人數(shù)=count(病人編號 from 病人表 where 醫(yī)生編號 = 醫(yī)生編號 go declare TOTAL int execute 某醫(yī)生看病人數(shù) '100001', TOTAL 6 select * from 醫(yī)生表 where 出生日期<'1970-01-01' 9. select 編號, 姓名, 性別, 出生日期, 職稱 from 醫(yī)生表 where 編號 in (select 醫(yī)生編號 from 醫(yī)療表 8. create default zhicheng AS '醫(yī)師' go exec sp_bi

19、ndefault 'zhicheng', '醫(yī)生表.職稱' 四、設計題(共 50 分 設計題( 現(xiàn)有關系數(shù)據(jù)庫如下： 數(shù)據(jù)庫名：學生選課數(shù)據(jù)庫 學生表（學號，姓名，性別，年齡，專業(yè)） 課程表（課程號，課程名，學分，先行課程號） 選課表（ID, 學號，課程號，分數(shù)） 用 SQL 語言實現(xiàn)下列功能的 sql 語句代碼： 1. 創(chuàng)建數(shù)據(jù)庫學生選課數(shù)據(jù)庫代碼(2 分； 2. 創(chuàng)建課程表代碼(2 分； 課程表(課程號 char(6，課程名，學分，先行課程號 要求使用：主鍵(課程號、非空(課程名, 學分 3. 創(chuàng)建學生表代碼(6 分； 學生表(學號 char(6，姓名，性別

20、，年齡，專業(yè) 要求使用：主鍵(學號、非空(姓名,專業(yè)、檢查(性別 4. 創(chuàng)建選課表代碼(4 分； 選課表(ID,學號，課號，分數(shù) 要求使用： 外鍵(選課表.學號， 選課表.課程號、 檢查(分數(shù),自動編號(ID 5. 將下列課程信息添加到課程表的代碼(8 分 課程號 課程名 學分 先行課程號 100001 C 語言 2 100002 數(shù)據(jù)結構 2 100001 100003 數(shù)據(jù)庫原理 2 修改 課程號為 100003 的課程名：SQL 數(shù)據(jù)庫 刪除 課程號為 100002 的課程信息 6. 寫出創(chuàng)建：選課表視圖(學號，姓名，課程號，課程名，學分，分數(shù)的代 碼；(4 分 7. 寫出創(chuàng)建：某門課程

21、成績 內嵌表值函數(shù)以及檢索的代碼；(6 分 檢索：所有修 SQL 數(shù)據(jù)庫 這門學生的成績； 8. 寫出創(chuàng)建：某門課程高低均分 計算某門課程成績最高分、最低分、平均 分 存儲過程以及執(zhí)行的代碼；(6 分 執(zhí)行：所有修 SQL 數(shù)據(jù)庫 這門學生的最高分、最低分、平均分； 9. 檢索姓張的女同學的情況：姓名、學號、專業(yè)。(2 分 10.檢索有一門或一門以上課程成績小于 60 分的所有學生的信息，包括學號、 姓名。(4 分 1. create database 學生選課數(shù)據(jù)庫 go use 學生選課數(shù)據(jù)庫 go 2. create table 學生表 (學號 char(6 primary key， 姓

22、名 nchar(4 not null， 性別 nchar(1 check(性別 in ('男', '女'， 年齡 numeric(2 ， 專業(yè) char(18 not null 3. create table 課程表 (課程號 char(6 primary key， 課程名 char(40 not null， 學分 numeric(2 not null， 先行課程號 char(6 4. create table 選課表 (ID IDENTITY(1, 1, 學號 char(6 references 學生表(學號, 課程號 char(6 references 課程表(課程號, 分數(shù) integer check(分數(shù) between 0 and 100 5. insert 課程表 values('100001', 'C 語言'，2 insert 課程表 values('100002', '數(shù)據(jù)結構', 2, '100001' insert 課程表 values('100003', '數(shù)據(jù)庫原理', 2 update 課程表 set 課程名='SQL 數(shù)據(jù)庫' where 課程號='100