数据库的复杂查询

实验三 数据库的复杂查询

一、实验学时

2学时

二、实验目的

（1）熟练掌握复杂查询的select语句。
（2）熟练掌握连接查询方法。
（3）熟练掌握嵌套查询方法。

三、实验要求

（1）硬件设备：奔腾II或奔腾II以上计算机，局域网。
（2）软件环境：WINDOWS 9X/NT、WINDOWS SERVER、WINDOWS XP、WINDOWS 7、SQL SERVER 2000/2005/2008中文版企业版或标准版。
（3）实验课前预习，课后及时完成实验内容。
（4）实验过程及记录按题目格式要求填写代码清单。

四、实验内容

（一）复杂查询
1．查询比“王敏”年纪大的男学生信息。
T-SQL语句：
select Student.*
from Student
where Sage >(
select Sage
from Student
where Sname=‘王敏’
)and Ssex=‘男’
2．检索所有学生的选课信息。（提示：使用外连接）
T-SQL语句：
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student left outer join SC on(Student.Sno=SC.Sno)
3．查询已选课学生的学号、姓名、课程名、成绩。（提示：连接查询）
T-SQL语句：
select Student.Sno,Sname,Cname,Grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno;
4．查询选修了“信息系统”的学生的学号和姓名。
T-SQL语句：
select Student.Sno,Sname
from Student,Course,SC
where Course.Cname=‘信息系统’ and Course.Cno=SC.Cno AND Student.Sno=SC.Sno;
5．查询与“刘晨”在同一个系的学生学号、姓名、性别。
子查询T-SQL语句：
select Sdept
from Student
where Sname=‘刘晨’;

select Sno,Sname,Ssex
from Student
where Sdept=‘CS’
连接查询T-SQL语句：
select Sno,Sname,Ssex
from Student
where Sdept in
(
select Sdept
from Student
where Sname=‘刘晨’
);
6．查询其他系中比计算机科学系任一学生年龄大的学生的学号、姓名。
带有ANY或ALL谓词的子查询语句：
select Sno,Sname
from Student
where Sage>any(
select Sage
from Student
where Sdept=‘CS’)
and Sdept<>‘CS’
用聚合函数实现：
select Sno,Sname
from Student
where Sage >(
select MIN(Sage)
from Student
where Sdept=‘CS’
)and Sdept<>‘CS’

7．检索学生的学号、姓名、学习课程名及课程成绩。
T-SQL语句：
select Student.Sno,Sname,Cname,Grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno;
8．检索选修3门以上课程的学生的学号、总成绩。
T-SQL语句：
select Sno,SUM(Grade)‘总成绩’
from SC
group by Sno
having COUNT()>=3
9．检索多于2名学生选修的课程号及平均成绩。
T-SQL语句：
select Cno,AVG(Grade)‘平均成绩’
from SC
group by Cno
having COUNT()>=2
（二）流程控制语句
1．如果Student表中有20岁的学生，把该学生的学号，姓名和性别查询出来，否则输出“没有20岁的学生”。写出T-SQL语句：（使用if…else语句）
if exists(select *from Student where Sage=‘20’)
select Sno,Sname,Ssex
from Student
where Sage=20
else
print ‘没有的学生’
2．使用While语句求1到100之间的累加和，输出结果。写出T-SQL语句：
declare @sum int,@count int
select @sum=0,@count=1
while @count<=100
begin
set @sum=@sum+@count
set @count=@count+1
end
print @sum
（三）用户自定义函数的应用
定义一个用户自定义函数Score_Rechange,将成绩从百分制转化为五级记分制。将该用户定义函数用在查询每个学生的成绩中，给出五级记分制的成绩。写出T-SQL语句：
create function Score_Rechange(@score tinyint)
returns char(10)
as
begin
return
case
when @score>=90 then’优秀’
when @score>=80 and @score<90 then’良好’
when @score>=70 and @score<80 then ‘中等’
when @score>=60 and @score<70 then’及格’
else ‘不及格’
end
end

select Student.Sno,Sname,dbo.Score_Rechange(Grade)‘五级’
from Student ,SC
where Student.Sno=SC.Sno