数据库考试题目和答案.doc

考试试卷2005 - 2006 学年 1 学期 数据库技术课程设计课程 40 学时 2 学分考试形式： 开 卷 专业年级：信管、金融、工商等，2004级 1、 对于教学数据库（TEACH）构造3个基本表：S(SNUM,SNAME,SDEPT,AGE,SEX)；( 注：SDEPT为“学生所属系/院名”)C(CNUM,CNAME,TEACHER,PCNUM)；( 注：PCNUM为“先修课程编号”)SC(SNUM,CNUM,GRADE)；(1) 创建教学数据库：学生信息表、课程信息表、学生选课表；(2) 为每个基表添加多条记录（自己添加，所添加的数据要能够满足以下各题的查询要求）；(3) 检索年龄大于23岁的男学生的学号和姓名；(4) 检索liu老师所授课程的课程号和课程名；(5) 检索学号为S3的学生所学课程的课程名与任课教师名；(6) 检索至少选修liu老师所授课程中一门课程的男学生姓名；(7) 检索没有选修liu老师所授课程的女学生姓名和学号；(8) 检索wang同学不学的课程的课程号；(9) 检索至少选修两门课程的学生的姓名和学号；(10) 检索全部学生都选修的课程的课程号与课程名；(11) 检索选修课程包含liu老师所授课程的学生学号；(12) 在表C中统计开设课程的教师人数；(13) 求选修C4课程的女学生的平均年龄；(14) 求每个学生选修课程（已有成绩）的门数和平均成绩；(15) 统计每个学生选修课程的门数（超过5门的学生才统计），要求输出学生学号和选修门数，查询结果按门数降序，若门数相同，按学号升序；(16) 检索学号比wang同学大，而年龄比他小的学生姓名；(17) 在表SC中检索成绩为空的学生学号和课程号；(18) 检索姓名以L打头的所有学生的姓名和年龄；(19) 求年龄大于女同学平均年龄的男学生姓名和年龄；(20) 求年龄大于所有女同学年龄的男学生姓名和年龄；(21) 检索所授课程平均成绩大于80分的教师姓名，并将检索到的值送往另一个表FACULTY(TNAME)；（注：该表由学生自己创建）(22) 在SC中删除尚无成绩的选课元组；(23) 将选修liu老师课程的女同学选课元组全部删除；(24) 将MATHS课不及格的成绩全改为60分；(25) 将低于所有课程总平均成绩的女同学成绩提高5；(26) 在表SC中修改C4课程的成绩，若成绩小于等于70分时提高5，若成绩大于70分时提高4；(27) 在表SC中，当某个成绩低于全部课程的平均成绩时，提高5；(28) 求男同学每一年龄的人数；(29) 求女同学的人数和平均年龄及平均成绩；(30) 检索开设两门以上课程的教师姓名；(31) 检索未选修wang老师所授全部课程的学生学号；(32) 统计选修每一门课程的男女学生人数和平均成绩，要求显示(C#,CNAME,TEACHER,SEX,NUM,AVG_GRADE)，其中NUM和AVG_GRADE为人数和平均成绩。2、 对于图书发行数据库（B_PUBLISH）构造4个基本表：STORE(SNO,SNAME,ADDRESS) ；( 注：STORE为“书店”)LIBRARY(LNO,LNAME,CITY,TEL)；( 注：LIBRARY为“图书馆”)BOOK(BNO,BNAME,PRICE) ；( 注：BOOK为“图书”)LBS(LNO,BNO,SNO,QTY)； ( 注：LBS为“馆藏”；QTY为“数量”)（1）创建图书发行数据库：书店信息表、图书馆信息表、图书信息表、馆藏信息表；（2）为每个基表添加多条记录（自己添加，所添加的数据要能够满足以下各题的查询要求）；（3）查找L1从书店S1购买的图书书号及其册数；（4）取出馆址在Shanghai的馆名及电话号码；（5）取出S3发行的图书书名和数量；（6）取出已发行图书中最贵和最便宜的那种书的书名和定价；（7）查找购买图书B4最多的图书馆馆名；（8）取出L4 收藏图书的书名；（9）取出收藏图书情报检索语言的馆名；（10）将B5的单价更改为32.50元；（11）删去B2及其相关的馆藏记录；（12）将(B6,人工智能原理,42.00)登入关系BOOK。3、实验结束时提交课程设计报告，内容包含：（1）列出每个基表的结构及记录构成；（2）对第2大题的每小题，均要求用关系代数表示；（3）用SQL Server 2000语句完成每一题；（4）每题的结果。中南大学考试试卷答案2005 - 2006 学年 1 学期 时间2周数据库技术课程设计课程 40 学时 2 学分考试形式： 开 卷 专业年级：信管、金融、工商等，2004级 注：此页不作答题纸，请将答案写在答题纸上3、 对于教学数据库（TEACH）构造3个基本表：S(SNUM,SNAME,SDEPT,AGE,SEX)；( 注：SDEPT为“学生所属系/院名”)C(CNUM,CNAME,TEACHER,PCNUM)；( 注：PCNUM为“先修课程编号”)SC(SNUM,CNUM,GRADE)；(33) 创建教学数据库：学生信息表、课程信息表、学生选课表；(34) 为每个基表添加多条记录（自己添加，所添加的数据要能够满足以下各题的查询要求）1检索年龄大于23岁的男学生的学号和姓名；select snum,sname from s where age>23 and sex=男2-检索liu老师所授课程的课程号和课程名； select cnum,cname from c where teacher=liu3检索学号为S3的学生所学课程的课程名与任课教师名； select c.cnum,c.teacher from c,sc where c.cnum=sc.cnum and sc.snum=s34检索至少选修liu老师所授课程中一门课程的男学生姓名； select sname from s where snum in(select snum from sc where cnum in(select cnum from c where teacher=liu) and sex=男5检索没有选修liu老师所授课程的女学生姓名和学号； select sname,snum from s where snum not in(select snum from sc where cnum in(select cnum from c where teacher=liu) and sex=女6检索wang同学不学的课程的课程号； select cnum from c where cnum not in(select cnum from sc where snum in (select snum from s where sname=wang)7检索至少选修两门课程的学生的姓名和学号； select sname,snum from s where snum in(select snum from sc group by snum having count(*)>=2)8检索全部学生都选修的课程的课程号与课程名； select cnum,cname from c where not exists(select * from s where not exists(select * from sc where snum=s.snum and cnum=c.cnum)9检索选修课程包含liu老师所授课程的学生学号； select distinct sc.snum from sc,c where sc.cnum=c.cnum and c.teacher=liu10-在表C中统计开设课程的教师人数； select count(distinct teacher) teacher_num from c11-求选修C4课程的女学生的平均年龄； select avg(age) avg_age from sc,swhere sc.snum=s.snum and sc.cnum=c4 and sex=女12-求每个学生选修课程（已有成绩）的门数和平均成绩； select snum,count(*) num,avg(grade) avg_grade from scwhere grade is not null group by snum13-统计每个学生选修课程的门数（超过5门的学生才统计），要求输出学生学号和选修门数，查询结果按门数降序，若门数相同，按学号升序； select snum,count(*) from scgroup by snum having count(*)>5order by count(*) desc,snum14检索学号比wang同学大，而年龄比他小的学生姓名； select sname from s where snum>(select snum from s where sname=wang)and age<(select age from s where sname=wang)15在表SC中检索成绩为空的学生学号和课程号； select snum,cnum from sc where grade is null16检索姓名以L打头的所有学生的姓名和年龄； select sname,age from s where sname like l%17求年龄大于女同学平均年龄的男学生姓名和年龄； select sname,age from s where sex=男 and age>(select avg(age) from s where sex=女)18求年龄大于所有女同学年龄的男学生姓名和年龄； select sname,age from s where sex=男 and age>(select max(age) from s where sex=女)19检索所授课程平均成绩大于80分的教师姓名，并将检索到的值送往另一个表FACULTY(TNAME)；（注：该表由学生自己创建） delete from faculty;insert into faculty select distinct teacher from c where cnum in(select cnum from sc where grade is not null group by cnum having avg(grade)>80);select * from faculty;在SC中删除尚无成绩的选课元组； delete from sc where grade is null20将选修liu老师课程的女同学选课元组全部删除； delete from sc where snum in (select snum from s where sex=女) and cnum in (select cnum from c where teacher=liu)21将MATHS课不及格的成绩全改为60分； update sc set grade=60 where grade<60 and cnum in(select cnum from c where cname=maths)22将低于所有课程总平均成绩的女同学成绩提高5； update sc set grade=grade*(1+0.05)where grade<(select avg(grade) from sc where grade is not null) and snum in(select snum from s where sex=女)23在表SC中修改C4课程的成绩，若成绩小于等于70分时提高5，若成绩大于70分时提高4； update sc set grade=grade*(1+0.05) where cnum=c4 and grade<70;update sc set grade=grade*(1+0.04) where cnum=c4 and grade>70;24在表SC中，当某个成绩低于全部课程的平均成绩时，提高5； update sc set grade=grade*(1+0.05) where grade<(select avg(grade) from sc where grade is not null) 25求男同学每一年龄的人数； select age,count(age) from s where sex=男 group by age26求女同学的人数和平均年龄及平均成绩； select count( sex) num,avg(age) avg_age,avg(grade) avg_grade from s,sc where sc.snum=s.snum and grade is not null and sex=女 group by sex27检索开设两门以上课程的教师姓名； select teacher from c group by teacher having count(*)>228检索未选修wang老师所授全部课程的学生学号； select snum from s where snum not in(select snum from sc where cnum in (select cnum from c where teacher=wang)29统计选修每一门课程的男女学生人数和平均成绩，要求显示(CNUM,CNAME,TEACHER,SEX,NUM,AVG_GRADE)，其中NUM和AVG_GRADE为人数和平均成绩。select sc.cnum,c.cname,c.teacher,s.sex,count(*) num,avg(grade) avg_grade from s,c,sc where sc.snum=s.snum and sc.cnum=c.cnum and grade is not null group by s.sex,sc.cnum,c.cname,c.teacher4、 对于图书发行数据库（B_PUBLISH）构造4个基本表：STORE(SNO,SNAME,ADDRESS) ；( 注：STORE为“书店”)LIBRARY(LNO,LNAME,CITY,TEL)；( 注：LIBRARY为“图书馆”)BOOK(BNO,BNAME,PRICE) ；( 注：BOOK为“图书”)LBS(LNO,BNO,SNO,QTY)； ( 注：LBS为“馆藏”；QTY为“数量”)（1）创建图书发行数据库：书店信息表、图书馆信息表、图书信息表、馆藏信息表；（2）为每个基表添加多条记录（自己添加，所添加的数据要能够满足以下各题的查询要求）；（3）查找L1从书店S1购买的图书书号及其册数； Select bno,qty from lbs where sno=S1 and lno=L1;（4）取出馆址在Shanghai的馆名及电话号码； Select lname,tel from library where city=shanghai;（5）取出S3发行的图书书名和数量； Select distinct bname,qty from book,lbs where book.bno=lbs.bno and sno=S3;（6）取出已发行图书中最贵和最便宜的那种书的书名和定价； Select bno,price from book where price=(select max(price) from book) or price=(select min(price) from book);（7）查找购买图书B4最多的图书馆馆名； Select lname from library where lno in(select lno from lbs where bno=B4 and qty=(select max(qty) from lbs);（8）取出L4 收藏图书的书名； Select bname from book where bno in (select bno from lbs where lno=L4);（9）取出收藏图书情报检索语言的馆名； Select lname from library where lno in(select lno from lbs where bno in(select bno from book where bname=情报检索语言)； （10）将B5的单价更改为32.50元； Update book set price=32.50 where bno=B5;（11）删去B2及其相关的馆藏记录； Delete from book where bno=B2; Delete from lbs where bno=B2;（12）将(B6,人工智能原理,42.00)登入关系BOOK。 Insert into book values(B6,人工智能原理,42.00)；3、实验结束时提交课程设计报告，内容包含：（1）列出每个基表的结构及记录构成；（2）对第2大题的每小题，均要求用关系代数表示；（3）用SQL Server 2000语句完成每一题；（4）每题的结果。