实验答案参考答案.docx

《实验答案参考答案.docx》由会员分享，可在线阅读，更多相关《实验答案参考答案.docx（9页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、试验四 简洁查询与连接查询1. 简洁查询试验用Transact-SQL语句表示下列操作，在“学生选课“数据库中实现其数据查询操作：(1) 查询数学系学生的学号与姓名。select sno,snamefrom studentwhere dept=数学系;(2) 查询选修了课程的学生学号。 select distinct(sno)from sc;(3) 查询选修课程号为0101的学生学号与成果，并要求对查询结果按成果降序排列，假如成果相同则按学号升序排列。select distinct(sno),gradefrom scwhere cno=0101order by grade desc,sno a

2、sc;(4) 查询选修课程号为0101的成果在80-90 分之间的学生学号与成果，并将成果乘以系数0.8 输出。select distinct(sno),grade*0.8 as sorefrom scwhere cno=0101 and grade between 80 and 90;(5) 查询数学系或计算机系姓张的学生的信息。 select *from studentwhere dept in (数学系,计算机系)and sname like 张% ;(6) 查询缺少了成果的学生的学号与课程号。 select sno,cnofrom scwhere grade is null;2. 连接

3、查询试验用Transact-SQL语句表示，并在“学生选课”数据库中实现下列数据连接查询操作：(1) 查询每个学生的状况以及他（她）所选修的课程。 select student.*,courseamefrom student,sc,coursewhere student.sno=sc.sno and sco=courseo; (2) 查询学生的学号, 姓名, 选修的课程名及成果。 select student.sno,sname,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and sco=courseo;(3) 查询选修离

4、散数学 课程且成果为90 分以上的学生学号, 姓名及成果。select student.sno,sname,gradefrom student,sc,coursewhere student.sno=sc.sno and sco=courseo and cname=离散数学 and grade=90;(4) 查询每一门课的间接先行课（即先行课的先行课）。select firsto,second.pcnofrom course as first,course as secondwhere first.pcno=secondo;试验五 嵌套查询用TransacTransact-SQL语句表示，在学生

5、选课库中实现其数据嵌套查询操作：(l) 查询选修了离散数学的学生学号与姓名。select sno,sname from student where sno in (select sno from sc where cno= (select cno from course where cname=离散数学);(2) 查询0101课程的成果高于张林的学生学号与成果。select sno,gradefrom scwhere cno=0101 and grade(select grade from sc where cno=0101 and sno=(select sno From student W

6、here sname=张林);(3) 查询其他系中年龄小于计算机系年龄最大者的学生。select * from student where dept计算机系 and age(select max(age) from student where dept=计算机系);(4) 查询其他系中比计算机系学生年龄都小的学生。(3)中的max换成min即可。(5) 查询同牟万里数据库原理课程分数相同的学生的学号。select sno from sc where grade=(select grade from student,sc,course where student.sno=sc.sno and s

7、co=courseo and courseame=数据库原理 and sname=牟万里);(6) 查询选修了0206 课程的学生姓名。select sname from student where sno in (select sno from sc where cno=0206);(7) 查询没有选修0206 课程的学生姓名。在(5)的in前加not即可。(8) 查询选修了全部课程的学生的姓名。SELECT SNAMEFROM STUDENT WHERE SNO IN ( SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(*)= ( SELECT C

8、OUNT(*) FROM COURSE);select snamefrom studentwhere not exists (select * from course where not exists (select * from sc where sno=student.sno and cno=courseo);(9) 查询与学号为“”的学生所选修的全部课程相同的学生学号与姓名。select sno,sname From student Where sno09001103 and not exists( select * From sc as x Where sno=09001103 and

9、 not exists (select * From sc as y Where y.sno=student.sno and xo=yo ) ; (10) 查询至少选修了学号为“”的学生所选修的全部课程的学生学号与姓名。select sno,snamefrom studentwhere sno in(select scx.sno from sc scx where not exists(select * from sc scy where scy.sno=09001103 and not exists(select * from sc scz where scz.sno=scx.sno and

10、 sczo=scyo);试验六 组合查询与统计查询在学生选课数据库中实现其查询操作：(1) 查找选修“计算机基础”课程的学生成果比此课程的平均成果大的学生学号，成果。select x.sno,x.grade From sc as x Where x.grade( select avg(y.grade) From sc as y,course as c Where came=计算机基础) and xo= (select cno From course Where cname=计算机基础);(2) 查询选修计算机基础课程的学生的平均成果。select avg(grade) From sc Wher

11、e sno in (select sno From sc Where cno= (select cno From course Where cname=计算机基础);(3) 查询年龄大于女同学平均年龄的男同学姓名与年龄。select sname,age From student Where sex=男 and age(select avg(age) From student Where sex=女);(4) 列出各系学生的总人数，并按人数进行降序排列。select dept ,count(*) as total From student Group by dept order by total

12、 desc;(5) 统计各系各门课程的平均成果。select dept ,cno,avg(grade) From student,sc Group by dept,cno(6) 查询选修计算机基础与离散数学的学生学号与平均成果。select s1.sno,avg(grade) as 平均分 From sc as s1 Where 计算机基础 in (select cname From course Where cno in (select s2o From sc as s2 Where s2.sno=s1.sno) and 离散数学 in (select cname From courseWhere cno in(select cnoFrom sc as s3Where s3.sno=s1.sno)Group by s1.sno;第 9 页