select deptno,dname
from dept
where deptno in (select deptno
from emp)
select deptno,dname
from dept
where deptno not in (select deptno
from emp)
select empno,ename,sal
from emp
where sal > (select avg(sal)
from emp)
select ename,sal
from emp
where sal > any (select min(sal)
from emp
group by deptno)
select ename,sal
from emp
where sal > all (select min(sal)
from emp
group by deptno)
select empno,ename,sal
from emp
where (deptno,sal) in (select
deptno,max(sal)
from emp
group by deptno)
--2
select deptno,dname
from dept
where exists (select deptno
from emp
where dept.deptno = emp.deptno)
select deptno,dname
from dept
where not exists (select deptno
from emp
where dept.deptno =
emp.deptno)
select a.empno,a.ename,a.sal
from emp a
where a.sal > (select avg(b.sal)
from emp b
where a.deptno = b.deptno)
select a.empno,a.ename,a.sal
from emp a,(select deptno,avg(sal) as
avgsal
from emp
group by deptno) b
where a.deptno = b.deptno
and
a.sal > b.avgsal
select (select count(*) from dept) +
(select count(*) from emp)
from dual
--3
select empno,ename,deptno
from emp
where deptno = 10 or deptno = 20
union
select empno,ename,deptno
from emp
where deptno = 10
order by empno
select empno,ename,deptno
from emp
where deptno = 10 or deptno = 20
union all
select empno,ename,deptno
from emp
where deptno = 10
order by empno
select empno,ename,deptno
from emp
where deptno = 10 or deptno = 20
intersect
select empno,ename,deptno
from emp
where deptno = 10
select empno,ename,deptno
from emp
where deptno = 10 or deptno = 20
minus
select empno,ename,deptno
from emp
where deptno = 10
-- 
學海無涯