数据库期末考试编程题

1、四、编程题（每小题 1 分，共 15 分）可能用到的表结构如下：学生表（学号，姓名，性别，年龄，班级）课程表（课程号，课程名，学分）成绩表（学号，课程号，成绩）1 .创建存储过程GetStudentBylD,带有参数sno,根据学生的学号查询学生情况Create procedure GetStudendByID sno varchar（10）AsBeginSelect * from 学生表 where 学号 =snoend2 .编写SQL语句，查询学生表中所有学生，要求第一按照班级升序排序，第二按照年龄降序排序select * from 学生表 order by 班级 asc年龄 desc3

2、.编写SQL语句，查询学生表中姓张的学生select * from 学生表 where 姓名like '张 %'4 .编写SQL语句，查询学生表中男女生的人数各多少人select 性别 ,count（*） from 学生表 group by 性别5 .编写SQL语句，查询所有比“王平”年龄大的情况Select * from 学生表 where 年龄 （select 年龄 from 学生表 where 姓名 =王平）6 .编写SQL语句，在学生表和成绩表中查询学号，姓名，课程号，成绩SELECTS生表.学号，学生表.姓名，成绩表.课程号，成绩表.成绩FROM学生表INNER JO

3、IN成绩表ON 学生表 .学号 = 成绩表.学号7 .编写SQL语句，将（1022,张望，男，19,信息2）的学生插入到学生表中insert into学生表（学号，姓名，性别，年龄，班级）values（'1022',张望','男',19,'信息2'）8 .编写SQL语句，将学号为1022学生的姓名改为张旺update 学生表 set 姓名 ='张旺 ' where 学号 ='1022'9 .编写SQL语句，删除没有选课的学生delete from 学生表 sno not in (select sno fro

