數(shù)據(jù)庫期末考試編程題

1、四、編程題（每小題1分，共15分） 可能用到的表結(jié)構(gòu)如下： 學(xué)生表（學(xué)號，姓名，性別，年齡，班級） 課程表（課程號，課程名，學(xué)分） 成績表（學(xué)號，課程號，成績） 1.創(chuàng)建存儲過程GetStudentByID，帶有參數(shù)sno，根據(jù)學(xué)生的學(xué)號查詢學(xué)生情況Create procedure GetStudendByID sno varchar(10)AsBeginSelect * from 學(xué)生表 where 學(xué)號=snoend2. 編寫SQL語句，查詢學(xué)生表中所有學(xué)生，要求第一按照班級升序排序，第二按照年齡降序排序select * from 學(xué)生表 order by 班級 asc,年齡 desc 3

2、. 編寫SQL語句，查詢學(xué)生表中姓張的學(xué)生select * from 學(xué)生表 where 姓名 like 張% 4. 編寫SQL語句，查詢學(xué)生表中男女生的人數(shù)各多少人select 性別,count(*) from 學(xué)生表 group by 性別5. 編寫SQL語句，查詢所有比“王平”年齡大的情況Select * from 學(xué)生表 where 年齡(select 年齡 from 學(xué)生表 where 姓名=王平)6. 編寫SQL語句，在學(xué)生表和成績表中查詢學(xué)號，姓名，課程號，成績SELECT 學(xué)生表.學(xué)號, 學(xué)生表.姓名, 成績表.課程號, 成績表.成績 FROM 學(xué)生表 INNER JOIN 成

3、績表 ON 學(xué)生表.學(xué)號 = 成績表.學(xué)號7. 編寫SQL語句，將（1022，張望，男，19，信息2）的學(xué)生插入到學(xué)生表中insert into 學(xué)生表 (學(xué)號,姓名,性別,年齡, 班級) values(1022,張望,男,19,信息2)8. 編寫SQL語句，將學(xué)號為1022學(xué)生的姓名改為張旺update 學(xué)生表 set 姓名=張旺 where 學(xué)號=10229. 編寫SQL語句，刪除沒有選課的學(xué)生delete from 學(xué)生表 sno not in (select sno from sc)10. 編寫SQL語句，查詢課程號9的先行課的情況select * from 課程表 where 課程號

4、= (select 先行課號 from 課程表 where 課程號=9) 11. 編寫關(guān)系代數(shù)，查詢選修3號課程的學(xué)生學(xué)號學(xué)號（課程號=3（選課表）12. 編寫關(guān)系代數(shù)，查詢至少選修了一門直接先行課為5號課程的學(xué)生姓名參看課本60頁13. 編寫SQL語句，創(chuàng)建學(xué)生表Create table 學(xué)生表 (學(xué)號 char（10），姓名 varchar(20)，性別 char(2),年齡 int,班級 char(30)14. 編寫SQL語句，建立計算機1班男生的視圖Create view as select * from 學(xué)生表 where 班級=計算機1班 and 性別=男15. 編寫SQL語句，將

5、對學(xué)生表的修改權(quán)限賦給用戶U1Grant update on table 學(xué)生表 to U11.設(shè)有一個學(xué)生課程數(shù)據(jù)庫，包括學(xué)生關(guān)系Student、課程關(guān)系Course、選修關(guān)系SC，圖1所示： Student學(xué)號Sno姓名Sname性別Ssex年齡Sage所在系Sdept95001李勇男20CS95002劉晨女19IS95003王敏女18MA95004張立男19ISCourse課程號Cno課程名Cname先行課Cpno學(xué)分Ccredit1數(shù)據(jù)庫542數(shù)學(xué) 23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理 27PASCAL語言64 SC學(xué)號Sno課程號Cno成績Grade95001192

6、95001285950013889500229095002380圖1 學(xué)生-課程數(shù)據(jù)庫寫出完成下列要求的SQL語句：1)查詢所有年齡在20歲以下的學(xué)生姓名與年齡。1. select Sname,Sage from Student where Sage=20;2)查詢年齡不在2023(包括20歲和23歲)之間的學(xué)生的姓名、系別和年齡。select Sname,Sdept,Sage from Student where Sage not between 20 and 23;3)查詢每個學(xué)生及其選修課程的情況。select Student.*,SC.* from Student,SC where S

7、tudent.Sno=SC.Sno;2.把下面用關(guān)系表示的實體、實體與實體之間的聯(lián)系，用E-R圖表示出來，要求在圖中表示聯(lián)系的類型(11、 1n、 mn)。實體1：學(xué)生(學(xué)號，姓名，性別，年齡) 關(guān)鍵字為：學(xué)號實體2：課程(課程號，課程名，學(xué)分?jǐn)?shù)) 關(guān)鍵字為：課程號實體1與實體2的聯(lián)系：學(xué)習(xí)(學(xué)號，課程號，成績) 關(guān)鍵字為：學(xué)號+課程號注：一個學(xué)生可以選多門課程，一門課程也可以被多個學(xué)生選，學(xué)生選課后有成績。 學(xué)生選課關(guān)系E-R圖五、編碼題1、下面給出三個關(guān)系模式：（1）學(xué)生關(guān)系S（SNO，SName，SAge，SSex）（2）課程關(guān)系C（CNO，CName，CTeacher）（3）學(xué)習(xí)關(guān)系S

