오늘의 요약
-- 급여 2000 이상인 사원들의 부서별 평균급여의 반올림값 (급여가 높은 순 정렬)
SQL> select DEPTNO, round(avg(SAL)) from EMP
where SAL>=2000 group by DEPTNO order by round(avg(SAL)) desc;
SQL> select DEPTNO, round(avg(SAL)) aa from EMP
where SAL>=2000 group by DEPTNO order by aa desc;
SQL> select DEPTNO, round(avg(SAL)) from EMP
where SAL>=2000 group by DEPTNO order by 2 desc;
--급여 1000이상인 사원들의 부서별 평균급여의 반올림값을 부서번호로 내림차순 정렬하라
-- (단, 부서별 평균급여가 2000 이상인 값만 출력!)
SQL> select DEPTNO, round(avg(SAL)) from EMP
where SAL>=1000
group by DEPTNO
having round(avg(SAL))>=2000
order by DEPTNO desc;
Err> select DEPTNO, round(avg(SAL)) aa from EMP
where SAL>=1000
group by DEPTNO
having aa>=2000
order by DEPTNO desc;
-- #주의: having 절에서는 alias를 사용할 수 없음
--각 부서별 같은 업무를 하는 사원의 인원수를 구하여 부서번호, 업무명, 인원수를 출력하라
--(단, 부서번호과 업무명으로 각각 내림차순 정렬!)
SQL> select DEPTNO, JOB, count(EMPNO) from EMP
group by DEPTNO, JOB
order by DEPTNO desc, JOB desc;
< 일반화 >
[1] 순서 ( SF - WGHO )
select -> from -> where -> group by -> having -> order by
[2] where 절에는 그룹함수를 사용 불가
[3] having 절에서는 alias를 사용할 수 없음
#1-3. 조인( Join )
1) 설명
하나의 테이블로는 원하는 컬럼정보를 참조할 수
없는 경우 관련된 테이블을 '논리적으로 결합'하여
원하는 컬럼정보를 참조하는 방법을 '조인'이라 한다.
2) 조건
논리적으로 결합되는 2개 이상의 테이블에는 반드시
'공통컬럼'이 존재해야하며 이 '공통 컬럼'은
동일한 데이터 타입과 공통된 데이터를 포함해야 한다.
3) 일반적인 조인예
<1> NATURAL 조인 ( EQUI 조인 )
select EMP.EMPNO, DEPT.DNAME from EMP, DEPT where EMP.DEPTNO=DEPT.DEPTNO;--형태1
select e.EMPNO, d.DNAME from EMP e, DEPT d where e.DEPTNO=d.DEPTNO;--형태1
select e.EMPNO, d.DNAME from EMP e join DEPT d on e.DEPTNO=d.DEPTNO;--형태2
select EMP.EMPNO, DEPT.DNAME from EMP join DEPT using(DEPTNO);--형태3
select EMPNO, DNAME from EMP natural join DEPT; --형태4
< 문제 >
-- 사원번호가 7900인 사원의 부서이름 출력하시오
SQL> select EMP.EMPNO, DEPT.DNAME from EMP, DEPT
where EMP.DEPTNO=DEPT.DEPTNO and EMP.EMPNO=7900; -- 형태1
SQL> select e.EMPNO, d.DNAME from EMP e, DEPT d
where e.DEPTNO=d.DEPTNO and e.EMPNO=7900; -- 형태1
SQL> select e.EMPNO, d.DNAME from EMP e join DEPT d
on e.DEPTNO=d.DEPTNO and e.EMPNO=7900; -- 형태2
SQL> select e.EMPNO, d.DNAME from EMP e join DEPT d
on e.DEPTNO=d.DEPTNO where e.EMPNO=7900; -- 형태2
SQL> select EMP.EMPNO, DEPT.DNAME from EMP join DEPT using(DEPTNO)
where EMP.EMPNO=7900; --형태3
SQL> select EMPNO, DNAME from EMP natural join DEPT
where EMPNO=7900;
< 일반화: DQL의 배치순서와 실행순서 >
- select XX --> 6
- from XX --> 1
- (join XX) --> 2
- where XX --> 3
- group by XX --> 4
- having XX --> 5
- order by XX --> 7
<2> SELF 조인
< 문제 >
-- ex) JAMES의 매니져는 BLAKE이다 ( 13명의 MGR를 출력하시오 )
SQL> select e.ENAME||'의 매니져는 ', m.ENAME||'이다'
from EMP e, EMP m where e.MGR=m.EMPNO;
SQL> select e.ENAME||'의 매니져는 ', m.ENAME||'이다'
from EMP e, EMP m where e.MGR=m.EMPNO and e.ENAME='JAMES';
SQL> select e.ENAME||'의 매니져는 ', m.ENAME||'이다'
from EMP e join EMP m on e.MGR=m.EMPNO
where e.ENAME='JAMES'; -- join on절로
#1-4. 서브쿼리( Sub Query )
1) 설명
하나의 DQL문장절에 부속된(포함된) 또 다른 SELECT문장
따라서, 두 번 이상 질의를 해야 얻을 수 있는 결과를
한번의 질의로 해결이 가능케하는 쿼리
2) 용어
[1] MAIN-QUERY or OUTER-QUERY
[2] SUB-QUERY or INNER-QUERY
3) 특징
[1] 괄호를 묶어야 한다.
[2] 실행순서는 '대부분' SUB-QUERY가 먼저 수행되고,
MAIN-QUERY가 실행된다.
[3] SUB-QUERY는 MAIN-QUERY의 다음 부분에 위치된다.
- SELECT/DELETE/UPDATE문장의 FROM절/WHERE절
- INSERT문장의 INTO절
- UPDATE문장의 SET절
[4] SUB-QUERY는 ORDER BY절을 사용할 수 없다.
(예외 : SELECT/DELETE/UPDATE문장의 FROM절 )
4) 종류
[1] 단일행 SUB-QUERY
-> SUB-QUERY의 실행결과가 '하나의 컬럼' AND
'하나의 행'만을 리턴해 주는 경우의 쿼리
즉, '하나의 데이터'를 리턴해 주는 쿼리
< 문제 >
-- 사원번호가 7900인 사원의 부서이름 출력하시오
Join> select e.EMPNO, d.DNAME from EMP e, DEPT d
where e.DEPTNO=d.DEPTNO and e.EMPNO=7900;
Sub> select DEPTNO from EMP where EMPNO=7900;
Main> select DNAME from DEPT where DEPTNO=?;
SQL> select DNAME from DEPT
where DEPTNO=(select DEPTNO from EMP where EMPNO=7900);
-- 부서번호가 10번인 사원급여와 급여가 같은 사원의 이름과 커미션을 출력
Sub> select SAL from EMP where DEPTNO=10;
Main> select ENAME, COMM from EMP where SAL=?
Err> select ENAME, COMM from EMP
where SAL=(select SAL from EMP where DEPTNO=10); --복수행
Sub> select SAL, COMM from EMP where EMPNO=7369;
Main> select ENAME, SAL, COMM from EMP where SAL=?;
Err> select ENAME, SAL, COMM from EMP
where SAL=(select SAL, COMM from EMP where EMPNO=7369);--복수컬럼
-- 평균급여보다 많은 받는 사원의 이름과 급여 출력!
Err> select ENAME, SAL from EMP where SAL>avg(SAL);
Sub> select avg(SAL) from EMP;
Main> select ENAME, SAL from EMP where SAL>?
SQL> select ENAME, SAL from EMP where SAL>(select avg(SAL) from EMP);
[2] 복수행 SUB-QUERY
-> SUB-QUERY의 실행결과가 '여러개의 행'을 return 하는 경우의 쿼리
( 연산자를 이용: in[=any], any, exists, all )
<1> in
-- 부서번호가 10번인 사원급여와 급여가 같은 사원의 이름과 급여를 출력
Sub> select SAL from EMP where DEPTNO=10;
Main> select ENAME, SAL from EMP where SAL=?
Err> select ENAME, SAL from EMP where SAL=(select SAL from EMP where DEPTNO=10);
SQL> select ENAME, SAL from EMP
where SAL in (select SAL from EMP where DEPTNO=10);
SQL> select ENAME, SAL from EMP
where SAL=any(select SAL from EMP where DEPTNO=10);
효과> select ENAME, SAL from EMP
where SAL=2450 or SAL=5000 or SAL=1300;
<2> all
-- 급여가 가장 많은 'MANAGER'보다 급여가 같거나 많은 사원의 JOB,SAL을 출력!!
Sub> select SAL from EMP where JOB='MANAGER';
Main> select JOB,SAL from EMP where SAL>=?;
SQL> select JOB,SAL from EMP
where SAL>=(select max(SAL) from EMP where JOB='MANAGER');
SQL> select JOB,SAL from EMP
where SAL>=all(select SAL from EMP where JOB='MANAGER');
효과> select JOB,SAL from EMP
where SAL>=2975 and SAL>=2850 and SAL>=2450;
<3> any
-- 급여가 가장 큰 'SALESMAN' 보다 급여가 작은 사원의 JOB, SAL을 출력!
Sub> select SAL from EMP where JOB='SALESMAN';
Main> select JOB,SAL from EMP where SAL<?;
SQL> select JOB,SAL from EMP
where SAL<any(select SAL from EMP where JOB='SALESMAN');
효과> select JOB,SAL from EMP
where SAL<1600 or SAL<1250 or SAL<1500;
<4> exists
-- '부서번호'가 10인 사원이 존재하면 모든 부서의 이름을 출력!
Sub> select * from EMP where DEPTNO=10;
Main> select DNAME from DEPT
where exists(select * from EMP where DEPTNO=10);
[3] 복수 컬럼 SUB-QUERY
-> SUB-QUERY의 실행결과가 여러개의 '컬럼을 값'
(AND 여러개의 행)을 리턴해 주는 쿼리
--'부서번호'가 30인 사원의 SAL과 COMM이 같은 사원들의 '이름'과 '부서번호' 출력!
Sub> select SAL, COMM from EMP where DEPTNO=30;
Main> select ENAME, DEPTNO from EMP
where (SAL, COMM)
in (select SAL, COMM from EMP where DEPTNO=30); --4개
비교> select ENAME, DEPTNO from EMP
where (SAL, nvl(COMM,0))
in (select SAL, nvl(COMM,0) from EMP where DEPTNO=30); --6개
[4] 상호 관련 SUB-QUERY
-> MAIN-QUERY절에 사용된 테이블이 SUB-QUERY절에 다시
재사용되는 경우의 서브쿼리
Sub> select avg(e1.SAL) from EMP e1, EMP e2
where e1.DEPTNO=e2.DEPTNO; --수행O
Sub-Err> select avg(SAL) from EMP e2
where e1.DEPTNO=e2.DEPTNO; --수행X
Main> select e1.EMPNO, e1.SAL from EMP e1
where SAL>(select avg(SAL) from EMP e2
where e1.DEPTNO=e2.DEPTNO);
cf) 연습문제
-- 사원번호가 7900인 사원번호와 부서이름을 출력하라
SQL> select e.EMPNO, d.DNAME from EMP e, DEPT d
where e.DEPTNO=d.DEPTNO and e.EMPNO=7900; -- by 조인
SQL> select DNAME from DEPT
where DEPTNO=(select DEPTNO from EMP where EMPNO=7900); -- by 서브쿼리
-- 부서번호가 10번인 사원 평균급여 보다 급여가 적은 사원의 이름과 급여
-- ( 단, 급여가 높은 순으로 정렬 )
Sub> select avg(SAL) from EMP where DEPTNO=10;
Main> select ENAME, SAL from EMP where SAL<?;
SQL> select ENAME, SAL from EMP
where SAL<(select avg(SAL) from EMP where DEPTNO=10);
SQL> select ENAME, SAL from EMP
where SAL<(select avg(SAL) from EMP where DEPTNO=10)
order by SAL desc;
--부서번호가 10번인 사원 평균급여 보다 급여가 적은 사원들의 부서별 평균 급여
--( 단 10번부서는 제외할 것, 부서번호 역정렬, 급여는 반올림할 것 )
Sub> select avg(SAL) from EMP where DEPTNO=10;
Main> select DEPTNO, round(avg(SAL)) from EMP
where SAL< ?
group by DEPTNO
having DEPTNO != 10
order by DEPTNO desc;
SQL> select DEPTNO, round(avg(SAL)) from EMP
where SAL< (select avg(SAL) from EMP where DEPTNO=10)
group by DEPTNO
having DEPTNO != 10
order by DEPTNO desc; 
휘발방지