SQL试题整理(上机1)

Sql试题一.有一个“学生－课程”数据库，数据库中包括三个表：(1)“学生”表Student由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，可记为：Student(Sno,Sname,Ssex,Sage,Sdept)Sno为关键字。(2)“课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，可记为：Course(Cno,Cname,Cpno,Ccredit)Cno为关键字。(3)“学生选课”表SC由学号（Sno）、课程号（Cno）、成绩（Grade）三个属性组成，可记为：SC(Sno,Cno,Grade)(SNO,CNO)为关键字。完成下列SQL语句：建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。向Student表增加“入学时间”列，其数据类型为日期型。3、删除Student表4、查询全体学生的学号与姓名5、查询全体学生的详细记录6、查所有选修过课的学生的学号7、查所有年龄在20岁以下的学生姓名及其年龄8、查考试成绩有不及格的学生的学号9、查询年龄在20至23岁之间的学生的姓名、系别、和年龄10、查所有姓刘的学生的姓名、学号和性别11、查姓“欧阳”且全名为三个汉字的学生的姓名12、查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列13、计算1号课程的学生平均成绩14、查询学习1号课程的学生最高分数15、查询与“刘晨”在同一个系学习的学生16、将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入Student表中17、将学生95001的年龄改为22岁18、将计算机科学系全体学生的成绩置零19、删除学号为95019的学生记录20、删除计算机科学系所有学生的选课记录二.设有一个学生课程数据库，包括学生关系表Student、课程关系表Course、选修关系表SC，图1所示：表Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001李勇男20计算机系95002刘晨女19自动化系95003王敏女18机械工程系95004张立男21电子系表Course课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学

23信息系统144操作系统635数据结构746数据处理

27PASCAL语言64表SC学号Sno课程号Cno成绩Grade9500119295001285950013889500229195002380图1

