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 student where sdept='计算机'

--（2）求选修了数学的学生学号、姓名和成绩。 select s.sno,sname,grade from student s,sc,course c

where s.sno=sc.sno and sc.cno=c.cno 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 Sname from student s where (

select count(*) from sc

where sno = s.sno select * from course c where not exists ( )

select * from sc

where sno = s.sno and cno = c.cno

)=(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)

--（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=' from sc

where sno='95001') and s.sno!='95001'

张三') )

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 ( )

--（20）检索至少选修课程“数学”和“操作系统”的学生学号。 select sc.sno from course c,sc

select s1.sno from sc s1,sc s2

where s1.cno='1' and s2.cno='3' and s1.sno=s2.sno

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,ssex,sdept from student s,sc

where s.sno=sc.sno and grade=

--（22）查询数据结构的成绩低于操作系统的成绩的学生姓名及该生的这两门课的成绩 select s1.sname,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

--（23）所有成绩都在分以上的学生姓名及所在系。 select sname,sdept from student s,sc where s.sno=sc.sno group by sname,sdept having min(grade)>=70

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

(

select max(grade) from course c,sc

where sc.cno=c.cno and cname='操作系统' )

本文来源：https://www.bwwdw.com/article/unrp.html