group by, inner join, left outer join, union,merge

--그룹 함수(복수 행 함수)- 여러 개의 행에 대해서 하나의 결과가 반환되는 함수
--null 값을 무시한다. count(*)은 null 허용한다.
select sum(sawon_pay) as 합, avg(sawon_pay) as 평균 from sawon;  --전체 레코드의 합과 평균을 구한다.
select count(sawon_pay) as 개수, max(sawon_pay) as 최대값, min(sawon_pay) as 최소값 from sawon; --개수,최대값, 최소값 구하기
select avg(sawon_pay), avg(nvl(sawon_pay,0)) from sawon; --주의할점 : 연산값중에 null이 있으면 자동으로 무시하고 계산한다.
select sum(sawon_pay) / 15, sum(sawon_pay) /16 from sawon;
select count(*), count(sawon_pay), count(sawon_jik), count(sawon_no) from sawon; --카운터는 (*)을 써주면 모든 레코드를 세어서 계산해준다.

--sawon table : 과장은 몇명?
select count(*) as 인원수 from sawon where sawon_jik='과장';
--sawon table : 2000년 이후에 입사한 남자는 몇명?
select count(*) as 인원수 from sawon where sawon_ibsail>='2000-01-01' and sawon_gen='남';
--sawon table : 2000년 ~ 2005년 사이에 입사한 여직원의 급여합, 급여평균, 인원수 출력(null은 0으로 처리)
select sum(nvl(sawon_pay,0)) as 급여합, avg(nvl(sawon_pay,0)) as 급여평균, count(*) as 인원수 from sawon where sawon_gen='여' and (sawon_ibsail >='2000-01-01' and sawon_ibsail<='2005-12-31');
select sum(nvl(sawon_pay,0)) as 급여합, avg(nvl(sawon_pay,0)) as 급여평균, count(*) as 인원수 from sawon where sawon_ibsail between '2000-01-01' and '2005-12-31' and  sawon_gen='여';

--group by : 소계 출력
select 그룹칼럼명, 계산함수(),,, from 테이블명 where 조건;
group by 그룹칼럼명 having 출력결과 조건;
 * 그룹칼럼에 order by 할 수 없다, 단 출력결과는 order by 가능;
--성별 급여의 평균, 인원수 출력;
 select sawon_gen,avg(sawon_pay)as avg, count(*)as count from sawon group by sawon_gen;
 
--부서별 급여합
select buser_num, sum(sawon_pay) from sawon group by buser_num ;

--부서별 급여합 : 8000 이상
select buser_num, sum(sawon_pay) from sawon group by buser_num having sum(sawon_pay)>=8000;

--부서별 급여합 : 여자
select buser_num, sum(sawon_pay) from sawon where sawon_gen='여'; group by buser_num;  --결과에 대한 제한은 having, 레코드에 대한 제한은 where.  잘 구분해서 사용할 것.

--부서별 급여합 : 5000이상, 남자
select buser_num, sum(sawon_pay) from sawon where sawon_gen='남' group by buser_num having sum(sawon_pay)>=5000;
select buser_num, sum(sawon_pay) from sawon order by buser_num group by buser_num; --error 그룹바이 전에 order by를 쓸 수 없다!!
select buser_num, sum(sawon_pay) from sawon group by buser_num order by buser_num; --성공 : 그룹바이 뒤에 order by는 쓸 수있다, 이유는 그룹바이는 order by를 포함하고 있기때문에.

select buser_num, sum(sawon_pay) from sawon group by buser_num order by sum(sawon_pay) desc;
select buser_num, sum(sawon_pay) as tot from sawon group by buser_num order by tot desc; --성공 : order by 기준키는 별명을 참조 가능.
--그룹 함수 중첩
select max(count(nvl(sawon_pay,0))) as 직급별평균 from sawon group by sawon_jik;  --직급별 합에서 가장 큰것을 나타내준다.
select min(avg(nvl(sawon_pay,0))) as 직급별평균 from sawon group by sawon_jik;    --max, min

 

-----------------------------------연습문제-----------------------------------

문1) 직급별 급여의 평균 (NULL인 직급 제외)
select sawon_jik, avg(sawon_pay) from sawon where sawon_jik is not null group by sawon_jik;

 

