Duritz's Fun Life

----   [A]        [B]
----  | 1 |        | a |   : A
----  | 1 | ---> | a |   : = [(1,a), (1,b) .... (2,a), (2,b) ....... (3,d)]
----  | 1 |        | a |   : ==> "데카르트곱"
----                 | a |   :
----    3    X     4     =  12개
select count(*)
from dept;

select count(*)
from EMP;

select *
from EMP, DEPT;  -- 모든경우의 수(cartesian product)가 다나오게 된다.

select *
from EMP, DEPT
where Emp.deptno = Dept.deptno; -- 이와 같이하면 같은 것끼리 연결이 된다.

/* select deptno, dname, ename, sal -- 이렇게 명시하면 deptno가 어떤테이블의 것인지 불확실해서 오류가난다. */
select E.deptno, D.dname, E.ename, E.sal -- 두 테이블에서 중첩이 없는 컬럼도 테이블명을 명시해주는 것을 권장.
from emp E, dept D                           -- 명시를 안하면 해당 컬럼을 검색하기 위해 두개의 테이블을
where E.deptno = D.deptno;               -- 모두 검색하나 명시하면 그것에 해당하는 테이블만 검색하게 된다.
--//// 바로위 문법은 SQL 1992 CODE, 바로 아래 문법은 SQL 1999 CODE(Oracle 9i 부터사용)
---- inner join (equi-join) : 중복되는 컬럼이있어도 출력값은 변함없다.
select E.deptno, D.dname, E.ename, E.sal
from emp E inner join dept D -- inner는 생략 가능하다.
on E.deptno = D.deptno;
--//// from emp E join dept D 는 from dept D join emp E 해도 무방하나
--//// from절에서 가장멀리있는 부분에 row가 적은것 또는 부모테이블을 적용시키는 것을 권장한다.
select E.deptno, D.dname, E.ename, E.sal -- E.DEPTNO를 D.deptno를 써도 무방하다.
from emp E, dept D
where E.deptno = D.deptno;
---- outer-join (+) : 중복되는 컬럼이 있으면 출력값이 달라진다. (애초에 부모컬럼(P.K)을 사용한다.)
select D.deptno, D.dname, E.ename, E.sal -- D.deptno를 E.DEPTNO로 쓰면 값이 달라진다.
from emp E, dept D
where E.deptno(+) = D.deptno;
--/// (+) 가 있는쪽을 다 보여주고 해당하는 값을 매핑한다.
/* sys계정으로 접속해서 실행해야한다. */
/* 다음 두문장(inner join)은 동일하다. 위는 1992년식, 아래는 1999년식이다. */
select D.department_id, E.FIRST_NAME
from hr.employees E, hr.departments D
where E.department_id = D.department_id;

select D.department_id, D.department_name, E.first_name
from hr.employees E left outer join hr.departments D   -- outer는 생략가능
on E.department_id = D.department_id;
--/// left는 왼쪽의 테이블을 다보여주고 매핑, right는 오른쪽에 테이블을 다 보여주고 매핑
/* 다음 두문장(outer join)은 동일하다. 위는 1992년식, 아래는 1999년식이다. */
select D.department_id, E.FIRST_NAME
from hr.employees E, hr.departments D
where E.department_id(+) = D.department_id;

select D.department_id, D.department_name, E.first_name
from hr.employees E right outer join hr.departments D   -- outer는 생략가능
on E.department_id = D.department_id;
--//////////////////////////////////////////////////////////////////////////////
select *
from hr.employees E full outer join hr.departments D   -- 정규화 하기 이전상태
on E.department_id = D.department_id;                 
------/// (+) left(right)가 없으면 inner(equi) join, 있으면 out join이다.
--/////////////////////////////////////////////////////////////////////////////--

---- non equi-join : 범위에 들어오는 값을 매핑시켜 준다.
------- 연습문제 : emp테이블에서 성명, 급여, 호봉
-------            단. 호봉은 1000미만이면 1호봉, 1000이상이면 2호봉, 2000이상이면 3호봉
-------            3000이상이면 4호봉, 4000이상이면 5호봉
select ename 성명, sal 급여,
    case when sal > 4000 then 5
            when sal >= 3000 then 4
            when sal >= 2000 then 3
            when sal >= 1000 then 2
            else 1
            end "호봉"
