数据库期末考试编程题.docx

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

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

3、NER JOIN 成绩表 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 课程表

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

5、 编写SQL语句，将对学生表的修改权限赋给用户U1Grant update on table 学生表 to U11.设有一个学生课程数据库，包括学生关系Student、课程关系Course、选修关系SC，图1所示： Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19ISCourse课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学 23信息系统144操作系统635数据结构746数据处理 27PASCAL语言64 SC学号Sno课程号Cno成绩Gra

6、de9500119295001285950013889500229095002380图1 学生-课程数据库写出完成下列要求的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,

7、SC where Student.Sno=SC.Sno;2.把下面用关系表示的实体、实体与实体之间的联系，用E-R图表示出来，要求在图中表示联系的类型(11、 1n、 mn)。实体1：学生(学号，姓名，性别，年龄) 关键字为：学号实体2：课程(课程号，课程名，学分数) 关键字为：课程号实体1与实体2的联系：学习(学号，课程号，成绩) 关键字为：学号+课程号注：一个学生可以选多门课程，一门课程也可以被多个学生选，学生选课后有成绩。 学生选课关系E-R图五、编码题1、下面给出三个关系模式：（1）学生关系S（SNO，SName，SAge，SSex）（2）课程关系C（CNO，CName，CTeache

8、r）（3）学习关系SC（SNO，CNO，Grade）根据要求用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、图书管理数据库BookManager由以下四个关系模式组成，根据该结构用T-SQL完成

9、以下各题。（1）读者（借书证号，姓名，性别，出生日期）（2）图书（ISBN，图书名称，作者，出版社，价格，数量）（3）借阅（借书证号，ISBN，借出时间）根据要求完成下列各题。（1）创建一个名称为BookManager的数据库，其初始大小为10MB，最大为20MB，允许数据库自动增长；日志文件初始大小为2MB，最大为5MB。文件存储在目录D:TEST下。（2）编写一个存储过程，查询某读者当前的借书情况。（3）创建一个触发器，如果在图书表中添加或更改数据，则向客户端显示一条信息。解：（1）CREATE DATABASE BookManagerON( NAME=BookManager_DATA,F

10、ILENAME=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 A. 借书证号

11、= lib_num（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)

12、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)904、图书管理数据库BookManager由以下四个关系模式组成，根据该结构用T-SQL完成以下各题。（1）读者（借书证号，姓名，性别，出生日期）（2）图书（ISBN，图书名称，作者，出版社，价格，数量）（3）借阅（借书证号，ISBN，借出时间）根据要求完成下列各题。（1）创建一个名称为BookManager的数据库，其初始大小

13、为10MB，最大为20MB，允许数据库自动增长；日志文件初始大小为2MB，最大为5MB。文件存储在目录D:TEST下。（2）编写一个存储过程，查询某读者当前的借书情况。（3）创建一个触发器，如果在图书表中添加或更改数据，则向客户端显示一条信息。解：（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,MAXS

14、IZE=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（3）CREATE TRIGGER trig_reminder ON ReaderFOR INSERT ,UPDATEAS RAISEERROR(4008,16,10)5、下面给出一个商品销售数据库中包含的三个基本表：（

15、1）客户表：table_Client（CNO，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 CNOfrom table_CG)（2）select CNO,Cname

16、from table_Client where Cbirthday=1978（3）select CNO,Cname from table_Client where CNO in (select CNOfrom table_CGgroup by CNOhaving count(*)3)6、学生-选课-课程数据库由以下四个关系模式构成：STUDENT（SNO，SName，SAge，SSex，SAddress）TEACHER（TNO，TTeacher，TPos，TAddress）COURSE（CNO，CName，CTerm，CCredit，TNO）SELECTCOURSE（SNO，CNO，Grade

17、）按要求完成下列各题。（1）用SQL语句建立STUDENT表。（2）为COURSE表建立插入触发器，保证教师编号的正确性。（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 course for insert as if(not exists (select tno from teacher where tno in

18、 (select inserted.tno from inserted) begin print 无此教师 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”的学生的学号（3）检索年龄在18到20之间（含1

19、8和20）的女生的学号、姓名和年龄（4）检索平均成绩超过80分的学生学号和平均成绩（5）检索选修了全部课程的学生姓名（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)

20、 SELECT S# ,AVG(GRADE) 平均成绩 FROM SC GROUP BY S# HAVING 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、设学生-课程数据库中包括三个表： 学生表：Student （Sno，Sn

21、ame，Sex，Sage，Sdept）课程表：Course（Cno，Cname，Ccredit）学生选课表：SC（Sno，Cno，Grade）其中Sno、Sname、Sex、Sage、Sdept、 Cno、Cname、Ccredit 、Grade分别表示学号、姓名、性别、年龄、所在系名、课程号、课程名、学分和成绩。试用SQL语言完成下列项操作：（1）查询选修课程包括“1042”号学生所学的课程的学生学号（2）创建一个计科系学生信息视图S_CS_VIEW，包括Sno学号、Sname姓名、Sex性别;（3）通过上面第2题创建的视图修改数据，把王平的名字改为王慧平（4）创建一选修数据库课程信息的视图

22、，视图名称为datascore_view，包含学号、姓名、成绩。答案：(1) SELECT DISTINCT SNO FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.SNO = 1042 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.SNO=SCX.SNO AND SCZ.CNO=SCY.CNO)； (2) CREATE VIEW S_CS_VIEW AS SELECT SNO,SNAME,SEX FROM STUDENT WHERE Sdept=CS (3)UPDATE S_CS_VIEW SET SNAME= 王慧平 WHERE SNAME= 王平(4) CREATE VIEW datascore_view AS SELECT SNO 学号、SNAME 姓名、GRADE 成绩 FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO AND CNAME= 数据库