오라클[sql] 연습문제
연습문제
▶ employees 테이블 / departments 테이블
-- 부서정보
CREATE TABLE departments AS SELECT * FROM HR.DEPARTMENTS;
▶ Zlotkey와 동일한 부서에 속한 모든 사원의 이름과 입사일을 표시하는 질의를 작성하십시오. -- Zlotkey는 제외
SELECT first_name || ' ' || last_name AS full_name, hire_date, department_id
FROM employees
WHERE department_id = ( SELECT department_id FROM employees WHERE last_name = 'Zlotkey')
AND last_name != 'Zlotkey';
-- AND last_name <> 'Zlotkey';
▶ 급여가 평균 급여보다 많은 모든 사원의 사원 번호와 이름을 표시하는 질의를 작성하고
결과를 급여에 대해 오름차순으로 정렬하십시오.
SELECT employee_id
, first_name || ' ' || last_name AS full_name
, salary
FROM employees
WHERE salary > (SELECT avg(salary) FROM employees)
ORDER BY salary ASC;
▶ 이름에 u가 포함된 사원과 같은 부서에서 일하는 모든 사원의 사원 번호와 이름을 표시하는 질의를 작성하십시오.
풀이1)
SELECT employee_id
, first_name || ' ' || last_name AS full_name
FROM employees
WHERE department_id IN ( SELECT DISTINCT department_id
FROM employees
WHERE first_name like '%u%' OR last_name like '%u%');
풀이2)
SELECT employee_id
, first_name || ' ' || last_name AS full_name
FROM employees
WHERE department_id IN ( SELECT DISTINCT department_id
FROM employees
WHERE first_name || last_name like '%u%');
▶ 부서 위치 ID가 1700인 모든 사원의 이름, 부서 번호 및 업무 ID를 표시하십시오.
풀이 1 ) 서브쿼리
SELECT first_name || ' ' || last_name AS full_name
, department_id
, job_id
FROM employees
WHERE department_id IN ( SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700);
풀이2 ) 조인
SELECT e.first_name || ' ' || e.last_name AS full_name
, e.department_id
, e.job_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.location_id = 1700;
풀이3) 조인
SELECT e.first_name, e.last_name, e.department_id, e.job_id
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id
WHERE d.location_id = 1700;
▶ King에게 보고하는 모든 사원의 이름과 급여를 표시하십시오.
-- King한테 보고한다 > 하급자가 상급자에게 내용을 전달, manager_id 나를 담당하고 있는 선배의 id
풀이1) 서브쿼리
SELECT first_name || ' ' || last_name AS full_name, salary
FROM employees
WHERE manager_id IN ( SELECT employee_id
FROM employees
WHERE last_name = 'King');
풀이 2) 조인
SELECT e1.first_name || ' ' || e1.last_name AS full_name, e1.salary
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id
AND e2.last_name = 'King';
▶ Executive 부서의 모든 사원에 대한 부서 번호, 이름 및 업무 ID를 표시하십시오.
풀이 1) 조인
SELECT e.department_id
, e.first_name || ' ' || e.last_name AS full_name
, e.job_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'Executive';
풀이 2) 조인
SELECT e.department_id, e.first_name, e.last_name, e.job_id
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'Executive';
풀이 3) 서브쿼리
SELECT department_id
, first_name || ' ' || last_name AS full_name
, job_id
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Executive');
▶ 평균 급여보다 많은 급여를 받고 이름에 u가 포함된 사원과 같은 부서에서 근무하는
모든 사원의 사원 번호, 이름 및 급여를 표시하는 질의를 작성하십시오.
풀이 1)
SELECT employee_id
, first_name || ' ' || last_name AS full_name
, salary
FROM employees
WHERE salary > (SELECT avg(salary) FROM employees)
AND department_id IN (SELECT department_id FROM employees WHERE first_name like '%u%' OR last_name like '%u%');
풀이 2)
SELECT employee_id
, e.first_name || ' ' || e.last_name AS full_name
, salary
FROM employees e
, (SELECT avg(salary) avg FROM employees) e1
, (SELECT DISTINCT department_id FROM employees WHERE first_name like '%u%' OR last_name like '%u%') e2
WHERE e.salary > e1.avg
AND e.department_id = e2.department_id;
▶ 업무별로 월급이 평균 월급보다 낮은 사원을 부서번호, 이름, 급여를 출력
SELECT deptno, ename, sal
FROM emp e1,( SELECT job, avg(sal) avg_sal FROM emp GROUP BY job) e2
WHERE e1.job = e2.job AND e1.SAL < e2.avg_sal;