8、C（SNO，CNO，Grade）根據(jù)要求用T-SQL完成下列各題。（1） 檢索學(xué)習(xí)課程號為“C01001”的學(xué)生學(xué)號與姓名；（2） 檢索至少選修課程號為“C00101”和“C02341”的學(xué)生學(xué)號。答：（1）SELECT S.S#,SNAME FROM S,SC WHERE S.S#=SC.S# AND C#=C01001（2）SELECT X.S# FROM SC AS X,SC AS Y WHERE X.S#=Y.S# AND X.C#=C00101 AND Y.C#=C023412、圖書管理數(shù)據(jù)庫BookManager由以下四個關(guān)系模式組成，根據(jù)該結(jié)構(gòu)用T-SQL完成以下各題。（1）讀者

9、（借書證號，姓名，性別，出生日期）（2）圖書（ISBN，圖書名稱，作者，出版社，價格，數(shù)量）（3）借閱（借書證號，ISBN，借出時間）根據(jù)要求完成下列各題。（1）創(chuàng)建一個名稱為BookManager的數(shù)據(jù)庫，其初始大小為10MB，最大為20MB，允許數(shù)據(jù)庫自動增長；日志文件初始大小為2MB，最大為5MB。文件存儲在目錄D:TEST下。（2）編寫一個存儲過程，查詢某讀者當(dāng)前的借書情況。（3）創(chuàng)建一個觸發(fā)器，如果在圖書表中添加或更改數(shù)據(jù)，則向客戶端顯示一條信息。解：（1）CREATE DATABASE BookManagerON( NAME=BookManager_DATA,FILENAME=D:

10、TESTBookManager.MDF,SIZE=10MB,MAXSIZE=20MB,FILEGROWTH=10%)LOG ON(FILENAME=D:TESTBookManager.LDF,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=10%)（2）CREATE PROCEDURE proc_reader lib_num char(8) AS SELECT A.借書證號,姓名,B.ISBN,書名 FROM Reader A, JY B , BOOK C WHERE A. 借書證號=B. 借書證號 AND B.ISBN=C.ISBN AND A. 借書證號= lib_num（

11、3）CREATE TRIGGER trig_reminder ON ReaderFOR INSERT ,UPDATEAS RAISEERROR(4008,16,10)3、設(shè)有學(xué)生表S（SNO，SN）（SNO為學(xué)生號，SN為姓名）和學(xué)生選修課程表SC（SNO，CNO，CN，G）（CNO為課程號，CN為課程名，G為成績），試用SQL語言完成以下各題： （1）建立一個視圖V_SSC(SNO,SN,CNO,CN,G)，并按CNO升序排序；（2）從視圖V_SSC上查詢平均成線在90分以上的SN、CN和G。答：（1）CREATE VIEW V_SSC(SNO,SN,CNO,CN,G) AS SELECT

12、S.SNO,S.SN,CNO,SC.CN,SC.G FROM S,SC WHERE S.SNO=SC.SNO ORDER BY CNO （2） SELECT SN,CN,G FROM V_SSC GROUP BY SNO HAVING AVG(G)904、圖書管理數(shù)據(jù)庫BookManager由以下四個關(guān)系模式組成，根據(jù)該結(jié)構(gòu)用T-SQL完成以下各題。（1）讀者（借書證號，姓名，性別，出生日期）（2）圖書（ISBN，圖書名稱，作者，出版社，價格，數(shù)量）（3）借閱（借書證號，ISBN，借出時間）根據(jù)要求完成下列各題。（1）創(chuàng)建一個名稱為BookManager的數(shù)據(jù)庫，其初始大小為10MB，最大為2

13、0MB，允許數(shù)據(jù)庫自動增長；日志文件初始大小為2MB，最大為5MB。文件存儲在目錄D:TEST下。（2）編寫一個存儲過程，查詢某讀者當(dāng)前的借書情況。（3）創(chuàng)建一個觸發(fā)器，如果在圖書表中添加或更改數(shù)據(jù)，則向客戶端顯示一條信息。字段名類型與寬度是否主碼是否允許空值借書證號Char(8)是NOT NULL姓名Char(8)NOT NULL專業(yè)名Char(12)NOT NULL性別Char(2)NOT NULL出生日期DatetimeNOT NULL借書量IntegerNOT NULL照片ImageNOT NULL字段名類型與寬度是否主碼是否允許空值ISBNChar(16)是NOT NULL書名Cha

