数据库期末考试编程题

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

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

3、）的学生插入到学生表中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课程表

4、where课程号=9)11 .编写关系代数，查询选修3号课程的学生学号n学号(/课程号='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语句，将对学生表的修改权限赋给用户U1Gr

5、antupdateontable学生表toU11 .设有一个学生课程数据库，包括学生关系Student、课程关系Courses选修关系SC图1所示：Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19ISCourse课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCALS言64学号课程号成绩SnoCnoGrade9500119295001285950013889500229095002

6、380SC图1学生-课程数据库写出完成下列要求的SQL语句：1）查询所有年龄在20岁以下的学生姓名与年龄。1 .selectSname,SagefromStudentwhereSage<20;或selectSname,SagefromStudentwherenotSage>=20;2）查询年龄不在2023（包括20岁和23岁）之间的学生的姓名、系别和年龄。selectSname,Sdept,SagefromStudentwhereSagenotbetween20and23;3）查询每个学生及其选修课程的情况。selectStudent.*,SC.*fromStudent,SCwhe

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

8、T-SQLI成下列各题。(1)检索学习课程号为“C01001”的学生学号与姓名；(2)检索至少选修课程号为“C00101”和“C02341”的学生学号。答：(1)SELECT#,SNAMEFROMS,SCWHERE#=#ANDC#='C01001'(2)SELECT#FROMSCASX,SCASYWHERE#=#AND#='C00101'AND#='C02341'2、图书管理数据库BookManager由以下四个关系模式组成，根据该结构用T-SQ戈成以下各题。(1)读者(借书证号，姓名,性别，出生日期)(2)图书(ISBN图书名称，作者，出版社，

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

10、LOGON(FILENAME=D:',SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=10%)(2) CREATEPROCEDUREproc_readerlib_numchar(8)ASSELECT错书证号，姓名,，书名FROMReaderA,JYB,BOOKCWHEREA.借书证号=B.借书证号AND=ANDA.借书证号二lib_num(3) CREATETRIGGERtrig_reminderONReaderFORINSERT,UPDATEASRAISEERROR(4008,16,10)3、设有学生表S(SNQSN)(SNO为学生号，SN为姓名)和学生选修课程表SC

11、(SNQCNQCN,G)(CNO为课程号，CN为课程名，G为成绩)，试用SQL语言完成以下各题：(1)建立一个视图V_SSC(SNO,SN,CNO,CN，金按CNO升序排序；(2)从视图V_SSCh查询平土成线在90分以上的SN、CN和G。答：(1)CREATEVIEWV_SSC(SNO,SN,CNO,CN,G)ASSELECT,CNO,FROMS,SCWHERE=ORDERBYCNO(2) SELECTSN,CN,GFROMV_SSCGROUPBYSNOHAVINGAVG(G)>904、图书管理数据库BookManager由以下四个关系模式组成，根据该结构用T-SQ戈成以下各题。(1)

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

13、OTNULL专业名Char(12)NOTNULL性别Char(2)NOTNULL出生日期DatetimeNOTNULL借书量IntegerNOTNULL字段名类型与宽度是否主码是否允许空值ISBNChar(16)是NOTNULL书名Char(26)NOTNULL作者Char(8)NOTNULL出版社Char(20)NOTNULL价格FloatNOTNULL副本量IntegerNOTNULL解：(1)CREATEDATABASEBookManagerON(NAME=BookManager_DATA,FILENAME=D:,SIZE=10MB,MAXSIZE=20MB,FILEGROWTH=10%

14、)LOGON(FILENAME=D:,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=10%)(2)CREATEPROCEDUREproc_readerlib_numchar(8)ASSELECT错书证号，姓名,，书名FROMReaderA,JYB,BOOKCWHEREA.借书证号=B.借书证号AND=ANDA.借书证号=lib_num(3) CREATETRIGGERtrig_reminderONReaderFORINSERT,UPDATEASRAISEERROR(4008,16,10)5、下面给出一个商品销售数据库中包含的三个基本表：(1)客户表：table_Client(

15、CNO,Cname,CsexCbirthday)(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_ClientwhereCbi

16、rthday='1978(3)selectCNO,Cnamefromtable_ClientwhereCNOin(selectCNOfromtable_CGgroupbyCNOhavingcount(*)>3)6、学生-选课-课程数据库由以下四个关系模式构成：STUDENTSNO,SName,SAg0SSexSAddressTEACHERTNO,TTeacherTPo§TAddressCOURSECNO,CName,CTerm,CCredit,TNO)SELECTCOURSENO,CNQGrade)按要求完成下列各题。(1)用SQL语句建立STUDENTS(2)为COU

17、RS法建立插入触发器，保证教师编号的正确性。(1)解：createtablestudent(snonvarchar(8),snamenvarchar(10),sageint,ssexchar(4),saddressnvarchar(50),primarykey(sno)(2)createtriggercourse_insert_tnooncourseforinsertasif(notexists(selecttnofromteacherwheretnoin(selectfrominserted)beginprint'无此教师'rollbacktransactionend1、设学

18、生课程数据库中有三个关系：学生关系S(S#，SNAME，AGE，SEX)学习关系SC(S#，C#，GRADE)课程关系C(C#，CNAME)其中S#C#SNAMEAGE、SEXGRADECNAME分别表示学号、课程号、姓名、年龄、性另成绩和课程名。用SQL语句表达下列操作(1)检索选修课程名称为“MATHS的学生的学号与姓名(2)检索至少学习了课程号为“C1”和“C2'的学生的学号(3)检索年龄在18到20之间(含18和20)的女生的学号、姓名和年龄(4)检索平均成绩超过80分的学生学号和平均成绩(5)检索选修了全部课程的学生姓名( 6 )检索选修了三门课以上的学生的姓名答案：( 1)

19、 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(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(SEL