190404

-- 비등가조인 실습을 위한 테이블 생성
CREATE TABLE JOB_GRADES (
   GRA CHAR(1) NOT NULL PRIMARY KEY,
   LOWEST_SALARY NUMBER(7),
   HIGHEST_SALARY NUMBER(7)
);
 
 
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('A', 1000,    2999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('B', 3000,    5999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('C', 6000,    9999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('D', 10000, 14999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('E', 15000, 24999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('F', 25000, 40000);
COMMIT;
 
-- 비등가 조인
-- = 연산자가 아닌 다른 연산자를 사용해서 조인 조건을 정의하는 조인
 
-- 사원의 이름, 사원의 급여, 급여 등급을 조회하기
-- 등호(=)를 사용하는 것이 아니라, 급여가 최소 급여와 최대 급여 사이에 해당하는 등급 관련 행이 조인되어야 함.
 
SELECT A.FIRST_NAME, A.SALARY, B.GRA
FROM EMPLOYEES A, JOB_GRADES B
WHERE A.SALARY >= B.LOWEST_SALARY
AND A.SALARY <= B.HIGHEST_SALARY
ORDER BY A.SALARY DESC;
 
-- 포괄조인(아우터조인:OUTER JOIN)
-- 포괄 조인을 사용하면 조인 조건을 만족하지 않는 행도 조회된다.
-- 포괄 조인 연산자는 +
 
-- 사원 이름, 부서 아이디, 부서 이름 조회하기
SELECT A.FIRST_NAME, A.DEPARTMENT_ID, B.DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID(+);
 
-- 부서 아이디, 부서 이름, 부서 담당자 아이디, 부서 담당자 이름, 부서 담당자 직종 아이디를 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, E.FIRST_NAME, E.JOB_ID
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.MANAGER_ID = E.EMPLOYEE_ID(+)
ORDER BY D.DEPARTMENT_ID;
 
-- 부서 아이디, 부서 이름, 부서 담당자 아이디, 부서 담당자 이름, 그 부서의 주소를 조회하기
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, A.MANAGER_ID, B.FIRST_NAME, C.CITY
FROM DEPARTMENTS A, EMPLOYEES B, LOCATIONS C
WHERE A.MANAGER_ID = B.EMPLOYEE_ID(+)
AND A.LOCATION_ID = C.LOCATION_ID
ORDER BY A.DEPARTMENT_ID;
 
-- 사원의 이름, 사원의 급여, 급여 등급, 소속 부서 이름을 조회하기(107명이 조회되어야 함)
SELECT A.FIRST_NAME, A.SALARY, B.GRA, C.DEPARTMENT_NAME
FROM EMPLOYEES A, JOB_GRADES B, DEPARTMENTS C
WHERE A.DEPARTMENT_ID = C.DEPARTMENT_ID(+)
AND A.SALARY >= B.LOWEST_SALARY
AND A.SALARY <= B.HIGHEST_SALARY
ORDER BY A.FIRST_NAME ASC;
 
-- 셀프 조인
-- 자기 자신의 테이블과 다시 조인을 해야하는 경우 사용된다.
-- 상하관계(재귀관계)가 있는 조직도, 메뉴 구성 정보, 카테고리 구성 정보 등이 셀프 조인을 통해서
-- 필요한 데이터를 조회할 수 있는 경우이다.
-- 같은 테이블을 서로 조인하기 때문에 테이블마다 적절한 역할을 부여해서 조인에 참여시켜야 한다.
 
-- 100번 부서에 근무하는 사원의 아이디, 사원의 이름, 그 사원의 상사 아이디, 그 사원의 상사 이름을 조회하기
SELECT A.EMPLOYEE_ID 직원_아이디, A.FIRST_NAME 직원_이름, B.EMPLOYEE_ID 상사_아이디, B.FIRST_NAME 상사_이름
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.MANAGER_ID = B.EMPLOYEE_ID AND A.DEPARTMENT_ID = 100;
 
-- 90번 부서에 근무하는 매니저에게 보고하는 직원들의 아이디, 이름, 급여, 부서명을 조회하기
SELECT 직원.EMPLOYEE_ID, 직원.FIRST_NAME, 직원.SALARY, 부서.DEPARTMENT_NAME
FROM EMPLOYEES 매니저, EMPLOYEES 직원, DEPARTMENTS 부서
WHERE 부서.DEPARTMENT_ID = 직원.DEPARTMENT_ID
AND 매니저.EMPLOYEE_ID = 직원.MANAGER_ID
AND 매니저.DEPARTMENT_ID = 90;
 
-- 다중행 함수(그룹함수)
-- 그룹함수는 행 집합(행 그룹)에 작용해서 그룹 당 하나의 결과를 생성한다.
-- 행 집합은 테이블 전체 행일 수도 있고,
-- 테이블에서 특정 컬럼의 값이 동일한 것끼리 그룹화된 행일수도 있다.
-- 그룹함수
-- COUNT(*), COUNT(컬럼명) 행의 개수, *일 때는 모든 행의 개수, 컬럼명을 지정하면 NULL이 아닌 행의 개수
--SUM(컬럼명) 합계를 계산, NULL은 무시한다.
--AVG(컬럼명) 평균을 계산, NULL은 무시
-- MIN(컬럼명) 최솟값을 계산, NULL 무시
-- MAX(컬럼명) 최댓값을 계산, NULL 무시
-- VARIANCE(컬럼명) 분산을 계산, NULL 무시
-- STDDEV(컬럼명) 
 
-- COUNT(*)
-- 직원의 수를 조회하기
SELECT COUNT(*)
FROM EMPLOYEES;
 
-- 커미션을 받는 직원의 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
-- COUNT(컬럼명)은 해당 컬럼의 값이 NULL이 아닌 행의 개수만 센다.
SELECT COUNT(COMMISSION_PCT)
FROM EMPLOYEES;
 
-- 80번 부서에 소속된 사원들의 급여 총합을 조회하기
SELECT SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 커미션의 평균을 조회하기
SELECT AVG(COMMISSION_PCT)
FROM EMPLOYEES;
-- 또는
SELECT SUM(COMMISSION_PCT/35)
FROM EMPLOYEES;
-- NULL이 아닌 것만 갖고 계산이 된다.
 
-- 최저급여를 조회하기
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE SALARY = (SELECT MIN(SALARY)
                        FROM EMPLOYEES);
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션 및 소속부서명을 조회하기
SELECT E.FIRST_NAME, E.SALARY, E.COMMISSION_PCT, D.DEPARTMENT_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.COMMISSION_PCT IS NOT NULL
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+);
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션 및 소속부서명과 직종, 직종제목을 조회하기
SELECT E.FIRST_NAME
, E.SALARY
, E.COMMISSION_PCT
, D.DEPARTMENT_ID
, J.JOB_ID
, J.JOB_TITLE
FROM EMPLOYEES E
, DEPARTMENTS D
, JOBS J
WHERE E.COMMISSION_PCT IS NOT NULL
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
AND J.JOB_ID = E.JOB_ID;
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션 및 그 직원의 매니저의 이름을 조회하기
SELECT 직원.FIRST_NAME
, 직원.SALARY
, 직원.COMMISSION_PCT
, 매니저.FIRST_NAME
FROM EMPLOYEES 직원, EMPLOYEES 매니저
WHERE 직원.COMMISSION_PCT IS NOT NULL
AND 직원.MANAGER_ID = 매니저.EMPLOYEE_ID;
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션, 소속부서명 및 그 직원의 매니저의 이름과 매니저의 
-- 소속부서명을 조회하기
SELECT 직원.FIRST_NAME
, 직원.SALARY
, 직원.SALARY
, 직원_부서.DEPARTMENT_NAME
, 매니저.FIRST_NAME
, 매니저_부서.DEPARTMENT_NAME
FROM EMPLOYEES 직원, EMPLOYEES 매니저, DEPARTMENTS 직원_부서, DEPARTMENTS 매니저_부서
WHERE 직원.MANAGER_ID = 매니저.EMPLOYEE_ID
AND 매니저_부서.DEPARTMENT_ID = 매니저.DEPARTMENT_ID
AND 직원_부서.DEPARTMENT_ID = 직원.DEPARTMENT_ID
AND 직원.COMMISSION_PCT IS NOT NULL;
 
-- 급여등급이 A등급인 직원 수를 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E, JOB_GRADES J
WHERE J.LOWEST_SALARY <= E.SALARY
AND J.HIGHEST_SALARY >= E.SALARY
AND J.GRA = 'A';
 
-- 급여등급이 E등급인 상사에게 보고하는 직원의 이름과 급여를 조회하기
SELECT 직원.FIRST_NAME, 직원.SALARY
FROM EMPLOYEES 직원, EMPLOYEES 상사, JOB_GRADES 상사_등급
WHERE 상사_등급.LOWEST_SALARY <= 상사.SALARY
AND 상사_등급.HIGHEST_SALARY >= 상사.SALARY
AND 직원.MANAGER_ID = 상사.EMPLOYEE_ID
AND 상사_등급.GRA = 'E';
 
-- 급여등급이 E등급인 상사에게 보고하는 직원의 이름, 급여, 소속부서명을 조회하기
SELECT 직원.FIRST_NAME, 직원.SALARY, 직원_부서.DEPARTMENT_NAME
FROM EMPLOYEES 직원, EMPLOYEES 상사, JOB_GRADES 상사_등급, DEPARTMENTS 직원_부서
WHERE 직원.MANAGER_ID = 상사.EMPLOYEE_ID
AND 상사_등급.LOWEST_SALARY <= 상사.SALARY
AND 상사_등급.HIGHEST_SALARY >= 상사.SALARY
AND 직원.DEPARTMENT_ID = 직원_부서.DEPARTMENT_ID
AND 상사_등급.GRA = 'E';
 
-- 급여등급이 E등급인 상사에게 보고하는 직원의 이름, 급여, 그 직원의 급여등급을 조회하기
SELECT 직원.FIRST_NAME, 직원.SALARY, 직원_등급.GRA
FROM EMPLOYEES 직원, EMPLOYEES 상사, JOB_GRADES 상사_등급, JOB_GRADES 직원_등급
WHERE 직원.MANAGER_ID = 상사.EMPLOYEE_ID
AND 상사_등급.LOWEST_SALARY <= 상사.SALARY
AND 상사_등급.HIGHEST_SALARY >= 상사.SALARY
AND 직원_등급.LOWEST_SALARY <= 직원.SALARY
AND 직원_등급.HIGHEST_SALARY >= 직원.SALARY
AND 상사_등급.GRA = 'E';
 
-- 급여등급에 따라서 인상된 급여를 조회하기
-- 직원이름, 급여, 급여등급, 인상된 급여
-- 인상률: A등급 1%, B등급:3%, C등급: 5%, D등급: 7%, E등급: 10%, F등급 15% 인상시킨다.
SELECT E.FIRST_NAME, E.SALARY, JG.GRA, 
CASE WHEN JG.GRA = 'A' THEN TRUNC(1.01*E.SALARY)
        WHEN JG.GRA = 'B' THEN TRUNC(1.03*E.SALARY)
        WHEN JG.GRA = 'C' THEN TRUNC(1.05*E.SALARY)
        WHEN JG.GRA = 'D' THEN TRUNC(1.07*E.SALARY)
        WHEN JG.GRA = 'E' THEN TRUNC(1.10*E.SALARY)
        WHEN JG.GRA = 'F' THEN TRUNC(1.15*E.SALARY)
        END AS 급여인상분
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY;
 
-- DECODE(JG.GRA, 'A' TRUNC(~)도 가능
 
-- 사원의 아이디, 사원이름, 급여, 급여등급, 직종아이디, 직종제목, 
-- 소속부서 아이디, 부서명, 소재지주소, 소재지의 국가를 조회하기(6개 조인)
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, JG.GRA, E.JOB_ID, J.JOB_TITLE, E.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY, C.COUNTRY_ID
FROM EMPLOYEES E, DEPARTMENTS D, JOB_GRADES JG, JOBS J, LOCATIONS L, COUNTRIES C
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
AND E.JOB_ID = J.JOB_ID
AND L.LOCATION_ID(+) = D.LOCATION_ID
AND L.COUNTRY_ID = C.COUNTRY_ID(+);