No.63 - Database7 : 데이터베이스 문제 모음

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);

2. 급여 및 job_ID가 위치 ID 1700에 있는 사원의 급여 및 job_ID와 일치하는 사원의 성,
부서 이름 및 급여를 표시합니다.

SELECT e.last_name, d.department_name, e.salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND (salary, job_id) IN (SELECT e.salary, e.job_id FROM 
employees e JOIN departments d
ON e.department_id =
d.department_id
AND d.location_id = 1700);

3. 급여 및 manager_ID가 Kochhar와 동일한 모든 사원의 성, 채용 날짜 및 급여를 표시하는
query를 작성합니다.
주: 결과 집합에 Kochhar를 표시하지 마십시오.

SELECT last_name, hire_date, salary
FROM employees
WHERE (salary, manager_id) IN
(SELECT salary, manager_id
FROM employees
WHERE last_name = 'Kochhar')
AND last_name != 'Kochhar';

4. 모든 영업 관리자(JOB_ID = 'SA_MAN')보다 많은 급여를 받는 사원을 표시하는 query를
작성합니다. 급여 결과를 하향식으로 정렬합니다.

SELECT last_name, job_id, salary
FROM employees
WHERE salary > ALL
(SELECT salary
FROM employees
WHERE job_id = 'SA_MAN')
ORDER BY salary DESC;

5. 이름이 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%'));

6. 해당 부서의 평균 급여보다 급여 수준이 높은 모든 사원을 찾는 query를 작성합니다. 해당
부서에 대해 사원의 성, 급여, 부서 ID 및 평균 급여를 표시합니다. 평균 급여를 기준으로
정렬하고 소수점 2자리수로 반올림합니다. 예제 출력에 표시된 대로 query에 의해 검색되는
행에 alias를 사용합니다.

SELECT e.last_name ename, e.salary salary,
e.department_id deptno, ROUND(AVG(a.salary),2)
dept_avg
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);

7. 관리자가 없는 모든 사원을 찾습니다.
a. NOT EXISTS 연산자를 사용하여 먼저 다음 작업을 수행합니다.

SELECT outer.last_name
FROM employees outer
WHERE NOT EXISTS (SELECT 'X'
FROM employees inner
WHERE inner.manager_id =
outer.employee_id);

b. NOT IN 연산자를 사용하여 이 작업을 수행할 수 있습니까? 가능하다면 그 방법은
무엇이며 가능하지 않다면 그 이유는 무엇입니까?

SELECT
outer.last_name
FROM employees outer
WHERE
outer.employee_id
NOT IN (SELECT inner.manager_id
FROM employees inner);

두번째 방법은 바람직하지 않습니다. Subquery가 NULL 값을 취하므로 전체 query는
아무 행도 반환하지 않습니다. NULL 값을 비교하는 조건은 모두 NULL이 되기 때문에
행이 반환되지 않는 것입니다. 값 집합에 NULL 값이 포함될 경우에는 NOT EXISTS 대신
NOT IN을 사용하지 마십시오. 다음과 같은 subquery가 훨씬 더 좋은 해결책일 수
있습니다.

SELECT last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees WHERE manager_id IS NOT
NULL);

8. 평균 급여보다 급여 수준이 낮은 사원의 성을 표시하는 query를 작성합니다.

SELECT last_name
FROM employees outer
WHERE outer.salary < (SELECT AVG(inner.salary)
FROM employees inner
WHERE inner.department_id
= outer.department_id);

9. 같은 부서에서 채용 날짜는 더 늦지만 더 높은 급여를 받는 1인 이상의 동료가 있는 사원의
성을 표시하는 query를 작성합니다.

SELECT last_name
FROM employees outer
WHERE EXISTS (SELECT 'X'
FROM employees inner
WHERE inner.department_id =
outer.department_id
AND inner.hire_date > outer.hire_date
AND inner.salary > outer.salary);

10. 모든 사원의 사원 ID, 성 및 부서 이름을 표시하는 query를 작성합니다.
주: scalar subquery를 사용하여 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;

11. 총 급여 비용이 전체 회사의 총 급여 비용의 8분의 1(1/8)을 초과하는 부서의 부서 이름을
표시하는 query를 작성합니다. WITH 절을 사용하여 이 query를 작성하고 query 이름을
SUMMARY로 지정합니다.

WITH
summary AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e JOIN departments d
ON 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;