实验答案（四-五-六）参考答案

实验四 简单查询和连接查询

1. 简单查询实验

用Transact-SQL语句表示下列操作，在“学生选课“数据库中实现其数据查询操作： (1) 查询数学系学生的学号和姓名。

select sno,sname

from student

where dept='数学系';

(2) 查询选修了课程的学生学号。 select distinct(sno)

from sc;

(3) 查询选修课程号为0101的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

select distinct(sno),grade

from sc

where cno='0101'

order by grade desc,sno asc;

(4) 查询选修课程号为0101的成绩在80-90 分之间的学生学号和成绩，并将成绩乘以系数0.8 输出。

select distinct(sno),grade*0.8 as 'sore'

from sc

where cno='0101' and grade between 80 and 90;

(5) 查询数学系或计算机系姓张的学生的信息。 select *

from student

where dept in ('数学系','计算机系')and sname like '张%' ;

(6) 查询缺少了成绩的学生的学号和课程号。 select sno,cno

from sc

where grade is null;

2. 连接查询实验

用Transact-SQL语句表示，并在“学生选课”数据库中实现下列数据连接查询操作： (1) 查询每个学生的情况以及他（她）所选修的课程。 select student.*,course.cname

from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno;

(2) 查询学生的学号、姓名、选修的课程名及成绩。 select student.sno,sname,cname,grade

from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno;

(3) 查询选修离散数学 课程且成绩为90 分以上的学生学号、姓名及成绩。

- 1 -

select student.sno,sname,grade

from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno

and cname='离散数学' and grade>=90;

(4) 查询每一门课的间接先行课（即先行课的先行课）。

select first.cno,second.pcno

from course as first,course as second where first.pcno=second.cno;

实验五 嵌套查询

用TransacTransact-SQL语句表示，在学生选课库中实现其数据嵌套查询操作： (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,grade

from sc

where cno='0101' and grade>

(select grade

from sc

where cno='0101' and sno=(select sno From student

Where sname='张林'));

(3) 查询其他系中年龄小于计算机系年龄最大者的学生。

select *

from student

where dept<>'计算机系' and age<(select max(age) from student

where dept='计算机系');

(4) 查询其他系中比计算机系学生年龄都小的学生。

(3)中的max换成min即可。

(5) 查询同牟万里数据库原理课程分数相同的学生的学号。

- 2 -

select sno

from sc

where grade=(select grade

from student,sc,course where student.sno=sc.sno and

sc.cno=course.cno and course.cname='数据库原理' 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 SNAME FROM STUDENT WHERE SNO IN (

SELECT SNO FROM SC

GROUP BY SNO

HAVING COUNT(*)=

( SELECT COUNT(*) FROM COURSE));

select sname

from student

where not exists (select *

from course

where not exists (select * from sc

where cno=course.cno));

sno=student.sno

and

(9) 查询与学号为“09001103”的学生所选修的全部课程相同的学生学号和姓名。

select sno,sname

From student

Where sno<>'09001103' and not exists( select * From sc as x

- 3 -

Where sno='09001103' and not exists (select * From sc as y

Where y.sno=student.sno and x.cno=y.cno )) ;

(10) 查询至少选修了学号为“09001103”的学生所选修的全部课程的学生学号和姓名。

select sno,sname from student

where 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

scz.cno=scy.cno)));

实验六 组合查询和统计查询

在学生选课数据库中实现其查询操作： (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 c.cname='计算机基础') and x.cno= (select cno From course

Where cname='计算机基础');

(2) 查询选修计算机基础课程的学生的平均成绩。

select avg(grade)

From sc Where sno in (select sno From sc Where cno=

(select cno From course

Where cname='计算机基础'));

(3) 查询年龄大于女同学平均年龄的男同学姓名和年龄。

- 4 -

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 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 s2.cno From sc as s2

Where s2.sno=s1.sno)) and '离散数学'(select cname

From course Where cno in

(select cno From sc as s3

Where s3.sno=s1.sno))

Group by s1.sno;

- 5 -

in

本文来源：https://www.bwwdw.com/article/xrxf.html