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

數(shù)據(jù)庫(kù)及應(yīng)用復(fù)習(xí)題一、設(shè)計(jì)題 有一個(gè)學(xué)生課程數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)中包括三個(gè)表：學(xué)生表Student由學(xué)號(hào)(Sno)、姓名(Sname)、性別(Ssex)、年齡(Sage)、所在系(Sdept)五個(gè)屬性組成，記為： Student(Sno,Sname,Ssex,Sage,Sdept) ，Sno 為關(guān)鍵字。課程表Course由課程號(hào)(Cno)、課程名(Cname)、先修課號(hào)(Cpno)、學(xué)分(Ccredit)四個(gè)屬性組成，記為：Course(Cno,Cname,Cpno,Ccredit) Cno為關(guān)鍵字。 成績(jī)表SG由學(xué)號(hào)(Sno)、課程號(hào)(Cno)、成績(jī)(Grade)三個(gè)屬性組成，記為： SG(Sno,Cno,Grade) (SNO, CNO)為關(guān)鍵字。用SQL語(yǔ)言實(shí)現(xiàn)下列功能：1建立學(xué)生表Student，其中學(xué)號(hào)屬性不能為空，并且其值是唯一的。2向Student表增加“入學(xué)時(shí)間(Scome)”列，其數(shù)據(jù)類(lèi)型為日期型。3查詢(xún)選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢(xún)結(jié)果按分?jǐn)?shù)的降序排列。4查詢(xún)學(xué)習(xí)1號(hào)課程的學(xué)生最高分?jǐn)?shù)、平均成績(jī)。5查詢(xún)與“李洋”在同一個(gè)系學(xué)習(xí)的學(xué)生。6將計(jì)算機(jī)系全體學(xué)生的成績(jī)置零。7刪除學(xué)號(hào)為05019的學(xué)生記錄。8刪除計(jì)算機(jī)系所有學(xué)生的成績(jī)記錄。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 = 計(jì)算機(jī)系)7DELETE FROM StudentWHERE Sno=050198 DELETE FROM SG WHERE Sno in( SELECT Sno FROM Student WHERE Sdept = 計(jì)算機(jī)系)二、設(shè)計(jì)題 現(xiàn)有關(guān)系數(shù)據(jù)庫(kù)如下： 數(shù)據(jù)庫(kù)名：教師數(shù)據(jù)庫(kù)教師表(編號(hào) char(6)，姓名，性別，民族，職稱(chēng)，身份證號(hào)) 課程表(課號(hào) char(6)，名稱(chēng)) 任課表(ID，教師編號(hào),課號(hào)，課時(shí)數(shù)) 用SQL語(yǔ)言實(shí)現(xiàn)下列功能的sql語(yǔ)句代碼：1.創(chuàng)建上述三表的建庫(kù)、建表代碼(14分)； 要求使用：主鍵(教師表.編號(hào)，課程表.課號(hào))、外鍵(任課表.教師編號(hào)，任課表.課號(hào))、默認(rèn)(民族)、非空(民族，姓名)、唯一(身份證號(hào))、檢查(性別、課時(shí)數(shù)),自動(dòng)編號(hào)(ID)2.將下列課程信息添加到課程表的代碼(6分) 課號(hào) 課程名稱(chēng) 100001 SQL Server數(shù)據(jù)庫(kù) 100002 數(shù)據(jù)結(jié)構(gòu) 100003 VB程序設(shè)計(jì) 修改 課號(hào)為100003的課程名稱(chēng)：Visual Basic程序設(shè)計(jì) 刪除 課號(hào)為100003的課程信息 3.寫(xiě)出創(chuàng)建任課表視圖(教師編號(hào)，姓名，課號(hào)，課程名稱(chēng)，課時(shí)數(shù))的代碼；(4分)4.寫(xiě)出創(chuàng)建某門(mén)課任課教師內(nèi)嵌表值函數(shù)以及檢索的代碼；(6分) 檢索：所有代SQL Server數(shù)據(jù)庫(kù)這門(mén)課程的老師姓名；5.寫(xiě)出創(chuàng)建統(tǒng)計(jì)課時(shí)數(shù)：輸出最大課時(shí)數(shù)、最低課時(shí)數(shù)、平均課時(shí)的存儲(chǔ)過(guò)程以及執(zhí)行代碼；(6分)6. 寫(xiě)出創(chuàng)建：計(jì)算某教師代課總課時(shí)，并將值返回的存儲(chǔ)過(guò)程以及執(zhí)行代碼。(6分)執(zhí)行：計(jì)算“郭老師”的總課時(shí)。(6分)7.檢索有一門(mén)或一門(mén)以上課程課時(shí)數(shù)大于90的所有教師的信息，包括編號(hào)、姓名。(4分)8.建一個(gè)規(guī)則，并將其綁定到教師表的職稱(chēng)列上，規(guī)定取值為（教授,副教授,講師, 助教）之一。(4分)1.create database 教師數(shù)據(jù)庫(kù) -（2分）use 教師數(shù)據(jù)庫(kù)gocreate table 教師表 -（6分）(編號(hào) char(6) primary key,姓名 nchar(4) not null,性別 nchar(1) check(性別 in (男, 女),民族 nchar(8) default 漢族 not null,職稱(chēng) nchar(12),身份證號(hào) char(18) unique) create table 課程表 -（2分）(課號(hào) char(6) primary key,名稱(chēng) char(40) not null) create table 任課表 -（4分）(ID IDENTITY(1, 1), 教師編號(hào) char(6) references 學(xué)生表(學(xué)號(hào)),課號(hào) char(6) references 課程表(課號(hào)),課時(shí)數(shù) integer check(課時(shí)數(shù) between 0 and 200) 2.insert 課程表 values(100001, SQL Server數(shù)據(jù)庫(kù)) insert 課程表 values(100002, 數(shù)據(jù)結(jié)構(gòu))insert 課程表 values(100003, VB程序設(shè)計(jì))update 課程表 set 名稱(chēng)=Visual Basic程序設(shè)計(jì) where 課號(hào)=100003delete 課程表 where 課號(hào)=1000033.create view 任課表視圖 asselect 教師編號(hào),姓名,課號(hào), 課程名稱(chēng),課時(shí)數(shù) from 教師表,任課表where 教師表.編號(hào)=任課表.教師編號(hào)4. create function 某門(mén)課任課教師(課程名 varchar(15)returns table asreturn (select 課程名稱(chēng), 課時(shí)數(shù), 教師姓名=姓名 from 任課表視圖where 課程名=課程名)goselect * from 某門(mén)課任課教師(SQL Server數(shù)據(jù)庫(kù))5.create procedure 統(tǒng)計(jì)課時(shí)數(shù) asselect 最大課時(shí)數(shù)=max(課時(shí)) ,最小課時(shí)數(shù)=min(課時(shí)),平均課時(shí)數(shù)=avg(課時(shí)) from 任課表goexecute 統(tǒng)計(jì)課時(shí)6.create procedure 統(tǒng)計(jì)課時(shí) 教師名 nchar(16),asbegin declare 總課時(shí) int select 總課時(shí)=sum (課時(shí)) from 任課表視圖 where 姓名 = 教師名 endgoexecute 統(tǒng)計(jì)課時(shí) 郭老師7. select 編號(hào), 姓名 from 教師表where編號(hào) in (select distinct 教師編號(hào) from 任課表 where課時(shí)數(shù)=90)8.create rule zhicheng _ruleas zhicheng in (教授,副教授,講師, 助教)gosp_bindrule zhicheng_rule, 教師表.職稱(chēng)三、設(shè)計(jì)題(共50分) 現(xiàn)有關(guān)系數(shù)據(jù)庫(kù)如下： 數(shù)據(jù)庫(kù)名：醫(yī)院數(shù)據(jù)庫(kù)醫(yī)生表(編號(hào)，姓名，性別，出生日期，職稱(chēng)) 病人表(編號(hào)，姓名，性別，民族，身份證號(hào)) 病歷表(ID，病人編號(hào),醫(yī)生編號(hào)，病歷描述) 用SQL語(yǔ)言實(shí)現(xiàn)下列功能的sql語(yǔ)句代碼：1.創(chuàng)建上述三表的建庫(kù)、建表代碼； 要求使用：主鍵(師醫(yī)生表.編號(hào)，病人表.課號(hào))、外鍵(病歷表.醫(yī)生編號(hào)，病歷表.病人課號(hào))、非空(職稱(chēng)，姓名)、檢查(性別),自動(dòng)編號(hào)(ID) (16分)2.將下列醫(yī)生信息添加到醫(yī)生表的代碼 編號(hào) 姓名 性別 出生日期 職稱(chēng)100001 杜醫(yī)生 男 1963-5-18 副主任醫(yī)師100002 郭醫(yī)生 女 1950-7-26 副主任醫(yī)師100003 劉醫(yī)生 男 1973-9-18 醫(yī)師 修改 編號(hào)為100002的醫(yī)生職稱(chēng)為主任醫(yī)師 刪除 編號(hào)為100003的醫(yī)生信息 (8分)3.寫(xiě)出創(chuàng)建：醫(yī)療表視圖(醫(yī)生編號(hào)，姓名，病人姓名，病歷)的代碼；(4分)4.寫(xiě)出所有病人編號(hào)、姓名、病歷、以及病人所對(duì)應(yīng)的醫(yī)生編號(hào)的查詢(xún)語(yǔ)句；(4分)7. 寫(xiě)出創(chuàng)建： 輸出某醫(yī)生（根據(jù)醫(yī)生編號(hào)即可）看病人數(shù)存儲(chǔ)過(guò)程以及執(zhí)行過(guò)程（要求輸入醫(yī)生姓名的參數(shù)，輸出病人數(shù)）(6分)。8. 寫(xiě)出查詢(xún)1970年以前出生的醫(yī)生(4分)。7.檢索有病人的醫(yī)生信息。(4分)8.創(chuàng)建一個(gè)默認(rèn)，并將其綁定到醫(yī)生表的成績(jī)職稱(chēng)列上，默認(rèn)值為“醫(yī)師”。(4分)1.create database 醫(yī)院數(shù)據(jù)庫(kù)use 醫(yī)院數(shù)據(jù)庫(kù)gocreate table 醫(yī)生表(編號(hào) char(6) primary key，姓名 nchar(4) not null，性別 nchar(1) check(性別 in (男, 女)，出生日期datetime ，職稱(chēng) char(18) not null) create table病人表(課號(hào) char(6) primary key,姓名 nchar(4) not null,性別 nchar(1) check(性別 in (男, 女)，民族 nchar(10),身份證號(hào) char(18) not null) create table病歷表(ID IDENTITY(1, 1), 病人編號(hào) char(6) references 病人表(學(xué)號(hào)),醫(yī)生編號(hào) char(6) references 醫(yī)生表(編號(hào)),病歷描述 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 職稱(chēng)=主任醫(yī)師 where 編號(hào)=100002delete醫(yī)生表 where 編號(hào)=1000033. create view 醫(yī)療表視圖 asselect br.姓名,ys.姓名,yl.病歷 from 病人表 as br join 醫(yī)療表 as ylon br.編號(hào)yl.病人編號(hào)join 醫(yī)生表 as yson yl.醫(yī)生編號(hào)=ys.編號(hào)4 Select br.編號(hào), br.姓名, br.病歷, yl.醫(yī)生編號(hào) from 病人表 br left join 醫(yī)療表 yl on br.編號(hào) = yl.病人編號(hào)5 create procedure 某醫(yī)生看病人數(shù)醫(yī)生編號(hào) nchar(16), 人數(shù) int outputasselect 人數(shù)=count(病人編號(hào))from 病人表where 醫(yī)生編號(hào) = 醫(yī)生編號(hào)godeclare TOTAL intexecute 某醫(yī)生看病人數(shù) 100001, TOTAL6 select * from 醫(yī)生表 where 出生日期1970-01-019.select 編號(hào), 姓名, 性別, 出生日期, 職稱(chēng) from 醫(yī)生表where 編號(hào) in (select 醫(yī)生編號(hào) from 醫(yī)療表)8.create default zhicheng AS 醫(yī)師goexec sp_bindefault zhicheng, 醫(yī)生表.職稱(chēng)四、設(shè)計(jì)題(共50分) 現(xiàn)有關(guān)系數(shù)據(jù)庫(kù)如下： 數(shù)據(jù)庫(kù)名：學(xué)生選課數(shù)據(jù)庫(kù)學(xué)生表（學(xué)號(hào)，姓名，性別，年齡，專(zhuān)業(yè)）課程表（課程號(hào)，課程名，學(xué)分，先行課程號(hào)）選課表（ID, 學(xué)號(hào)，課程號(hào)，分?jǐn)?shù)） 用SQL語(yǔ)言實(shí)現(xiàn)下列功能的sql語(yǔ)句代碼：1.創(chuàng)建數(shù)據(jù)庫(kù)學(xué)生選課數(shù)據(jù)庫(kù)代碼(2分)；2.創(chuàng)建課程表代碼(2分)； 課程表(課程號(hào) char(6)，課程名，學(xué)分，先行課程號(hào)) 要求使用：主鍵(課程號(hào))、非空(課程名, 學(xué)分)3.創(chuàng)建學(xué)生表代碼(6分)； 學(xué)生表(學(xué)號(hào) char(6)，姓名，性別，年齡，專(zhuān)業(yè)) 要求使用：主鍵(學(xué)號(hào))、非空(姓名,專(zhuān)業(yè))、檢查(性別)4.創(chuàng)建選課表代碼(4分)； 選課表(ID,學(xué)號(hào)，課號(hào)，分?jǐn)?shù)) 要求使用：外鍵(選課表.學(xué)號(hào)，選課表.課程號(hào))、檢查(分?jǐn)?shù)),自動(dòng)編號(hào)(ID)5.將下列課程信息添加到課程表的代碼(8分) 課程號(hào) 課程名 學(xué)分 先行課程號(hào) 100001 C語(yǔ)言 2 100002 數(shù)據(jù)結(jié)構(gòu) 2 100001 100003 數(shù)據(jù)庫(kù)原理 2 修改 課程號(hào)為100003的課程名：SQL數(shù)據(jù)庫(kù) 刪除 課程號(hào)為100002的課程信息 6.寫(xiě)出創(chuàng)建：選課表視圖(學(xué)號(hào)，姓名，課程號(hào)，課程名，學(xué)分，分?jǐn)?shù))的代碼；(4分)7.寫(xiě)出創(chuàng)建：某門(mén)課程成績(jī) 內(nèi)嵌表值函數(shù)以及檢索的代碼；(6分) 檢索：所有修 SQL數(shù)據(jù)庫(kù) 這門(mén)學(xué)生的成績(jī)；8.寫(xiě)出創(chuàng)建：某門(mén)課程高低均分 計(jì)算某門(mén)課程成績(jī)最高分、最低分、平均分 存儲(chǔ)過(guò)程以及執(zhí)行的代碼；(6分) 執(zhí)行：所有修 SQL數(shù)據(jù)庫(kù) 這門(mén)學(xué)生的最高分、最低分、平均分；9.檢索姓張的女同學(xué)的情況：姓名、學(xué)號(hào)、專(zhuān)業(yè)。(2分)10.檢索有一門(mén)或一門(mén)以上課程成績(jī)小于60分的所有學(xué)生的信息，包括學(xué)號(hào)、姓名。(4分)1. create database 學(xué)生選課數(shù)據(jù)庫(kù)gouse 學(xué)生選課數(shù)據(jù)庫(kù)go2. create table 學(xué)生表(學(xué)號(hào) char(6) primary key， 姓名 nchar(4) not null， 性別 nchar(1) check(性別 in (男, 女)， 年齡 numeric(2) ， 專(zhuān)業(yè) char(18) not null) 3. create table課程表(課程號(hào) char(6) primary key， 課程名 char(40) not null， 學(xué)分 numeric(2) not null， 先行課程號(hào) char(6) ) 4. create table選課表(ID IDENTITY(1, 1), 學(xué)號(hào) char(6) references 學(xué)生表(學(xué)號(hào)), 課程號(hào) char(6) references 課程表(課程號(hào)), 分?jǐn)?shù) integer check(分?jǐn)?shù) between 0 and 100)5.insert 課程表 values(100001, C語(yǔ)言，2)insert 課程表 values(100002, 數(shù)據(jù)結(jié)構(gòu), 2, 100001)insert 課程表 values(100003, 數(shù)據(jù)庫(kù)原理, 2)update課程表 set 課程名=SQL數(shù)據(jù)庫(kù) where 課程號(hào)=100003delete課程