문2) 부장,과장에 대해 직급별 급여의 총합 (NULL인 성별 제외)
select sawon_jik, sum(sawon_pay) from sawon where sawon_gen is not null and sawon_jik='부장' or sawon_jik='과장' group by sawon_jik;
select sawon_jik, sum(sawon_pay) from sawon where sawon_gen is not null and sawon_jik in('부장','과장') group by sawon_jik; --선생님 방법 in('XX', 'YY') 참고

 

문3) 2003년 이전에 입사한 자료 중 년도별 직원수 출력
select to_char(sawon_ibsail,'YY'), count(sawon_ibsail) from sawon where sawon_ibsail<='2002-12-31' group by to_char(sawon_ibsail,'YY');

 

문4) 직급별 성별 인원수, 급여합 출력 (NULL인 직급은 임시직으로 표현)
select nvl(sawon_jik,'임시직'),sawon_gen, count(*), sum(sawon_pay) from sawon group by sawon_jik, sawon_gen order by sawon_jik asc;

 

문5) 부서번호 10,20에 대한 부서별 급여 합 출력
select buser_num, sum(sawon_pay) from sawon where buser_num=10 or buser_num=20 group by buser_num;
select buser_num, sum(sawon_pay) from sawon where buser_num in(10, 20) group by buser_num;  -- in('XX', 'YY') 참고

 

문6) 급여의 총합이 7000 이상인 직급 출력(NULL인 직급은 임시직으로 표현)
select nvl(sawon_jik,'임시직'), sum(sawon_pay) from sawon group by sawon_jik having sum(sawon_pay)>=7000;

 

문7) 직급별 인원수, 급여합계를 구하되 인원수가 3명 이상인 직급만 출력
   --     (NULL인 직급은 임시직으로 표현)
select nvl(sawon_jik, '임시직'),count(*), sum(sawon_pay) from sawon group by nvl(sawon_jik, '임시직') having count(*)>=3 order by sum(sawon_pay) desc ;

 

--join : 하나 이상의 테이블에서 자료를 추출
  --테이블 간의 공통 칼럼이 있을때만 join이 가능하다.
--cross join : 두 개간의 칼럼이 서로간에 한번씩 다 연결이 되는것.,
select sawon_name, buser_name from sawon, buser;   --오라클 표현(아래랑 같다.)
select sawon_name, buser_name from sawon cross join buser;  --표준 표현

--self join : 하나의 데이블에서 별명을 주고 join 하는 것(즉, 자기 참조)
select scott.sawon.sawon_name, sawon_jik from scott.sawon;
select a.sawon_name, b.sawon_jik from sawon a, sawon b where a.sawon_no = b.sawon_no;

--equi join : = 연산자 사용
desc buser;
desc sawon;

insert into buser values(50,'비서실','서울','02-100-5555');
alter table sawon modify buser_num number(4) null;
update sawon set buser_num=null where sawon_no=1;
commit;

-- inner join : 일치하는 자료만 작업에 참여한다.  null 자료는 제외된다.
select sawon_name, buser_name, sawon_jik from sawon, buser where sawon.buser_num = buser.buser_no; --오라클 sql
select sawon_name, buser_name, sawon_jik from sawon inner join buser on sawon.buser_num = buser.buser_no;  --ansi(표준) sql, 되도록 표준으로 쓸것.(위와같다. 표준)
--홍길동은 작업에 참여하지 못했다. 부서가 없어서.

-- left outer join : 누락 행(null)도 작업에 참여, 즉 강제 조인이라고도 한다.
select sawon_name, buser_name, sawon_gen from sawon, buser where  sawon.buser_num = buser.buser_no(+); --sawon 테이블 모든 자료 출력
select sawon_name, buser_name, sawon_gen from sawon left outer join buser on sawon.buser_num = buser.buser_no; --위와 같은 뜻(표준)

-- right outer join
select sawon_name, buser_name, sawon_gen from sawon, buser where  sawon.buser_num(+) = buser.buser_no; --buser 테이블 모든 자료 출력
select sawon_name, buser_name, sawon_gen from sawon right outer join buser on sawon.buser_num = buser.buser_no; --위와 같은 뜻(표준)

 

--non-equi join : = 이외의 연산자 사용
create table paygrade(grade number(1) primary key, lpay number, hpay number);
insert into paygrade values(1,0,1999);
insert into paygrade values(2,2000,2999);
insert into paygrade values(3,3000,3999);
insert into paygrade values(4,4000,4999);
insert into paygrade values(5,5000,9999);
commit;
select * from paygrade;
select sawon_name, sawon_pay,grade from sawon,paygrade where sawon.sawon_pay >= paygrade.lpay and sawon.sawon_pay <= paygrade.hpay;
select sawon_name, sawon_pay,grade from sawon inner join paygrade on sawon.sawon_pay >= paygrade.lpay and sawon.sawon_pay <= paygrade.hpay;

