数据库实验——2

实验二　数据查询

实验目的：

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

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

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

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

实验要求：

1. 在进行本实验之前，应熟练课程内容，在上机之前做好实验计划，编写好相应的代码。

2. 认真填写实验报告，记录所有实验用例。

实验内容：

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

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 0.8*grade,sno from sc where cno='2'and grade between 80 and 90;

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

select *from student where sname like '张%' and sdept in('MA','CS');

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

select sno,cno from sc where grade is null;

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

select cno,count(*) from sc group by cno;

（二） 多表连接查询

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

select * from student,sc where student.SNO=sc.SNO;

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

3． select student.sno,student.sname,course.cname,grade from student,sc,course where student.SNO=sc.SNO and sc.CNO=course.CNO;

之前写法：select sno,sname,cname,grade from student,sc,course where student.SNO=sc.SNO and sc.CNO=course.CNO;

column ambiguously defined错误，原因是select后面元素语句中的列名，不明确

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

select student.sno,student.sname,grade

from student,sc,course where student.SNO=sc.SNO and sc.CNO=course.CNO and sc.CNO='1' and grade>90;

错误sc.CNO=course.CNO=sc.CNO='1'

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

    select a.CNAME 课程,c.cname间接先行课

from course a,course b,course c where a.CPNO=b.CNO and b.cpno=c.CNO;

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

 select * from student a where a.SDEPT=

 (select sdept

from student b where sname='刘晨');

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

select student.sno,student.sname

from course,SC,STUDENT

where STUDENT.SNO=SC.SNO and course.CNO=SC.CNO and CNAME='信息系统';

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

select student.sno,avg(grade)

from course,SC,STUDENT

where STUDENT.SNO=SC.SNO and course.CNO=SC.CNO

group BY STUDENT.SNO

having avg(grade)>80;

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

select student.sno,count(sc.cno)

from SC,STUDENT

where STUDENT.SNO=SC.SNO

group BY STUDENT.SNO

having count(sc.cno)>1;

（三） 嵌套查询

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

select student.sno,STUDENT.SNAME

from STUDENT,SC

where STUDENT.SNO=SC.SNO and SC.CNO=

(select cno from course where cname='信息系统');

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

select *

from STUDENT

where sdept=

(select STUDENT.SDEPT from student where STUDENT.SNAME='刘晨')and STUDENT.SNAME!='刘晨';

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

   select student.sno,sc.grade

from sc,student

where cno='1' and SC.SNO=STUDENT.SNO

group by student.sno,sc.grade

having sum(sc.grade)=(

select sum(grade)

from sc,student

where sc.sno=student.sno and sname='刘晨');

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

select sname,sage

from student

where sdept!='CS'

group by sname,sage

HAVING sage<(

select max(sage)

from student

where  sdept='CS');

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

select sname,sage

from student

where sdept!='CS'

group by sname,sage

HAVING sage<(

select min(sage)

from student

where  sdept='CS');

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

select sname

from student

where sno not 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))

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

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

 

select DISTINCT sno

from sc a

where not exists

(

select * from sc b

where a.SNO='200215121' and

not EXISTS(

select * from sc c

where a.SNO=c.sno and c.CNO=c.CNO

)

);

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

select sno,sname

from student

where sno=

(SELECT sno

from sc

GROUP BY sno

having count(*)>2);

10. 查询每门课程的名称和选课人数，一门课没人选，选课人数为0、

   select course.CNAME,count(*) 选课人数

from SC,course

where SC.CNO=course.CNO

group by course.CNAME;

（四） 视图

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

create view cs_view

as

select * from student

where sdept='cs';

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

第一个才是此题答案

create view cs_view_c1 as

select cs_view.sno,sname,sdept,cno,grade

from cs_view , sc

where cs_view.sno=sc.sno and cno='1'

 

 

Create View IS_S1

  As Select Student.Sno,Sname,Grade

  From student,sc

  Where Sdept='IS' AND sc.Sno=Student.Sno  AND  SC.Cno='1';、

用户输入的数据如果是小写但显示的时候是大写但存储区仍然是小写

create view cs_view_c1

as

select *

from cs_view,SC

where cs_view.SNO=SC.SNO and SC.CNO='1';

 

 

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

create VIEW stu_sno_gra(sno,avggrade)

as

select sc.sno,avg(grade)

from student,sc

where student.Sno=sc.SNO

group by sc.sno;

 

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

create view F_stu1（sno，sname，sdept，sex）

as

select sno,sname,sdept,sex

from student

where sex='F'

with check option;

 

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

insert into F_stu1 (sno,sname,sdept)VALUES('200215129','smith','MA');

在行: 1 上开始执行命令时出错-

视图 WITH CHECK OPTIDN where 子句违规

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

insert into F_stu1 VALUES('200215129','smith','MA','F');

显示插入成功

 

5.6有什么区别？

 

提示内容：

Create View <视图名>[(<列名>[,<列名>]…）

AS  <子查询>
[ With CHECK OPTION];

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

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

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

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

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

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

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

 

（五）思考题：

1．Where子句中能否用聚集函数作为条件表达式。

2．多表连接查询中如果显示的某一属性不止一个表中出现，应如何处理。（例：（二）多表连接查询中的第2题）。

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