学生-课程数据库写出完成下列要求的SQL语句：查询所有年龄在20岁（包括20岁）以下的学生信息。2）查询姓“刘”的学生信息。3）查询先行课为6的课程名称。4）查询课程学分大于3的课程号和课程名称。5）在表SC中，按照学生成绩降序排列。6）求最高成绩7）求平均成绩8）查询成绩在90分以上的学生姓名和课程名。9）向表Student中添加一条记录：学号为“95005”，姓名为“孙辉”，性别为“男”，年龄为“19”，所在系为“机电系10）删除表Course中课程名为“操作系统”的课程信息。11）将学生刘晨的年龄改为“17”，所在系改为“会计系”三.1 将SQL2000SERVER自带的NORTHWIND数据库打开，完成以下的操作。2 新建视图testview，显示1996年12月份的分类销售情况汇总（CategorySalesfor1996/12）。要求视图显示的字段为“CategoryName”、“CategorySales”。注意：需要用到的基表为：“Orders”、“OrderDetails”、“Products”和“Categories”。3 在表“Employees”中增加一字段，字段名为“MobelPhone”，数据类型为“char(20)”,可以为空,其中“NancyDavolio”的手机号为4 修改表“Suppliers”中，CompanyName为“Pavlova,Ltd.”的ContactName为“NancyDavolio”。5 为表“EmployeeTerritories”建立一个参照完整性(既外键),要求表“EmployeeTerritories”的所有记录的“EmployeeID”字段的值，在表“Employees”中已经存在，并以“k1”6 为表“Orders”建立一个参照约束（check约束），要求表“Orders”的字段“ShippedDate”必须比“OrderDate”晚一周，且比“RequiredDate”早二周，且以“y1”7 建立删除触发器，要求当表“Orders”的记录被删除后，表“OrderDetails”中相应的记录也能自动删除,相应产品在表“Products”的字段“UnitsInStock”和“UnitsOnOrder”的数字也要做相应的修改，并以“cfq1”8 将books.txt中的数据，导入到数据库中（注意：文件books.txt为纯文本文档）。9 编写一个存储过程cc1，要求为“CategoryName”为“Meat/Poultry”的所有Products在原价格（指“UnitPrice”）的基础上提价10%。10建立一个角色sales，要求此角色能够胜任根据客户要求，输入新定单（Order）且对已有的定单进行修改的工作所需要的相应权限,要求只赋予必要的最小权限。11对表“Suppliers”建立一个全文索引index，然后在表“Suppliers”中查找所有包含字符串“SalesManager”的记录，并将查找结果以表cts保存在数据库中。12打开查询分析器，首先声明一个游标yb,要求返回表“Products”中“CategoryName”为“Condiments”（即“CategoryID”为“2”），“QuantityPerUnit”为“36boxes”的产品，且该游标允许前后滚动和修改；然后打开该游标；第三步对该游标所指定的记录进行修改，将“QuantityPerUnit”由原来的“36boxes”改为“24boxes”；然后关闭该游标并删除该游标。该过程在查询分析器中调试成功后，将上述程序过程以“cc213 建立一个用户函数SalesSum，要求以年、月份(如：1996年10月)和员工姓（LastName）为实参，返回该员工在该月的销售总金额。14 备份数据库到考试目录下，并以test为文件名保存。练习题1：现有图书管理数据库的三个关系模式：图书（总编号，分类号，书名，作者，出版单位，单价）读者（借书证号，单位，姓名，性别，职称，地址）借阅（借书证号，总编号，借书日期）利用SQLServer2000创建图书管理库和图书、读者和借阅三个基本表的表结构：利用SQLServer2000在三个表中分别插入以下所给元组：图书：总编号分类号书名作者出版单位单价445501TP3/12数据库导论王强科学出版社17.90445502TP3/12数据库导论王强科学出版社17.90445503TP3/12数据库导论王强科学出版社17.90332211TP5/10计算机基础李伟高等教育出版社18.00112266TP3/12FoxBASE张三电子工业出版社23.60665544TS7/21高等数学刘明高等教育出版社20.00114455TR9/12线性代数孙业北京大学出版社20.80113388TR7/90大学英语胡玲清华大学出版社12.50446601TP4/13数据库基础马凌云人民邮电出版社22.50446602TP4/13数据库基础马凌云人民邮电出版社22.50446603TP4/13数据库基础马凌云人民邮电出版社22.50449901TP4/14FoxPro大全周虹科学出版社32.70449902TP4/14FoxPro大全周虹科学出版社32.70118801TP4/15计算机网络黄力钧高等教育出版社21.80118802TP4/15计算机网络黄力钧高等教育出版社21.80读者：借书证号单位姓名性别职称地址111信息系王维利女教授1号楼424112财会系李立男副教授2号楼316113经济系张三男讲师3号楼105114信息系周华发男讲师1号楼316115信息系赵正义男工程师1号楼224116信息系李明男副教授1号楼318117计算机系李小峰男助教1号楼214118计算机系许鹏飞男助工1号楼216119计算机系刘大龙男教授1号楼318120国际贸易李雪男副教授4号楼506121国际贸易李爽女讲师4号楼510122国际贸易王纯女讲师4号楼512123财会系沈小霞女助教2号楼202124财会系朱海男讲师2号楼210125财会系马英明男副教授2号楼212借阅：借书证号总编号借书日期1124455011997-3-191253322111997-2-121114455031997-8-211121122661997-3-141146655441997-10-211201144551997-11-21201188011997-10-181194466031997-12-121124499011997-10-231154499021997-8-211181188011997-9-10练习题2：学会利用导入的方法创建上面的数据库并用SQL完成如下查询：找出姓李的读者姓名和所在单位。列出图书库中所有藏书的书名及出版单位。查找高等教育出版社的所有图书及单价，结果按单价降序排序。查找价格介于10元和20元之间的图书种类，结果按出版单位和单价升序排序。查找书名以计算机打头的所有图书和作者。检索同时借阅了总编号为112266和449901两本书的借书证号。查找所有借了书的读者的姓名及所在单位。找出李某所借图书的所有图书的书名及借书日期。查询1997年10月以后借书的读者借书证号、姓名和单位。找出借阅了FoxPro大全一书的借书证号。找出与赵正义在同一天借书的读者姓名、所在单位及借书日期。查询1997年7月以后没有借书的读者借书证号、姓名及单位。练习题3：学会利用导出的方法创建图书管理数据库并完成下面SQL高级查询：求科学出版社图书的最高单价、最低单价、平均单价。求信息系当前借阅图书的读者人次数。求出各个出版社图书的最高价格、最低价格和册数。分别找出各单位当前借阅图书的读者人数。找出当前至少借阅了2本图书的读者及所在单位。分别找出借书人次超过1人次的单位及人次数。找出藏书中各个出版单位的册数、价值总额。查询经济系是否还清所有图书。如果还清，显示该系所有读者的

