--========================================
-- JOIN
--========================================
-- 1.직원들의 이름과 직급명(job_title)을 조회하시오.
SELECT first_name, job_title
FROM employees a, jobs b
WHERE a.job_id = b.job_id;
-- 2.부서이름과 부서가 속한 도시명(city)을 조회하시오.
SELECT department_name, city
FROM departments a, locations b
WHERE a.location_id = b.location_id;
-- 3. 직원의 이름과 근무국가명을 조회하시오.
SELECT first_name, city
FROM employees a, departments b, locations c
WHERE a.department_id = b.department_id
and b.location_id = c.location_id;
-- 4. 직책(job_title)이 'manager' 인 사람의 이름, 직책, 부서명을 조회하시오.
SELECT first_name, a.manager_id, department_name, job_title
FROM employees a, departments b, jobs c
WHERE a.department_id = b.department_id
and a.job_id = c.job_id
and lower(job_title) like '%manager';
-- 5. 직원들의 이름, 입사일, 부서명을 조회하시오.
SELECT first_name, start_date, department_name
FROM employees a, job_history b, departments c
WHERE a.employee_id = b.employee_id
and a.department_id = c.department_id;
-- 6. 직원들의 이름, 입사일, 부서명을 조회하시오.
--단, 부서가 없는 직원이 있다면 그 직원정보도 출력결과에 포함시킨다.
SELECT first_name, start_date, department_name, nvl2(a.employee_id,'소속', '소속없음')
FROM employees a, job_history b, departments c
WHERE a.department_id = c.department_id
and a.employee_id = b.employee_id(+);
--7. 직원의 이름과 직책(job_title)을 출력하시오.
--단, 사용되지 않는 직책이 있다면 그 직책정보도 출력결과에 포함시키시오.
-- SELF JOIN
SELECT a.first_name, nvl(job_title,'직책없음')
FROM employees a, employees b, jobs c
WHERE a.manager_id = b.employee_id
and a.job_id = c.job_id(+);
--8. 직원의 이름과 관리자 이름을 조회하시오.
SELECT a.first_name 직원, b.first_name 관리자
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;
--9. 직원의 이름과 관리자 이름을 조회하시오.
--관리자가 없는 직원정보도 모두 출력하시오.
SELECT a.first_name 직원, nvl(b.first_name,'관리자없음') 관리자
FROM employees a, employees b
WHERE a.manager_id = b.employee_id(+);
--10. 관리자 이름과 관리자가 관리하는 직원의 수를 조회하시오.
--단, 관리직원수가 3명 이상인 관리자만 출력되도록 하시오.
SELECT b.first_name 관리자, a.first_name 관리직원
FROM employees a, employees b
WHERE a.manager_id = b.employee_id
GROUP BY b.first_name, a.first_name
HAVING count(b.manager_id) >= 3;
========================================
SubQuery or join
========================================
1. 'IT'부서에서 근무하는 직원들의 이름, 급여, 입사일을 조회하시오.
SELECT first_name, salary, hire_date
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
2. 'Alexander' 와 같은 부서에서 근무하는 직원의 이름과 부서id를 조회하시오.
SELECT first_name, salary, hire_date
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE first_name = 'Alexander');
3. 직원들의 이름, 입사일, 부서명을 조회하시오.
단, 부서가 없는 직원이 있다면 그 직원정보도 출력결과에 포함시킨다.
그리고 부서가 없는 직원에 대해서는 '<부서없음>' 이 출력되도록 한다.
select e.first_name, e.hire_date, nvl(d.department_name,'<부서없음>' )
from employees e left join departments d
on e.department_id = d.department_id
4. 직원의 직책에 따라 월급을 다르게 지급하려고 한다.
직책에 'Manager'가 포함된 직원은 급여에 0.5를 곱하고
나머지 직원들에 대해서는 원래의 급여를 지급하도록 한다.
적절하게 조회하시오.
select e.first_name, j.job_title, salary,
decode(substr(job_title, -7), 'Manager', salary*0.5, salary)
from employees e, jobs j
where e.job_id = j.job_id;