oracle sql (3)

/*
3일차

다중행 함수
- 여러행을 하나의 결과값으로 도출
- 종류
    1. sum : 합계
    2. count : 데이터의 객수
    3. max : 최대값
    4. min : 최소값
    5. avg : 평균

*/

-- 1. sum : 합계
SELECT sum(sal) FROM emp;
SELECT sal FROM emp;
SELECT sum(DISTINCT sal), sum(ALL sal), sum(sal) FROM emp;

-- 2. count : 데이터의 객수

-- emp 테이블의 데이터 개수
SELECT count(*) FROM emp;
SELECT * FROM emp;

-- 부서번호가 30번인 직원 수
SELECT count(*) FROM emp WHERE deptno = 30;
SELECT count(DISTINCT sal), count(ALL sal), count(sal) FROM emp;

SELECT count(comm) FROM emp;
SELECT count(comm) FROM emp WHERE comm IS NOT NULL;

-- 3. max : 최대값
-- 4. min : 최소값
SELECT deptno, sal FROM emp;
SELECT max(sal) FROM emp WHERE deptno = 10;

-- 부서가 20인 사원 중에 가장 단기 근무자를 출력
SELECT min(hiredate) FROM emp WHERE deptno = 20;

-- 5. avg : 평균
SELECT avg(sal) FROM emp;
SELECT round(avg(sal), 2) FROM emp;

/*
데이터 그룹화
- group by 절 : 여러 데이터에서 의미있는 하나의 결과를 특정 열 값별로 묶어서 출력
- 기본 구조
    select 컬럼명
    from 테이블명
    where 조건식
    group by 그룹화할 컬럼 지정
    order by 정렬하고자 하는 컬럼 지정;
    
- 기본 구조 : HAVING 절(group by 조건식)
    select 컬럼명
    from 테이블명
    where 조건식
    group by 그룹화할 컬럼을 지정
    having 조건식
    order by 정렬하려는 컬럼 지정;
    
- 그룹화 관련 함수
    1. ROLLUP : 그룹화 데이터의 합계
    2. CUBE : 그룹화 데이터의 합계
    3. GROUPING SETS
    
- 그룹화 함수
    1. GROUPING : 현재 결과가 그룹화 대상의 열의 그룹화가 이루어진 상태의 집계인지 판단
    2. GROUPING_ID : GROUPING 함수의 특성과 검산할 열을 여러개 지정

*/

-- 다중행 함수를 이용한 부서별 급여 평균

SELECT avg(sal) FROM emp WHERE deptno = 10;
SELECT avg(sal) FROM emp WHERE deptno = 20;
SELECT avg(sal) FROM emp WHERE deptno = 30;

-- 집합연산자 UNION ALL

SELECT avg(sal) FROM emp WHERE deptno = 10
UNION ALL
SELECT avg(sal) FROM emp WHERE deptno = 20
UNION ALL
SELECT avg(sal) FROM emp WHERE deptno = 30;

-- group by 절
SELECT avg(sal), deptno FROM emp GROUP BY deptno;
SELECT avg(sal) FROM emp GROUP BY deptno;

-- 부서번호 및 직책별 평균 급여를 부서번호의 직책별로 정렬
SELECT deptno, job, avg(sal) FROM emp GROUP BY deptno, job ORDER BY deptno, job;

-- 부서번호 및 직책별 평균 급여를 평균 급여의 내림차순으로 정렬
SELECT deptno, job, avg(sal) FROM emp GROUP BY deptno, job ORDER BY deptno, avg(sal) DESC;

-- group by 사용시 주의!!!
SELECT ename, deptno, avg(sal) FROM emp GROUP BY deptno;  -- 오류

-- HAVING 절(group by 조건식)
-- 각 부서의 직책별 평균 급여를 구하되, 평균 급여가 2000 이상인 그룹만 출력
SELECT  deptno, job, avg(sal) FROM emp 
GROUP BY deptno, job 
HAVING avg(sal) >= 2000 
ORDER BY deptno, avg(sal);

