数据库原理上机实验3（数据高级查询实验）

本次实验用到的一些基本关键字

1、in 嵌套查询

2、带有ANY(SOME)或 ALL谓词的子查询

ANY是任意一个满足条件即可;ALL是全部都要满足

3、带有EXISTS（NOT EXISTS）谓词的子查询

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

4、distinct 去重 DISTINCT

5、集合查询

交集：union   UNION  或者

并集：incersect  INCERSECT  并且 又

差集：except  EXCEPT 差

6、FROM子句中的嵌套查询(基于派生表的查询)

from里面套查询

源码

#(1) in 嵌套查询
#查询与“刘晨”同学于同一个系学习的学生.(应用嵌套查询和单表查询描述)
select *
from student 
where sdept in 
	(select sdept
	from student
	where sname='刘晨'
	);
#查询选修了课程名为“信息系统”的学生学号和姓名.
select *
from student
where sno IN
		(select sno
		from course
		where cno in
				(select cno
				from cs
				where cname='信息系统'
				)
		);
		
#(2) 带有比较运算符的子查询
#查询每个学生超过他自己选修课程平均成绩的课程号
select sno,cno
from course x
where grade>=
	(select avg(grade)
	from course y
	where y.sno=x.sno
	);
	
#(3) 带有ANY(SOME)或 ALL谓词的子查询(ANY是任意;ALL是全部)
#查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄.(请用any和聚集函数max分别描述)
select sname,sage
from student
where sage <ANY
	(select sage
	from student
	where sdept='cs'
	)
and sdept!='cs';

select sname,sage
from student
where sage <
	(select max(sage)
	from student
	where sdept='cs'
	)
and sdept!='cs';

#查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄.(请用all和聚集函数min分别描述)
select sname,sage
from student
where sage <ALL
	(select sage
	from student
	where sdept='cs'
	)
and sdept!='cs';

select sname,sage
from student
where sage <
	(select min(sage)
	from student
	where sdept='cs'
	)
and sdept!='cs';

#(4) 带有EXISTS谓词的子查询(先查外查询，然后用外查询数据去内查询根据where做匹配，如果存在则保留外查询记录。)
#查询所有选修了1号课程的学生姓名.
select sname
from student
where exists (
	select *
	from course
	where student.sno=course.sno
	and course.cno='1'
);

#查询没有选修1号课程的学生姓名.
select sname
from student
where not exists (
	select *
	from course
	where student.sno=course.sno
	and course.cno = '1'
);

#查询选修了全部课程的学生姓名.
select sname
from student
where not exists( #不存在
	select *   #遍历总课程表
	from cs
	where not EXISTS( #没有选择这门课程
			select *
			from course
			where student.sno=course.sno
			and course.cno=cs.cno
			)
	);
#查询至少选修了学生201215122选修的全部课程的学生学号.
select distinct sno #distinct 用来去重
from course a
where not exists(
	select *
	from course b
	where b.sno='201215122'
	and not exists(
		select *
		from course c
		where a.sno=c.sno
		and b.cno=c.cno
		)
	)
	
#(5) 集合查询(交) union UNION 或者	
#查询计算机科学系的学生或者年龄不大于19岁的学生的交集.
select *
from student
where sage<=19 
union
select *
from student
where sdept='CS';

#(6)集合查询(并) incersect INCERSECT 并且 又
#查询既选修了课程1又选修了课程2的学生.
select sno
from course
where cno='1'
incersect
select *
from course
where cno=='2';

#(7) 集合查询(差) except
#查询计算机科学系的学生与年龄不大于19岁的学生的差集.
select *
from student
where sdept='CS';
except
select *
from student
where sage<=19 

#(8)FROM子句中的嵌套查询(基于派生表的查询)
#查询每个学生超过他自己选修课程平均成绩的课程号.(请用派生表方法描述此查询)
SELECT Sno,Cno
FROM course,(
	SELECT Sno,AVG(Grade) 
	FROM course 
	GROUP BY Sno) 
AS Avg_sc(avg_sno,avg_grade)
WHERE course.Sno = Avg_sc.avg_sno AND course.Grade >= Avg_sc.avg_grade