4、m sc)10 .编写SQL语句，查询课程号9的先行课的情况select * from 课程表 where课程号=(select先行课号from 课程表 where课程号=9)11 .编写关系代数，查询选修3号课程的学生学号n学号(/课程号='3(选课表)12 .编写关系代数，查询至少选修了一门直接先行课为5号课程的学生姓名参看课本60页13 .编写SQL语句，创建学生表Create table 学生表(学号 char (10),姓名 varchar(20),性别 char(2)，年龄 int,班级 char(30)14 .编写SQL语句，建立计算机1班男生的视图Create view

5、 as select * from 学生表 where 班级='计算机 1 班'and 性别='男'15 .编写SQL语句，将对学生表的修改权限赋给用户U1Grant update on table 学生表 to U11 .设有一个学生课程数据库，包括学生关系Student、课程关系Courses选修关系SC图1所示：Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19ISCourse课程号Cno课程名Cname先行课Cpno学分Ccredit1

6、数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCALS 言64学号课程号成绩SnoCnoGrade9500119295001285950013889500229095002380SC图1学生-课程数据库写出完成下列要求的SQL语句：1）查询所有年龄在20岁以下的学生姓名与年龄。1 . select Sname,Sage from Student where Sage<20;或 select Sname,Sage from Student where not Sage>=20;2）查询年龄不在2023（包括20岁和23岁）之间的学生的姓名、系别和年龄

7、。select Sname,Sdept,Sage from Student where Sage not between 20 and 23;3）查询每个学生及其选修课程的情况。select Student.*,SC.* from Student,SC where =;(1 :2 .把下面用关系表示的实体、实体与实体之间的联系，用E-R图表示出来，要求在图中表示联系的类型1 ： n、 m ： n）。实体1：学生（学号，姓名，性别，年龄） 关键字为：学号实体2:课程（课程号，课程名，学分数） 关键字为：课程号实体1与实体2的联系：学习（学号，课程号，成绩） 关键字为：学号+课程号注：一个学生可以

8、选多门课程，一门课程也可以被多个学生选，学生选课后有成绩。学生选课关系E-R图五、编码题1、下面给出三个关系模式：（1）学生关系 S （SNO）, SName, SAg0 SSeX(2)课程关系 C (CNQ CName, CTeacher (3)学习关系 SC (SNO, CNQ Grade)根据要求用T-SQLI成下列各题。(1)检索学习课程号为“ C01001”的学生学号与姓名；(2)检索至少选修课程号为“ C00101”和“C02341”的学生学号。答：(1) SELECT #,SNAME FROM S,SCWHERE #=# AND C#='C01001' (2) S

9、ELECT # FROM SC AS X,SC AS YWHERE #=# AND #='C00101' AND #= 'C02341'2、图书管理数据库BookManager由以下四个关系模式组成，根据该结构用T-SQ戈成以下各题。(1)读者(借书证号，姓名,性别，出生日期)(2)图书(ISBN图书名称，作者，出版社，价格，数量)(3)借阅(借书证号，ISBN借出时间) 根据要求完成下列各题。(1)创建一个名称为BookManager的数据库，其初始大小为10MB,最大为20MB,允许数据库自 动增长；日志文件初始大小为 2MB,最大为5MB。文件存储在目录D

10、:TESTTo(2)编写一个存储过程，查询某读者当前的借书情况。(3)创建一个触发器，如果在图书表中添加或更改数据，则向客户端显示一条信息。解：(1) CREATE DATABASE BookManagerON( NAME= BookManager_DATA , FILENAME= D: ', SIZE=10MB, MAXSIZE=20MB, FILEGROWTH=10%)LOG ON( FILENAME= D: ', SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=10%)(2) CREATE PROCEDURE proc_reader lib_num c

11、har(8)ASSELECT错书证号，姓名,，书名FROM Reader A, JY B , BOOK CWHERE A.借书证号=B.借书证号 AND = AND A.借书证号二 lib_num(3) CREATE TRIGGER trig_reminder ON ReaderFOR INSERT ,UPDATEASRAISEERROR(4008,16,10)3、设有学生表S (SNQ SN) (SNO为学生号，SN为姓名)和学生选修课程表 SC (SNQ CNQ CN,G) (CNO为课程号，CN为课程名，G为成绩)，试用SQL语言完成以下各题：(1)建立一个视图V_SSC(SNO,SN,

12、CNO,CN，金按CNO升序排序；(2)从视图V_SSCh查询平土成线在90分以上的SN、CN和G。答：(1) CREATE VIEW V_SSC(SNO,SN,CNO,CN,G)AS SELECT ,CNO,FROM S,SCWHERE =ORDER BY CNO(2) SELECT SN,CN,GFROM V_SSCGROUP BY SNOHAVING AVG(G)>904、图书管理数据库BookManager由以下四个关系模式组成，根据该结构用 T-SQ戈成以下各题。(1)读者(借书证号，姓名,性别，出生日期)(2)图书(ISBN图书名称，作者，出版社，价格，数量)(3)借阅(借书

13、证号、ISBN借出时间)根据要求完成下列各题。(1)创建一个名称为BookManager的数据库，其初始大小为10MB,最大为20MB,允许数据库自动增长；日志文件初始大小为 2MB,最大为5MB。文件存储在目录D:TESTTo(2)编写一个存储过程，查询某读者当前的借书情况。(3)创建一个触发器，如果在图书表中添加或更改数据，则向客户端显示一条信息。表读者表俵名为XS)表图书表俵名为BOOK)字段名类型与宽度是否主码是否允许空值借书证号Char(8)是NOT NULL姓名Char(8)NOT NULL专业名Char(12)NOT NULL性别Char(2)NOT NULL出生日期Dateti

14、meNOT NULL借书量IntegerNOT NULL字段名类型与宽度是否主码是否允许空值ISBNChar(16)是NOT NULL书名Char(26)NOT NULL作者Char(8)NOT NULL出版社Char(20)NOT NULL价格FloatNOT NULL副本量IntegerNOT NULL解： ( 1) CREATE DATABASE BookManagerON( NAME= BookManager_DATA,FILENAME= D:,SIZE=10MB,MAXSIZE=20MB,FILEGROWTH=10%)LOG ON(FILENAME= D:,SIZE=2MB,MAXS

15、IZE=5MB,FILEGROWTH=10%)( 2 ) CREATE PROCEDURE proc_reader lib_num char(8)ASSELECT错书证号，姓名,，书名FROM Reader A, JY B , BOOK CWHERE A.借书证号=B.借书证号 AND = AND A.借书证号= lib_num(3) CREATE TRIGGER trig_reminder ON ReaderFOR INSERT ,UPDATEASRAISEERROR(4008,16,10)5、下面给出一个商品销售数据库中包含的三个基本表： (1)客户表：table_Client (CNO,

16、 Cname, Csex Cbirthday) (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 CNO from table_CG) (2) select CNO,Cnamefrom

17、 table_Clientwhere Cbirthday='1978(3) select CNO,Cname from table_Client where CNO in (select CNOfrom table_CG group by CNO having count(*)>3)6、学生-选课-课程数据库由以下四个关系模式构成： STUDENT SNO, SName, SAg0 SSex SAddress TEACHERTNO, TTeacher TPo§ TAddress COURSECNO, CName, CTerm, CCredit, TNO) SELECTC

18、OURSENO, CNQ Grade)按要求完成下列各题。(1)用SQL语句建立STUDENTS(2)为COURS法建立插入触发器，保证教师编号的正确性。(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

19、 tno in(select from inserted)beginprint '无此教师 'rollback transaction end1 、设学生课程数据库中有三个关系：学生关系 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'的学生的学号(

20、3)检索年龄在18 到 20之间(含 18和 20)的女生的学号、姓名和年龄( 4 )检索平均成绩超过80 分的学生学号和平均成绩( 5 )检索选修了全部课程的学生姓名( 6 )检索选修了三门课以上的学生的姓名答案：( 1) SELECT SNAME,AGEFROM S,SC,CWHERE #=#AND #=#AND CNAME= MATHS(2) SELECT S#FROM SCWHERE CNO =C1 AND S#IN( SELECT S#FROMSCWHERE CNO =C2)(3)SELECT S#,SNAME,AGEFROM SWHERE AGE BETWEEN 18 AND 20

21、(4) SELECT S# ,AVG(GRADE均成绩FROM SCGROUP BY S#2 、设学生-课程数据库中包括三个表：学生表： Student ( Sno， Sname， Sex， Sage，课程表： Course( Cno， Cname， Ccredit)学生选课表：SC( Sno， Cno， Grade)其中 Sno、 Sname、 Sex、 Sage、 Sdept、别、年龄、所在系名、课程号、课程名、学分和成绩。HAVING AVG(GRADE)>80(5) SELECT SNAMEFROM SWHERE NOT EXISTS(SELECT *FROM CWHERE NOT EXISTS(SELECT