-- having 사용시 주의!!!
-- where는 행을 제한, having은 그룹화된 데이터를 제한
SELECT  deptno, job, avg(sal) FROM emp 
WHERE avg(sal) >= 2000 
GROUP BY deptno, job 
ORDER BY deptno, avg(sal);  -- 오류

-- having 절만 사용
SELECT  deptno, job, avg(sal) FROM emp 
GROUP BY deptno, job 
HAVING avg(sal) >= 2000 
ORDER BY deptno, avg(sal);

-- where 절과 having 절 사용 : 애초에 그룹 대상에서 제외할 대상 데이터가 있을 때 사용
SELECT  deptno, job, avg(sal) FROM emp 
WHERE sal <= 3000 
GROUP BY deptno, job 
HAVING avg(sal) >= 2000 
ORDER BY deptno, avg(sal);

-- 그룹화 관련 함수
-- 기존 group by 만 사용 : 각 부서의 평균은 보이지 않는다
SELECT deptno, job, count(*), max(sal), sum(sal), min(sal), avg(sal) FROM emp
GROUP BY deptno, job
ORDER BY deptno, job;

-- 1. ROLLUP : 그룹화 데이터의 합계 : 1차 기준 분류에 대한 합계
SELECT deptno, job, count(*), max(sal), sum(sal), min(sal), round(avg(sal), 2) FROM emp
GROUP BY ROLLUP(deptno, job);

-- 그룹화 데이터의 일부만 ROLLUP 또는 CUBE를 할 수 있다.
SELECT deptno, job, count(*), max(sal), sum(sal), min(sal), round(avg(sal), 2) FROM emp
GROUP BY deptno, ROLLUP(job);

-- 2. CUBE : 그룹화 데이터의 합계 : 1차 기준 분류에 대한 합계와 2차 기준의 합계
SELECT deptno, job, count(*), max(sal), sum(sal), min(sal), round(avg(sal), 2) FROM emp
GROUP BY CUBE(deptno, job)
ORDER BY deptno, job;

-- 3. GROUPING SETS : 같은 수준의 그룹화 열이 여러개 일때 각 열별 그룹화를 통해 결과 출력
SELECT deptno, job, count(*), max(sal), sum(sal), min(sal), round(avg(sal), 2) FROM emp
GROUP BY GROUPING SETS(deptno, job)
ORDER BY deptno, job;

-- 1. GROUPING : GROUPING 여부 -> 0 : GROUPING, 1 : GROUPING X
SELECT deptno, job, count(*), max(sal), sum(sal), min(sal), round(avg(sal), 2),
GROUPING(deptno), GROUPING(job)
FROM emp
GROUP BY CUBE(deptno, job)
ORDER BY deptno, job;

-- 2. CROUPING_ID
SELECT deptno, job, count(*), max(sal), sum(sal), min(sal), round(avg(sal), 2),
GROUPING(deptno), GROUPING(job),
GROUPING_ID(deptno, job)
FROM emp
GROUP BY CUBE(deptno, job)
ORDER BY deptno, job;

/*
조인
- 두개 이상의 테이블을 연결하여 하나의 테이블처럼 출력할때 사용
- 컬럼명을 비교하는 조건식으로 조인

- 종류
    1. equi join (등가조인) : inner join(내부조인), simple join(단순조인)
    2. non-equi join (비등가조인) : 등가조인 외의 방식
    3. self join (자체조인) : 하나의 테이블을 여러개의 테이블처럼 활용하여 조인
    4. outter join (외부조인) : 두 테이블 간에 조인 수행조건에서 조인 기준 컬럼의 어느 한쪽이 NULL이어도 강제적으로 출력하는 방식
        4-1. left outter join : 왼쪽을 기준으로 오른쪽 컬럼의 데이터 존재여부와 상관없이 데이터 출력
        4-2. right outter join : 오른쪽을 기준으로 왼쪽 컬럼의 데이터 존재여부와 상관없이 데이터 출력
*/

