🤳 [ORACLE]_복습

🤳 8장 연습문제 오답 노트

연습문제, 실습문제 공부 방법
1. 답지 안보고 마무리
2. 답지 보고 채점 (오답 부분 체크)
3. 이해가 가지 않는 부분 q 표시 후 구글링, 벨로그 정리

8-1) 평균 급여 이상을 받는 '모든' 사원의 사원 번호, 성 및 급여를 출력하는 쿼리를 작성합니다.
급여의 오름차순으로 결과를 정렬합니다.
select employee_id, last_name, salary
from employees
where salary > (select avg(salary) from employees)
order by salary

8-2) '성에 문자 "u"가 포함된' 사원과 같은 '부서'에 근무하는 모든 사원의 사원 번호와 성을 출력하는 쿼리를 작성합니다.

select employee_id, last_name
from employees
where department_id in (select department_id from employees where last_name like '%u%');

8-3) 부서 위치 ID(location_id) 가 1700인 사원의 성, 부서 ID 및 작업 ID를 출력하는 쿼리를 작성하세요

select last_name, department_id, job_id
from employees
where department_id in (select department_id from departments where location_id = 1700);

8-4) king(last_name) 에게 보고하는 모든 사원의 성과 급여를 표시하는 보고서를 작성
=매니저가 king인 모든 사원의 last_name, salary 작성

select last_name, salary
from employees
where manager_id in (select employee_id from employees where lower(last_name) = 'king');

8-5) executive 부서의 사원에 대해 부서ID, 성 및 직무 ID를 출력하는 쿼리 작성

select department_id, last_name, job_id
from employees
where department_id in (select department_id from departments where lower(department_name) = 'executive');

8-6) '평균보다 많은 급여를 받고' 성(last_name)에 문자 "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);

cf. 8-2) '성에 문자 "u"가 포함된' 사원과 '같은 부서에 근무하는' '모든 사원'의 사원 번호와 성을 출력하는 쿼리를 작성합니다.

8-7) 커미션을 받는 사원의 부서 번호 및 급여 '둘 다 일치하는' 사원 정보를 출력하는 쿼리를 작성하세요 (다중열 서브쿼리)

select last_name, department_id, salary
from employees
where (department_id, salary) in (select department_id , salary from employees where commission_pct is not null);

8-8) 관리자가 아닌 사원을 출력하는 쿼리를 작성하세요 (not in 연산자 활용)

select last_name
from employees
where employee_id
not in (select manager_id from employees where manager_id is not null);

8-9) '사원이 없는' 모든 부서의 부서번호(department_id), 부서명(department_name)을 출력하는쿼리문을 작성하시오

select department_id, department_name, manager_id, location_id
from departments
where department_id in (select department_id from departments where employee_id is null);

오답)
select *
from departments
where department_id not in (select department_id from employees where department_id is not null);

8-10) 어려움
소속 부서의 평균 급여보다 많이 받는 사원을 출력하는 쿼리 작성 .
사원의 성(last_name), 급여(salary), 부서 ID (department_id), '소속 부서의 평균 급여'(cf. 전체 부서의 평균 급여) 표시.
평균 급여 순으로 정렬, 평균 급여는 소수점 아래 2자리로 반올림하여 표시
출력 행 as 사용

select ename, salary, deptno,
select(avg(sal) from employees where department_id in (select department_id from employees)) dept_avg
from employees
where sal > (select avg(sal) from employees) ;

오답) 특정 부서 소속 ->셀프 조인 위한 테이블 분리, 알리아스 지정
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);

8-11) 관리자가 아닌 사원 정보를 출력하는 쿼리를 exists 연산자를 이용해 작성
select employee_id, last_name
from employees
where employee_id not in (select manager_id from employees where manager_id is not null);

select employee_id, last_name
from employees
where not exists (select 1 from employees);

오답)
select employee_id, last_name
from employees o
where not exists (select 1 from employees e where o.employee_id = e.employee_id);

8-12) 모든 사원의 사원 ID, 성 및 부서 이름을 표시하는 쿼리를 작성합니다. 부서 이름 순서로 정렬합니다. 스칼라 서브쿼리를 사용하여 부서 이름을 표시하시오.

select employee_id, last_name,
(select department_name from departments where ) department
from employees;

오답)
select employee_id, last_name,
(select department_name from departments d where d.department_id = e.department_id) department
from employees e
order by department ;

8-13) 부서의 총 급여가 회사 전체 총급여의 8분의 1(1/8)을 초과하는 부서의 이름과 해당 부서의 총 급여를 표시하는 쿼리를 작성합니다. 부서별 총 급여를 구하는 부분은 with 절로 작성합니다.

with summary
(select department_name, sum(d.sal) dept_total
from departments d
where sal > (select sum(sal)*1/8 from employees e where e.department_id = d.department_id)
select department_name, dept_total from summary;

오답)
with summary as
(select d.department_name, sum(e.salary) 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 sum(e.salary)> (select sum(dept_total)*1/8 from summary);

🤳 실습 연습문제 오답 노트

🤳 ref.

• Oracle(SQL Plus) 서브 쿼리(Sub Query) 이용하기
  https://halfmoon9.tistory.com/32
• 다중열 서브쿼리 이해
  https://yzink.tistory.com/48
• [MSSQL] EXISTS, NOT EXISTS 사용법 쉽게 정리
  https://gent.tistory.com/506