2018년 5월 10일

--JOIN 실습 1

--EQUJOIN

select s_emp.name,s_dept.name,s_emp.dept_id,s_dept.id

from s_emp,s_dept

where s_emp.dept_id = s_dept.id;

--Q : 직원(s_emp)테이블과 부서(s_dept)테이블을 join해서 //사원의 이름과 부서,부서명을 나타내시오

select s_emp.name,s_emp.dept_id 부서,s_dept.name 부서명

from s_emp, s_dept

where s_emp.dept_id = s_dept.id;

--Q : 서울 지역에 근무하는 사원에 대해 각 사원의 이름과 근무하는 부서명을 나타내시오. ALIAS로 다시하기

select s_emp.name as 사원이름, s_dept.name as 부서

from s_emp, s_dept,s_region

where s_emp.dept_id = s_dept.id --테이블 3개 JOIN 된다.

and s_dept.region_id = s_region.id

and s_dept.region_id=1

and title='사원';

select e.name, d.name,r.id

from s_emp e, s_dept d, s_region r

where e.dept_id = d.id--1.u_emp.dept_id와 u_dept.id를 join

and d.region_id=r.id --u_dept.id 와 s_region.id를 join

and r.id=1

--and title='사원'

;

--------------------------------------

--테이블 확인용------------------------

select *

from s_region;

select *

from s_dept;

select *

from s_emp;

select *

from salgrade;

select *

from s_customer;

------------------------------------------------

--NON-EQUIJOIN 예제

--Q : 직원 테이블 (S_EMP)과 급여 테이블 ( SALGRADE)를 조인해서 /사원의 이름과 급여, 해당 급여등급을 나타내시오.

select e.name, e.salary, g.grade --사원이름, 급여, 급여등급을 나타낸다 

from s_emp e,salgrade g  -- s_emp 테이블과, salgrade 테이블에서

where e.salary between g.losal and g.hisal -- 직원의 급여가 g.losal과 g.hisal 사이의 등급을 매김

;

--OUTER JOIN

SELECT e.name 사원명, e.id,c.name 고객명

FROM s_emp e , s_customer c

where e.id = c.SALES_REP_Id (+); --(+)를 e.id 쪽에 붙히면 담당사원이 없는 고객 (+)를 c.뒤에 붙히면 담당고객이 없는 사원

select e.id 사번,e.name 사원, c.name 고객명

from s_emp e, s_customer c

where c.sales_rep_id = e.id(+)

order by 1 asc

;

 --담당사원이 null인사람 count

select c.name 고객명

from s_emp e, s_customer c

where c.sales_rep_id = e.id(+)

and e.id is null;

--Q : 담당고객이 없는 직원원의 목록을 구하시오

SELECT e.name 사원명, c.name 고객명

FROM s_emp e , s_customer c

where e.id = c.SALES_REP_Id(+)

and c.name is null;

--Q : 직원별로 담당고객의 수를 출력하시오

SELECT e.name 사원명,count(c.SALES_REP_ID) 담당고객수

FROM s_emp e , s_customer c

where e.id = c.SALES_REP_Id (+) --e.id와 c.sales_rep_id 가 같은경우 카운트

group by e.name, c.SALES_REP_ID

order by 2 desc;

--Q : 직원(s_emp)테이불과 고객(s_customer)테이블에서 사원의 이름과 사번, 그리고 각 사원의 담당고객 이름을 나타내시오

--    단, 고객에 대하여 담당 영업사원이 없더라도 모든 고객의 이름을 나타내고, 사번 순으로 오름차순 정렬하시오

select e.id 사번, e.name 사원명, c.name 고객명

from s_emp e,s_customer c

where e.id (+) = c.sales_rep_id

order by 1 asc;

--SELF JOIN-

select e.name 직원 ,e2.name 관리자

from s_emp e, s_emp e2

where e.manager_id = e2.id;

--SELF JOIN 예제

--Q : 직원 중 '김정미'(s_emp.name) 와 같은 직책(title)을 가지는 사원의 이름과 직책, 급여, 부서번호를 나타내시오 (SELF JOIN을 사용할 것)

select e2.name

from s_emp e, s_emp e2

where e.title = e2.title --직책이 같으면서

and e.name ='김정미' --이름이 김정미 ---> e.name ='김정미'~~~

and e2.name<>'김정미'

;

--UNION

select name, title from s_emp where title ='과장'

union

select name, title from s_emp where title ='사원';

select name, dept_id, title

from s_emp

where dept_id = 110

union 

select name, dept_id, title

from s_emp

where dept_id = 113

order by 1;

--SUBQUERY

--Single Row SubQuery

select name, title 직책, dept_id 부서번호

from s_emp

where dept_id = (select dept_id from s_emp where name = '김정미') --dept_id 중에 김정미와 같은부서 

;

select name,dept_id

from s_emp

where dept_id = (select dept_id from s_emp where name = '김정미');

--Q : 직원 중에 김정미와 같은 직책을 가진 사람들을 모두 출력하시오

select name,dept_id

from s_emp

where dept_id = (select dept_id from s_emp where name = '김정미')

and name<>'김정미'

;

--Multi Row subQUERY

select name, dept_id

from s_emp

where dept_id in (select id from s_dept where region_id =3);

select name, dept_id, salary

from s_emp

where (salary,dept_id) in (select min(salary), dept_id from s_emp group by dept_id);

--having 절에서의 subquery

select dept_id, avg(salary)

from s_emp

group by dept_id

having avg(salary) > (select avg(salary) from s_emp where dept_id=113);

--Q: 가장 적은 평균급여를 받는 직책에 대해 그 직책과 평균 급여를 나타내시오.

select  min(e.avg_sal)

from (select title, avg(salary) as avg_sal

from s_emp group by title) e

;

select title, avg(salary)

from s_emp e

group by title

having avg(salary) = 809

order by 2

;

select title, min(salary) --직책별 최소 평균연봉

from s_emp e

group by title  

having avg(salary) = (select min(avg(salary)) from s_emp e group by title)

;

create table emp_113 (id, name, mailid, start_date)

as select id, name, mailid, start_date

from s_emp

where dept_id = 113

--where 1<> --테이블의 구조만 만들고 싶은경우

;

insert into emp_113 (id,name,mailid, start_Date)

select id, name, mailid, start_date

from s_emp

where start_date < '16/01/01';