-- 여러 테이블 사용 : from절
SELECT * FROM emp;
SELECT * FROM emp, dept;
SELECT * FROM emp, dept ORDER BY empno;

-- 조인에 조건을 줘서 출력
-- 컬럼명을 비교하는 조건식으로 조인 -> 테이블명.컬럼명
SELECT * FROM emp, dept 
WHERE emp.deptno = dept.deptno 
ORDER BY empno;

-- 테이블명 간략화 : 별칭 설정
SELECT * FROM emp E, dept D
WHERE E.deptno = D.deptno 
ORDER BY empno;

-- inner join(내부조인)
-- 두 테이블에 부서 번호가 똑같은 컬럼명으로 포함되어 있는 경우
SELECT E.empno, E.ename, E.deptno, D.dname, D.loc FROM emp E, dept D
WHERE E.deptno = D.deptno 
ORDER BY D.deptno, E.empno;

-- where 절에 추가 조건 주기
SELECT E.empno, E.ename, E.sal, D.deptno, D.dname, D.loc FROM emp E, dept D
WHERE E.deptno = D.deptno AND sal >= 3000
ORDER BY D.deptno, E.empno;

-- 2. non-equi join (비등가조인)
-- emp와 salgrade 테이블에서 사원의 급여 등급을 출력 -> 연관성있는 컬럼 분석
SELECT * FROM emp;
SELECT * FROM salgrade;

-- 급여 범위를 통해 급여 등급 부여 -> 급여 범위를 지정(salgrade) -> BETWEEN AND
SELECT * FROM emp E, salgrade S
WHERE E.sal BETWEEN S.losal AND S.hisal;

-- 3. self join (자체조인)
SELECT E1.empno, E1.ename, E1.mgr, 
E2.empno AS mgr_empno, E2.ename AS mgr_ename 
FROM emp E1, emp E2
WHERE E1.mgr = E2.empno;

-- 4. outter join (외부조인) : 조인 기준 컬럼의 NULL을 처리하기 위한 목적
-- 왼쪽이든 오른쪽이든 한쪽에 (+)기호를 붙인다
-- (+)기호의 반대쪽으로 조인
SELECT * FROM emp;

-- 4-1. left outter join
SELECT E1.empno, E1.ename, E1.mgr, E2.empno AS mgr_empno, E2.ename AS mgr_ename
FROM emp E1, emp E2
WHERE E1.mgr = E2.empno(+)
ORDER BY E1.empno;

-- 4-2. right outter join
SELECT E1.empno, E1.ename, E1.mgr, E2.empno AS mgr_empno, E2.ename AS mgr_ename
FROM emp E1, emp E2
WHERE E1.mgr(+) = E2.empno
ORDER BY E1.empno;

/*
SQL-99 표준문법 조인
- 다른 DBMS 에서도 사용할 수 있는 조인
- 종류
    1. NATURAL JOIN : 등가조인과 비슷, 두 테이블의 컬럼명과 타입이 같은 컬럼을 자동으로 찾아서 조인
    2. JOIN ~ USING : NATURAL JOIN과 비슷, USING 키워드에 조인 기준으로 사용할 컴럼명 명시
    3. JOIN ~ ON : 기존의 where 절에 있는 조건식을 ON 키워드 다음에 기술
    4. outter join : from 절에서 외부조인을 선언
    
    
*/

-- 1. NATURAL JOIN 
SELECT E.empno, E.ename, E.job, E.hiredate, E.sal, E.comm,
deptno, D.dname, D.loc FROM emp E NATURAL JOIN dept D
ORDER BY deptno, E.empno;

-- 2. JOIN ~ USING 
SELECT E.empno, E.ename, E.job, E.hiredate, E.sal, E.comm,
deptno, D.dname, D.loc FROM emp E JOIN dept D USING(deptno)
ORDER BY deptno, E.empno;