from EMP; -- 업데이트 할려면 소스에 써진 모든 값을 다 변경해야 해서 유지 보수가 힘들다.
/*
select *
from salgrade; */
--/// 위 문장과 동일하나 아래 문장은 테이블값만 변경하면 되기때문에 유지보수가 편하다. ///--
select E.ename, E.sal, G.GRADE
from emp E, SALGRADE G
where E.sal between G.losal and G.hisal;

---- self join :
select *
from EMP;

/* 1992년식 */
select E1.empno 사원번호, E1.ename 사원명, E1.job 직종, E2.EMPNO 관리자사원번호,
          E2.ENAME 관리자명, E2.JOB 관리자직종 
from emp E1, emp E2
where E1.mgr = E2.empno(+); -- E1에 mgr은 F.K가 되고 E2의 empno는 P.K가 된다.

/* 1999년식 */
select E1.empno 사원번호, E1.ename 사원명, E1.job 직종, E2.EMPNO 관리자사원번호,
          E2.ENAME 관리자명, E2.JOB 관리자직종 
from emp E1 left join emp E2
    on E1.mgr = E2.empno; -- E1에 mgr은 F.K가 되고 E2의 empno는 P.K가 된다.

---- all 함수 : 해당컬럼을 모두 비교해라. (and와 같다.)
--///// 하위 문법은 Oracle DB 에서는 잘되나, MS-SQL은 문법오류가 생긴다. 그래서 all 함수를 사용한다.
select deptno, sum(sal)
from EMP
group by deptno
having sum(sal) = (select max(sum(sal)) -- 그룹함수안에 그룹함수는 한번만 사용가능하다.
                   from EMP
                   group by deptno);
                  
select deptno, sum(sal)
from EMP
group by deptno
having sum(sal) >= all(select sum(sal) -- 그룹함수는 null값이 존재치 않기때문에
          from EMP            -- where 컬럼 is not null을 사용하지 않아도 된다.
       group by deptno);
-------- 연습문제 : sal가 최고인 사람의 사원명과 직종과 직급과 급여를 나타내시오.                  
select ename, job, sal
from emp
where sal = (select max(sal) from emp);

select ename, job, sal
from EMP
where sal >= all(select sal from emp); -- sal을 비교해서 sal과 같거나 큰것을 찾는다.
-------- 연습문제 : comm이 최고인 사람의 사원명과 직종과 직급과 급여를 나타내시오.                  
select ename, job, comm
from emp
where comm = (select max(comm) from emp);

select ename, job, comm
from EMP
where comm >= all(select comm from emp); -- 해당 컬럼에 null이 존재하면 오류가 나온다.

select ename, job, comm
from EMP
where comm >= all(select comm from emp where comm is not null);

---- rollup, cube 함수
select deptno, job, sum(sal)
from emp
group by deptno, job;  -- group by 1차,2차

select deptno, sum(sal)
from EMP
group by rollup(deptno); -- rollup() : 요약값, 그룹지어진것의 합을 구한다.

select deptno, job, sum(sal)
from emp
group by rollup(deptno, job); -- 직종에 상관없이 부서별 합계가 나오고, 부서번호&직종에 상관없이 전체합이 나온다.

select deptno, sum(sal)
from EMP
group by cube(deptno);

select deptno, job, sum(sal)
from emp                     -- cube() : rollup과 동일하나 추가로 2차 컬럼의 종류별 합계도 나온다.
group by cube(deptno, job);  --          cube는 2차그룹이 있을때 더 효율적이다.
/* sys로 접속해서 실행 */
--////// rollup을 사용할때 그룹할 column에 null이 존재하면 결과값중에 실값과 요약값이 구분하기 힘들다.
--////// 이럴때 grouping 을 쓰면 구분하기가 좋다.
--////// grouping의 값이 0이면 실값이고, 1이면 요약값이다.
select department_id, grouping(department_id), sum(salary)
from hr.employees
group by rollup(department_id);

select case grouping(department_id)
      when 1 then '전체부서'  -- 해당 컬럼데이터가 숫자이면 숫자만 가능하나 문자로 입력하려면
      else to_char(department_id) -- 해당컬럼값을 문자로 바꾸면 문자 출력이 가능하다.
    end as "부서번호",
    sum(salary) as "급여합"
