数据库Oracle-实验二 数据查询

一、实验目的

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

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

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

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

二、实验环境

计算机

windows7操作系统，

Oracle 11g， SQL Developer

三、实验内容及结果

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

求数学系学生的学号和姓名。

代码：select sno,sname from student where sdept=‘MA’;

截图：

求选修了课程的学生学号。

代码：select sno from SC;

截图：

求选修课程号为‘2’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。

代码：select sno,grade from sc where cno=‘2’

order by grade desc,sno;

截图：

求选修课程号为’2’且成绩在80～90之间的学生学号和成绩，并将成绩乘以0.8输出。

代码：select sno,0.8*grade Grade2 from sc where cno=‘2’

and grade >=80 and grade<=90;

截图：

求数学系或计算机系姓张的学生的信息。

代码：select * from student

where sdept in (‘CS’,‘MA’) and sname like ‘张%’;

截图：

求缺少了成绩的学生的学号和课程号。

代码：select sno,cno from sc where GRADE is null;

截图：

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

代码：select cno,count(*) from SC group by CNO;

截图：

（二） 多表连接查询

查询每个学生的情况以及他所选修的课程。

代码：select student.sno,sname,sage,ssex,sdept,cno from student,sc

where student.sno=sc.sno;

截图：

求学生的学号、姓名、选修的课程及成绩。

代码：select student.sno,sname,cname,grade from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno;

截图：

求选修课程号为‘1’且成绩在90分以上的学生学号、姓名和成绩。

代码：select student.sno,sname,grade from student,sc

where cno=‘1’ and grade > 90;

截图：

查询每一门课程的间接先行课。

代码：select fir.cno,sec.cpno

from COURSE fir,COURSE sec

where fir.cpno=sec.CNO;

截图：

查询与’刘晨’在同一个系学习的学生。

代码：select sno,sname from student

where sdept in(select sdept from student where sname = ‘刘晨’)

and sname !=‘刘晨’;//其中如果去掉第三行的代码则表示也显示刘晨

截图：

查询选修了课程名为‘信息系统‘的学生学号和姓名。

代码：select sno,sname from student

where sno in(select sno from sc

where cno in(select cno from course where cname=‘信息系统’));

截图：

查询平均成绩在80分以上的学生学号和平均成绩。

代码：select sno,AVG(grade) from SC

group by sno

having AVG(grade)>80;

截图：

查询选修了1门以上课程的学生的学号。

代码：select sno, count(*)from sc

group by sno

having count(*)>=1;

截图：

（三） 嵌套查询

求选修了信息系统的学号和姓名。

代码：select sno,sname from student

where sno in(select sno from sc

where cno in(select cno from course where cname=‘信息系统’));

截图：

查询与刘晨在同一个系学习的学生。

代码：select sno,sname from student

where sdept in(select sdept from student where sname = ‘刘晨’)

and sname !=‘刘晨’;//其中如果去掉第三行的代码则表示也显示刘晨

截图：

求选修1号课程的成绩高于刘晨的成绩（指刘晨选修的所有的课程的成绩）的学生学号及成绩。

代码：select sno, grade from sc

where grade > (select max(grade) from sc

where sno in(select sno from student where sname=‘刘晨’));

截图：

求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）。

代码：select sno, sname from student

where sage < (select max(sage) from student

where sdept=‘CS’) and sdept !=‘CS’;

截图：

求其他系中比计算机系学生年龄都小的学生姓名及年龄。

代码：select sno, sname from student

where sage < ALL(select sage from student

where sdept=‘CS’) and sdept !=‘CS’;

截图：

求没有选修3号课程的学生姓名。

代码：select sname from student

where not exists

(select * from sc

where sno=student.sno and cno=‘3’);//括号里相当于sc，student的自然连接，并从中选取选课号为3的学号（自己理解，错了狗头保命）

截图：

查询选修了全部课程的学生姓名。

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

试做：查询所有学生都选修的课程名

代码：select sname from student

where not exists

(select * from course where not exists

(select * from sc where sno=student.sno and cno=sc.cno));

截图：

求至少选修了学号为“200215121”的学生所选修全部课程的学生学号和姓名。

代码：select sno,sname from student where sno in(

select DISTINCT sno from sc x

where not EXISTS(select * from sc y where y.sno='200215121’and

not exists(select * from sc z where z.sno=x.sno and z.cno=y.cno)));

截图：

求选修课程超过2门的学生的学号和姓名。

代码：select sno, count(*)from sc

group by sno

having count(*)>2;

截图：

（四） 视图

1. 建立信息系学生的视图。并查询此视图，观察结果。

代码：CREATE VIEW IS_student as

SELECT * FROM student where SDEPT=‘IS’;

截图：

（在视图上建立）建立信息系选修了1号课程的学生的视图。查询此视图，并观察结果。

代码：CREATE VIEW IS_s1(sno,sname,grade) as

SELECT * FROM student,sc where SDEPT=‘IS’

and student.sno=sc.sno and sc.cno=‘1’;

select * from IS_S1;

截图：

将学生的学号及其平均成绩定义为一个视图。查询此视图，观察结果。

代码：create VIEW s_g(sno,avg) as

select sno,avg(grade) from sc group by sno;

select * from s_g;

截图：

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

代码：create VIEW F_stu(sno,sname,sdept,ssex) as

select sno,sname,sdept,ssex from student where ssex=‘女’

with check option;

截图：

对4中的视图进行insert操作，将sno为200215129，sname为‘smith’，sdept为‘MA’插入视图中，结果如何？ 

代码：insert into F_STU (sno,sname,sdept)

values (‘200215129’,‘smith’,‘MA’);

截图：

结果失败，子句违规

对4中的视图进行insert操作，将sno为200215129，sname为‘smith’，sdept为‘MA’,sex为‘女’插入视图中，结果如何？

代码：insert into F_STU (sno,sname,sdept,ssex)

values (‘200215129’,‘smith’,‘MA’,‘女’);

截图：

结果成功插入

5.6有什么区别？

区别：在6当中加入了sex为女的信息。

原因：在定义视图的时候加上了子句with check option，使得对该视图进行更新操作的时候，关系数据库管理系统会自动加上ssex=‘女’的条件，如果不满足条件，则显然子句违规。