주말 1주차 오라클 (서브쿼리)

1.select max(sal) from emp ;

결과값 : 5000

2.select ename , sal , dept no from emp where sal = 5000;


서브쿼리 = Select ename , sal , deptno from emp where sal = (select max(sal) from emp);

##########################################################################





##########################################################################
1. select sal from emp where job='MANAGER';

의 결과값은 3개이다.

 

2. Select ename , sal , deptno from emp where sal>(select sal from emp where job='MANAGER') 
    결과 는 Error

3. Select ename , sal , deptno from emp where sal> all (select sal from emp where job='MANAGER') ;
/ all 일때는 모두를 만족 (3개). (전부)

4. Select ename , sal , deptno from emp where sal> any (select sal from emp where job='MANAGER') 
/any 일때는 한개만 만족.(하나이상 만족)

in = any 랑똑같다.

#####################################################################################

#####################################################################################
급여 순위 5등까지 출력하시오.
(top-n 분석운용)

select rowid , rownum, dept.* from dept;   //rownum 특성

select rowid , rownum, dept.* from dept order by dname;


-- 테이블 형태의 subquery
select  * from  (Select * From dept)

select rownum,ename , sal from

(select ename , sal  from emp order by sal desc)

 where rownum <5  ;

#####################################################################################

########   7장*( UNION , UNION ALL) #####################################################

-- 부서명 , 직원이름 중 이름에 T자 들어가는 사람 찾기.
Select ename from emp
union
select dname from dept;

Select * From (select '직원명' , ename from emp union select '부서명' , dname from dept order by ename desc)
where ename like '%T%';
# 맨뒤에 있는 order by 절은 맨 앞에 있는 컬럼명을 따른다. 


1.
select empno , ename , sal from emp where deptno = 20
Union
select empno , ename, sal from emp where job='MANAGER'

2.
select empno , ename , sal from emp where deptno = 20
Union  all
select empno , ename, sal from emp where job='MANAGER'

3.
select empno , ename , sal from emp where deptno = 20
intersect
select empno , ename, sal from emp where job='MANAGER'

4.
select empno , ename , sal from emp where deptno = 20
minus
select empno , ename, sal from emp where job='MANAGER'

결과값 확인 하시오!!!!!!!

####################################################################################