Database_73_SQL FUNDAMENTALS_실습

    -- Practice 1
    
-- 부서 번호와 급여가 커미션을 받는 사원의 부서 번호 및 급여와 일치하는 모든 사원의 성, 부서 번호 및 급여를 표시하는 query 를 작성
SELECT last_name, department_id, salary
FROM employees
WHERE ( salary, department_id ) IN ( SELECT salary, department_id
                                                FROM employees
                                                WHERE commission_pct IS NOT NULL );

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

-- Kochhar 와 동일한 급여 및 커미션을 받는 모든 사원의 성, 채용 날짜 및 급여를 표시
    -- 주의: 결과 집합에 Kochhar 를 표시하지 마십시오.
SELECT hire_date, salary, last_name
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, job_id, salary
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%'));

-- 해당 부서의 평균 급여보다 급여 수준이 높은 모든 사원을 찾는 query 를 작성
    -- 해당 부서에 대해 사원의 성, 급여, 부서 ID 및 평균 급여를 표시
    -- 평균 급여를 기준으로 정렬
SELECT e.salary AS sal, e.department_id AS dp_id, TRUNC(AVG(a.salary),2) AS dept_avg, e.last_name AS name
FROM employees e, employees a
WHERE e.department_id=a.department_id
AND e.salary>( SELECT AVG(salary)
                FROM employees
                WHERE department_id=e.department_id )
GROUP BY e.last_name, e.salary, e.department_id
ORDER BY AVG(a.salary);

-- NOT EXISTS 연산자를 사용하여 관리자가 아닌 모든 사원을 찾습니다.
SELECT outer.last_name
FROM employees outer
WHERE NOT EXISTS ( SELECT 'X'
                                FROM employees inn
                                WHERE inn.manager_id=outer.employee_id);

-- 해당 부서의 평균 급여보다 급여 수준이 낮은 사원의 성을 표시
SELECT outer.last_name
FROM employees outer
WHERE outer.salary<( SELECT AVG(inn.salary)
                            FROM employees inn
                            WHERE inn.department_id=outer.department_id);

-- 같은 부서에서 자신보다 채용 날짜는 늦지만 더 높은 급여를 받는 동료 사원이 한 명 이상인 사원의 성을 표시
SELECT outer.last_name
FROM employees outer
WHERE EXISTS ( SELECT 'X'
                    FROM employees inn
                    WHERE inn.department_id=outer.department_id
                    AND inn.hire_date>outer.hire_date
                    AND inn.salary>outer.salary);

-- 모든 사원의 사원 ID, 성 및 부서 이름을 표시
    -- 주: SELECT 문에서 scalar subquery 를 사용하여 부서 이름을 검색
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;

-- 총 급여 비용이 전체 회사의 총 급여 비용의 8 분의 1(1/8)을 초과하는 부서의 부서 이름을 표시
    -- WITH 절을 사용하여 이 query 를 작성하고 query 이름을 SUMMARY 로 지정
WITH
summary AS (
    SELECT d.department_name, SUM(e.salary) AS dept_total
    FROM departments d, employees e
    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;