SQL理论课-Class 4

-- 把查询语句嵌套在where筛选条件中：

select ename
from emp
where hiredate > (select hiredate 
                  from emp
                  where ename = 'king')

-- 里面这个子查询返回一行可以直接使用比较运算符，因为就这一个人这个名字

select name
from student
where ID in(筛选出来的很多行)

select ENAME,EMPTNO,SAL
from EMP
where DEPTNO in (select DEPTNO
                 from DEPT
                 where LOC = 'NewYork')
                
select count(salary)
from EMP
where DEPTNO in (Select DEPTNO
                from EMP
                where ENAME = 'blake')

select *
from EMP
where DETPNO not in(Select DEPTNO
                    from EMP
                    where ENAME = 'blake')  
and JOB in (select JOB
            from EMP
            where DEPTNO in (Select DEPTNO  from EMP  where ENAME = 'blake')
           )

select name 
from instructor
where salary > some (select salary from instructor where dept_name='Biology')
-- 查找出来老师只要工资  比生物系最起码一个老师的工资高就行。

         =    <>          >=     <=
some    in    --         >=min    <=max
all     --    not in     <=max    <=min

select name
from student
where id <>all(select id
               from takes
               where course_id = 'cd=347')
               

select DEPTNO,AVG(SAL)
from EMP
group by DEPTNO
having AVG(SAL) >ALL AVG(select SAL from EMP group by DEPTNO)

select ENAME
from EMP
where EMPNO in (select MGR from EMP)

-- 陷阱：如果是not in，一定要小心NULL，因为NULL运算完了什么也找不到。

-- 加一个
EMPNO is not NULL