복습하기
직무별 급여 출력 (tester1)
select job
, sum(decode(deptno, '10', sal)) as d10
, sum(decode(deptno, '20', sal)) as d20
, sum(decode(deptno, '30', sal)) as d30
, sum(sal)
from emp
group by job;
직무별 급여 출력 (ace)
select job_id
, sum(decode(department_id, '90', salary)) as d10
, sum(decode(department_id, '60', salary)) as d20
, sum(decode(department_id, '50', salary)) as d30
, sum(decode(department_id, '80', salary)) as d40
, sum(decode(department_id, '10', salary)) as d50
, sum(decode(department_id, '20', salary)) as d60
, sum(decode(department_id, '110', salary)) as d70
, sum(salary)
from employees
group by job_id;10. 조인
ORACLE JOIN ANSI JOIN- CROSS JOIN(Cartesian JOIN) CROSS JOIN
- EQUI JOIN NATURAL JOIN
- MON EQUI JOIN JOIN ON
- OUTER JOIN JOIN USING
LEFT
RIGHT
FULL - SELF
조인 사용해보기 (tester1)
select e.ename, e.deptno, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
order by ename;
select e.ename as 사원명
,e.sal as 급여
,s.grade as 등급
from emp e, salgrade s
where s.losal <= e.sal and e.sal <= s.hisal;
-- outer join
-- left outer join
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno(+)
order by ename;
-- right outer join
select e.name, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno
order by ename;
-- self join
select e.ename as 사원
, m.ename as 멘토
from emp e, emp m
where e.ngr = m.empno;
-- cross join
select e.ename, e.deptno, d.deptno, d.dname
from emp e cross join dept d;
-- natural join
select ename, dname
from emp natural join dept;join 표준안으로 수정하기
-- EQUI JOIN
select e.ename, d.danme
from emp e inner join dept d
on e.deptno = d.deptno
order by ename;
-- NON EQUI JOIN
select e.ename as
,e.sal
,s.grade
-- left outer join
select e.ename, d.dname
from emp e left outer join dept d
on e.deptno = d.deptno
order by ename;
-- right outer join
select e.ename, d.dname
from emp e right outer join dept d
on e.deptno = d.deptno
order by ename;
-- full outer join
select e.ename, d.dname
from emp e full outer join dept d
on e.deptno = d.deptno
order by ename;
-- self join
select e.ename as 사원
, m.ename as 멘토
from emp e, emp m
where e.mgr = m.empno;-- Threeways JOIN
-- 사원명 | 부서명 | 등급
select e.ename as 사원명
, nvl(d.dname, ''대기발령) as 부서명
, s.grade as 등급
from emp e, dept d, salgrade s
where e.deptno = d.deptno(+)
and e.sal between s.losal and s.hisal;-- Threeways JOIN (ANSI)
-- 사원명 | 부서명 | 등급
select e.ename as 사원명
, d.dname as 부서명
, e.sal as 급여
, s.grade as 등급
from emp e
left join dept d
on e.deptno = d.deptno
inner join salgrade s
on e.sal between s.losal and s.hisal;조인 사용해보기 (ace)
-- NON EQUI JOIN
-- employees.last_name 사원명
-- employees.salary 급여
-- job_grades.grade_level 등급
select e.last_name as 사원명
, e.salary as 급여
, j.grade_level as 등급
from employees e, job_grades j
where e.salary between j.lowest_sal and j.highest_sal;
select last_name, department_name
from employees e, departments d
where e.department_id = d.department_id(+); 
-- right outer join
select e.last_name, d.department_name
from employees e, departments d
where e.employee_id(+) = d.manager_id;
-- full outer join
select e.last_name as 사원명
, m.last_name as 상사명
from employees e, employees m
where e.manager_id = m.employee_id(+);
-- natural join
select last_name, department_name
from emplyees natural join departments;-- EQUI JOIN
select e.department_id, e.first_name, e.last_name, d.department_id, d.dname
from employees e join departments d
on e.deptno = d.deptno
order by ename;select e.last_name as 사원명
, e.salary as 급여
, j.grade_level as 등급
from employees e join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;-- left outer join
select last_name, department_name
from employees e left outer join departments d
on e.department_id = d.department_id;-- right outer join
select e.last_name, d.department_name
from employees e right outer join departments d
on e.employee_id = d.manager_id;-- full outer join
select e.last_name as 사원명
, m.last_name as 상사명
from employees e full outer join employees m
on e.manager_id = m.employee_id;select last_name, department_name
from employees e, departments d;select last_name, department_name
from employees natural join departments;select last_name, department_name
from employees join departments
using(department_id); --------------------------------------------------
-- 사원명 (employees.last_name)
-- 부서명 (departments.department_id)
-- 등급 (job_grades.grade_level)
-- Threeways join (oracle)
--------------------------------------------------
select e.last_name as 사원명
, d.department_name as 부서명
, j.grade_level as 등급
from employees e, departments d, job_grades j
where e.department_id = d.department_id(+)
and e.salary between j.lowest_sal and j.highest_sal;--------------------------------------------------
-- 사원명 (employees.last_name)
-- 부서명 (departments.department_id)
-- 급여 (employees.salary)
-- 등급 (job_grades.grade_level)
-- Threeways join (ANSI)
--------------------------------------------------
select e.last_name as 사원명
, d.department_id as 부서명
, e.salary as 급여
, j.grade_level as 등급
from employees e
left join departments d
on e.department_id = d.department_id
inner join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;select e.last_name as 사원명
, d.department_name as 부서명
, m.last_name as 사수명
, lo.city as 도시명
from employees e, departments d, employees m, locations lo, jobs j
where e.department_id = d.department_id(+)
and e.manager_id = m.employee_id(+)
and d.location_id = lo.location_id(+)
and e.job_id = j.job_id;select e.last_name as 사원명
, e.salary as 급여
, j.grade_level as 등급
, d.department_name as 부서명
, lo.city as 도시명
, c.country_name as 나라명
from employees e
inner join job_grades j
on e.salary between j.lowest_sal and j.highest_sal
left join departments d
on e.department_id = d.department_id
left join locations lo
on d.location_id = lo.location_id
join countries c
on lo.country_id = c.country_id;질의 작성기로 join하기
- 새 워크시트 > 질의 작성기 > 테이블 드래그 > 공통값 연결 > 출력 컬럼 선택 > 화살표 더블 클릭 후 조인 설정
SELECT
ace.employees.last_name AS 사원명,
ace.departments.department_name AS 부서명,
ace.locations.city AS 도시,
ace.countries.country_name AS 나라,
ace.regions.region_name AS 대륙,
employees1.last_name AS 사수명
FROM
ace.employees
LEFT JOIN ace.departments ON ace.employees.department_id = ace.departments.department_id
LEFT JOIN ace.locations ON ace.departments.location_id = ace.locations.location_id
INNER JOIN ace.countries ON ace.locations.country_id = ace.countries.country_id
INNER JOIN ace.regions ON ace.countries.region_id = ace.regions.region_id
LEFT JOIN ace.employees employees1 ON ace.employees.manager_id = employees1.employee_id
11. 서브 쿼리
서브 쿼리 (tester1)
-- 서브쿼리
select ename, sal
from emp
ㅈhere sal < (select sal
from emp
where ename='조향기');
from emp
where deptno = (select deptno
from emp
where ename = '이문세');
-- 이문세가 속한 부서가 아닌 소속의 모든 사원 출력
select ename, deptno
from emp
where deptno <> (select deptno
from emp
where ename = '이문세');
-- 가장 많은 급여를 받는 사원의 이름과 급여 출력
select ename, sal
from emp
where sal = (select max(sal)
from emp);
select ename, sal
from emp
where deptno in (
select distinct deptno
from emp
where sal > 500);
select ename, sal
from emp
where sal > ALL (select sal
from emp
where deptno = 30);
select ename, sal
from emp
where sal > ANY (select sal
from emp
where deptno = 30);
select *
from dept
where EXISTS (select *
from emp
where emp.deptno=dept.deptno);
개발 기록장