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

數(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)三個屬性組成，記為： 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刪除計算機系所有學生的成績記錄。1CREATETABLE Student (Sno CHAR(5) NOT NULL UNIQUE,Sname CHAR(20),Ssex CHAR(2),Sage INT,Sdept CHAR(15)2ALTER TABLE Student ADD Scome DATETIME3 SELECT Sno, GradeFROM SGWHERE Cno=3 ORDER BY Grade DESC4 SELECT MAX(Grade), AVG(Grade)FROM SC WHERE Cno=15 SELECT Sno, Sname, SdeptFROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname=李洋)6 UPDATE SG SET Grade=0 WHERE Sno in( SELECT Sno FROM Student WHERE Sdept = 計算機系)7DELETE FROM StudentWHERE Sno=050198 DELETE FROM SG WHERE Sno in( SELECT Sno FROM Student WHERE Sdept = 計算機系)二、設計題 現(xiàn)有關系數(shù)據(jù)庫如下： 數(shù)據(jù)庫名：教師數(shù)據(jù)庫教師表(編號 char(6)，姓名，性別，民族，職稱，身份證號) 課程表(課號 char(6)，名稱) 任課表(ID，教師編號,課號，課時數(shù)) 用SQL語言實現(xiàn)下列功能的sql語句代碼：1.創(chuàng)建上述三表的建庫、建表代碼(14分)； 要求使用：主鍵(教師表.編號，課程表.課號)、外鍵(任課表.教師編號，任課表.課號)、默認(民族)、非空(民族，姓名)、唯一(身份證號)、檢查(性別、課時數(shù)),自動編號(ID)2.將下列課程信息添加到課程表的代碼(6分) 課號 課程名稱 100001 SQL Server數(shù)據(jù)庫 100002 數(shù)據(jù)結構 100003 VB程序設計 修改 課號為100003的課程名稱：Visual Basic程序設計 刪除 課號為100003的課程信息 3.寫出創(chuàng)建任課表視圖(教師編號，姓名，課號，課程名稱，課時數(shù))的代碼；(4分)4.寫出創(chuàng)建某門課任課教師內(nèi)嵌表值函數(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的所有教師的信息，包括編號、姓名。(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 null,職稱 nchar(12),身份證號 char(18) unique) create table 課程表 -（2分）(課號 char(6) primary key,名稱 char(40) not null) create table 任課表 -（4分）(ID IDENTITY(1, 1), 教師編號 char(6) references 學生表(學號),課號 char(6) references 課程表(課號),課時數(shù) integer check(課時數(shù) between 0 and 200) 2.insert 課程表 values(100001, SQL Server數(shù)據(jù)庫) insert 課程表 values(100002, 數(shù)據(jù)結構)insert 課程表 values(100003, VB程序設計)update 課程表 set 名稱=Visual Basic程序設計 where 課號=100003delete 課程表 where 課號=1000033.create view 任課表視圖 asselect 教師編號,姓名,課號, 課程名稱,課時數(shù) from 教師表,任課表where 教師表.編號=任課表.教師編號4. create function 某門課任課教師(課程名 varchar(15)returns 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 procedure 統(tǒng)計課時 教師名 nchar(16),asbegin declare 總課時 int select 總課時=sum (課時) from 任課表視圖 where 姓名 = 教師名 endgoexecute 統(tǒng)計課時 郭老師7. select 編號, 姓名 from 教師表where編號 in (select distinct 教師編號 from 任課表 where課時數(shù)=90)8.create rule zhicheng _ruleas zhicheng in (教授,副教授,講師, 助教)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.將下列醫(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分)。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 null) create table病人表(課號 char(6) primary key,姓名 nchar(4) not null,性別 nchar(1) check(性別 in (男, 女)，民族 nchar(10),身份證號 char(18) not null) create table病歷表(ID IDENTITY(1, 1), 病人編號 char(6) references 病人表(學號),醫(yī)生編號 char(6) references 醫(yī)生表(編號),病歷描述 varchar（1000）) 2.insert 醫(yī)生表 values(100001, 杜醫(yī)生, 男, 1963-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 編號=100002delete醫(yī)生表 where 編號=1000033. create view 醫(yī)療表視圖 asselect br.姓名,ys.姓名,yl.病歷 from 病人表 as br join 醫(yī)療表 as ylon br.編號yl.病人編號join 醫(yī)生表 as yson 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 outputasselect 人數(shù)=count(病人編號)from 病人表where 醫(yī)生編號 = 醫(yī)生編號godeclare TOTAL intexecute 某醫(yī)生看病人數(shù) 100001, TOTAL6 select * from 醫(yī)生表 where 出生日期1970-01-019.select 編號, 姓名, 性別, 出生日期, 職稱 from 醫(yī)生表where 編號 in (select 醫(yī)生編號 from 醫(yī)療表)8.create default zhicheng AS 醫(yī)師goexec sp_bindefault 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)，姓名，性別，年齡，專業(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)建：某門課程成績 內(nèi)嵌表值函數(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ù)庫gouse 學生選課數(shù)據(jù)庫go2. create table 學生表(學號 char(6) primary key， 姓名 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 課程號=100003delete課程