-- 3. JOIN ~ ON 
SELECT E.empno, E.ename, E.job, E.hiredate, E.sal, E.comm,
E.deptno, D.dname, D.loc FROM emp E JOIN dept D ON(E.deptno = D.deptno)
ORDER BY deptno, E.empno;

-- 4. outter join

-- 4-1. left
SELECT E1.empno, E1.ename, E1.mgr, 
E2.empno AS mgr_empno, E2.ename AS mgr_ename
FROM emp E1 LEFT OUTER JOIN emp E2 ON(E1.MGR = E2.empno)
ORDER BY E1.empno;

-- 4-2. right
SELECT E1.empno, E1.ename, E1.mgr, 
E2.empno AS mgr_empno, E2.ename AS mgr_ename
FROM emp E1 RIGHT OUTER JOIN emp E2 ON(E1.MGR = E2.empno)
ORDER BY E1.empno;

-- 4-3. full : 왼쪽, 오른쪽 컬럼의 모든 NULL을 출력
SELECT E1.empno, E1.ename, E1.mgr, 
E2.empno AS mgr_empno, E2.ename AS mgr_ename
FROM emp E1 FULL OUTER JOIN emp E2 ON(E1.MGR = E2.empno)
ORDER BY E1.empno;

-- 4-4. 세 개이상의 테이블 조인
/*SELECT 
FROM T1, T2, T3
WHERE T1.COL = T2.COL AND T2.COL = T3.COL
*/
/*
서브쿼리
- SQL문 안에 또 다른 SQL문
- SQL문을 실행하는데 필요한 데이터를 추가로 조회하기 위해서 SQL내부에 SELECT문을 사용
- 기본 구조
    select 컬럼명
    from 테이블명
    where 조건식 ( select 컬럼명
                  from 테이블명
                  where 조건식 )
                  
- 단일행 서브쿼리 : 실행결과가 단 하나의 행으로 나오는 서브쿼리
    연산자 : 비교연산자(>, >=, <, <=, !=, <>, ^=)
    
- 다중행 서브쿼리 : 실행결과가 여러개의 행으로 나오는 서브쿼리
    연산자
    1. IN : 서브쿼리의 결과 중 하나라도 일치하면 TRUE
    2. ANY, SOME : 메인 쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 TRUE
    3. ALL : 메인 쿼리의 조건식을 만족하는 서브쿼리의 결과 모두가 만족하면 TRUE
    4. EXISTS : 서브쿼리의 결과가 존재하면 TRUE

*/

-- 사원이름이 JONES인 사원의 급여 출력
SELECT sal FROM emp WHERE ename = 'JONES';

-- 급여가 2957보다 높은 사원의 정보 출력
SELECT * FROM emp WHERE SAL > 2957;

-- 서브쿼리 작성
/* 특징
    1. 연산자와 같은 비교 또는 조회 대상의 오른쪽에 ()로 작성
    2. 특수한 경우를 제외한 서브쿼리에서는 ORDER BY 사용 X
    3. 서브쿼리의 select절에 명시한 컬럼은 메인쿼리의 비교대상과 같은 개수로 지정
    4. 서브쿼리에 있는 select문의 결과로 나오는 행의 수는 메인쿼리에 호환되어야 한다
    
*/

SELECT *
FROM emp
WHERE sal > (SELECT sal
             FROM emp
             WHERE ename = 'JONES');
             
-- 단일행 서브쿼리
-- 1. 날짜형 데이터
-- 'BLAKE'보다 빨리 입사한 사원 정보 출력
SELECT * FROM emp WHERE hiredate < (SELECT hiredate 
                                    FROM emp 
                                    WHERE ename = 'BLAKE');
                                    
-- 2. 함수
-- 부서가 20번이면서 전체 급여 평균보다 높은 급여를 받는 사원 정보 출력
SELECT E.empno, E.ename, E.job, E.sal, D.deptno, D.dname, D.loc
FROM emp E, dept D
WHERE E.deptno = D.deptno 
AND E.deptno = 20
AND E.sal > (SELECT avg(sal)
             FROM emp);