-- 부서 내 직원 목록(부서 없는 직원은 제외)
select buser.buser_name, sawon.sawon_name, sawon.sawon_jik, buser.buser_tel from sawon inner join buser on sawon.buser_num = buser.buser_no order by buser.buser_name asc;

 

select * from gogek;
--관리 고객이 있는 직원만 고객자료와 함께 출력(inner join) 겹치는 자료가 없으면 표시안함.
select sawon.sawon_name, gogek.gogek_name, gogek_tel from sawon inner join gogek on sawon.sawon_no = gogek.gogek_damsano order by sawon.sawon_name asc;

--모든 직원목록의 고객자료와 함께 출력(left outer join) 겹치는 자료가 없어도 표시해줌. null값으로
select sawon.sawon_name, gogek.gogek_name, gogek_tel from sawon left outer join gogek on sawon.sawon_no = gogek.gogek_damsano order by sawon.sawon_name asc;

--부서(이름)별 급여합, 급여평균
select nvl(buser_name,'계약직') as 부서명, sum(sawon_pay) as 급여합 , avg(nvl(sawon_pay,0)) as 급여평균, count(*) as 인원수 from sawon, buser where sawon.buser_num = buser.buser_no(+) group by buser_name; -- 오라클식표현
select nvl(buser_name,'계약직') as 부서명, sum(sawon_pay) as 급여합 , avg(nvl(sawon_pay,0)) as 급여평균, count(*) as 인원수 from sawon left outer join buser on sawon.buser_num = buser.buser_no group by buser_name; --표준표현

문1) 직급이 사원인 직원이 관리하는 고객 출력
출력 ==>  사번   사원명   직급      고객명    고객전화    고객성별
                    3     한국인   사원     우주인    123-4567        남
                   
select sawon_no as 사번, sawon_name as 사원명, sawon_jik as 직급, gogek_name as 고객명, gogek_tel as 고객전화,
case substr(gogek_jumin ,8,1) when '1' then '남' when '2' then'여' end as 고격성별 from sawon left outer join
gogek on sawon.sawon_no = gogek.gogek_damsano where sawon_jik='사원';

 

문2) 직원별 고객 확보 수  -- GROUP BY 사용
    - 모든 직원 참여
   
    select sawon_name as 직원명, count(gogek_damsano) as 고객수 from sawon left outer join gogek on sawon.sawon_no = gogek.gogek_damsano group by sawon_name;
   
   
    - 고객이 없는 직원 제외
 select sawon_name as 직원명, count(gogek_damsano) as 고객수 from sawon inner join gogek on sawon.sawon_no = gogek.gogek_damsano group by sawon_name;
 

문3) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면     담당직원 자료 출력   --동명이인을 생각해야한다!

        :    ~ WHERE GOGEK_NAME='강나루'
       출력 ==>  직원명       직급
                한국인       사원
               
select sawon_name, sawon_jik from sawon inner join gogek on sawon.sawon_no = gogek.gogek_damsano where gogek.gogek_name='강나루';
select sawon_no, sawon_jik from sawon inner join gogek on sawon.sawon_no = gogek.gogek_damsano where gogek.gogek_name='강나루';            
   
문4) 직원명을 입력하면 관리고객 자료 출력
       : ~ WHERE SAWON_NAME='한국인'
        출력 ==> 고객명   고객전화          주민번호                 나이
                강나루   123-4567      700512-1234567                38

select gogek_name as 고객명, gogek_tel as 전화번호, gogek_jumin as 주민번호, case when substr(gogek_jumin, 8,1) <=2 then (to_char(sysdate,'YYYY') - concat('19',substr(gogek.gogek_jumin,1,2))+1) when substr(gogek_jumin, 8,1) <=4 then  (to_char(sysdate,'YYYY') - concat('20',substr(gogek.gogek_jumin,1,2))+1) end as 나이 from sawon inner join gogek on gogek.gogek_damsano = sawon.sawon_no where sawon_name='홍길동';

