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

四、编程题（每小题 1 分，共 15 分） 可能用到的表结构如下： 学生表（学号，姓名，性别，年龄，班级） 课程表（课程号，课程名，学分） 成绩表（学号，课程号，成绩） 1.创建存储过程 GetStudentByID，带有参数 sno，根据学生的学号查询学生情况 Create procedure GetStudendByID sno varchar(10) As Begin Select * from 学生表 where 学号=sno end 2. 编写 SQL 语句，查询学生表中所有学生，要求第一按照班级升序排序，第二按照年龄降序排序 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 语句，在学生表和成绩表中查询学号，姓名，课程号，成绩 SELECT 学生表.学号, 学生表.姓名, 成绩表.课程号, 成绩表.成绩 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 (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) 14. 编写 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李勇男20CS 95002刘晨女19IS 95003王敏女18MA 95004张立男19IS Course 课程号 Cno 课程名 Cname 先行课 Cpno 学分 Ccredit 1数据库54 2数学2 3信息系统14 4操作系统63 5数据结构74 6数据处理2 7PASCAL 语言64 SC 学号 Sno 课程号 Cno 成绩 Grade 95001192 95001285 95001388 95002290 95002380 图 1学生-课程数据库 写出完成下列要求的 SQL 语句： 1)查询所有年龄在 20 岁以下的学生姓名与年龄。 1.select Sname,Sagefrom Studentwhere Sage=20; 2)查询年龄不在 2023(包括 20 岁和 23 岁)之间的学生的姓名、系别和年龄。 select Sname,Sdept,Sagefrom Student where Sage not between 20 and 23; 3)查询每个学生及其选修课程的情况。 select Student.*,SC.*from Student,SCwhere 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，CTeacher） （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#ANDC#=C01001 （2）SELECT X.S# FROM SCAS X,SCAS Y WHERE X.S#=Y.S#ANDX.C#=C00101ANDY.C#=C02341 2、图书管理数据库 BookManager 由以下四个关系模式组成，根据该结构用 T-SQL 完成以下各题。 （1）读者（借书证号，姓名，性别，出生日期） （2）图书（ISBN，图书名称，作者，出版社，价格，数量） （3）借阅（借书证号，ISBN，借出时间） 根据要求完成下列各题。 （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, FILEGROWTH=10%) LOG ON( FILENAME=D:TESTBookManager.LDF, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=10%) （2）CREATE PROCEDURE proc_reader lib_num char(8) AS SELECTA.借书证号,姓名,B.ISBN,书名 FROM ReaderA, JYB , BOOKC WHEREA. 借书证号=B. 借书证号 AND B.ISBN=C.ISBNANDA. 借书证号= 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）CREATE 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 HAVINGAVG(G)90 4、图书管理数据库 BookManager 由以下四个关系模式组成，根据该结构用 T-SQL 完成以下各题。 （1）读者（借书证号，姓名，性别，出生日期） （2）图书（ISBN，图书名称，作者，出版社，价格，数量） （3）借阅（借书证号，ISBN，借出时间） 根据要求完成下列各题。 （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, 字段名类型与 宽度 是否 主码 是否允许 空值 借书证号Char(8)是NOT NULL 姓名Char(8)NOT NULL 专业名Char(12)NOT NULL 性别Char(2)NOT NULL 出生日期DatetimeNOT NULL 借书量IntegerNOT NULL 照片ImageNOT NULL 字段名类型与 宽度 是 否 主码 是否允许 空值 ISBNChar(16)是NOT NULL 书名Char(26)NOT NULL 作者Char(8)NOT NULL 出版社Char(20)NOT NULL 价格FloatNOT NULL 副本量IntegerNOT NULL 库存量IntegerNOT NULL 字段名类型与 宽度 是否 主码 是否允许 空值 借书证号Char(8)NOT NULL ISBNChar(16)NOT NULL 索书号Char(10)是NOT NULL 借书时间Datetime是NOT NULL 还书时间DatetimeNOT NULL 字段名类型与 宽度 是否 主码 是否允许 空值 借书证号Char(8)NOT NULL ISBNChar(16)NOT NULL 索书号Char(10)是NOT NULL 借书时间DatetimeNOT NULL 表 4.1 读者表(表名为 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 SELECTA.借书证号,姓名,B.ISBN,书名 FROM ReaderA, JYB , BOOKC WHEREA. 借书证号=B. 借书证号 AND B.ISBN=C.ISBNANDA. 借书证号= 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）查询购买了商品的客户号及姓名。 （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 count(*)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 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 teacherwhere tno in (select inserted.tno from inserted) begin print 无此教师 rollback transaction end 1、设学生课程数据库中有三个关系： 学生关系 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）检索选修了全部课程的学生姓名 （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 AVG(GRADE)80 (5) SELECTSNAME FROMS WHERENOTEXISTS (SELECT* FROMC WHERE NOTEXISTS (SELECT* FROMSC 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（Cn