数据库--嵌套查询

三、嵌套查询（有些可用连接查询替代，有些不可以）

在写嵌套查询是需要明确条件是父还是子的

这里需要熟练掌握 in与exists

关于in

查询结果返回的是一个集合

关于exists

子查询不返回任何数据，只产生逻辑真和逻辑假

若使用not exists内层查询为空，则外层where子句返回真

--查询订购的产品至少包含了订单102893中所订购产品的订单

SELECT DISTINCT A.order_no AS 订单编号
FROM sale_item A
WHERE a.order_no!='102893'and  not EXISTS
(
SELECT *
FROM sale_item B
WHERE B.order_no = '102893' AND  not exists (
select * 
from sale_item c
where a.order_no=c.order_no and b.book_no=c.book_no))

--查询订购的产品至少包含了订单102893中所订购产品的订单

SELECT DISTINCT A.order_no AS 订单编号
FROM sale_item A
WHERE a.order_no!='102893'and  not EXISTS
(
SELECT *
FROM sale_item B
WHERE B.order_no = '102893' AND  not exists (
select * 
from sale_item c
where a.order_no=c.order_no and b.book_no=c.book_no))

--找出至少选修了“200515004”号同学所选修课程的学生学号（双否）

select sno 
from sc a
where sno<>'200515004' and not exists
    (select * 
	 from  sc c
	 where  sno='200515004' and not exists
	   (select *
	    from sc d
		where c.cno=d.cno and d.sno=a.sno) )

--找出被所有同学选修了的课程号（双否）

select cno
from course
where not exists
(select * 
 from student
 where not exists
 (select *
  from sc
  where sc.cno=course.cno and sc.sno=student.sno))

--查找各个部门薪资最高的员工信息。提示：排需要用到分组查询，

select a.*
from employee a
where salary=(select max(salary)
              from employee b
              where  a.dept=b.dept) 

--4、找出目前业绩未超过300元的员工编号和姓名 （聚合不能在where子句中，故用了group by）

SELECT emp_no  员工编号, emp_name  姓名
FROM employee
WHERE emp_no  not  IN 
(
SELECT sale_id
FROM sales
GROUP BY sale_id
HAVING SUM(total_amt) >= 300
) 

--3、找出既选修了“001”又选修了“004”课程的学生姓名

SELECT Sname AS '学生姓名'
FROM Student
WHERE Sno IN 
	(
	SELECT A.Sno
	FROM SC A, SC B
	WHERE A.Cno = '001' AND B.Cno = '004' AND A.Sno = B.Sno
	)

--9、找出“数据库系统概论”这门课成绩最高的学生学号，姓名。

select sno,sname
from student
where sno in
(select sno 
 from sc
 where grade= 
 (select max(grade)
  from sc,course
  where sc.cno=course.cno and cname='数据库'))

 --10、讲解--找出“沈菁菁”同学所选的课程中，成绩最高的课程信息，包括课程号，课程名。（注意sc和student）

select cno,cname
from course
where cno in
(select cno
 from sc
 where grade= 
 (select max(grade)
  from student,sc
  where sname='沈菁菁'and student.sno=sc.sno))

相关子查询

--查询数据库且数据库成绩大于自己所选课程平均成绩的学生学号、姓名、院系

--父查询要参与子查询，要属于同一个学生
select distinct student.sno,sname,sdept
from student,sc a
where student.sno=a.sno
and cno=(select cno
from course
where cname='数据库'
and grade >
(select avg(grade)
from sc b
where a.sno=b.sno
))

in 是不相关子查询而exists是相关子查询 

--not in将不再sale_id里面的也查出来了，结果会多，改成exists就好了
SELECT emp_no  员工编号, emp_name  姓名
FROM employee
WHERE emp_no  not  IN 
(
SELECT sale_id
FROM sales
GROUP BY sale_id
HAVING SUM(total_amt) >= 300
) 

--少了结果
SELECT emp_no  员工编号, emp_name  姓名
FROM employee
WHERE emp_no in
(
SELECT sale_id
FROM sales
GROUP BY sale_id
HAVING SUM(total_amt) < 300
 ) 

辨析

1、查询选修了“数据库”且“数据库”成绩大于75分的学生学号、姓名、所在院系。

select student.sno,sname,sdept
from student,sc,course
where student.sno=sc.sno and 
                    sc.cno=course.cno 
                    and cname='数据库' and grade>75;

2、查询选修了“数据库”且“数据库”成绩大于自己选修课程平均分的学生学号、姓名、所在院系。

select student.sno,sname,sdept
from student,sc sc1,course
where student.sno=sc1.sno and sc1.cno=course.cno 
and cname='数据库' and grade>(
     select avg(grade)
     from sc sc2
     where sc1.sno=sc2.sno
);

3、查询选修了“数据库”且“数据库”成绩大于75分的学生学号、姓名、所有选修的课程名及成绩。
 

select student.sno,sname,cname,grade 
from student,sc,course
where student.sno=sc.sno and   
sc.cno=course.cno and student.sno in(
    select sno
    from sc,course
    where sc.cno=course.cno 
    and cname='数据库' and grade>75
);