ICT블로그

-- OUTER JOIN (오라클)

select a.emp_id, b.emp_id, b.comm, b.work_year

from temp a, tcom b

where b.work_year(+) = '2001'

and b.emp_id(+) = a.emp_id;

-- OUTER JOIN (ANSI 표준)

select a.emp_id, b.emp_id, b.comm

from temp a LEFT JOIN tcom b

ON a.emp_id = b.emp_id

and b.work_year = '2001';

-- EQUI JOIN (오라클)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a, emp_level b

where a.lev = b.lev;

-- EQUI JOIN (ANSI 표준)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a JOIN emp_level b

ON a.lev = b.lev;

-- OUTER JOIN (오라클)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a, emp_level b

where a.lev = b.lev(+);

-- LEFT OUTER JOIN (ANSI 표준)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a LEFT OUTER JOIN emp_level b

ON a.lev = b.lev;

-- JOINING TABLE ITSELT (SELF JOIN)

select * from tdept;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a join tdept b

on a.parent_dept = b.dept_code;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a, tdept b

where a.parent_dept = b.dept_code;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a, tdept b

where a.parent_dept = b.dept_code

and b.dept_name = '영업';

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a join tdept b

on a.parent_dept = b.dept_code

where b.dept_name = '영업';

select * from temp;

-- non equi join (Q/A : 03-8번)

select emp_id, emp_name, birth_date from temp;

-- 직원이 자신보다 생일이 빠른 사람과 매칭했을 때 나타나는 경우의 수 (SELF JOIN, NON-EQUI JOIN)

select a.emp_id, a.emp_name, a.birth_date, b.emp_id, b.emp_name, b.birth_date

from temp a, temp b

where a.birth_date > b.birth_date(+);

-- 20 + ... + 1 = 210 개

-- 19 + ... + 1 = 190 개

-- 직원이 자신보다 생일이 빠른 사람과 매칭했을 때 나타나는 경우의 수2 

-- 단순히 숫자만 COUNT 시 (SELF JOIN, NON-EQUI JOIN)

-- 1. GROUP BY 를 쓴다

select a.emp_id, a.emp_name, a.birth_date, count(*)

from temp a, temp b

where a.birth_date >= b.birth_date

group by a.emp_id, a.emp_name, a.birth_date

order by count(*);

-- 2. 조금 더 조건을 명확히 나타내고 싶다면, >= 대신 OUTER JOIN을 함께 쓰고, null 이 나오는 컬럼을 count한다.

select a.emp_id, a.emp_name, a.birth_date, count(b.emp_id)

from temp a, temp b

where a.birth_date > b.birth_date(+)

group by a.emp_id, a.emp_name, a.birth_date

order by count(b.emp_id);

-- 3. 2번을 ANSI 표준 SQL로 바꾸기

select a.emp_id, a.emp_name, a.birth_date, count(b.emp_id)

from temp a LEFT OUTER JOIN temp b

  ON a.birth_date > b.birth_date

group by a.emp_id, a.emp_name, a.birth_date

order by count(b.emp_id);

-- Q/A : 03-9 #상위부서 찾기

select * from temp;

select * from tdept;

-- 1단계

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명

from temp a, tdept b

where a.dept_code = b.dept_code;

select * from tdept;

select * from temp;

-- 2단계

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명, c.dept_code 상위부서코드, c.dept_name 상위부서명, c.boss_id 상위부서장코드, d.emp_name 상위부서장명

from temp a, tdept b, tdept c, temp d

where b.parent_dept = 'CA0001'

  and a.dept_code = b.dept_code

  and b.parent_dept = c.dept_code

  and c.boss_id = d.emp_id(+);

-- 2단계 (ANSI 표준--> 다중 테이블 조인 연습)

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명, c.dept_code 상위부서코드, c.dept_name 상위부서명, c.boss_id 상위부서장코드, d.emp_name 상위부서장명

from temp a JOIN tdept b

  ON (a.dept_code = b.dept_code and b.parent_dept = 'CA0001') 

     JOIN  tdept c

  ON (b.parent_dept = c.dept_code)

     LEFT OUTER JOIN temp d

  ON c.boss_id = d.emp_id;

     --WHERE b.parent_dept = 'CA0001';

     --> 참고: ANSI 표준 SQL에서 where 절로 필터링 하고 싶을땐, 맨 밑에 where 절을 쓰거나 혹은 JOIN 시 ON 구문안에 and 절로 표시한다. 단 ON 절에 넣는 것은 데이터 검증 조건을 추가하는 것이며, 단순 검색 조건 추가일 경우 WHERE절을 쓸 것을 권고한다. (SQL 전문가 가이드 316p 참조)

-- 참조 부서장을 맡고 있는 부서원 및 해당 부서의 상위부서코드/명/부서장코드/부서장명   

