数据库期末考试编程题

1、本文格式为word版，下载可任意编辑数据库期末考试编程题 四、编程题（每小题 1 分，共 15 分） 可能用到的表结构如下： 同学表（学号，姓名，性别，年龄，班级） 课程表（课程号，课程名，学分） 成果表（学号，课程号，成果） 1.创建存储过程 getstudentbyid，带有参数 sno，依据同学的学号查询同学状况 create procedure getstudendbyid sno varchar(10) as begin select * from 同学表 where 学号=sno end 2. 编写 sql 语句，查询同学表中全部同学，要求第一根据班级升序排序，其次根据年龄降序排序

2、 select * from 同学表 order by 班级 asc,年龄 desc 3. 编写 sql 语句，查询同学表中姓张的同学 select * from 同学表 where 姓名 like 张% 4. 编写 sql 语句，查询同学表中男女生的人数各多少人 select 性别,count(*) from 同学表 group by 性别 5. 编写 sql 语句，查询全部比王平年龄大的状况 select * from 同学表 where 年龄(select 年龄 from 同学表 where 姓名=王平) 6. 编写 sql 语句，在同学表和成果表中查询学号，姓名，课程号，成果 sele

3、ct 同学表.学号, 同学表.姓名, 成果表.课程号, 成果表.成果 from 同学表 inner join 成果表 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

4、(select sno from sc) 10. 编写 sql 语句，查询课程号 9 的先行课的状况 select * from 课程表 where 课程号= (select 先行课号 from 课程表 where 课程号=9) 11. 编写关系代数，查询选修 3 号课程的同学学号 学号（课程号=3（选课表） 12. 编写关系代数，查询至少选修了一门直接先行课为 5 号课程的同学姓名 参看课本 60 页 13. 编写 sql 语句，创建同学表 create table 同学表 (学号 char（10），姓名 varchar(20)，性别 char(2),年龄 int,班级 char(30) 1

5、4. 编写 sql 语句，建立计算机 1 班男生的视图 create view as select * from 同学表 where 班级=计算机 1 班 and 性别=男 15. 编写 sql 语句，将对同学表的修改权限赋给用户 u1 grant update on table 同学表 to u1 1.设有一个同学课程数据库，包括同学关系 student、课程关系 course、选修关系 sc，图 1 所示： student 学号 sno 姓名 sname 性别 ssex 年龄 sage 所在系 sdept 95001 李勇 男 20 cs 95002 刘晨 女 19 is 95003 王敏

6、 女 18 ma 95004 张立 男 19 is course 课程号 cno 课程名 cname 先行课 cpno 学分 ccredit 1 数据库 5 4 2 数学 2 3 信息系统 1 4 4 操作系统 6 3 5 数据结构 7 4 6 数据处理 2 7 pascal 语言 6 4 sc 学号 sno 课程号 cno 成果 grade 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80 图 1 同学-课程数据库 写出完成下列要求的 sql 语句： 1)查询全部年龄在 20 岁以下的同学姓名与年龄。 1. select sname

7、,sage from student where sage20; 或 select sname,sage from student where not 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 student.sno=sc.sno; 2.把下面用关系表示的实体、实体与

8、实体之间的联系，用 e-r 图表示出来，要求在图中表示联系的类型(11、 1n、 mn)。 实体 1：同学(学号，姓名，性别，年龄) 关键字为：学号 实体 2：课程(课程号，课程名，学分数) 关键字为：课程号 实体 1 与实体 2 的联系：学习(学号，课程号，成果) 关键字为：学号+课程号 注：一个同学可以选多门课程，一门课程也可以被多个同学选，同学选课后有成果。 同学选课关系 e-r 图 五、编码题 1、下面给出三个关系模式： （1）同学关系 s（sno，sname，sage，ssex） （2）课程关系 c（cno，cname，cteacher） （3）学习关系 sc（sno，cno，gra

9、de） 依据要求用 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#=c02341 2、图书管理数据库 bookmanager 由以下四个关系模式组成，依据该结构用 t-sql 完成以下各题。 （1）读者（借书证号，

