SQL实验二：数据库查询实验报告

实验二数据库的查询实验
实验目的和要求
(1)掌握SQL Server查询分析器的使用方法，加深对SQL和Transact-SQL语言的查询语句的理解。

(2)熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。

(3)熟练掌握数据查询中的分组、统计、计算和组合的操作方法。

实验内容和原理
在实验一定义的“学生成绩数据库”中，使用T-SQL 语句完成以下查询：
(1 )求计算机系学生的学号和姓名。

(2)求选修了数学的学生学号、姓名和成绩。

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

(4)查找选修课程的平均成绩位于前三名的学生的学号。

(5)查询计算机系的姓刘且单名的学生的信息。

(6)查询至少选修两门课程的学生学号。

(7)查询学生的学号、课程号以及对应成绩与所有学生所有课程的最高成绩的百分比。

(8)查询选修“数据库”课程，且成绩在80 分以上的学生的学号和成绩。

(9)查询所有姓“王”的同学没有选修的课程名。

(请分别用exists和in完成该查询) (10)查询选修了全部课程的学生的姓名。

(请至少写出两种查询语句)
(11)求选修了学生“ 95001”所选修的全部课程的学生学号和姓名。

(12)查询每一门课的间接先修课。

(13)列出所有学生所有可能的选课情况。

(14)列出每个学生的学号及选修课程号，没有选修的学生的学号也一并列出。

(15)输出与“张三”同性别并位于同一个系的所有同学的姓名。

(请至少写出两种
查询语句)
(16)查询至少被两名男生选修的课程名。

(17)对被两名以上学生所选修的课程统计每门课的选课人数。

要求输出课程号和选修人数，查询结果按人数降序排列；若人数相同，按课程号升序排列。

(18)列出选修课程超过 3 门的学生姓名及选修门数。

(19)检索至少选修课程号为01 和03 的学生姓名。

(20)检索至少选修课程“数学”和“操作系统”的学生学号。

(21 )查询‘操作系统'课程的最高分的学生的姓名、性别、所在系
(22)查询数据结构的成绩低于操作系统的成绩的学生姓名及该生的这两门课的成绩
(23)所有成绩都在70 分以上的学生姓名及所在系。

三、实验环境
四、实验方法
1. 将查询需求用Transact-SQL语言表示。

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

3. 发布执行命令，查看查询结果；如果结果不正确，进行修改，直到正确为止。

4. 查询分析器及使用方法。

查询分析器是在开发数据库应用系
统时使用最多的工具。

查询分析器的主要
作用是编辑Transact-SQL，将其发送到
服务器，并将执行结果及分析显示出来
(或进行存储)。

查询分析功能主要通过测
试查询成本，判断该查询是否需要增加索
引以提高查询速度，并可以实现自动建立
索引的功能、查询分析器
的界面如图1所示。

在查询分析器中的左边窗口是对象
浏览器，其中按树结构列出了数据库对
象；右上方是SQL代码区域•用于输入
SQL的查询语句；右下方为结果区，用于
显示查询结果和分析结果、对
图1 SQL Server 2000查询分析器
于TSQL语句的执行结果，在结果区中
可以有4种不同的输出形式：标准执行将
结果直接显示在结果区：网格执行将结果以表格形
式显示在结果区；计划执行显示执行计划；索引分析为在结果区中显示查询的索引情况。

上述输出形式，可以通过菜单或按钮选择。

五、调试过程
五、实验结果
六、总结
附录：
--(1)求计算机系学生的学号和姓名。

select sno , sname
from stude nt
where sdept ='计算机'
--(2)求选修了数学的学生学号、姓名和成绩。

select s . sno , sname , grade
from student s , sc , course c
where s . sno =sc . sno and sc . eno =c. eno and cname ='数学'
--(3 )求选修课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排
列。

select sno , grade from sc where cno ='1' order by grade desc , sno asc
-- (4)查找选修课程的平均成绩位于前三名的学生的学号。

select top 3 sno from sc group by sno order by avg ( grade ) desc
-- (5 )查询计算机系的姓刘且单名的学生的信息。

select *
from student
where sdept =' 计算机' and sname like ' 刘_'
-- (6)查询至少选修两门课程的学生学号。

select sno from sc group by sno having count (*)>= 2
-- (7)查询学生的学号、课程号以及对应成绩与所有学生所有课程的最高成绩的百分比。

select sno , cno , grade , 最高成绩百分比= grade *100 /( select max( grade ) from sc ) from sc
-- (8)查询选修“数据库”课程，且成绩在分以上的学生的学号和成绩。

