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

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

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

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

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

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

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

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

8、C（SNO，CNO，Grade）根據(jù)要求用T-SQL完成下列各題。（1） 檢索學習課程號為“C01001”的學生學號與姓名；（2） 檢索至少選修課程號為“C00101”和“C02341”的學生學號。答：（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由以下四個關系模式組成，根據(jù)該結構用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）編寫一個存儲過程，查詢某讀者當前的借書情況。（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、設有學生表S（SNO，SN）（SNO為學生號，SN為姓名）和學生選修課程表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由以下四個關系模式組成，根據(jù)該結構用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）編寫一個存儲過程，查詢某讀者當前的借書情況。（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、學生-選課-課程數(shù)據(jù)庫由以下四個關系模式構成：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ù)據(jù)庫中有三個關系：學生關系S（S#，SNAME，AGE，SEX）學習關系SC（S#，C#，GRADE）課程關系C（C#，CNAME）其中S#、C#、SNAME、AGE、SEX、GRADE、CNAME分別表示學號、課程號、姓名、年齡、性別、成績和課程名。用SQL語句表達下列操作（1）檢索選修課程名稱為“MATHS”的學生的學號與姓名 （2）檢索至少學習了課程號為“C1”和“C2”的學生的學號（3）檢索年齡在18到20之間（含18和20）的女生的學號、姓名和年齡（4）檢索平均成績超過80分的學生學號和平均成績（5）檢索選修了全部課程的

21、學生姓名（6）檢索選修了三門課以上的學生的姓名答案：（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ù)據(jù)庫中包括三個表： 學生表：Student （Sno，Sname，Sex，Sage，Sdept）課程表：Course（Cno，Cname，Ccredit）學生選課表：SC（