from hr.employees
group by rollup(department_id);

---- having절 : 그룹함수에 대한 조건절
select deptno, sum(sal)
from EMP
where sum(sal) >= 9000 -- 여기에 where절에 sum(sal)을 넣으면 어느그룹의 것인지 알 수 없다.(문법오류)
group by deptno;

select deptno, sum(sal)
from EMP
group by deptno
having sum(sal) >= 9000;  -- 그룹함수 조건인 having절을 넣어야 오류없이 실행된다.

------------- 다음 문장을 비교하였을때 두문장은 결과는 같으나 아래쪽이 더 빠르게 실행된다.
------------- 일반컬럼은 having절에 쓰는걸 권장하지 않는다. (그룹함수만 쓰자.)
select job, sum(sal)     -- 이문장은 job의 모든 행을 메모리에 로드시키고 실행한다.
from EMP
group by job
having job in ('MANAGER','SALESMAN');

---- group by : group by column (컬럼으로 그룹지어라) , 자동적으로 첫번째 컬럼기준 오름차순으로 정렬된다.
select deptno, sum(sal) -- 부서별 sal 합
from EMp
group by deptno;

select job, sum(sal) -- 직종별 sal 합
from EMp
group by job;

select deptno, count(*) -- 부서별 인원합
from emp          -- count를 하기위해서는 null이 없는 컬럼을 선택해야하는데 찾으려면 시간이 소요되니
group by deptno         -- 대신 *을 사용한다.

select job, count(*) -- 직종별 인원합
from EMp
group by job;

--------------- 연습문제 : 사원테이블에서 성별인원수를 구하시오.
insert into sawon values
(1005,'이영애','8011012234567','2000-03-02');
commit;
select * from SAWON;

select S.성별, count(*) "인원수"
from (select case when substr(minbun,7,1) in ('1','3') then '남'
    else '여' end "성별"
from SAWON) S
group by S.성별;
--------------- 연습문제 : 연령대별 인원수를 구해보자.
select Y.연령대, count(*)
from
(select case when substr(minbun,7,1) in ('1','2')
        then floor((extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1899))/10)
         when substr(minbun,7,1) in ('3','4')
     then floor((extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1999))/10)
   end "연령대"
from SAWON) Y
group by Y.연령대

--- 단일행 함수와 그룹함수의 차이점
---- Column             함수             결과
----   1  ------------> |단| ---------->  a
----   2  ------------> |일| ---------->  b    : 단일행은 한행에 대한 결과값이 1개씩 매핑이 된다.
----   3  ------------> |행| ---------->  c
---------------------------------------------
----   1  ------------> |그| ---------->  a
----   2  ------------> |룹|                   : 그룹함수는 여러행이 그룹지어 하나의 결과 값이 나온다.
----   3  ------------> |함|                   : 그룹함수는 null이 있으면 null은 제외해 버린다.
----   4  ------------> |수|             
---------------------------------------------
select sum(sal),  -- 합계
    avg(sal),  -- 평균  sum(sal) / count(sal)
    max(sal),  -- 최대값
    min(sal),  -- 최저값
    count(sal) -- 행의갯수
from emp;

---- null이 포함되면 평균치 결과가 null 이 포함되어지지 않는 사람들만의 평균이 된다.
select sum(comm),  
    avg(comm), 
    max(comm), 
    min(comm), 
    count(comm) -- null이 포함되어 있으면 null을 제외하므로 제대로된 값이 나오지 않는다.
from emp;

select avg(comm), avg(nvl(comm,0)) -- null을 0으로 간주해야 제대로된 평균치가 계산된다.
from EMP;

select variance(sal), -- 분산 = 편차^2(편차의 제곱)
    stddev(sal),   -- 편차
    avg(sal)       -- 평균
from emp;

---- rank 함수
select deptno "부서번호", ename "성명", sal "급여",
    rank() over(order by sal desc) "등수", -- 해당 column의 등수를 준다.
    dense_rank() over(order by sal desc) "서열" --  서열을 나타내 준다.
from emp;

