180904_Oracle 문제풀이(14제)

180904_Oracle_문제풀이

-- 문제1) hr.employees 테이블에서 입사일(hire_date) 정보 중에서 1, 2, 3월 입사자만 출력하는 쿼리 작성. IN 연산자 사용.
SELECT employee_id, first_name, last_name, hire_date
	FROM hr.employees
	WHERE TO_CHAR(hire_date, 'MM') IN (01, 02, 03);

-- 문제2) hr.employees 테이블에서 job_id가 Manager('SA_MAN', 'ST_MAN', 'PU_MAN', 'AC_MGR')인 경우 출력하는 쿼리 작성. IN 연산자 사용.
SELECT employee_id, first_name, last_name, job_id
	FROM hr.employees
	WHERE job_id IN ('SA_MAN', 'ST_MAN', 'PU_MAN', 'AC_MGR');

-- 문제3) hr.employees 테이블에서 급여(salary)가 10000 이상이면서, 커미션(commission_pct)이 없는(값으로 NULL을 가진) 경우 출력하는 쿼리 작성. IS 연산자 사용.
SELECT employee_id, first_name, last_name, salary, commission_pct
	FROM hr.employees
	WHERE salary >= 10000
	AND commission_pct IS NULL;

-- 문제4) hr.employees 테이블에서 급여(salary) 수준에 따라서 'A', 'B', 'C' 등급으로 출력하는 쿼리 작성. CASE~END 구문 사용.
-- 급여가 20000 이상인 경우 'A'
-- 급여가 10000 이상인 경우 'B'
-- 급여가 10000 미만인 경우 'C'
SELECT employee_id, first_name, last_name, salary,
	CASE
		WHEN TRUNC(salary/10000) = 2 THEN 'A'
		WHEN TRUNC(salary/10000) = 1 THEN 'B'
		WHEN TRUNC(salary/10000) = 0 THEN 'C'
	END "GRADE"
	FROM hr.employees;
-- 4번 틀림. CASE-END문 공부할것.

-- 문제5) insa 테이블에서 전화번호(tel)가 있으면 '-'을 제거하고 출력하고, 없으면 '전화번호없음'을 출력하는 쿼리 작성. DECODE, REPLACE 함수 사용.
SELECT num, name, tel, DECODE(tel, NULL, '전화번호없음', REPLACE(tel, '-', '')) "tel1"
	FROM insa;

-- 문제6) hr.employees 테이블에서 입사일(hire_date) 정보 중에서 년도별로 인원수 출력하는 쿼리 작성. GROUP BY 구문, COUNT 함수 사용.
SELECT TO_CHAR(hire_date, 'YYYY'), COUNT(*) "인원수"
	FROM hr.employees
	GROUP BY TO_CHAR(hire_date, 'YYYY');
-- 6번 틀림. COUNT(*) 하기전에 무엇을 기준으로 할지 정해야함.

-- 문제7) insa 테이블에서 전체인원수, 남자인원수, 여자인원수를 동시 출력하는 쿼리 작성. COUNT, DECODE 함수 사용.
-- 출력 예)
-- 전체인원수   남자인원수   여자인원수
--    60	     31	          29
SELECT COUNT(*) "전체인원수", COUNT(DECODE(SUBSTR(ssn, 8, 1), 1, 1)) "남자직원수", COUNT(DECODE(SUBSTR(ssn, 8, 1), 2, 1)) "여자직원수"
	FROM insa;
-- 7번 틀림, DECODE 활용법 다시 생각하기

-- 문제8) hr.employees 테이블에서 급여(salary)를 많이 받는 직원부터 순서대로 출력하되, 순번 부여해서 출력하는 쿼리 작성. ROW_NUMBER() 함수 사용.
SELECT employee_id, first_name, last_name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) "RANK"
	FROM hr.employees;

-- 문제9) hr.employees 테이블에서 employee_id가 150인 사원과 같은 급여를 받는 사원 출력하는 쿼리 작성. 서브 쿼리 사용.
SELECT employee_id, first_name, last_name, salary
	FROM hr.employees
	WHERE salary IN (SELECT salary
			FROM hr.employees
			WHERE employee_id = 150);

-- 문제10) hr.employees 테이블에서 department_id가 100인 사원 정보 중에서 job_id, salary가 일치하는 사원 출력하는 쿼리 작성. 서브 쿼리 사용.
SELECT employee_id, first_name, last_name, department_id
	FROM hr.employees
	WHERE (job_id, salary) IN (SELECT job_id, salary
					FROM hr.employees
					WHERE department_id = 100);

-- 문제11) hr.employees 테이블에서 사원 정보 출력시, 부서명(hr.departments 테이블의 department_name)을 같이 출력하는 쿼리 작성. 연관 서브쿼리 사용.
SELECT employee_id, first_name, last_name, (SELECT department_name
                                            FROM hr.departments
                                            WHERE department_id = e.department_id) department_name
	FROM hr.employees e;
-- 11번 틀림. 연관서브쿼리의 개념 다시 이해.

-- 문제12) hr.employees, hr.jobs 테이블을 조인 지정하고 job_title이 'Stock Manager', 'Stock Clerk'인 직원 정보 출력하는 쿼리 작성. Oracle JOIN 표기법 사용.
SELECT employee_id, first_name, last_name, job_title
	FROM hr.employees e, hr.jobs j
	WHERE e.job_id = j.job_id
	AND job_title IN ('Stock Manager', 'Stock Clerk');


-- 문제13) hr.departments 테이블에서 직원이 없는 부서 정보 출력하는 쿼리 작성. LEFT OUTER JOIN 사용.  Oracle JOIN 표기법 사용.
SELECT department_name, employee_id, first_name, last_name
	FROM hr.employees e, hr.departments d
	WHERE d.department_id = e.department_id(+)
    AND employee_id IS NULL;
-- 13번 틀림. 직원이 없는 부서 정보만 출력해야 하므로 employee_id IS NULL 추가.

-- 문제14) hr.employees 테이블에서 말단 직원(부하 직원이 없는 직원) 정보 출력하는 쿼리 작성.  SELF JOIN + OUTER JOIN 사용.  Oracle JOIN 표기법 사용.
SELECT e1.employee_id, e1.first_name, e1.last_name, e2.employee_id, e2.first_name, e2.last_name
	FROM hr.employees e1, hr.employees e2
	WHERE e1.employee_id = e2.manager_id(+)
	AND e2.manager_id IS NULL;
-- 14번 틀림. 부하직원이 없는 사람이 나와야 하므로 e2.manager_id에 OUTER JOIN을 해야함.