select sno , grade from course c , sc
where cname =' 数据库' and grade >80 and c . cno =sc . cno
--(9)查询所有姓王”的同学没有选修的课程名。

(请分别用exists 和in完成该查询)
--exists 方法
select cname
from course c
where not exists
(
select s . sno
from student s , sc
where s . sno =sc . sno and sname like ' 王%' and c . cno =sc . cno )
--in 方法
select cname from course c where cno not in
(
select cno
from student s , sc
where s . sno =sc . sno and sname like ' 王%' and c . cno =sc . cno )
-- (10 )查询选修了全部课程的学生的姓名。

(请至少写出两种查询语句)
-- 法一select Sname from student s where not exists
(
select * from course c where not exists
(
select *
from sc
where sno = s . sno and cno = c . cno
)
)
-- 法二
select Sname from student s where (
select count (*)
from sc
where sno = s . sno
)=( select count (*) from course )
-- 法三select Sname from student where Sno in
(
select Sno from sc group by sno
having count (*) = ( select count (*) from course ) )
-- (11 )求选修了学生“”所选修的全部课程的学生学号和姓名select s . sno , sname from student s , sc where sc . cno in
( select cno
from sc
where sno = '95001'
)and s . sno =sc . sno
group by s . sno , sname
having count ( cno )=( select count ( cno )
from sc
where sno = '95001' ) and s . sno != '95001'
-- (12 )查询每一门课的间接先修课。

select c1 . cno , c2 . cpno
from course c1 , course c2
where c1 . cpno = c2 . cno
-- (13 )列出所有学生所有可能的选课情况。

select s . sno , s. sname , c. cno , c. cname
from student s cross join course c
-- (14 )列出每个学生的学号及选修课程号，没有选修的学生的学号也一并列出。

select s . sno , sc . cno
from student s left outer join sc
on s . sno = sc . sno
-- ( 15 )输出与“张三”同性别并位于同一个系的所有同学的姓名。

(请至少写出两种查询语句) -- 法一
select sname
from student
where sdept in
(
select sdept
from student
where sname = ' 张三' and ssex =( select ssex from student where sname = 张三' )
)
group by sname
having sname != ' 张三'
-- 法二
select sname from student where sdept = (
select sdept
from student
where sname = ' 张三' and ssex =( select ssex from student where 张三' )
sname = )
group by sname
having sname != ' 张三'
-- (16 )查询至少被两名男生选修的课程名。

select cname
from course c , student s , sc
where ssex =' 男' and c . cno =sc . cno and s . sno =sc . sno group by cname
having count (*)>= 2
-- (17 )对被两名以上学生所选修的课程统计每门课的选课人数。

-- 要求输出课程号和选修人数，查询结果按人数降序排列；若人数相同，按课程号升序排列。

select cno , count (*) as ' 选修人数'
from sc
group by cno
having count (*)> 2
order by ' 选修人数' desc , cno asc
-- (18 )列出选修课程超过门的学生姓名及选修门数。

select sname , count (*) as ' 选修门数' from student s , sc
where s . sno =sc . sno group by s . sno , sname having count (*)> 3
-- (19 )检索至少选修课程号为和的学生姓名。

select sname
from student
where sno in
(
select s1 . sno from sc s1 , sc s2
where s1 . cno ='1' and s2 . cno ='3' and s1 . sno =s2 . sno
)
-- (20 )检索至少选修课程“数学”和“操作系统”的学生学号。

select sc . sno
from course c , sc
where c . cname = ' 数学' and c . cno = sc . cno and sno in
(
select sc . sno
from sc , course c
)
where c . c name = ' 操作系统' and c .cno = sc cno
-- （21 ）查询
‘操作系统'课程的最高分的学生的姓名、性别、所在系
select sname , ssex , sdept
from student s , sc
where s . sno =sc . sno and
(
grade =
select max( grade )
from course c , sc
)
where sc . cno =c. cno and cname =' 操作系统
-- （22 ）查询数据结构的成绩低于操作系统的成绩的学生姓名及该生的这两门课的成绩select s1 . s nam e,sc1 . grade as ' 操作系统成绩' , sc2 . grade as ' 数据结构成绩from course c1 , course c2 , sc sc1 > sc sc2 , student s1 , student s2
where c1 . cname =' 操作系统' and c2 . cname =' 数据结构'
and sc1 . grade >sc2 . grade
and sc1 . sno =sc2 . sno
and c1 . cno =sc1 . cno and c2 . cno =sc2 . cno
and s1 . sno =sc1 . sno and s2 . sno =sc2 . sno
-- (23 )所有成绩都在分以上的学生姓名及所在系select sname , sdept from student s , sc
where s . sno =sc . sno group by sname , sdept。