14、r(26)NOT NULL作者Char(8)NOT NULL出版社Char(20)NOT NULL價格FloatNOT NULL副本量IntegerNOT NULL庫存量IntegerNOT NULL字段名類型與寬度是否主碼是否允許空值借書證號Char(8)NOT NULLISBNChar(16)NOT NULL索書號Char(10)是NOT NULL借書時間Datetime是NOT NULL還書時間DatetimeNOT NULL字段名類型與寬度是否主碼是否允許空值借書證號Char(8)NOT NULLISBNChar(16)NOT NULL索書號Char(10)是NOT NULL借書時間D

15、atetimeNOT NULL表4.1 讀者表(表名為XS)表4.2 圖書表(表名為BOOK)表4.3 借閱表(表名為JY)表4.4 借閱歷史表(表名為JYLS)解：（1）CREATE DATABASE BookManagerON( NAME=BookManager_DATA,FILENAME=D:TESTBookManager.MDF,SIZE=10MB,MAXSIZE=20MB,FILEGROWTH=10%)LOG ON(FILENAME=D:TESTBookManager.LDF,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=10%)（2）CREATE PROCEDUR

16、E proc_reader lib_num char(8) AS SELECT A.借書證號,姓名,B.ISBN,書名 FROM Reader A, JY B , BOOK C WHERE A. 借書證號=B. 借書證號 AND B.ISBN=C.ISBN AND A. 借書證號= lib_num（3）CREATE TRIGGER trig_reminder ON ReaderFOR INSERT ,UPDATEAS RAISEERROR(4008,16,10)5、下面給出一個商品銷售數(shù)據(jù)庫中包含的三個基本表：（1）客戶表：table_Client（CNO，Cname，Csex，Cbirthd

17、ay）（2）商品表：table_Goods（GNO，Gname，Gunit，Gprice）（3）購物表：table_CG（CNO，GNO，CGquantity）用T-SQL語句完成下列各題。（1）查詢購買了商品的客戶號及姓名。（2）查詢1978年出生的客戶號及姓名。（3）購買了三種以上商品的客戶號及客戶姓名。答：（1）select CNO,Cnamefrom table_Clientwhere CNO in(select distinct CNOfrom table_CG)（2）select CNO,Cname from table_Client where Cbirthday=1978（3）

18、select CNO,Cname from table_Client where CNO in (select CNOfrom table_CGgroup by CNOhaving count(*)3)6、學(xué)生-選課-課程數(shù)據(jù)庫由以下四個關(guān)系模式構(gòu)成：STUDENT（SNO，SName，SAge，SSex，SAddress）TEACHER（TNO，TTeacher，TPos，TAddress）COURSE（CNO，CName，CTerm，CCredit，TNO）SELECTCOURSE（SNO，CNO，Grade）按要求完成下列各題。（1）用SQL語句建立STUDENT表。（2）為COURSE

19、表建立插入觸發(fā)器，保證教師編號的正確性。（1） 解：create table student(sno nvarchar(8),sname nvarchar(10),sage int,ssex char(4),saddress nvarchar(50),primary key(sno)（2）create trigger course_insert_tno on coursefor insertas if(not exists(select tno from teacher where tno in (select inserted.tno from inserted)beginprint 無此教

20、師rollback transactionend1、設(shè)學(xué)生課程數(shù)據(jù)庫中有三個關(guān)系：學(xué)生關(guān)系S（S#，SNAME，AGE，SEX）學(xué)習(xí)關(guān)系SC（S#，C#，GRADE）課程關(guān)系C（C#，CNAME）其中S#、C#、SNAME、AGE、SEX、GRADE、CNAME分別表示學(xué)號、課程號、姓名、年齡、性別、成績和課程名。用SQL語句表達(dá)下列操作（1）檢索選修課程名稱為“MATHS”的學(xué)生的學(xué)號與姓名 （2）檢索至少學(xué)習(xí)了課程號為“C1”和“C2”的學(xué)生的學(xué)號（3）檢索年齡在18到20之間（含18和20）的女生的學(xué)號、姓名和年齡（4）檢索平均成績超過80分的學(xué)生學(xué)號和平均成績（5）檢索選修了全部課程的

21、學(xué)生姓名（6）檢索選修了三門課以上的學(xué)生的姓名答案：（1）SELECT SNAME,AGE FROM S,SC,C WHERE S.S#=SC.S# AND C.C#=SC.C# AND CNAME= MATHS (2) SELECT S# FROM SC WHERE CNO=C1 AND S# IN( SELECT S# FROM SC WHERE CNO=C2) (3)SELECT S#,SNAME,AGE FROM S WHERE AGE BETWEEN 18 AND 20 (4) SELECT S# ,AVG(GRADE) 平均成績 FROM SC GROUP BY S# HAVING

22、 AVG(GRADE)80(5) SELECT SNAMEFROM SWHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE S#=S.S# AND C#=C.C# ) (6) SELECT SNAME FROM S,SC WHERE S.S#=SC.S# GROUP BY SNAME HAVING COUNT(*)3 2、設(shè)學(xué)生-課程數(shù)據(jù)庫中包括三個表： 學(xué)生表：Student （Sno，Sname，Sex，Sage，Sdept）課程表：Course（Cno，Cname，Ccredit）學(xué)生選課表：SC（