Database_61_SQL 활용 실습 7

    -- PRACTICE 7
-- Zlotkey와 동일한 부서에 속한 모든 사원의 이름과 입사일을 표시하는 질의를 작성하시오. Zlotkey는 결과에서제외
SELECT last_name, hire_date 
FROM employees
WHERE department_id=( SELECT department_id
                                 FROM employees
                                 WHERE last_name='Zlotkey')
AND last_name <> 'Zlotkey';

-- 급여가 평균 급여보다 많은 모든 사원의 사원 번호와 이름을 표시하는 질의를 작성하고 결과를 급여에 대해 오름차순으로 정렬
SELECT employee_id, last_name
FROM employees
WHERE salary>( SELECT AVG(salary) FROM employees)
ORDER BY salary;

-- 이름에 u가 포함된 사원과 같은 부서에서 일하는 모든 사원의 사원 번호와 이름을 표시
SELECT employee_id, last_name
FROM employees
WHERE department_id IN ( SELECT department_id 
                                FROM employees
                                WHERE last_name LIKE '%u%');

-- 부서 위치 ID가 1700인 모든 사원의 이름, 부서 번호 및 업무 ID를 표시
SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (SELECT department_id
                                    FROM departments
                                    WHERE location_id=1700);

-- King에게 보고하는(manager가 King) 모든 사원의 이름과 급여를 표시
SELECT last_name, salary
FROM employees
WHERE manager_id=( SELECT employee_id
                            FROM employees
                            WHERE last_name='King');

-- Executive 부서의 모든 사원에 대한 부서 번호, 이름 및 업무 ID를 표시
SELECT department_id, last_name, job_id
FROM employees
WHERE department_id=( SELECT department_id
                                FROM departments
                                WHERE department_name='Executive');

-- 평균 급여보다 많은 급여를 받고 이름에 u가 포함된 사원과 같은 부서에서 근무하는 모든 사원의 사원 번호, 이름 및 급여를 표시
    -- 아무도 없음
    /*
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id IN ( SELECT department_id 
                                    FROM employees
                                    WHERE last_name LIKE '%u%')
AND salary>( SELECT AVG(salary) FROM employees);
    */

-- EMPL_DEMO 테이블로부터 커미션을 받는 사원과 부서 번호 및 급여가 일치하는 사원의 이름, 부서 번호 및 급여를 표시
DESC EMPL_DEMO; 
    /*
        이름             널?               유형           
    --------------      --------        ------------ 
    EMPLOYEE_ID                     NUMBER(6)    
    FIRST_NAME                      VARCHAR2(20) 
    LAST_NAME        NOT NULL VARCHAR2(25) 
    EMAIL               NOT NULL VARCHAR2(25) 
    PHONE_NUMBER                VARCHAR2(20) 
    HIRE_DATE         NOT NULL DATE         
    JOB_ID              NOT NULL VARCHAR2(10) 
    SALARY                           NUMBER(8,2)  
    COMMISSION_PCT            NUMBER(2,2)  
    MANAGER_ID                   NUMBER(6)    
    DEPARTMENT_ID               NUMBER(4)    
    */

SELECT last_name, department_id, salary
FROM employees
WHERE (salary, department_id) IN ( SELECT salary, department_id
                                            FROM empl_demo
                                            WHERE commission_pct IS NOT NULL);

-- 위치 ID가 1700인 사원과 급여 및 커미션이 일치하는 사원의 이름, 부서 이름 및 급여를 표시
SELECT e.last_name, d.department_name, e.salary
FROM employees e JOIN departments d
ON (e.department_id=d.department_id)
WHERE (salary, NVL(commission_pct, 0))
IN ( SELECT salary, NVL(commission_pct, 0)
    FROM employees e JOIN departments d
    ON ( e.department_id=d.department_id)
    AND d.location_id=1700);

--  Kochhar와 동일한 급여 및 커미션을 받는 모든 사원의 이름, 입사일 및 급여를 표시하는 질의를 작성하시오. 
    -- 단, 결과 집합에 Kochhar는 표시하지 않습니다.
SELECT last_name, hire_date, salary
FROM employees
WHERE (salary, NVL(commission_pct, 0)) IN ( SELECT salary, NVL(commission_pct, 0) 
                                                        FROM employees
                                                        WHERE last_name='Kochhar')
AND last_name!='Kochhar';

-- 모든 영업 관리자(JOB_ID = 'SA_MAN')보다 급여를 많이 받는 사원을 표시
    -- 결과를 최고 급여 에서 최저 급여의 순으로 정렬
SELECT last_name
FROM employees
WHERE salary>ALL( SELECT salary 
                            FROM employees
                            WHERE job_id='SA_MAN')
ORDER BY salary DESC;

-- 문자T로 시작하는 도시에 사는 사원의 세부 정보인 사원 ID, 이름 및 부서 ID를 표시
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id IN ( SELECT department_id
                                    FROM departments
                                    WHERE location_id IN ( SELECT location_id
                                                                    FROM locations
                                                                    WHERE city LIKE 'T%'));

-- 소속 부서의 평균 급여보다 적은 급여를 받는 사원의 이름을 표시하는 질의를 작성
SELECT last_name 
FROM employees o
WHERE o.salary<( SELECT AVG(i.salary) 
                    FROM employees i
                    WHERE i.department_id= o.department_id);

    
-- 소속 부서에서 입사일이 늦지만 더 많은 급여를 받는 동료가 있는 사원의 이름을 표시하는 질의를 작성
SELECT o.last_name
FROM employees o
WHERE EXISTS ( SELECT 'X' 
                    FROM employees i
                    WHERE i.department_id=o.department_id
                    AND i.hire_date>o.hire_date);
 

-- 모든 사원의 사원 ID, 이름 및 부서 이름을 표시
    -- 스칼라 서브 쿼리를 사용하여 SELECT 문에서 부서 이름을 검색해서 해결
SELECT employee_id, last_name, (SELECT department_name 
                                            FROM departments d
                                            WHERE e.department_id=d.department_id) department
FROM employees e
ORDER BY department;

-- 총 급여가 전체 회사 총 급여의 1/8보다 많은 부서의 이름을 표시
    -- WITH 절 사용하여 작성하되, SUMMARY라는 이름을 부여

WITH
summary AS ( SELECT d.department_name, SUM(e.salary) AS dept_total
                FROM employees e, departments d
                WHERE e.department_id=d.department_id
                GROUP BY d.department_name)
SELECT department_name, dept_total
FROM summary
WHERE dept_total>( SELECT SUM(dept_total)*1/8 
                            FROM summary)
ORDER BY dept_total DESC;