实验三数据查询实验.docx

1、实验三数据查询实验实验三：数据查询实验一、实验目的了解在企业管理器或查询分析器中执行数据查询的方法；掌握SQL Server Query Analyzer中简单查询、连接查询、嵌套查询操作方法。二、实验内容1、使用企业管理器进行查询（验证性，了解部分）（1）查询数据表的全部数据在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，这时将显示该表的全部数据。（2）使用SELECT语句查询数据表的数据在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，在打开返回表内容的子窗口中，单击工具栏按钮“显示/隐藏SQL窗

2、格”，把子窗口分为上下两部分，上面部分能输入不同的SQL命令。执行时单击工具栏按钮“运行”即可。（3）学习和使用QBE查询在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，在打开返回表内容的子窗口中，单击工具栏按钮“显示/隐藏条件窗格”，把子窗口分为上下两部分，上面部分能选择QBE查询条件，执行时单击工具栏按钮“运行”即可。若单击工具栏按钮“显示/隐藏SQL窗格”，则在SQL窗格中会显示自动生成的对应SQL语句。2、使用查询分析器进行查询（验证性）（1）查询Student表中所有学生的学号、姓名和性别。SELECT Sno, Sname, Ssex

3、FROM Student（2）可以用“*”来选取数据表的全部列例如：查询Student表中所有学生的基本情况。SELECT *FROM Student（3）在查询结果中增加计算列，还可修改数据列的显示名称。例如：查询Student表中所有学生的学号、姓名、性别和出生年份。SELECT Sno 学号, Sname 姓名, Ssex 性别, Year(GetDate() - Sage 出生年份FROM Student（4）使用WHERE子句，可以选择满足条件的部分记录例如：查询成绩在8590分之间的学生情况。SELECT Sno, Cno, GradeFROM SCWHERE Grade = 85

4、 AND Grade = 200)该语句执行成功后，在企业管理器中可以看到已经创建的数据表GradeList，并将查询结果是存储在该表中了。（12）将查询结果保存到变量中例如：查询学号为200215121的学生1号课的成绩，将其保存到变量Grade中。DECLARE Grade INT -定义变量SELECT Grade = Grade -给变量赋值FROM SCWHERE Sno = 200215121PRINT Grade -显示变量的值注意：要将查询结果保存到变量中，只能将查询结果集中第一条记录的值赋给变量。3、分析设计部分在学生选课库中实现下列数据查询操作，并写出相应的SQL脚本。(1

5、)求计算机系学生的学号和姓名(2)求选修3号课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列(3)求选修课程2成绩在80-90之间的学生学号和成绩,并将成绩乘以0.8输出(4)求数学或计算机系姓张的学生的信息(5)求缺少了成绩的学生的学号和课程号(6)查询各门课程及相应的选课人数。(7)查询总成绩在200分以上的学生的学号、总成绩和平均成绩(8)在FROM子句中用INNER JOIN连接符指定连接条件查询所有有2号课程成绩的学生的学号、姓名和成绩。 (9)查询学生中年龄相同的学生情况（使用自连接查询）。(10)查询所有学生的总成绩（包括没有成绩的学生）、学号和姓名（外部连接查询

6、）。(11)查询某课程成绩在90分以上的学生的学号和姓名（使用谓词IN连接子查询）。(12)查询有课程成绩的学生的学号和姓名(使用谓词EXISTS连接子查询)。(13)求没有选修2号课程的学生姓名；(14)求选修了1号课程的学生中，成绩高于张三选修1号课程的学生学号和成绩；(15)列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩。三、实验学时2学时四、实验设备与环境Windows 2003平台 + SQL Server 2008系统 -(1)求计算机系学生的学号和姓名select Sno,Snamefrom studentwhere Sdept=CS;-(2

7、)求选修3号课程的学生学号和成绩，结果按成绩降序排列，如成绩同按学号升序排列select Sno,Gradefrom SCwhere Cno=3order by Grade desc,Sno ASC;-(3)求选修课程2成绩在8090之间的学生学号和成绩，并将成绩乘以0.8输出select Sno,Grade*0.8 Gradefrom SCwhere Cno=2 and Grade between 80 and 90;-(4)求数学或计算机系姓“张”的学生信息select *from studentwhere Sname like 张% and Sdept in(MA,CS) ;-(5)求缺

8、少了成绩的学生的学号和课程号select Sno ,Cnofrom SCwhere Grade is null;-(6)查询各门课程及相应的选课人数select Course.Cno,Course.Cname,count(Sc.Sno) 选课人数from Courseleft join SC on SC.Cno=Course.Cnogroup by Course.Cno ,Course.Cname;-(7)查询总成绩在200分以上的学生的学号、总成绩和平均成绩select Sno,SUM(Grade)总成绩,AVG(Grade) 平均成绩from SCgroup by Snohaving SU

9、M(Grade)200;-(8)在form子句中用inner join连接符指定连接条件查询所有有2号课程成绩的学生的学号、姓名和成绩select student.Sno,student.Sname,Gradefrom student inner join SC on student.Sno=SC.Snowhere SC.Cno=2 -(9)查询学生中年龄相同的学生情况(使用自连接查询)select distinct a.Sno,a.Sname,a.Sagefrom student as a,student as bwhere a.Sage=b.Sage and a.Snob.Sno-(10)

10、查询所有学生的总成绩(包括没有成绩的学生)、学号和姓名(外部连接查询)select Student.Sno,Student.Sname,SUM(Grade)总成绩from Student left join SC on Student.Sno=SC.Snogroup by Student.Sno,Student.Sname-(11)查询某课程成绩在90分以上的学生的学号和姓名（使用谓词IN连接子查询）select student.Sno,student.Snamefrom Student,SCwhere Student.Sno=SC.Sno and Grade IN (select Grade

11、 from SC where Grade=90 )-(12)查询有课程成绩的学生的学号和姓名(使用谓词EXISTS连接子查询)select student.Sno,student.Snamefrom Studentwhere exists (select * from SC where SC.Sno=Student.Sno and Grade is not null )-(13)求没有选修2号课程的学生姓名select Sname from student where not exists (select * from SC where Sno=student.Sno and Cno=2)-(

12、14)求选修了1号课程的学生中，成绩高于张三选修1号课程的学生学号和成绩select Sno,Grade from Sc where Cno=1 and Grade ( select Grade from Sc where Cno=1 and Sno= ( select Sno from student where Sname=张三 ) ) -(15)列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩select SC1.Sno,1=SC1.Grade,2=SC2.Gradefrom SC SC1,SC SC2where SC1.Cno=1 and SC2.Cno=2 and SC1.Sno=SC2.Sno and SC1.GradeSC2.Grade