190405

-- 80번 부서에 근무하는 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = 80;
 
-- 커미션을 받는 사원 중에서 급여가 10000달러 이상인 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E
WHERE E.COMMISSION_PCT IS NOT NULL
AND E.SALARY >= 10000;
 
-- 사원들 중에서 급여 등급이 B등급인 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
AND JG.GRA = 'B';
 
-- 부서별 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
 
-- 오류 DEPARTMENT_ID는 107개, COUNT는 1개 나오므로.
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES;
 
-- 그룹 바이 절에 등장한 컬럼은 가능
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
 
-- SELECT 5
-- FROM 1
-- WHERE 2
-- GROUP BY 3
-- HAVING 4 그룹함수가 적용된 행에 제한을 가함. 따라서 그룹 바이가 있어야 함.
-- ORDER BY 6
-- GROUP BY 절에서는 SELECT의 별칭을 사용할 수 없다.
-- GROUP 함수는 한 번만 중첩이 가능하다.
-- SUM(COUNT(*)) 가능
-- AVG(SUM(COUNT(*))) 불가능
 
-- 부서별 사원 수 조회하기
SELECT DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) >= 30;
 
-- 부서별 평균 급여를 조회하기(10000달러 미만)
SELECT E.DEPARTMENT_ID, TRUNC(AVG(E.SALARY))
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID IS NOT NULL
GROUP BY E.DEPARTMENT_ID
HAVING TRUNC(AVG(E.SALARY)) < 10000;
 
-- 급여 등급별 사원 수를 조회하기
SELECT JG.GRA, COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
GROUP BY JG.GRA
ORDER BY 1;
 
-- 급여별 사원 수를 조회하기
-- 2000    10    2000~2999
-- 3000    14    3000~3999
-- 4000     7    4000~4999
-- 5000    11    5000~5999
SELECT TRUNC(SALARY, -3), COUNT(*)
FROM EMPLOYEES
GROUP BY TRUNC(SALARY, -3)
ORDER BY 1;
 
SELECT SUBSTR(FIRST_NAME, 1, 1), COUNT(*)
FROM EMPLOYEES
GROUP BY SUBSTR(FIRST_NAME, 1, 1)
ORDER BY 1;
 
-- 부서별, 직종별 사원수 계산하기
SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY 1, 2;
 
-- 부서 소재지(LOCATION의 CITY)별 사원 수 조회하기
SELECT L.CITY, COUNT(L.CITY)
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.DEPARTMENT_ID IS NOT NULL
GROUP BY L.CITY
ORDER BY L.CITY;
 
-- 각 부서별 최고/최저/평균 급여를 조회하기
SELECT D.DEPARTMENT_NAME, MAX(E.SALARY), MIN(E.SALARY), TRUNC(AVG(E.SALARY))
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.DEPARTMENT_ID IS NOT NULL -- 있어도 그만, 없어도 그만
GROUP BY D.DEPARTMENT_NAME
HAVING MAX(E.SALARY) > 10000;
 
-- 서브쿼리
-- 다른 SQL문의 WHERE절에서 사용되는 쿼리
-- SELECT
-- FROM
-- WHERE 컬럼 연산자 (SELECT 컬럼 FROM WHERE)
 
-- 전체 사원의 평균급여보다 급여를 적게 받는 사원의 아이디, 이름, 급여를 조회하기
-- 1. 평균 급여를 계산한다
 
 
-- 2. 평균 급여보다 급여를 적게 받는 사원
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
                        FROM EMPLOYEES);
                        
-- Neena와 같은 해에 입사한 사원의 아이디, 이름, 입사일 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') IN (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE FIRST_NAME = 'Steven');         -- 다중행 서브쿼리일 땐 IN으로 해야 안전, 가능하면 IN으로 할 것.
 
 
-- Sundita 직원과 같은 부서에 일하고, Sundita보다 급여를 많이 받는 사원 조회하기
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                   FROM EMPLOYEES
                                   WHERE FIRST_NAME = 'Sundita')
AND SALARY > (SELECT SALARY
                     FROM EMPLOYEES
                     WHERE FIRST_NAME = 'Sundita');
 
-- 급여를 가장 적게 받는 사원과 같은 해에 입사한 사원의 이름, 입사일, 급여를 조회하기
 
SELECT FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE SALARY = (SELECT MIN(SALARY)
                                                                          FROM EMPLOYEES) );
                                                                          
-- 오라클 에러
-- 테이블 이름이 틀린 경우: TABLE OR VIEW DOES NOT EXISTS
-- SELECT *
-- FROM EMPLOYEE;
 
-- 컬럼의 이름이 틀린 경우: INVALID IDENTIFIER. 식별자가 잘못되었다네.
-- SELECT NAMED
-- FROM EMPLOYEES;
 
-- 콤마가 누락된 경우: FROM keyword not found where expected
-- SELECT FIRST_NAME LAST_NAME SALARY -- 두 개만 쓰면 AS 별칭으로 인식해서 실행은 됨
-- FROM EMPLOYEES; 
 
-- 콤마가 너무 많은 경우: missing expression 뒤에 하나 더 와야 하는데 안 왔네요?
-- SELECT FIRST_NAME, LAST_NAME, SALARY,
-- FROM EMPLOYEES;
 