select deptno "부서번호", ename "성명", sal "급여",
    rank() over(partition by deptno order by sal desc) "부서등수",
    -- partition by 는 해당 컬럼별 순위를 나타낸다.
      rank() over(order by sal desc) "등수" -- 해당 column의 등수를 준다.
from emp;

select deptno "부서번호", ename "이름", sal "급여",
    rank() over(partition by deptno order by sal desc) "부서등수",
    rank() over(order by sal desc) "전체등수",
    dense_rank() over(partition by job order by coalesce(sal*12+comm, sal*12, 0) desc) "직종별등수",
    dense_rank() over(order by coalesce(sal*12+comm, sal*12, 0) desc) "전체서열"
from emp
order by 1;

select deptno "부서번호", ename "성명",
    coalesce(sal*12+comm, sal*12, 0) "연봉",
    rank() over(order by coalesce(sal*12+comm, sal*12, 0) desc) "등수"
from emp;

select deptno "부서번호", ename "성명", sal "급여",
    rank() over(order by sal desc) "등수" -- 해당 column의 등수를 준다.
from emp
where rank() over(order by sal desc) <= 5; -- rank 함수는 where절에 사용이 안된다.

--- ○ 변환형 함수
--    ↙-- to_number -┐↙--- to_char --┐
-- number            char              date
--   └-- to_char ---↗└-- to_date ---↗

select sysdate,
    to_char(sysdate,'year'),  -- two thousnad seven
    to_char(sysdate,'yyyy'),  -- 2007
    to_char(sysdate,'month'), -- february
    to_char(sysdate,'mon'),   -- feb
    to_char(sysdate,'mm'),    -- 02
    to_char(sysdate,'dd'),    -- 14
    to_char(sysdate,'hh am'), -- 03 오후 
    to_char(sysdate,'hh24'),  -- 15
    to_char(sysdate,'mi'),    -- 39
    to_char(sysdate,'ss'),    -- 43
    to_char(sysdate,'sssss')  -- 56383 : 현재날짜의 0시0분0초로부터 현재까지 지나간 초를 나타낸다.
from dual;

select to_char(sysdate,'day') -- 요일명 표시
from dual;
select to_char(sysdate,'ddd') -- 현재 년도의 첫째일부터 현재날짜까지 지나온 일수를 표시
from dual;
select to_char(sysdate,'dd') -- 현재 달의 첫째일로부터 현재날짜까지 지나온 일수를 표시
from dual;
select to_char(sysdate,'d') -- 현재 주의 첫째일로부터 현재날짜까지 지나온 일수를 표시
from dual;

select to_char(sysdate, 'q') -- 현재 년도의 분기를 표시한다
from dual;

select to_char(sysdate, 'fmyyyy-mm-dd') from dual; -- fm을 표시하면 앞에 숫자0이 포함되면 나타지않는다.
--------- 연습문제 : 오늘의 날짜를 다음 같이 나타내시오. (2007년 2월 14일 수요일)
select to_char(sysdate, 'yyyy') || '년 ' ||  to_char(sysdate, 'fmmm') || '월 ' ||
    to_char(sysdate, 'fmdd') || '일 ' ||to_char(sysdate, 'day') "오늘의 날짜"
from dual;
--------- 연습문제 : 내가 태어난 요일명은 무엇일까?
select to_char(to_date('1981-08-09','yyyy-mm-dd'), 'day') "내가태어난 요일" from dual;

---- 숫자 -> 문자 변환 기타
select 1234567, to_char(1234567, 'L9,999,999') -- 'L9,999,999' 이렇게 입력하면 해당국가의 통화표시가 나온다.
from dual;

select 1234567, to_char(1234567, 'L9,999,999.99') -- 소숫점 표시도 가능하다.
from dual;

select 1234567, to_char(1234567, '09999999') -- 해당 숫자앞에 0을 넣을수도 있다.
from dual;

select ename, to_char(sal, '$9,999') sal
from emp;

select sysdate,
    to_char(sysdate,'yyyy'),  -- to_char 는 문자형태로 보여지므로 왼쪽정렬
    to_char(sysdate,'mm'),
    to_char(sysdate,'dd'),
    extract(year from sysdate),  -- extract는 숫자형태로 보여지므로 오른쪽정렬
    extract(month from sysdate), -- 시, 분, 초는 표시되지 않는다.
    extract(day from sysdate)