-- 다중행 서브쿼리
-- 1. IN 
SELECT * FROM emp WHERE deptno IN(20, 30);

-- 각 부서별 최고 급여와 동일한 급여를 받는 사원 정보 출력
SELECT * FROM emp WHERE sal IN (SELECT max(sal)
                                FROM emp
                                GROUP BY deptno);

-- 2. ANY, SOME 
SELECT * FROM emp WHERE sal = ANY(SELECT max(sal)
                                  FROM emp
                                  GROUP BY deptno);

SELECT * FROM emp WHERE sal = SOME(SELECT max(sal)
                                  FROM emp
                                  GROUP BY deptno);

-- 부서번호가 30인 사원들의 최소 급여보다 더 적은 급여를 받는 사원 정보 출력
SELECT * FROM emp WHERE sal < ANY(SELECT max(sal)
                                  FROM emp
                                  WHERE deptno = 30)
ORDER BY sal, empno;

-- 3. ALL 
SELECT * FROM emp WHERE sal < ALL(SELECT max(sal)
                                  FROM emp
                                  WHERE deptno = 30)
ORDER BY sal, empno;

-- 4. EXISTS : 서브쿼리에 결과값이 존재하면 TRUE, 그렇지않으면 FALSE
-- 서브쿼리에 결과값이 존재하는 경우
SELECT * FROM emp
WHERE EXISTS (SELECT dname
              FROM dept
              WHERE deptno = 10);

-- 서브쿼리에 결과값이 존재하지않는 경우
SELECT * FROM emp
WHERE EXISTS (SELECT dname
              FROM dept
              WHERE deptno = 100);

-- 비교할 컬럼이 여러개인 다중컬럼 서브쿼리
SELECT * FROM emp
WHERE (deptno, sal) IN (SELECT deptno, max(sal)
                        FROM emp
                        GROUP BY deptno);
                        
/*
from절에서 사용하는 서브쿼리와 with
- from절에서 사용되는 서브쿼리 : inlineview
- inlineview : 특정 테이블 전체 데이터가 아닌 select문을 통해 일부 데이터를 추출해서 별칭으로 만든 테이블

*/

-- inlineview : 테이블 내에 데이터를 일부만 사용하고자 할때 사용
SELECT E1.empno, E1.ename, E1.deptno, D.dname, D.loc
FROM (SELECT * FROM emp WHERE deptno = 10) E1,
     (SELECT * FROM dept) D
WHERE E1.deptno = D.deptno;

-- with 절 : 가독성
WITH
E1 AS (SELECT * FROM emp WHERE deptno = 10),
D AS (SELECT * FROM dept)
SELECT E1.empno, E1.ename, E1.deptno, D.dname, D.loc FROM E1, D
WHERE E1.deptno = D.deptno;

-- select 절에서 사용하는 서브쿼리
SELECT empno, ename, job, sal,
       (SELECT grade
        FROM salgrade
        WHERE E.sal BETWEEN losal AND hisal) AS salgrade,
        deptno,
       (SELECT dname
        FROM dept
        WHERE E.deptno = dept.deptno) AS dname
FROM emp E;

SELECT * FROM emp;
SELECT avg(sal) FROM emp;
SELECT * FROM dept;
-- 조인
-- 1. ACCOUNTING 부서 소속 사원의 이름과 입사일을 출력
SELECT ename, hiredate, dname FROM emp 
inner join dept 
ON emp.deptno = dept.deptno 
WHERE dname = 'ACCOUNTING';

-- 2. 커미션을 받는 사원의 이름과 그가 속한 부서명을 출력
SELECT ename, dname, comm FROM emp 
JOIN dept 
ON emp.deptno = dept.deptno 
WHERE comm > 0;

-- 3. 뉴욕에서 근무하는 사원의 이름과 급여를 출력
SELECT ename, sal, loc FROM emp 
JOIN dept 
ON(emp.deptno = dept.deptno) 
WHERE loc = 'NEW YORK';