-- FROM 키워드에 오타가 있는 경우: FROM keyword not found where expected
-- SELECT FIRST_NAME, LAST_NAME, SALARY
-- FORM EMPLOYEES;
 
-- SELECT 키워드에 오타가 있는 경우: invalid SQL statement
-- SELET FIRST_NAME, LAST_NAME, SALARY
-- FROM EMPLOYEES;
 
-- WHERE 키워드에 오타가 있는 경우: SQL command not properly ended
-- SELECT FIRST_NAME, LAST_NAME, SALARY
-- FROM EMPLOYEES
-- WHER DEPARTMENT_ID = 10;
 
-- 모든 사원의 급여 최고액/최저액/평균을 조회하기
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEES;
 
-- 직종별 사원수를 조회하기(직종아이디, 사원수)
SELECT E.JOB_ID, COUNT(*)
FROM EMPLOYEES E
WHERE E.JOB_ID IS NOT NULL
GROUP BY E.JOB_ID
ORDER BY E.JOB_ID;
 
-- 직종별 사원수를 조회했을 때, 사원수가 10명이 넘어가는 직종과 사원수를 조회하기
SELECT JOB_ID, COUNT(*)
FROM EMPLOYEES 
GROUP BY JOB_ID
HAVING COUNT(*) > 10;
 
-- 관리자별 사원수를 조회하기(관리자 이름, 사원수)
SELECT manager.FIRST_NAME, COUNT(*)
FROM EMPLOYEES worker, EMPLOYEES manager
WHERE worker.MANAGER_ID = manager.EMPLOYEE_ID
GROUP BY MANAGER.FIRST_name;
 
-- 입사년도별 사원수를 조회하기(연도, 사원수)
SELECT TO_CHAR(HIRE_DATE, 'YYYY'), COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')
ORDER BY TO_CHAR(HIRE_DATE, 'YYYY');
 
-- 급여 등급별 사원수를 조회하기(급여등급, 사원수)
SELECT JG.GRA, COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
GROUP BY JG.GRA
ORDER BY JG.GRA;
 
-- "Hermann"가 부서담당자로 지정된 부서에 소속된 사원의 아이디와 이름, 입사일을 조회하기
SELECT 직원.EMPLOYEE_ID, 직원.FIRST_NAME, 직원.HIRE_DATE
FROM EMPLOYEES 직원, EMPLOYEES 담당자
WHERE 직원.MANAGER_ID= 담당자.EMPLOYEE_ID
AND 담당자.FIRST_NAME = 'Hermann'; -- 틀렸어 ㅜㅜ
 
-- 서브 쿼리로 구하기
-- 1. 사원 번호 구하기
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE FIRST_NAME = 'Hermann';
 
-- 2. 1이 관리자로 있는 부서 찾기
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE MANAGER_ID = 204;
 
-- 3. 2 부서에 근무하는 사원 구하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 70;
 
-- 종합
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM DEPARTMENTS
                                  WHERE MANAGER_ID = (SELECT EMPLOYEE_ID
                                                                 FROM EMPLOYEES
                                                                 WHERE FIRST_NAME = 'Hermann'));
 
-- 조인으로 구하기
SELECT WORKER.EMPLOYEE_ID, WORKER.FIRST_NAME, WORKER.HIRE_DATE
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER, DEPARTMENTS DEPT
WHERE MANAGER.EMPLOYEE_ID = DEPT.MANAGER_ID
AND DEPT.DEPARTMENT_ID = WORKER.DEPARTMENT_ID
AND MANAGER.FIRST_NAME = 'Hermann';
 
-- 최고 급여가 $10000를 넘는 부서별 부서의 번호 및 평균 급여를 조회하기
SELECT DEPARTMENT_ID, TRUNC(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY) >= 10000
ORDER BY DEPARTMENT_ID;
 
-- 관리자별 번호 및 해당 관리자에 속한 사원의 최저 급여를 조회하기
SELECT MANAGER.EMPLOYEE_ID, MIN(WORKER.SALARY)
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER
WHERE WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID
GROUP BY MANAGER.EMPLOYEE_ID
ORDER BY MANAGER.EMPLOYEE_ID;
 
-- 'Hermann'와 같은 급여등급에 속하는 사원들의 이름과 급여를 조회하기
 
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
AND B.GRA = (SELECT Y.GRA
                  FROM EMPLOYEES X, JOB_GRADES Y
                  WHERE X.SALARY >= Y.LOWEST_SALARY AND X.SALARY <= Y.HIGHEST_SALARY
                  AND X.FIRST_NAME = 'Hermann');
-- 메인 쿼리와 서브 쿼리 별칭을 다르게 하라.
 
 
-- 엄청 어려운 문제. 인터넷에서 검색해보고 풀 것. 입사연도별 사원수 조회하기
-- 조회 예)
-- 2001 2002 2003 2004 2005 2006 2007 2008
-- 1     7      6      10    29    24    19   11
SELECT 
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2001', 1, 0)) "2001년", 
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2002', 1, 0)) "2002년", 
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2003', 1, 0)) "2003년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2004', 1, 0)) "2004년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2005', 1, 0)) "2005년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2006', 1, 0)) "2006년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2007', 1, 0)) "2007년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2008', 1, 0)) "2008년"
 
FROM EMPLOYEES;