实验二 数据查询

 

实验名称	实验二　数据查询
实验教室	913	实验日期	2018年10月15日
学    号	2016214220	姓    名	**
专业班级	计算机科学与技术2016级 *班
指导教师	王阿川

 

东北林业大学

信息与计算机科学技术实验中心

 

一、实验目的

（1）通过本实验能够掌握投影、选择条件表达、排序、分组的sql语句表达。

（2）通过本实验能够熟练应用sql语言进行查询，具体包括单表查询，多表连接查询。

（3）通过本实验能够熟练应用sql语言使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。

（4）掌握视图的定义、查询、修改。

二、实验环境

计算机

windows7操作系统，

Oracle 11g， SQL Developer

三、实验内容及结果

• 包括排序、分组的单表查询

1. 求数学系学生的学号和姓名。
   select Sno,Sname from Student where Sdept='MA';
   
2. 求选修了课程的学生学号。
   select distinct Sno from SC;
   
3. 求选修课程号为‘2’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。
   select Sno,Grade from SC where Cno='2' order by Grade desc,Sno asc;
   
4. 求选修课程号为’2’且成绩在80～90之间的学生学号和成绩，并将成绩乘以0.8输出。
   select Sno,0.8*Grade from SC where Cno='2' and Grade between 80 and 90;
   
5. 求数学系或计算机系姓张的学生的信息。
   select * from student where Sdept in(‘MS’,’CS’) or sname like ‘张%’
   
6. 求缺少了成绩的学生的学号和课程号。

Select sno,cno from sc where grade is ‘null’

1. 查询各个课程号与相应的选课人数。

Select sno,cout(*) from sc group by cno

（二） 多表连接查询

1. 1. 1. 查询每个学生的情况以及他所选修的课程。
         select * from Student,SC where Student.Sno=SC.Sno;
         
      2. 求学生的学号、姓名、选修的课程及成绩。
         select Student.Sno,Student.Sname,SC.Cno,SC.Grade from Student,SC where Student.Sno=SC.Sno;
         
      3. 求选修课程号为‘1’且成绩在90分以上的学生学号、姓名和成绩。
         select Student.Sno,Student.Sname,SC.Grade from Student,SC where Student.Sno=SC.Sno and SC.Cno='1' and sc.grade>90;
         
      4. 查询每一门课程的间接先行课。
         Select  First.Cno，Second.Cpno From Course First，Course Second Where First.Cpno=Second.Cno;
         
      5. 查询与’刘晨’在同一个系学习的学生。
         select s1.* from student s1,student s2 where s1.sdept=s2.sdep and s2.sname=’刘晨’
      6. 查询选修了课程名为‘信息系统‘的学生学号和姓名。
         select Student.Sno,Student.Sname from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Course.Cname='信息系统';
         
      7. 查询平均成绩在80分以上的学生学号和平均成绩。
         select Sno,avg(Grade) from SC group by Sno having avg(Grade)>80;
         
      8. 查询选修了1门以上课程的学生的学号。
         select Sno,count(*) from SC group by Sno having count(*)>1;
         

（三） 嵌套查询

1. 求选修了信息系统的学号和姓名。
   select Sno,Sname from Student where Sno in(select Sno from SC where Cno=(select Cno from Course where Cname='信息系统'));
   
2. 查询与刘晨在同一个系学习的学生。
   select Sname from student where sdept in(select SDEPT from student where sname='刘晨')
   
3. 求选修1号课程的成绩高于刘晨的成绩（指刘晨选修的所有的课程的成绩）的学生学号及成绩。
   select sno,grade from sc where cno=’1’ and grade>(select max(grade) from sc where sno=(select sno from student where sname=’刘晨’))
   
4. 求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）。
   select sname from STUDENT where sdept!='CS' and  Sage < (select max(Sage) from Student where Sdept='CS');
   
