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

1、实验二 数据库的查询实验一、 实验目的和要求（1）掌握SQL Server查询分析器的使用方法，加深对SQL和Transact-SQL语言的查询语句的理解。（2）熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。（3）熟练掌握数据查询中的分组、统计、计算和组合的操作方法。二、 实验内容和原理在实验一定义的“学生成绩数据库”中，使用T-SQL语句完成以下查询：（1）求计算机系学生的学号和姓名。（2）求选修了数学的学生学号、姓名和成绩。（3）求选修01课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。（4）查找选修课程的平均成绩位于前三名的学生的学号。（

2、5）查询计算机系的姓刘且单名的学生的信息。（6）查询至少选修两门课程的学生学号。（7）查询学生的学号、课程号以及对应成绩与所有学生所有课程的最高成绩的百分比。（8）查询选修“数据库”课程，且成绩在80分以上的学生的学号和成绩。（9）查询所有姓“王”的同学没有选修的课程名。（请分别用exists和in完成该查询）（10）查询选修了全部课程的学生的姓名。（请至少写出两种查询语句）（11）求选修了学生“95001”所选修的全部课程的学生学号和姓名。（12）查询每一门课的间接先修课。（13）列出所有学生所有可能的选课情况。（14）列出每个学生的学号及选修课程号，没有选修的学生的学号也一并列出。（15）

3、输出与“张三”同性别并位于同一个系的所有同学的姓名。（请至少写出两种查询语句）（16）查询至少被两名男生选修的课程名。（17）对被两名以上学生所选修的课程统计每门课的选课人数。要求输出课程号和选修人数，查询结果按人数降序排列；若人数相同，按课程号升序排列。（18）列出选修课程超过3门的学生姓名及选修门数。（19）检索至少选修课程号为01和03的学生姓名。（20）检索至少选修课程“数学”和“操作系统”的学生学号。（21）查询操作系统课程的最高分的学生的姓名、性别、所在系（22）查询数据结构的成绩低于操作系统的成绩的学生姓名及该生的这两门课的成绩（23）所有成绩都在70分以上的学生姓名及所在系。三

4、、实验环境四、实验方法 1. 将查询需求用Transact-SQL语言表示。 2. 在 SQL Server查询分析器的输入区中输入 Transact-SQL查询语句。 3. 发布执行命令，查看查询结果；如果结果不正确，进行修改，直到正确为止。 4. 查询分析器及使用方法。图1 SQL Server 2000查询分析器 查询分析器是在开发数据库应用系统时使用最多的工具。查询分析器的主要作用是编辑Transact-SQL，将其发送到服务器，并将执行结果及分析显示出来（或进行存储）。查询分析功能主要通过测试查询成本，判断该查询是否需要增加索引以提高查询速度，并可以实现自动建立索引的功能、查询分析器

5、的界面如图1所示。 在查询分析器中的左边窗口是对象浏览器，其中按树结构列出了数据库对象；右上方是SQL代码区域用于输入 SQL的查询语句；右下方为结果区，用于显示查询结果和分析结果、对于TSQL语句的执行结果，在结果区中可以有4种不同的输出形式：标准执行将结果直接显示在结果区：网格执行将结果以表格形式显示在结果区；计划执行显示执行计划；索引分析为在结果区中显示查询的索引情况。上述输出形式，可以通过菜单或按钮选择。五、调试过程五、实验结果六、总结附录： -（1）求计算机系学生的学号和姓名。select sno,sname from student where sdept='计算机'

6、;-（2）求选修了数学的学生学号、姓名和成绩。select s.sno,sname,grade from student s,sc,course c where s.sno=sc.sno and o=o and cname='数学'-（3）求选修课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。select sno,grade from sc where cno='1' order by grade desc,sno asc-（4）查找选修课程的平均成绩位于前三名的学生的学号。select top 3 sno from sc g

7、roup 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(gr

8、ade)from sc)from sc -（8）查询选修“数据库”课程，且成绩在分以上的学生的学号和成绩。select sno,gradefrom course c,scwhere cname='数据库' and grade>80 and o=o-（9）查询所有姓“王”的同学没有选修的课程名。（请分别用exists和in完成该查询）-exists方法select cnamefrom course cwhere not exists(select s.snofrom student s,scwhere s.sno=sc.sno and sname like '王%&

9、#39; and o=o )-in方法select cnamefrom course cwhere cno not in(select cnofrom student s,scwhere s.sno=sc.sno and sname like '王%' and o=o )-（10）查询选修了全部课程的学生的姓名。（请至少写出两种查询语句）-法一select Snamefrom student swhere not exists(select *from course cwhere not exists(select *from scwhere sno = s.sno and c

10、no = o)-法二select Snamefrom student swhere (select count(*)from sc where sno = s.sno)=(select count(*) from course)-法三select Snamefrom studentwhere Sno in(select Sno from sc group by sno having count(*) = (select count(*) from course)-（11）求选修了学生“”所选修的全部课程的学生学号和姓名。select s.sno,sname from student s,scw

11、here o in( select cno from sc where sno='95001')and s.sno=sc.snogroup by s.sno,snamehaving count(cno)=(select count(cno) from sc where sno='95001') and s.sno!='95001'-（12）查询每一门课的间接先修课。select o,c2.cpnofrom course c1,course c2where c1.cpno = o-（13）列出所有学生所有可能的选课情况。select s.sno,s

12、.sname,o,amefrom student s cross join course c-（14）列出每个学生的学号及选修课程号，没有选修的学生的学号也一并列出。select s.sno,ofrom student s left outer join scon s.sno = sc.sno-（15）输出与“张三”同性别并位于同一个系的所有同学的姓名。（请至少写出两种查询语句）-法一select snamefrom studentwhere sdept in(select sdept from studentwhere sname = '张三' and ssex =(sele

13、ct ssex from student where sname='张三') group by snamehaving sname!='张三'-法二select snamefrom student where sdept =(select sdept from studentwhere sname = '张三' and ssex =(select ssex from student where sname='张三')group by snamehaving sname!='张三'-（16）查询至少被两名男生选修的课

14、程名。select cnamefrom course c,student s,scwhere ssex='男' and o=o and s.sno=sc.snogroup by cnamehaving count(*)>=2-（17）对被两名以上学生所选修的课程统计每门课的选课人数。- 要求输出课程号和选修人数，查询结果按人数降序排列；若人数相同，按课程号升序排列。select cno,count(*) as '选修人数'from scgroup by cnohaving count(*)>2order by '选修人数'desc,c

15、no asc-（18）列出选修课程超过门的学生姓名及选修门数。select sname,count(*) as '选修门数'from student s,scwhere s.sno=sc.snogroup by s.sno,snamehaving count(*)>3-（19）检索至少选修课程号为和的学生姓名。select snamefrom studentwhere sno in(select s1.snofrom sc s1,sc s2where o='1' and o='3' and s1.sno=s2.sno)-（20）检索至少选修

16、课程“数学”和“操作系统”的学生学号。select sc.snofrom course c,sc where ame = '数学' and o = o and sno in ( select sc.snofrom sc ,course c where ame = '操作系统' and o = o)-（21）查询操作系统课程的最高分的学生的姓名、性别、所在系select sname,ssex,sdeptfrom student s,scwhere s.sno=sc.sno and grade= (select max(grade) from course c,sc where o=o and cname='操作系统')-（22）查询数据结构的成绩低于操作系统的成绩的学生姓名及该生的这两门课的成绩select s1.sname,sc1.grade as '操作系统成绩',sc2.grade as '数据结构成绩'from course c1,course c2,sc sc1,sc sc2,student s1,student s2where