--세개의 테이블 조인
select sawon_name, buser_name,gogek_name from sawon, buser, gogek where sawon.buser_num = buser.buser_no and sawon.sawon_no = gogek.gogek_damsano; --오라클 방법
select sawon_name, buser_name,gogek_name from sawon  --표준 방법 : 첫번째와 두번째가 조인을하고 그 결과물과 세번째를 조인하는 개념이다.
inner join buseron sawon.buser_num = buser.buser_no
inner join gogek on sawon.sawon_no = gogek.gogek_damsano;

문1) 전산부에서 관리하는 고객수 출력 (고객 30살 이상만 작업에 참여)
select buser_name as 부서명, count(gogek.gogek_no) as 인원수 from sawon inner join buser on sawon.buser_num = buser.buser_no
left outer join gogek on sawon.sawon_no = gogek_damsano where (to_char(sysdate,'YYYY') - concat('19',substr(gogek.gogek_jumin,1,2))+1) >= 30 and buser.buser_name='전산부' group by buser_name ;

문2) 부서명별 고객 인원수 (부서가 없으면 "무소속")
select nvl(buser_name,'무소속') as 부서명, count(gogek.gogek_no) as 인원수 from sawon left outer join buser on sawon.buser_num = buser.buser_no
left outer join gogek on sawon.sawon_no = gogek_damsano group by buser_name;
 
문3) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면     담당직원 자료 출력 

        :    ~ WHERE GOGEK_NAME='강나루'
출력 ==>  직원명    직급   부서명  부서전화    성별

select sawon_name as 직원명, sawon_jik as 직급, buser_name as 부서명, buser_tel as 부서전화, sawon_gen as 직원성별 from sawon left outer join buser on sawon.buser_num = buser.buser_no
inner join gogek on sawon.sawon_no = gogek.gogek_damsano where GOGEK_NAME='강나루' ;

select * from sawon;
 

문4) 부서와 직원명을 입력하면 관리고객 자료 출력
        ~ WHERE BUSER_NAME='영업부' AND SAWON_NAME='이순신'
      출력 ==>  고객명    고객전화      성별
                 강나루   123-4567       남
                
select gogek_name, gogek_tel, case substr(gogek.gogek_jumin,8,1) when '1' then '남' when '2' then '여' end as 성별
from sawon
inner join buser on sawon.buser_num = buser.buser_no
inner join gogek on sawon.sawon_no = gogek.gogek_damsano WHERE BUSER_NAME='영업부' AND SAWON_NAME='이순신';

 

--Union : 구조가 일치하는 두 개 이상의 테이블 하나로 합쳐 보기
create table pum1(bun int, pummok varchar2(20));
desc pum1;
insert into pum1 values(1,'사과');
insert into pum1 values(2,'귤');
insert into pum1 values(3,'한라봉');
insert into pum1 values(50,'바나나');

create table pum2(num int, sangpum varchar2(20));
desc pum2;
insert into pum2 values(100,'수박');
insert into pum2 values(200,'참외');
insert into pum2 values(300,'딸기');
insert into pum2 values(400,'토마토');
insert into pum2 values(50,'바나나');

drop table pum2;
select * from pum1;
select * from pum2;
-- 유니온의 별명은 첫번째 나온 것에 줘야한다.
select bun as 번호, pummok as 상품명 from pum1 union (select num, sangpum from pum2);    --union은 중복 데이터가 겹쳐서 나온다. 즉 한번만 나온다.  (a b c)
select bun, pummok from pum1 union all (select num, sangpum from pum2);--union all은 중복 데이터도 따로따로 다 표시해준다. 즉, 중복 데이터가 두 개 이상 표현된다.  (a b b c)
select bun, pummok from pum1 minus (select num, sangpum from pum2);  -- a,b,c 중에 중복자료인 b를 제거하고 a or c 중에 하나만 출력.
select bun, pummok from pum1 intersect select num, sangpum from pum2;  -- 중복된 자료만 출력. 즉, b

--직원명과 고객명 출력
select sawon_name from sawon union select gogek_name from gogek;
--고객을 관리하는 직원 목록 출력
select sawon_no, sawon_name from sawon
where sawon_no in(select sawon_no from sawon intersect select gogek_damsano from gogek);

select distinct gogek_damsano from gogek order by gogek_damsano asc;

--고객을 관리하지 않는 직원 목록 출력
select sawon_no, sawon_name from sawon
where sawon_no in(select sawon_no from sawon minus select gogek_damsano from gogek);

 