select a.emp_id 사번, a.emp_name 부서장명, b.dept_name 부서명, b.parent_dept 상위부서코드, c.dept_name 상위부서명, d.emp_id 상위부서장코드, d.emp_name 상위부서장명 

from temp a, tdept b, tdept c, temp d

where a.emp_id = b.boss_id

  and b.parent_dept=c.dept_code

  and c.boss_id = d.emp_id(+);

-- 참고 자료

select * from temp;

19970101 김길동 74/01/25 AA0001 정규 Y 등산 100000000 부장

19960101 홍길동 73/03/22 AB0001 정규 Y 낚시 72000000 과장

19970201 박문수 75/04/15 AC0001 정규 Y 바둑 50000000 과장

19930331 정도령 76/05/25 BA0001 정규 Y 노래 70000000 차장

19950303 이순신 73/06/15 BB0001 정규 Y 56000000 대리

19966102 지문덕 72/07/05 BC0001 정규 Y 45000000 과장

19930402 강감찬 72/08/15 CA0001 정규 Y 64000000 차장

19960303 설까치 71/09/25 CB0001 정규 Y 35000000 사원

19970112 연흥부 76/11/05 CC0001 정규 Y 45000000 대리

19960212 배뱅이 72/12/15 CD0001 정규 Y 39000000 과장

*빈 값은 모두 NULL

-- 그룹함수          

-- (1)COUNT           

select count(*), count(8) from temp; -- 10, 10 

select count(*), count(8) from dual; -- 1,1 

--> *나 의미없는 값을 넣으면 row수만큼 읽어온다

select * from temp order by lev, hobby;

select count(emp_id), count(tel), count(hobby) from temp; -- 10, 0, 4

--> count() 괄호 안에 컬럼을 넣으면, 해당 컬럼이 null이 아닌 레코드의 개수를 읽어온다

select lev, 

count(*),  

count(hobby)

from temp

group by lev

order by lev;

-- 과장 4 2

-- 대리 2 0

-- 부장 1 1

-- 사원 1 0

-- 차장 2 1

--참고. GROUP 함수의 특징

select count(*) from temp

where hobby = '공부';

-- 또는

select count(hobby) from temp

where hobby = '공부';

-- count(*)

----------

-- 0

-- 1 row selected. --> 0 rows selected 가 아님. 해당 1개의 그룹(*group by 절이 없으므로 레코드 전체가 하나의 그룹)에 대해 1건의 결과를 출력하며, 그 그룹 중 '공부'가 취미인 레코드가 0개라는 1건의 결과를 출력함

select count(*)

from temp

where lev = '과장'

and salary >= 40000000;

select count(distinct lev) from temp;

-- min과 max

select min(emp_id) from temp;

select lev, min(emp_id)

from temp

group by lev;

-- 연습문제 그룹함수 연습

TEMP에서 직급별로 최소연봉을 가진 직원의 사번과 연봉을 읽어오자. (단,SUBQUERY를 사용하면 안 된다.)

select to_char(salary) from temp;

select lpad(to_char(salary),10,'0') from temp;

select min(lpad(to_char(salary),10,'0')) from temp;

select substr(min(lpad(to_char(salary),10,'0')||emp_id),11,8) EMP_ID from temp;

select lev, substr(min(lpad(to_char(salary),10,'0')||emp_id),11,8) EMP_ID, min(salary) SAL

from temp

group by lev;

-- SUM, AVG

-- 주의 : number type 컬럼만 인수로 받을 수 있다

-- 따라하기

select lev, count(*), sum(salary), avg(salary), sum(salary)/count(*) AVG2

from temp

group by lev;

-- 주의! 평균을 구할때는 NULL 값을 유의해야 한다.

--실험

select avg(salary) from temp; --57600000

update temp set salary = 0 where emp_name = '홍길동';

select count(salary), avg(salary), sum(salary)/count(*) AVG2 from temp; -- 10, 50400000, 50400000

update temp set salary = null where emp_name = '홍길동';

select count(*), count(salary), avg(salary), sum(salary), sum(salary)/count(*) AVG2 from temp; -- 10, 9, 56000000, 504000000, 50400000

--> 결국 avg(salary)= sum(salary)/count(salary) 로 구해진다는 것 (홍길동을 제외한 나머지 9명의 연봉 평균이 구해졌다.)

--> 전체 10명의 평균을 구하기 위해서는 nvl 함수를 사용해줘야 한다.

select avg(nvl(salary,0)) from temp; --50400000

rollback;

-- 데이터형 변환함수

-- 기본 사항              

--          날짜 ---> 문자 ---> 숫자

--             TO_CHAR   TO_NUMBER

--          날짜 <--- 문자 <--- 숫자

--             TO_DATE   TO_CHAR

--          날짜  --/--> 숫자 (불가능)

