数据库期末考试编程题

四、编程题（每小题1分，共15分）可能用到的表结构如下：学生表（学号，姓名，性别，年龄，班级）课程表（课程号，课程名，学分）成绩表（学号，课程号，成绩）1.创建存储过程GetStudentByID，带有参数sno，根据学生的学号查询学生情况CreateprocedureGetStudendByIDsnovarchar(10)AsBeginSelect*from学生表where学号=snoend2.编写SQL语句，查询学生表中所有学生，要求第一按照班级升序排序，第二按照年龄降序排序select*from学生表orderby班级asc,年龄desc3.编写SQL语句，查询学生表中姓张的学生select*from学生表where姓名like'张%'4.编写SQL语句，查询学生表中男女生的人数各多少人select性别,count(*)from学生表groupby性别5.编写SQL语句，查询所有比“王平”年龄大的情况Select*from学生表where年龄(select年龄from学生表where姓名=’王平’)6.编写SQL语句，在学生表和成绩表中查询学号，姓名，课程号，成绩SELECT学生表.学号,学生表.姓名,成绩表.课程号,成绩表.成绩FROM学生表INNERJOIN成绩表ON学生表.学号=成绩表.学号7.编写SQL语句，将（1022，张望，男，19，信息2）的学生插入到学生表中insertinto学生表(学号,姓名,性别,年龄,班级)values('1022','张望','男',19,'信息2')8.编写SQL语句，将学号为1022学生的姓名改为张旺update学生表set姓名='张旺'where学号='1022'9.编写SQL语句，删除没有选课的学生deletefrom学生表snonotin(selectsnofromsc)10.编写SQL语句，查询课程号9的先行课的情况select*from课程表where课程号=(select先行课号from课程表where课程号=9)11.编写关系代数，查询选修3号课程的学生学号∏学号（∫课程号=‘3’（选课表））12.编写关系代数，查询至少选修了一门直接先行课为5号课程的学生姓名参看课本60页13.编写SQL语句，创建学生表Createtable学生表(学号char（10），姓名varchar(20)，性别char(2),年龄int,班级char(30))14.编写SQL语句，建立计算机1班男生的视图Createviewasselect*from学生表where班级=‘计算机1班’and性别=‘男’15.编写SQL语句，将对学生表的修改权限赋给用户U1Grantupdateontable学生表toU11.设有一个学生课程数据库，包括学生关系Student、课程关系Course、选修关系SC，图1所示：Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19ISCourse课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64SC学号Sno课程号Cno成绩Grade9500119295001285950013889500229095002380图1学生-课程数据库写出完成下列要求的SQL语句：1)查询所有年龄在20岁以下的学生姓名与年龄。1.selectSname,SagefromStudentwhereSage20;或selectSname,SagefromStudentwherenotSage=20;2)查询年龄不在20～23(包括20岁和23岁)之间的学生的姓名、系别和年龄。selectSname,Sdept,SagefromStudentwhereSagenotbetween20and23;3)查询每个学生及其选修课程的情况。selectStudent.*,SC.*fromStudent,SCwhereStudent.Sno=SC.Sno;2.把下面用关系表示的实体、实体与实体之间的联系，用E-R图表示出来，要求在图中表示联系的类型(1∶1、1∶n、m∶n)。实体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）SELECTS.S#,SNAMEFROMS,SCWHERES.S#=SC.S#ANDC#=’C01001’（2）SELECTX.S#FROMSCASX,SCASYWHEREX.S#=Y.S#ANDX.C#=’C00101’ANDY.C#=’C02341’2、图书管理数据库BookManager由以下四个关系模式组成，根据该结构用T-SQL完成以下各题。（1）读者（借书证号，姓名，性别，出生日期）（2）图书（ISBN，图书名称，作者，出版社，价格，数量）（3）借阅（借书证号，ISBN，借出时间）根据要求完成下列各题。（1）创建一个名称为BookManager的数据库，其初始大小为10MB，最大为20MB，允许数据库自动增长；日志文件初始大小为2MB，最大为5MB。文件存储在目录D:\TEST下。（2）编写一个存储过程，查询某读者当前的借书情况。（3）创建一个触发器，如果在图书表中添加或更改数据，则向客户端显示一条信息。解：（1）CREATEDATABASEBookManagerON(NAME=’BookManager_DATA’,FILENAME=’D:\TEST\BookManager.MDF’,SIZE=10MB,MAXSIZE=20MB,FILEGROWTH=10%)LOGON(FILENAME=’D:\TEST\BookManager.LDF’,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=10%)（2）CREATEPROCEDUREproc_reader@lib_numchar(8)ASSELECTA.借书证号,姓名,B.ISBN,书名FROMReaderA,JYB,BOOKCWHEREA.借书证号=B.借书证号ANDB.ISBN=C.ISBNANDA.借书证号=@lib_num（3）CREATETRIGGERtrig_reminderONReaderFORINSERT,UPDATEASRAISEERROR(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）CREATEVIEWV_SSC(SNO,SN,CNO,CN,G)ASSELECTS.SNO,S.SN,CNO,SC.CN,SC.GFROMS,SCWHERES.SNO=SC.SNOORDERBYCNO（2）SELECTSN,CN,GFROMV_SSCGROUPBYSNOHAVINGAVG(G)904、图书管理数据库BookManager由以下四个关系模式组成，根据该结构用T-SQL完成以下各题。（1）读者（借书证号，姓名，性别，出生日期）（2）图书（ISBN，图书名称，作者，出版社，价格，数量）（3）借阅（借书证号，ISBN，借出时间）根据要求完成下列各题。（1）创建一个名称为BookManager的数据库，其初始大小为10MB，最大为20MB，允许数据库自动增长；日志文件初始大小为2MB，最大为5MB。文件存储在目录D:\TEST下。（2）编写一个存储过程，查询某读者当前的借书情况。（3）创建一个触发器，如果在图书表中添加或更改数据，则向客户端显示一条信息。解：（1）CREATEDATABASEBookManagerON(NAME=’BookManager_DATA’,FILENAME=’D:\TEST\BookManager.MDF’,SIZE=10MB,MAXSIZE=20MB,字段名类型与宽度是否主码是否允许空值借书证号Char(8)是NOTNULL姓名Char(8)NOTNULL专业名Char(12)NOTNULL性别Char(2)NOTNULL出生日期DatetimeNOTNULL借书量IntegerNOTNULL照片ImageNOTNULL字段名类型与宽度是否主码是否允许空值ISBNChar(16)是NOTNULL书名Char(26)NOTNULL作者Char(8)NOTNULL出版社Char(20)NOTNULL价格FloatNOTNULL副本量IntegerNOTNULL库存量IntegerNOTNULL字段名类型与宽度是否主码是否允许空值借书证号Char(8)NOTNULLISBNChar(16)NOTNULL索书号Char(10)是NOTNULL借书时间Datetime是NOTNULL还书时间DatetimeNOTNULL字段名类型与宽度是否主码是否允许空值借书证号Char(8)NOTNULLISBNChar(16)NOTNULL索书号Char(10)是NOTNULL借书时间DatetimeNOTNULL表4.1读者表(表名为XS)表4.2图书表(表名为BOOK)表4.3借阅表(表名为JY))表4.4借阅历史表(表名为JYLS)FILEGROWTH=10%)LOGON(FILENAME=’D:\TEST\BookManager.LDF’,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=10%)（2）CREATEPROCEDUREproc_reader@lib_numchar(8)ASSELECTA.借书证号,姓名,B.ISBN,书名FROMReaderA,JYB,BOOKCWHEREA.借书证号=B.借书证号ANDB.ISBN=C.ISBNANDA.借书证号=@lib_num（3）CREATETRIGGERtrig_reminderONReaderFORINSERT,UPDATEASRAISEERROR(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）selectCNO,Cnamefromtable_ClientwhereCNOin(selectdistinctCNOfromtable_CG)（2）selectCNO,Cnamefromtable_ClientwhereCbirthday=’1978’（3）selectCNO,Cnamefromtable_ClientwhereCNOin(selectCNOfromtable_CGgroupbyCNOhavingcount(*)3)6、学生-选课-课程数据库由以