--Merge : 구조가 일치하는 두 개 이상의 테이블 하나로 합치기
        --기존에 존재하는 행이 있다면 갱신되고, 존재하지 않는다면 insert(추가) 된다.
create table msa1 as select sawon_no, sawon_name, sawon_pay from sawon
where sawon_no <= 10;  --as select : 기존의 테이블의 값을 참조해서 새로운 테이블을 만드는 것. 단,제약 조건(PK,FK,uniq등등...)은 넘어가지 않는다.

create table msa2 as select sawon_no, sawon_name, sawon_pay from sawon
where sawon_no <= 10 and sawon_jik='사원';

insert into msa1 values(100,'소나타',4500);
insert into msa2 values(200,'이슬비',4550);
select * from msa1;
select * from msa2;

merge into msa1 a using msa2 b on(a.sawon_no=b.sawon_no)     -- a, b는 별명을 준것이다.
when matched then  --matched : 두 자료가 매치가 될때는 update
update set a.sawon_name=b.sawon_name,a.sawon_pay=b.sawon_pay
when not matched then  --not matched : 일치하지 않으면 insert해라
insert values(b.sawon_no, b.sawon_name, b.sawon_pay);  

--Subquery : 질의 속에 질의가 있는 형태 (안쪽 질의의 결과를 바깥쪽 질의에서 사용하는 것이 일반적이다)
select * from sawon;
--'김이화'와 직급이 같은 직원 출력
select sawon_jik from sawon where sawon_name='김이화';  --직급을 얻었다
select * from sawon where sawon_jik='사원';  --해당 직급 자료 출력
-- 아래처럼 두개를 합치는 이유는 서버 퍼포먼스를 향상시킬수 있기때문이다. 서프쿼리가 한번에 처리 할 수 있기때문에.
--1) 일반적인 Subquery 참조 방식 : 안쪽 질의의 결과를 바깥 질의에서 사용
select * from sawon where sawon_jik=(select sawon_jik from sawon where sawon_name='김이화');  --위에 두개를 합친것이다. 즉 김이화의 직급을 구해서 구한 값으로 sawon_jik에서 찾는다.

--직급이 대리중 가장 먼저 입사한 직원은?
select min(sawon_ibsail) from sawon where sawon_jik='대리';
-- select * from sawon where sawon_ibsail='2002-3-2'; --(x) 착각한것.  2002-3-2일에 입사한 사람 모두 나와라. 이뜻임.
select * from sawon where sawon_jik='대리' and sawon_ibsail='2002-3-2';  --(o) 이렇게 써줘야한다.
select * from sawon
where sawon_jik='대리' and    --옆에말을 꼭 써줘야한다.  아래 주석부터 읽어볼 것. sawon_jik='대리' and 없다고 생각하고 아래부터 읽어볼것.
sawon_ibsail=(select min(sawon_ibsail) from sawon where sawon_jik='대리'); --(x) *중요* 착각하면 안된다. 안쪽 질의가 준값은 '2002-3-2'일 이라는 값을 줬기때문에. 바깥쿼리에서 그 값으로 검색을하면 '2002-3-2'에 입사한 모든 사람이 출력되게 된다. 그러므로 바깥쪽쿼리에서 '대리'만 출력하는 조건을 또 걸어줘야한다.

--'김해자'고객과 담당 직원이 같은 고객자료 출력
select * from gogek where gogek_samsano=(select gogek_damsano from gogek where gogek_name='김해자');

--고객중 '차일호'와 나이가 같은 고객자료 모두 출력
select * from gogek where substr(gogek_jumin,1,2)=(select substr(gogek_jumin,1,2) from gogek where gogek.gogek_name='차일호');

--인천에서 근무하는 직원 출력
select * from sawon where buser_num=(select buser_no from buser where buser.buser_loc='인천');  --*중요*테이블이 두개가 같이 사용되었다.
select * from buser;

--인천에서 이외의 지역에서 근무하는 직원 출력
select * from sawon where buser_num in(select buser_no from buser where not buser.buser_loc='인천');  --not을 붙이면 인천을 제외한 나머지값들을 다 넘어오기때문에 =로 받을 수 없다. in을 걸어야한다.

--사번이 3번 직원과 직급이 같고, 직급이 사원인 직원의 평균 연봉보다 귭여가 많은 직원 출력
select * from sawon where sawon_jik=(select sawon_jik from sawon where sawon_no=3) and sawon_pay>=(select avg(sawon_pay) from sawon where sawon_jik='사원');