from dual;

---- 날짜연산
select sysdate, sysdate + 1, sysdate + 1/24, sysdate + 1/(24*60*60)
from dual;

select '2007-02-14' - '2007-02-12' -- 이러한 형식은 문자로 인식하기 때문에 실행이 안된다.
from dual;
select to_date('2007-02-14','yyyy-mm-dd') -  -- to_date('입력값','x') :  해당값은 X라는 형식의 날짜라고 표시한다.
    to_date('2007-02-12','yyyy-mm-dd')
from dual;
---- months_between 함수 :
select months_between('2007-2-12','2007-01-01') -- 문자 형식이라도 잠정적으로 날짜로 인식한다.
    -- months_between(a,b): a와b의 차이를 달(month)수로 나타낸다.
from dual;

---- add_months 함수
select add_months(sysdate, 1) -- add_months(a,b) : a날짜로부터 b달만큼 더한다.
from dual;
----------- 예제문제 : 지금부터 1달 2일 3시간 4분 5초후는?
select sysdate, add_months(sysdate,1) + 2 + 3/24 + 5/(24*60*60)
from dual;  -- oracle 8i 까지 써왔던 함수.
----------- 예제문제 : 지금부터 1년 1달 2일 3시간 4분 5초후는?
select sysdate, sysdate + to_yminterval('01-01') + to_dsinterval('002 03:04:05')
from dual; -- oracle 9i 부터 나온 시간계산함수

---- next_day 함수 : next_day(날짜,'요일') - 해당날짜에서 가장빨리 돌아오는 요일을 나타낸다.
select next_day(sysdate,'금'),   -- 리눅스,유닉스 기반에서는 요일이 한글이 아닌 영어라 안될수 있다.
       next_day(sysdate,'월'),
       next_day(sysdate,'토요일'),
       next_day(sysdate,'화요일')
from dual;

select to_char(sysdate,'day'), to_char(sysdate,'dy') -- 해당함수는 날짜에대한 요일을 알려준다.
from dual;  -- 유닉스나 리눅스는 한글이 아닌 영어로 나타난다.

select to_char(sysdate, 'd')
from dual;
------ 1, 2, 3, 4, 5, 6, 7
------ 월 화 수 목 금 토 일 로 매핑이 된다.

---- 미리배워보는 (case, when, then, end) 문
select case 3-1                       -- oracle 8i부터 생성된 함수
    when 1 then '3-1=1 입니다.'
    when 2 then '3-2=1 입니다.'
    else '3-1=몰라요'
    end
from dual;

select case
    when 3 > 4 then '3 > 4 입니다.'
    when 3 > 2 then '3 > 2 입니다.'
    else '몰라요'
    end
from dual;

select decode(3-1, 1, '3-1=1 ok', 2, '3-1=2 ok', '3-1= I don`t know')
from dual;   -- 오라클 8i 이전에 사용하던 함수
----------------------------------------------------------------------------------------
---- case~ 함수
select case to_char(sysdate, 'd')
    when '1' then '일요일'
    when '2' then '월요일'
    when '3' then '화요일'
    when '4' then '수요일'
    when '5' then '목요일'
    when '6' then '금요일'
    when '7' then '토요일'
    end "오늘의 요일명"
from dual;

select decode(to_char(sysdate, 'd')
           ,'1','일요일'
           ,'2','월요일'
           ,'3','화요일'
           ,'4','수요일'
           ,'5','목요일'
           ,'6','금요일'
           ,'7','토요일') "오늘의 요일명"
from dual;

---- last_day 함수 : 해당 날짜에 맨 마지막 날을 표시한다.
select last_day(sysdate)
from dual;

---- log 함수 : 상용로그 값을 구한다.
select log(10,100)
from dual;

---- sign 함수 : 결과물이 음수이면(-)로 양수면(+)로 표시한다.
select sign(2-5), sign(2-2), sign(5-2)
from dual;
------ 참고
select sin(90), cos(90), tan(90), asin(180), acos(180), atan(180)
from dual;

----chr 함수
select ascii('A'), chr(65) -- chr(숫자) : 해당숫자를 아스키값으로 인식하여 그값에 대한 문자를 출력
from dual;