5. 求其他系中比计算机系学生年龄都小的学生姓名及年龄。
   select Sname,Sage from Student where Sdept!='CS' and Sage < (select min(Sage) from Student where Sdept='CS');
   
6. 求没有选修3号课程的学生姓名。
   select Sname from Student where Sno in(select Sno from SC where Cno!='3');
   
7. 查询选修了全部课程的学生姓名。
   select Sname from Student where not exists(select * from Course where not exists(select * from SC where Sno=Student.Sno and Cno=Course.Cno));
   

SQL语言中没有全称量词∨（，all）。但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词。(∨x)P≡∟(exists x(∟P))

试做：查询所有学生都选修的课程名
select Cname from Course where not exists(select * from Student where not exists(select * from SC where Sno=Student.Sno and Cno=Course.Cno));

1. 求至少选修了学号为“200215121”的学生所选修全部课程的学生学号和姓名。
   select Sno,Sname from Student where Sno in(select distinct Sno from SC s1 where not exists(select * from SC s2 where s2.Sno='200215121' and not exists(select * from SC s3 where s1.Sno=s3.Sno and s2.Cno=s3.Cno)));
   
2. 求选修课程超过2门的学生的学号和姓名。
   select Sno,Sname from Student where Sno in(select Sno from SC group by Sno having count(*)>2);
   

（四） 视图

1. 建立信息系学生的视图。并查询此视图，观察结果。
create view StuView (Sno,Sname,Ssex,Sage) as select Sno,Sname,Ssex,Sage from Student where Sdept='IS';

select * from StuView;

2.（在视图上建立）建立信息系选修了1号课程的学生的视图。查询此视图，并观察结果。
create view StuView_1 (Sno,Sname,Ssex,Sage,Grade) as select StuView.Sno,Sname,Ssex,Sage,Grade from StuView,SC where Cno='1';
 

select * from StuView_1;

3.将学生的学号及其平均成绩定义为一个视图。查询此视图，观察结果。
create view StuAvg (Sno,Avg) as select Sno,avg(Grade) from SC group by Sno;
select * from StuAvg;

4.将Student表中所有女生记录定义为一个视图F_stu（sno，sname，sdept，sex），并设置其更新限制with check option
。

5.对4中的视图进行insert操作，将sno为200215129，sname为‘smith’，sdept为‘MA’插入视图中，结果如何？
  insert into F_stu (sno，sname，sdept，sex) values ('200215129','smith','MA');     
  插入失败

6. 对4中的视图进行insert操作，将sno为200215129，sname为‘smith’，sdept为‘MA’,sex为‘女’插入视图中，结果如何？
     I nsert into F_stu (sno，sname，sdept，sex) values ('200215129','smith','MA','女');   

5.6有什么区别？

视图：视图子查询中允许任意复杂的SELECT语句，但通常不允许含有Order By子句和distinct短语。

组成视图的属性列名或者全部省略，或者全部指定。

一下三种情况必须明确指定组成视图的所有列名：

1）某个目标列不是单纯的属性名，而是聚集函数或列表达式。

2）多表连接时选出了几个同名列作为视图的字段。

3）需要为某个列启用新的更合适的名字。

With CHECK OPTION 表示对视图进行update、insert、delete操作时要保证更新、插入或删除的行满洲子查询中的条件表达式。

 

 

 

 

 

 

 

 

 

 

 

 

 

四、实验过程分析与讨论

1． 实验过程分析与讨论：

2．思考题

（1）Where子句中能否用聚集函数作为条件表达式。
不能，聚集函数的条件表达式用having实现。

（2）多表连接查询中如果显示的某一属性不止一个表中出现，应如何处理。（例：（二）多表连接查询中的第2题）。
确定要显示的为那个表出现的属性，如确定是student.sno或sc.sno;

（3）在嵌套查询中，什么情况下用“IN”和“＝”都可以。

内层查询只返回一个值。

 

五、指导教师意见

       

指导教师签字：

                                          年  月   日