实验五 嵌套、分组统计查询实验(报告)

一、实验目的

使学生掌握SQL Server查询分析器的使用方法，加深对SQL语言的查询语句的理解。熟练掌握嵌套查询、分组统计的操作方法。 二、实验内容

该实验包括 1) 嵌套查询

在SQL Server查询分析器中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。

2) 分组统计

使用函数进行查询。

三、实验方法

1．将查询需求用SQL语言表示。

2．在SQL Server查询分析器的输入区中输入SQL查询语句。

3．发布执行命令，查看查询结果；如果结果不正确，进行修改，直到正确为止。 四、实验步骤 （1）嵌套查询 用SQL语句的嵌套查询实现学生选课库中的下列查询：

① 求选修了高等数学的学生学号和姓名；

SELECT Sno,Sname FROM Student WHERE EXISTS (SELECT * FROM Sc

WHERE EXISTS (SELECT * FROM Course

WHERE Course.Cno=Sc.Cno AND Student.Sno=Sc.Sno AND Cname='数学' )

)

② 求1号课程成绩高于张三的学生学号和成绩；

SELECT Sno,Grade FROM Sc

WHERE Cno=1 AND Grade>( SELECT Grade FROM Sc

WHERE Cno=1 AND Sno=( SELECT Sno FROM Student

WHERE Sname='张三' ) )

③ 求其他系中年龄小于计算机系年龄最大者的学生；

SELECT Sname FROM Student WHERE Sage<

(SELECT MAX(Sage) FROM Student

WHERE Sdept='Cs')AND Sdept<>'CS'

④ 求其他系中比计算机系学生年龄都小的学生；

SELECT Sname FROM Student WHERE SageFROM Student

WHERE Sdept='CS')AND Sdept<>'CS'

⑤ 求选修了2号课程的学生姓名；

SELECT Sname FROM Student WHERE EXISTS (

SELECT * FROM SC

WHERE Sno=Student.Sno AND Cno=2 )

⑥ 求没有选修2号课程的学生姓名；

SELECT Sname FROM Student

WHERE NOT EXISTS (

SELECT * FROM SC

WHERE Sno=Student.Sno AND Cno=2 )

⑦ 查询选修了全部课程的学生的姓名（至少用两种方法实现）。

SELECT Sname FROM student

WHERE NOT EXISTS (

SELECT* FROM course

WHERE NOT EXISTS (

SELECT * FROM sc

WHERE Sno=student.Sno AND Cno=course.Cno ) )

select Sname from student

where Sno IN

(select Sno from SC

group by Sno

having count(*) = (select count(*) from course ))

（2）分组统计 用SQL语句实现学生选课库中的下列查询：

① 查询最高分同学的学号，课程号和成绩； SELECT Sno,Cno,Grade FROM sc WHERE( Grade>ANY( SELECT Grade FROM sc WHERE 1

GROUP BY Cno ) )

② 查询有多少同学选修了“数学”课程； SELECT COUNT(Sno) FROM course,sc

WHERE Cname='数学'AND course.Cno=sc.Cno ③ 查询有多少同学选修课程； SELECT COUNT( DISTINCT Sno) FROM sc WHERE 1

④ 查询选课同学所有课程的总分、平均分，输出姓名、总分、平均分； SELECT Sname,SUM(Grade),AVG(Grade ) FROM student,sc

WHERE student.Sno=sc.Sno GROUP BY Sname

⑤ 查询每个同学（即使没有选课）所有课程的总分、平均分，输出姓名、总分、平均分； SELECT Sname,SUM(Grade),AVG(Grade )

FROM student LEFT OUTER JOIN sc ON (student.Sno=sc.Sno ) GROUP BY Sname

⑥ 查询所有课程的总分>150的同学的姓名、总分； SELECT Sname,SUM(Grade) FROM student,sc

WHERE student.Sno=sc.Sno GROUP BY Sname

HAVING SUM(Grade)>150

⑦ 查询选修了二门或以上课程的同学名单； SELECT Sno,Sname FROM student

WHERE 2<=(SELECT COUNT(Cno) FROM sc

WHERE sc.Sno=student.Sno GROUP BY Sno)

⑧ 查询最低分大于70，最高分小于90的学生的学号。 SELECT Sno FROM sc

GROUP BY Sno

HAVING MIN(Grade)>70 AND MAX(Grade)<90

五、实验报告要求

1．用SQL语言写出实验操作的查询语句。 2．实验步骤和实验结果。 3．实验中的问题。 六、注意事项

1． 注意选择数据库。

2． 输入SQL语句时应注意，语句中均使用西文操作符号。 七、思考题

1．WHERE与HAVING的区别。