实验二数据库的查询实验

一、实验目的和要求

掌握 SQL Server 查询分析器的使用方法，加深对 SQL 和 Transact-SQL 语言的查询 语句的理解。

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

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

二、实验内容和原理

在实验一定义的“学生成绩数据库”中，使用 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 分以上的学生姓名及所在系。

三、实验环境

环境：sqlsever 2010

四、实验方法

1.将查询需求用Transact-SQL语言表示。

2.在SQL Server查询分析器的输入区中输入于TSQL语句的执行结果，在结果区中 可以有4种不同的输出形式：标准执行将结果直接显示在结果区。

网格执行将结果以表格形式显示在结果区；计划执行显示执行计划；索引分析为在结果区中显示查询的索引情况。述输出形式可以通过菜单或按钮选择。

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

查询分析器及使用方法。

查询分析器是在开发数据库应用系统时使用最多的工具。查询分析器的主要作用是编辑Transact-SQL，其发送到 服务器，并将执行结果及分析显示出来(或进行存储)。查询分析功能主要通过测 试查询成本，判断该查询是否需要增加索引以提高查询速度，并可以实现自动建立索引的功能、查询分析器的界面如图1所示。

图（1-1）

在查询分析器中的左边窗口是对 象 浏览器，其中按树结构列出了数据库 对 象；右上方是 SQL代码区域？用于 输入SQL的查询语句；右下方为结果 区，用于显示查询结果和分析结果。

1. 调试过程（实验结果）

--(1)求计算机系学生的学号和姓名

select sno , sname

from stude nt

where sdept ='计算机'

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

from stude nt s ,sc , course c

select s . sno , sname , grade

where s . sno 二sc . sno and sc . eno 二c. eno and cn ame =' 数学

--(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) 查询选修 “数据库 ”课程，且成绩在分以上的学生的学号和成绩

sc

select sno , grade from course c ,

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 )求选修了学生 “95001”所选修的全部课程的学生学号和姓名

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 张三 ' ) ⁾ group by sname having sname != ' 张三）

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

）

from course c

，sc

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

select sc . sno

where c . cname =' 数学 'and c . eno 二 sc . eno and sno in

(

select sc . sno

from sc , course c

where c . cn ame = '操作系统 ’and c .eno 二 sc eno

）

--(21 )查询操作系统'课程的最高分的学生的姓名、性别、所在系

select sname,ssex , sdept

from stude nt s,sc

where s . sno =sc . sno and ₍grade =

select max( grade )

where sc . eno 二c. eno

and cnane =’操作系统

from course c,sc

--(22 )查询数据结构的成绩低于操作系统的成绩的学生姓名及该生的这两门课的成绩

select	si . snam e,sc1 . grade		as '	操作系统成绩',sc2 .	grade as '数据结构成绩
from	course ci	,course c2 ,	sc sei	sc2 , stude nt si	,stude nt s2
where	ci . ename	='操作系统'	and c2	.ename ='数据结构'
	and sei	.grade >sc2 .	grade
	and sei	.sno 二sc2 . sno
	and ci .	eno 二sci . eno	and	c2 . eno 二sc2 . eno
	and si .	sno 二sci . sno	and	s2 . sno 二sc2 . sno

--(23 )所有成绩都在分以上的学生姓名及所在系

select sname , sdept from stude nt s , sc

where s . sno 二sc . sno group by sname , sdept

实验总结

通过学习数据库，我学到了很多知识，并且清楚的认识到数据库对于我们专业的重要性，sql sever的实验学习使我对数据库的学习更进一步。