姓名、所在单位和职称。二、数据查询及更新1、对于教学数据库的三个基本表：学生S(S#,SNAME,AGE,SEX)--字段含义[学号,姓名,年龄,性别]学习SC(S#,C#,GRADE)--字段含义[学号,课程号,成绩]课程C(C#,CNAME,TEACHER)--字段含义[课程号,课程名,授课老师]试用SQL的查询语句表达下列查询：（1）检索LIU老师所授课程的课程号和课程名。SELECTC#，CNAMEFROMCWHERETEACHER=‘LIU’（2）检索年龄大于23岁的男学生的学号和姓名。SELECTS#，SNAMEFROMSWHERE(AGE>23)AND(SEX=‘M’)（3）检索至少选修LIU老师所授课程中一门课程的女学生姓名。SELECTSNAMEFROMSWHERESEX=‘F’ANDS#IN(SELECTS#FROMSCWHEREC#IN(SELECTC#FROMCWHERETEACHER=‘LIU’)NOTICE：有多种写法，比如联接查询写法：SELECTSNAMEFROMS,SC,CWHERESEX=‘F’ANDSC.S#=S.S#ANDSC.C#=C.C#ANDTEACHER='LIU'但上一种写法更好一些。（4）检索WANG同学不学的课程的课程号。SELECTC#FROMCWHEREC#NOTIN(SELECTC#FROMSCWHERES#IN(SELECTS#FROMSWHERESNAME='WANG'))（5）检索至少选修两门课程的学生学号。SELECTDISTINCTX.SNOFROMSCX,SCYWHEREX.SNO=Y.SNOANDX.CNO<>Y.CNONotice:对表SC进行自连接，X，Y是SC的两个别名。（6）检索全部学生都选修的课程的课程号与课程名。SELECTC#,CNAMEFROMCWHERENOTEXISTS(SELECT*FROMSWHERES#NOTIN(SELECT*FROMSCWHERE

SC.C#=C.C#))（7）检索选修课程包含LIU老师所授课的学生学号。SELECTDISTINCTS#FROMSCWHEREC#IN(SELECTC#FROMCWHERETEACHER='LIU'))2、设有两个基本表R（A，B，C）和S（D，E，F），试用SQL查询语句表达下列关系代数表达式：；（1）πA(R)(2)σB='17'(R)(1)SELECTAFROMR；(2)SELECT*FROMRWHEREB='17'；4、试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询：（1）统计有学生选修的课程门数。SELECTCOUNT(DISTINCTC#)FROMSC（2）求选修C4课程的学生的平均年龄。SELECTAVG(AGE)FROMSWHERES#IN(SELECTS#FROMSCWHEREC#='C4')或者SELECTAVG(AGE)FROMS，SCWHERES.S#=SC.S#ANDC#='004'（3）求LIU老师所授课程的每门课程的学生平均成绩。SELECTCNAME,AVG(GRADE)FROMSC,CWHERESC.C#=C.C#ANDTEACHER='LIU'GROUPBYC#（4）统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。SELECTDISTINCTC#,COUNT(S#)FROMSCGROUPBYC#HAVINGCOUNT(S#)>10ORDERBY2DESC,C#ASC（5）检索学号比WANG同学大，而年龄比他小的学生姓名。SELECTX.SNAMEFROMSASX,SASYWHEREY.SNAME='WANG'ANDX.S#>Y.S#ANDX.AGE<Y.AGE（6）检索姓名以WANG打头的所有学生的姓名和年龄。SELECTSNAME,AGEFROMSWHERESNAMELIKE'WANG%'（7）在SC中检索成绩为空值的学生学号和课程号。SELECTS#,C#FROMSCWHEREGRADEISNULL（8）求年龄大于女同学平均年龄的男学生姓名和年龄。SELECTSNAME,AGEFROMSASXWHEREX.SEX='男'ANDX.AGE>(SELECTAVG(AGE)FROMSASYWHEREY.SEX='女')（9）求年龄大于所有女同学年龄的男学生姓名和年龄。SELECTSNAME,AGEFROMSASXWHEREX.SEX='男'ANDX.AGE>ALL(SELECTAGEFROMSASYWHEREY.SEX='女')5、试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作：（1）往基本表S中插入一个学生元组（‘S9’，‘WU’，18）。INSERTINTOS(S#,SNAME,AGE)VALUES('59','WU',18)（2）在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT（S＃，SANME，SEX）。INSERTINTOSTUDENT(S#,SNAME,SEX)SELECTS#,SNAME,SEXFROMSWHERENOTEXISTS(SELECT*FROMSCWHEREGRADE<80ANDS.S#=SC.S#)（3）在基本表SC中删除尚无成绩的选课元组。DELETEFROMSCWHEREGRADEISNULL（4）把WANG同学的学习选课和成绩全部删去。DELETEFROMSCWHERES#IN(SELECTS#FROMSWHERESNAME='WANG')（5）把选修MATHS课不及格的成绩全改为空值。UPDATESCSETGRADE=NULLWHEREGRADE<60ANDC#IN(SELECTC#FROMCWHERECNAME='MATHS')（6）把低于总平均成绩的女同学成绩提高5%。UPDATESCSETGRADE=GRADE*1.05WHEREGRADE<(SELECTAVG(GRADE)FROMSC)ANDS#IN(SELECTS#FROMSWHERESEX='F')（7）在基本表SC中修改C4课程的成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%（用两个UPDATE语句实现）。UPDATESCSETGRADE=GRADE*1.05WHEREC#='C4'ANDGRADE<=75UPDATESCSETGRADE=GRADE*1.04WHEREC#='C4'ANDGRADE>75《数据库原理及应用》习题集参考答案一、简答题1．什么是数据库管理系统答：一种负责数据库的建立，操作，管理和维护的软件系统。。2．数据库系统有哪几种模式分别用来描述什么答：(1) 外模式 是用户的数据视图，用来描述数据的局部逻辑结构，是模式的子集。(2) 模式 是所有用户的公共数