--          날짜 <--/--  숫자 (불가능)

-- TO_CHAR(1)

-- 날짜를 문자로

-- 기본예제

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; --2016-01-28 21:37:07

select to_char(sysdate,'YyyY') from dual; --2016

select to_char(sysdate,'YYY') from dual; --016

select to_char(sysdate,'YY') from dual; --16

select to_char(sysdate,'Y') from dual; --6

select to_char(sysdate,'SYEAR') from dual; -- TWENTY SIXTEEN

select to_char(sysdate,'YEAR') from dual; --TWENTY SIXTEEN

select to_char(sysdate,'Q') from dual; --1

select to_char(sysdate,'mm') from dual; --01

select to_char(sysdate,'month') from dual; --1월(January)

select to_char(sysdate,'mon') from dual; --1월(JAN)

select to_char(sysdate,'D') from dual; --5

select to_char(sysdate,'DD') from dual; --28

select to_char(sysdate,'DDD') from dual; --028

select to_char(sysdate,'DAY') from dual; --목요일 (Thursday)

select to_char(sysdate,'DY') from dual; --목 (THU)

select to_char(sysdate,'AM') from dual; --오후

select to_char(sysdate,'PM') from dual; --오후

select to_char(sysdate,'A.M.') from dual; --오후

select to_char(sysdate,'P.M.') from dual; --오후

select to_char(sysdate,'HH') from dual; --09

select to_char(sysdate,'HH24') from dual; --21

select to_char(sysdate,'MI') from dual; --37

select to_char(sysdate,'SS') from dual; --07

--응용

select to_char(sysdate,'DD') from dual; --28 --> 원본

select to_char(sysdate,'DDTH') from dual; --28TH (서수)

select to_char(sysdate,'DDSP') from dual; --TWENTY-EIGHT (숫자를 철자로)

select to_char(sysdate,'DDSPTH') from dual; --TWENTY-EIGHTH (서수를 철자로)

select to_char(sysdate,'DDTHSP') from dual; --TWENTY-EIGHTH (서수를 철자로2)

--따라하기

select to_char(sysdate,'yy/mm/dd') date1, --16/01/28

to_char(sysdate,'yyyy.mm.dd') date2, --2016.01.28

to_char(sysdate,'month dd.yyyy') date3, --1월 28.2016

to_char(sysdate,'DY DD MON YY') date4, --목 28 1월 16

to_char(sysdate,'Day Mon DD') date5 --목요일 1월 28

from dual;

-- 연습문제

select emp_name, emp_id, birth_date from temp; --> 생일과 요일 출력변환

select emp_name, emp_id, to_char(birth_date,'yyyy "년" month dd"일" Day') from temp; --> 정답

-- TO_CHAR(2)

-- 숫자를 문자로

select to_char(192939,'999999') from dual; --192939

select to_char(192939,'00000000') from dual; --00192939

select to_char(192939,'999,999') from dual; --192,939

select to_char(192.939,'999.99') from dual; --192.94

select to_char(192939,'$999999') from dual; --$192939

select to_char(192939,'L999,999') from dual; --         ￦192,939

select to_char(-192939,'999999MI') from dual; --192939- (마이너스표시)

select to_char(192939,'999999EEEE') from dual;   2E+05 (과학적표기)

--따라하기

select to_char(1234,'09,999') number1, --01,234

       to_char(1234.56,'99,999.99') number2, --1,234.56

       to_char(1234,'$99,999') number3, --$1,234

       to_char(1234,'L99,999') number4, --         ￦1,234

       to_char(-1234,'99,999MI') number5 --1,234-

       -- to_char(-1234,'99,999EEEE')

       from dual;

--연습문제

select salary,

       to_char(salary, '099,999,999') SAL1,

       to_char(salary, '999,999,999.00') SAL2, 

       to_char(salary, '$999,999,999') SAL3,

       trim(to_char(salary, 'L999,999,999')) SAL4,

       to_char(salary, '999,999,999') SAL5 ,

       to_char(salary, '99EEEE') SAL6

       from temp;

--TO_NUMBER

--문자를 숫자로(O)

--날짜를 숫자로(X)

--따라하기

select to_number('123456') TONUM1, --123456

       to_number('123,456','999999') TONUM2 --123456

       from dual;

--TO_DATE

--문자를 날짜로(O)

--숫자를 날짜로(X)

select to_date('1997-12-31 13:33:44','yyyy-mm-dd hh24:mi:ss')

from dual; -- 97/12/31

select round(sysdate - to_date('19700101','yyyy-mm-dd')) "며칠"

from dual; --16829

--1970년 1월 1일 00시00분00초에서 946075441초가 지나면??

select to_date('19700101000000','yyyymmddhh24miss') +

           946075441 / (24*60*60)

           from dual; -- 99/12/24