Chapter 8 子查询

Chapter 8 子查询


select job
from emp
where ename='SMITH'

select empno,ename,sal,job
from emp
where job ='CLERK'


select empno,ename,sal,job
from emp
where job=(select job from emp where ename='SMITH')


select empno,ename,sal,job
from emp
where job=(select job from emp where ename='SMITH') and sal<= 1100


SELECT empno,ename,sal,job
from emp
where sal<(select avg(sal)from emp)


select empno,ename,sal,job
from emp
where sal<(select avg(sal) from emp group by job);
--  返回错误：单行勃勃生机加多于一行




 select empno,ename,sal,job
 from emp
 where ename=(select ename from emp where ename='ADAM')
 -- 返回未选定行Ｒ蛭猻elect语句返回空值。
 
  select job,min(sal),avg(sal),max(sal)
  from emp
  where job not like'PRESID%'
  group by job
  having avg(sal)>
                  (select min(avg(sal))
                    from emp
                    group by job)
 
 
 select e.empno,e.ename,e.sal,e.job,a.avesal
 fromemp e ,(select job,avg(sal) avesal from emp group by job) a
 where e.job=a.job
 and e.sal>a.avesal
 and e.job!='CLERK'
 
 
 select empno,ename,job,sal
 from emp
 where sal in(select max(sal) from emp group by job)
 and job <>'CLERK'
 and job not like'PRES%'
 
 
 select max(sal)
 from emp
 group by job
 
 
 
 select empno,ename,job,sal
 from emp
 where sal in(3000,1300,2975,5000,1600)
 and job<>'CLERK'
 and job not like 'PRES%'
 
 select empno,ename,job,sal
 from emp
 where sal in(select max(sal) from emp where job<>'CLERK'  and job not like'PRES%' group by job);


select e.empno, e.ename, e.sal, e.job
from emp e
where e.sal < ALL(
                  select avg(sal)
                  from emp
                  group by job
                  )
                  
<ALL 小于最小的
>ALL 大于最大的


select e.empno, e.ename, e.sal, e.job
from emp e
where e.sal = ALL(
                  select avg(sal)
                  from emp
                  group by job
                  )
                  
                  
select e.empno, e.ename, e.sal, e.job
from emp e
where e.sal < ANY(
                  select avg(sal)
                  from emp
                  group by job
                  )
                  
select e.empno, e.ename, e.sal, e.job
from emp e
where e.sal > ANY(
                  select avg(sal)
                  from emp
                  group by job
                  )
                  
                  
< ANY 小于最大的
> ANY 大于最小的
= ANY 相当于In

select e.empno, e.ename, e.sal, e.job
from emp e
where e.sal = ANY(
                  select avg(sal)
                  from emp
                  group by job
                  )
                  
select e.empno, e.ename, e.sal, e.job
from emp e
where e.sal in (
                  select avg(sal)
                  from emp
                  group by job
                  )
                  
                  
子查询中的空值(NULL)问题
select e.empno, e.ename, e.sal, e.job
from emp e
where e.mgr in (
               select w.mgr
               from emp w
               where w.mgr is null
               )

               
               
               
select e.empno, e.ename, e.sal, e.job
from emp e
where e.mgr not in (
               select w.mgr
               from emp w
               where w.mgr is null
               )
               
select e.empno, e.ename, e.sal, e.job
from emp e
where e.mgr not in (
               select w.mgr
               from emp w
               where w.mgr is not null
               )
               
select e.empno, e.ename, e.sal, e.job
from emp e
where e.mgr in (
               select w.mgr
               from emp w
               where w.mgr is not null
               )
               
update manager
set sal = 1300
where empno = 7521;



update manager
set sal = 1600
where empno = 7782;

commit;

select empno, ename, sal, job
from manager
where (sal, job) in (
            select max(sal), job
            from manager
            group by job
            );

            
select empno, ename, sal, job
from manager
where sal in (
            select max(sal)
            from manager
            group by job
            )
and  job in (
            select distinct job
            from manager
            group by job
            );