10、姓名，性别，诞生日期） （2）图书（isbn，图书名称，出版社，价格，数量） （3）借阅（借书证号，isbn，借出时间） 依据要求完成下列各题。 （1）创建一个名称为 bookmanager 的数据库，其初始大小为 10mb，最大为 20mb，允许数据库自动增长；日志文件初始大小为 2mb，最大为 5mb。文件存储在名目 d:test 下。 （2）编写一个存储过程，查询某读者当前的借书状况。 （3）创建一个触发器，假如在图书表中添加或更改数据，则向客户端显示一条信息。 解：（1）create database bookmanager on( name=bookmanager_data, fil

11、ename=d: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

12、a. 借书证号= lib_num （3）create trigger trig_reminder on reader for insert ,update as 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）cre

13、ate view v_ssc(sno,sn,cno,cn,g) as select 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)90 4、图书管理数据库 bookmanager 由以下四个关系模式组成，依据该结构用 t-sql 完成以下各题。 （1）读者（借书证号，姓名，性别，诞生日期） （2）图书（isbn，图书名称，出版社，价格，数量） （3）借阅（借书证号，isbn，借出时间） 依据要求

14、完成下列各题。 （1）创建一个名称为 bookmanager 的数据库，其初始大小为 10mb，最大为 20mb，允许数据库自动增长；日志文件初始大小为 2mb，最大为 5mb。文件存储在名目 d:test 下。 （2）编写一个存储过程，查询某读者当前的借书状况。 （3）创建一个触发器，假如在图书表中添加或更改数据，则向客户端显示一条信息。 解：（1）create database bookmanager on( name=bookmanager_data, filename=d:testbookmanager.mdf, size=10mb, maxsize=20mb, 字段名 类型与 宽度

15、是否 主码 是否允许 空值 借书证号 char(8) 是 not null 姓名 char(8) not null 专业名 char(12) not null 性别 char(2) not null 诞生日期 datetime not null 借书量 integer not null 照片 image not null 字段名 类型与 宽度 是 否主码 是否允许 空值 isbn char(16) 是 not null 书名 char(26) not null char(8) not null 出版社 char(20) not null 价格 float not null 副本量 intege

16、r not null 库存量 integer not null 字段名 类型与 宽度 是否 主码 是否允许 空值 借书证号 char(8) not null isbn char(16) not null 索书号 char(10) 是 not null 借书时间 datetime 是 not null 还书时间 datetime not null 字段名 类型与 宽度 是否 主码 是否允许 空值 借书证号 char(8) not null isbn char(16) not null 索书号 char(10) 是 not null 借书时间 datetime not null 表 4.1 读者表

17、(表名为 xs) 表 4.2 图书表(表名为 book) 表 4.3 借阅表(表名为 jy) 表 4.4 借阅历史表(表名为 jyls) 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.i

18、sbn=c.isbn and a. 借书证号= lib_num （3）create trigger trig_reminder on reader for insert ,update as raiseerror(4008,16,10) 5、下面给出一个商品销售数据库中包含的三个基本表： （1）客户表：table_client（cno，cname，csex，cbirthday） （2）商品表：table_goods（gno，gname，gunit，gprice） （3）购物表：table_cg（cno，gno，cgquantity） 用 t-sql 语句完成下列各题。 （1）查询购买了商品的客

19、户号及姓名。 （2）查询 1978 年诞生的客户号及姓名。 （3）购买了三种以上商品的客户号及客户姓名。 答：（1）select cno,cname from table_client where cno in (select distinct cno from table_cg) （2）select cno,cname from table_client where cbirthday=1978 （3）select cno,cname from table_client where cno in (select cno from table_cg group by cno having co

20、unt(*)3) 6、同学-选课-课程数据库由以下四个关系模式构成： 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 表建立插入触发器，保证老师编号的正确性。 （1） 解：create table student ( sno nvarchar(8), sname nvarch

21、ar(10), sage int, ssex char(4), saddress nvarchar(50), primary key(sno) ) （2） create trigger course_insert_tno on course for insert as if(not exists (select tno from teacher where tno in (select inserted.tno from inserted) begin print 无此老师 rollback transaction end 1、设同学课程数据库中有三个关系： 同学关系 s（s#，sname，a

22、ge，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）检索选修了全部课程的同学姓名 （6）检索选修了三门课以上的同学的姓名 答案：（1）select sn

23、ame,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 avg(grade)80 (5) select sname from s where 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、设同学-课程数据库中包括三个表： 同学表：student （sno，sname，sex，sage，sdept） 课程表：course（