2024年3月6日发(作者：)
实验二 数据库的查询实验
实验目的和要求
(1) 掌握SQL Server查询分析器的使用方法，加深对 SQL和Transact-SQL语言的 查询语句的理解。
(2) 熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。
(3) 熟练掌握数据查询中的分组、统计、计算和组合的操作方法。
实验内容和原理
在实验一定义的“学生成绩数据库”中，使用 T-SQL 语句完成以下查询：
(1 )求计算机系学生的学号和姓名。
(2) 求选修了数学的学生学号、姓名和成绩。
(3) 求选修 01 课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果 成绩相同则按学号升序排列。
(4) 查找选修课程的平均成绩位于前三名的学生的学号。
(5) 查询计算机系的姓刘且单名的学生的信息。
(6) 查询至少选修两门课程的学生学号。
(7) 查询学生的学号、 课程号以及对应成绩与所有学生所有课程的最高成绩的百分 比。
(8)
以上的学生的学号和成绩。
(9)
的课程名。
(10)
少写出两种查询语句)
(11) 求选修了学生“ 95001”所选修的全部课程的学生学号和姓名。
(12) 查询每一门课的间接先修课。
(13) 列出所有学生所有可能的选课情况。
(14) 列出每个学生的学号及选修课程号，没有选修的学生的学号也一并列出。
(15)
性别并位于同一个系的所有同学的姓名。
查询语句)
(16) 查询至少被两名男生选修的课程名。
(17) 对被两名以上学生所选修的课程统计每门课的选课人数。要求输出课程号和 选修人数，查询结果按人数降序排列；若人数相同，按课程号升序排列。
(18) 列出选修课程超过 3 门的学生姓名及选修门数。
(19) 检索至少选修课程号为 01 和 03 的学生姓名。
(20) 检索至少选修课程“数学”和“操作系统”的学生学号。
(21 )查询‘操作系统'课程的最高分的学生的姓名、性别、所在系
(22) 查询数据结构的成绩低于操作系统的成绩的学生姓名及该生的这两门课的成 绩
(23) 所有成绩都在 70 分以上的学生姓名及所在系。
输出与“张三”同(请至少写出两种
查询所有姓“王”的同学没有选修(请分别用exists和in完成该查 询)
查询选修了全部课程的学生的姓名。 (请至 查询选修“数据库”课程，且成绩在 80 分
三、实验环境
四、实验方法
1. 将查询需求用Transact-SQL语言表示。
2. 在SQL Server查询分析器的输入区中输入
4. 查询分析器及使用方法。
查询分析器是在开发数据库应用 系统时使用最多的工具。查询分析器的 主要作用是编辑Transact-SQL，将其发 送到服务器，并将执行结果及分析显示 出来(或进行存储)。查询分析功能主 要通过测试查询成本，判断该查询是否 需要增加索引以提高查询速度，并可以 实现自动建立索引的功能、
的界面如图1所示。
在查询分析器中的左边窗口是对 象浏览器，其中按树结构列出了数据库 对象；右上方是 SQL代码区域•用于 输入SQL的查询语句；右下方为结果 区，用于显示查询结果和分析结果、 对
图1 SQL Server 2000查询分析器
Transact-SQL查询语句。
3. 发布执行命令，查看查询结果；如果结果不正确，进行修改，直到正确为止。
查询分析器
于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 方法
select cname
from course c
where not exists
(
王”的同学没有选修的课程名。(请分别用 exists 和in完成该查询)
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 . cname =
' 操作系统 ' and
)
c .cno = sc cno
-- （21 ）查询
‘
select sname
from student s
where s . sno
操作系统 '课程的最高分的学生的姓名、性别、 所在系
, ssex , sdept
, sc
=sc . sno
and
grade =
(
select
max( grade )
from course c , sc
where sc . cno =c. cno
)
and cname ='
操作系统
-- （22 ）查询数据结构的成绩低于操作系统的成绩的学生姓名及该生的这两门课的成绩
select s1 . snam e,sc1 . grade
, course c2 ,
as '
sc sc1
操作系统成绩 ' , sc2 .
> sc sc2 , student s1
grade as ' 数据结构成绩
, student s2
from course c1
where c1 . cname
=' 操作系统 '
and sc1 . grade >sc2 .
and c2 .
cname =' 数据结构 '
grade
and sc1 . sno =sc2 . sno
and c1 . cno =sc1 . cno
and s1 . sno =sc1 . sno
and c2 . cno =sc2 . cno
and s2 . sno =sc2 . sno
-- (23 )所有成绩都在分以上的学生姓名及所在系 select sname , sdept from student s , sc
where s . sno =sc . sno group by sname , sdept
发布评论