SQL Join

<외부조인 : 조인 조건이 없는 데이터도 출력>
 -> 제품에는 있지만 판매에는 없는 데이터를 조회

* employees 테이블에 들어 있는 카디널리티(tuple의 개수)를 확인해보시오. ->(107개) 
* department와 조인하여 first_name, department_name을 출력하시오. -> (106개 : 부서가 없는 직원이 1명 있음을 의미)

-- 부서가 없는 직원도 출력하시오. -> 외부조인이 필요.

 

1. 오라클의 외부조인 : 무조건 출력할 테이블의 반대쪽에 (+)
select first_name, department_name from employees e, departments d where d.DEPARTMENT_ID=e.DEPARTMENT_ID(+);

2.  ANSI 표준 외부조인 : 왼쪽은 무조건 출력하려면 왼쪽 외부 조인.
select first_name, department_name from departments d left outer join employees e on e.DEPARTMENT_ID=d.department_ID; 

 

3. 왼쪽, 오른쪽 외부조인을 다 해라 : 양쪽 테이블의 모든 행을 모두 출력 - full outer join (오라클 외부조인으로는 못함.)
select e.deptno, d.dname from emp e full outer join dept d on e.deptno=d.deptno;

 

 예제>

 

1. select empno 사원번호, ename 사원이름, sal 연봉, sal+nvl(comm,0) 실급여, grade 급여등금 from emp, salgrade
where sal+nvl(comm,0) between losal and hisal;

 

2. JOB별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서명을 출력하시오. 단, 직업별로 내림차순 정렬
select empno, ename, e.job, deptno from emp, (select job, min(sal) min from emp group by job) e
where e.job=emp.job and sal = min;

 

3. 1981년 6월 1일 ~1981년 12월 31일 입사자 중 부서명이 SALES인 사원의 부서번호, 사원명, 직업, 입사일을 출력.

(단, 입사일 오름차순 정렬하시오. )
select emp.deptno, ename, job, hiredate from emp, dept where emp.deptno=dept.deptno and dname='SALES' and hiredate >= '1981-06-01' and hiredate <='1981-12-31';

 

4. employees, departments, locations를 이용해 각 부서의 부서명, 도시, 인원수를 출력.
    commission_pct를 받으며 2005년 이후 입사한 사원을 대상으로 최소급여가 1000이상인 부서만 출력하시오.
select department_name, city, count(*) from employees e, departments d, locations l
where e.DEPARTMENT_ID=d.DEPARTMENT_ID and d.LOCATION_ID=l.LOCATION_ID and e.COMMISSION_PCT is not null and e.HIRE_DATE>='2005.01.01'
group by d.DEPARTMENT_name, city having min(e.SALARY)>=1000;

 

 

 

 

<셀프 조인>

1.  SMITH 의 관리자번호를 조회해서 그 관리자의 이름을 출력
select mgr from emp where ename='SMITH';  --사원을 조회
select ename from emp where empno=7902; -- 관리자를 조회
-->
select m.ename from emp e, emp m where e.mgr=m.empno and e.ename='SMITH';

 

2. employees  테이블을 이용해 부서 번호가 100인 사원들의 first_name, 관리자 번호( manager_id), 관리자 이름을 출력
select e.FIRST_NAME, e.MANAGER_ID, m.FIRST_NAME  from employees e, employees m
where e.MANAGER_ID=m.EMPLOYEE_ID and e.DEPARTMENT_ID=100;

 

3. 부서번호가 10번인 사원들의 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하세요
select d.deptno, dname, ename, sal, grade from dept d, emp e, salgrade s
where d.DEPTNO=e.DEPTNO and sal between s.LOSAL and s.HISAL and d.DEPTNO=10;

 

4. 부서번호가 10번, 20번인 사원들의 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하고 출력된 결과물을 부서번호가 낮은 순으로, 월급이 높은 순으로 정렬
select d.deptno, dname, ename, sal, grade from dept d, emp e, salgrade s
where d.DEPTNO=e.DEPTNO and sal between s.LOSAL and s.HISAL and d.deptno in (10, 20)
order by d.deptno, sal desc;

 

5. 사원번호와 사원이름, 그리고 그 사원을 관리하는 관리자의 사원번호와 사원이름을 출력하되 각각의 컬럼명을 사원번호','사원이름','관리자번호','관리자이름'으로 하여 출력
select e.empno 사원번호, e.ename 사원이름, m.empno 관리자번호, m.ename 관리자이름 from emp e, emp m
where e.MGR=m.EMPNO;

 

6. 부서명과 부서별 사원수를 출력하시오. sawon, dept 테이블을 사용하시오.
-- 그룹 함수에서 중복을 제거하려면 distinct(컬럼명)을 준다.
select dname, count(*) from sawon s, dept d where s.deptno=d.deptno group by dname;

 

7.  사원 테이블에서 각 사원의 사원번호 사원명, 매니저번호, 매니저명을 출력하세요. 단, 각 사원의 급여(sal)는 매니저 급여보다 많거나 같다.
select s.saname, m.sabun , m.saname from sawon s, sawon m
where s.samgr=m.sabun and s.sapay>=m.sapay;

 

8. 각 부서별로 1981년 5월 31일 이후 입사자의 부서번호, 부서명, 사원번호, 사원명, 입사일을 출력
   단, 부서별 사원정보가 없더라도 부서번호, 부서명은 출력하고, 부서번호 오름차순 정렬하고, 입사일 오름차순 정렬
select d.deptno, dname, sabun, sahire from sawon s, dept d
where s.deptno = d.deptno and sahire >= '1981.05.31' order by d.deptno, sahire;