SQL Server 数据库实验二 数据查询实验
一、实验目的
熟悉和掌握对数据表中数据的查询操作和SQL命令的使用,学会灵活熟练的使用SQL 语句的各种形式;
二、需用仪器、设备
Pentium 166MHz及以上微机;
Windows XP/2000/7及以上操作系统;
安装了SQL Server;
三、实验内容及步骤
1.在表S,C,SC上进行简单查询;
2.在表S,C,SC上进行连接查询;
3.在表S,C,SC上进行嵌套查询;
4.使用聚合函数的查询;
5.对数据的分组查询;
6.对数据的排序查询。
四、查询语句及结果
1. 查询学生的基本信息;
select * from S
2. 查询“CS”系学生的基本信息;
select * from S
where sdept = 'CS'
3. 查询“CS”系学生年龄不在19到21之间的学生的学号、姓名;
select Sno, Sname from S
where sdept = 'CS'
and Sage not between 19 and 21
4. 找出最大年龄;
select MAX(Sage) maxAge from S
5. 找出“CS”系年龄最大的学生,显示其学号、姓名;
select Sno, Sname from S
where sdept = 'CS'and Sage = (
select MAX(Sage) from S where sdept='CS')
6. 找出各系年龄最大的学生,显示其学号、姓名;
select Sno,Sname from S a
where Sage = (
select max(Sage) from S b
where a.sdept = b.sdept )
7. 统计“CS”系学生的人数;
select COUNT(*) number from S
where sdept = 'CS'
8. 统计各系学生的人数,结果按升序排列;
select sdept,COUNT(*) number
from S
group by sdept
order by number
9. 按系统计各系学生的平均年龄,结果按降序排列;
select sdept,AVG(Sage) avgAge
from S
group by sdept
order by avgAge desc
10.查询每门课程的课程名;
select Cname from C
11.查询无先修课的课程的课程名和学分;
select Cname, Ccredit
from C
where Cpno is null
12.统计无先修课的课程的学时总数(总学分);
select SUM(Ccredit) total
from C
where Cpno is null
13.统计每位学生选修课程的门数、学分及其平均成绩;
select SC.Sno,COUNT(SC.Cno) number,SUM(C.Ccredit) credit, AVG(grade)avgGrade
from SC,C
where SC.Cno = C.Cno
group by SC.Sno
14.统计选修每门课程的学生人数及各门课程的平均成绩;
select COUNT(SC.Sno) number,AVG(grade)avgGrade
from SC
group by Cno
15.找出平均成绩在85分以上的学生,结果按系分组,并按平均成绩的升序排列;
select S.Sno, AVG(grade) avgGrage
from SC,S
where SC.Sno = S.Sno
group by S.sdept,S.Sno
having AVG(grade) > 85
order by avgGrage
16.查询选修了“1”或“2”号课程的学生学号和姓名;
方法一:
select distinct S.Sno,Sname
from S,SC
where SC.Sno=S.Sno and Cno in('1','2')
方法二:
select distinct SC.Sno, S.Sname from SC
join S on S.Sno = SC.Sno
where SC.Cno = 1 or SC.Cno = 2
17.查询选修了“1”和“2”号课程的学生学号和姓名;
方法一:
select distinct S.Sno,Sname
from S,SC
where SC.Sno = S.Sno and Cno = '1' and S.Sno in
(select distinct Sno from SC where Cno = '2' )
方法二:
select SC.Sno, S.Sname from SC
join S on S.Sno = SC.Sno
where SC.Sno in
( select Sno from SC where Cno = 1)
and Cno = 2
18.查询选修了课程名为“数据库”且成绩在60分以下的学生的学号、姓名和成绩;
select S.Sno, S.Sname,SC.Cno,SC.grade from SC
join S on S.Sno = SC.Sno
join C on C.Cno = SC.Cno
where C.Cname = '数据库'
and grade < 60
19.查询每位学生选修了课程的学生信息(显示:学号,姓名,课程号,课程名,成绩);
select S.Sno, S.Sname, SC.Cno, C.Cname, SC.grade from SC
join S on S.Sno = SC.Sno
join C on C.Cno = SC.Cno
20.查询没有选修课程的学生的基本信息;
select * from S
where not exists (select * from SC
where S.Sno = SC.Sno )
21.查询选修了3门以上课程的学生学号;
select Sno from SC
group by Sno
having COUNT(*) > 3
22.查询选修课程成绩至少有一门在80分以上的学生学号;
select Sno from SC
group by Sno
having max(Grade) > 80
23.查询选修课程成绩均在80分以上的学生学号;
select Sno from SC
group by Sno
having min(Grade)>80
24.查询选修课程平均成绩在80分以上的学生学号;
select distinct Sno from SC
group by Sno
having AVG(grade) > 80
五、实验总结
具体的子查询相关知识可以参考其他博主所写的一篇文章
SQL Server 数据库常用操作:子查询(嵌套查询)