SQL理论课-Class 5

select ENAME
from EMP
where salary = some (select salary from EMP where EMPNO = 30) and deptno <> 30

select name 
from student as s
where not exists (select *  --exists子查询的select后面属性可以随便写，因为exists只判断是否符合条件。
                  from takes as t
                  where s.ID=t.ID and course_id='cd-347')

-- 注意：not exists 不会受表中数据存在NULL的影响。                                    

select DEPTNO
from EMP
where not exists (select distinct job 
                  from EMP 
                  where MGR is not NULL)  --所有除了总经理的职位
                  
                  except 
                  
                  (select distinct job 
                   from EMP a 
                   where emp.deptno = a.deptno)--减去部门包含的职位
-- 减去部门包含的职位这里，外面的emp.deptno = 里面a.deptno 说明子查询的职位对应部门，正在外部查询的部门   

with 表名(属性名1,属性名2) as 表

with 表1
select 属性
from 表2
where 小表和大表的条件

with 表1
     由表1定义的表2
from 表1，表2
where 表1和表2条件

-- from写法：

select ename
from emp a,(select AVG(sal) avgs,deptno   
            from emp    
            group by deptno) as b
where a.DEPTNO=b.DEPTNO and a.sal>b.avgs



-- with写法：

with avg_SAL(avgs,deptno) as (select avg(sal),deptno 
                              from EMP 
                              group by DEPTNO)
select ENAME
from EMP a,avg_SAL --这里EMP记为a和上面子查询区分开。
where a.DEPTNO = avg_SAL.DEPTNO and a.SAL > avg_SAL.avgs


-- where写法：

select ename
from emp a
where a.sal >(select avg(sal)
              from emp b
			        where a.DEPTNO=b.DEPTNO)

select empno,sal-(select avg(sal) from emp),(count(*) from emp b where a.deptno=b.deptno)
from emp a