-- 4. KING과 동일한 근무지에서 근무하는 사원의 이름을 출력
SELECT ename, loc FROM emp 
JOIN dept 
ON (emp.deptno = dept.deptno) 
WHERE loc = (SELECT loc FROM emp 
             JOIN dept 
             ON(emp.deptno = dept.deptno) 
             WHERE ename = 'KING');

-- 문제 풀이
-- 조인과 서브 쿼리

-- <<1>> EMP와 DEPT TABLE을 JOIN하여 부서 번호, 부서명, 이름, 급여를 출력하라.
SELECT E.deptno, D.dname, E.ename, E.sal FROM emp E, dept D WHERE E.deptno = D.deptno;

-- <<2>> 이름이 'ALLEN'인 사원의 부서명을 출력하라.
SELECT E.ename, D.dname FROM emp E, dept D WHERE E.deptno = D.deptno AND E.ename = 'ALLEN';

-- <<3>> EMP Table의 데이터를 출력하되 해당사원에 대한 상관번호와 상관의 성명을 함께 출력하라.
SELECT E1.ename, E2.mgr 상관번호, E2.ename 상관이름
FROM emp E1, emp E2 WHERE E2.mgr = E1.empno;

-- <<4>> DEPT Table 에는 존재하는 부서코드이지만 해당부서에 근무하는 사람이 존재하지 않는 경우의 결과를 출력하라.  
SELECT * FROM dept WHERE deptno != ALL(SELECT deptno FROM emp);

-- <<5>> 'ALLEN'의 직무와 같은 사람의 이름, 부서명, 급여, 직무를 출력하라.
SELECT ename, dname, sal, job FROM emp, dept 
WHERE emp.deptno = dept.deptno AND job = (SELECT job FROM emp WHERE ename = 'ALLEN');

-- <<6>> 'JONES'가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사일자, 급여를 출력하라.
SELECT empno, ename, hiredate, sal FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'JONES');

-- <<7>> 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일, 지역, 급여를 출력하라.
SELECT empno, ename, dname, hiredate, loc, sal FROM emp, dept 
WHERE emp.deptno = dept.deptno AND sal > (SELECT avg(sal) FROM emp);

-- <<8>> 10번 부서 사람들 중에서 20번 부서의 사원과 같은 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일, 지역을 출력하라.
SELECT empno, ename, dname, hiredate, loc, job, emp.deptno FROM emp, dept 
WHERE emp.deptno = dept.deptno AND emp.deptno = 10 AND job IN(SELECT job FROM emp WHERE emp.deptno = 20);

-- <<9>> 10번 부서 중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일자, 지역을 출력하라.
SELECT empno, ename, dname, hiredate, loc, job, emp.deptno FROM emp, dept 
WHERE emp.deptno = dept.deptno AND emp.deptno = 10 AND job != ALL (SELECT job FROM emp WHERE emp.deptno = 30);

-- <<10>> 10번 부서에 근무하는 사원의 사원번호, 이름, 부서명, 지역, 급여를 급여가 많은 순으로 출력하라.
SELECT empno, ename, dname, hiredate, loc, job, emp.deptno, sal FROM emp, dept 
WHERE emp.deptno = dept.deptno AND emp.deptno = 10 ORDER BY sal DESC;

-- <<11>> 'MARTIN'이나 'SCOTT'의 급여와 같은 사원의 사원번호, 이름, 급여를 출력하라.
SELECT empno, ename, sal FROM emp WHERE sal = ANY(SELECT sal FROM emp WHERE ename = 'MARTIN' OR ename = 'SMITH');

-- <<12>> 급여가 30번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.
SELECT empno, ename, sal FROM emp WHERE sal > (SELECT max(sal) FROM emp WHERE deptno = 30);

-- <<13>> 급여가 30번 부서의 최저 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.
SELECT empno, ename, sal FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30);