[새싹] 현대IT&E 231030 기록 - Oracle 10~11

최정윤·2023년 10월 30일
oraclesql
0

새싹

목록 보기
10/67
post-custom-banner

복습하기

직무별 급여 출력 (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
  1. CROSS JOIN(Cartesian JOIN) CROSS JOIN
  2. EQUI JOIN NATURAL JOIN
  3. MON EQUI JOIN JOIN ON
  4. OUTER JOIN JOIN USING
    LEFT
    RIGHT
    FULL
  5. 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);
profile
최정윤
개발 기록장
이전 포스트

[새싹] 현대IT&E 231027 기록 - Oracle 02~04

다음 포스트

[새싹] 현대IT&E 231031 기록 - Oracle 7~11

post-custom-banner

0개의 댓글