Duritz's Fun Life

select *
from BALJEON;
--|| 발전일별로 누적량을 보여라 ||--
select to_char(balday,'yyyy-mm-dd') "발전일", balyang "발전량",
    sum(balyang) over(order by balday) "누적량"
from BALJEON;
--|| 서브상관쿼리로 발전일별 누적량 출력 ||--
select to_char(balday,'yyyy-mm-dd') "발전일",   -- 그룹지어진 컬럼 갯수만큼 출력을 해야한다.
    balyang "발전량",                        -- 컬럼수가 초과되면 오류가 난다.
    (select sum(balyang) from BALJEON T2 where T2.BALDAY <= T1.balday) "누적량"
from BALJEON T1
order by 1;
----// 위쿼리문에서 바깥쪽에 있는부분을 외부쿼리, 안쪽에 있는부분을 내부쿼리라 하고
--     외부쿼리문의 컬럼명이 내부쿼리의 조건절에 사용되어질때 이것을 상관서브쿼리(서브상관쿼리)라 한다.
------------------------------------------------------------------------------------------------------
select *
from OLDSALESINFO;

select *
from SALESINFO;
--|| 제품별로 판매량이 최고이였던 날짜와 총판매량을 나타내시오. ||--
select jpname "제품명", to_char(saleday,'yyyy-mm-dd') "판매일자", sum(su) "판매량"
from
(
select *
from OLDSALESINFO
union
select *
from SALESINFO
) T1
group by jpname, to_char(saleday,'yyyy-mm-dd')
having sum(su) = (
             select max(sum(su))  -- max만 하게되면 제품전체에 대한 max값만 나오기때문에
      from                 -- 하단에 where로 조건을 넣어서 제품별 max값이 나오게 해야한다.
      (
        select *
           from OLDSALESINFO
        union
        select *
        from SALESINFO
      ) T2
      where T2.jpname = T1.jpname -- 제품별로 최대치가 나오게 하기위한 조건부
      -- 외부쿼리에 컬럼이 내부쿼리 조건에 포함
      group by jpname, to_char(saleday,'yyyy-mm-dd')
     );
--// 외부쿼리 컬럼이 내부쿼리 조건절에 포함된 경우도 상관서브쿼리라 할 수 있다.
--------------------------------------------------------------------------------------------
---||| 연습문제 |||---
--문제 13. Emp테이블에서 부서별로 월급이 평균 월급보다 높은사원을 부서번호, 이름, 급여 출력.
select deptno, empno, ename, sal
from EMP E1
where sal > (
        select avg(sal)
    from EMP E2
        where E2.deptno = E1.deptno
       )
order by deptno
--문제 14. Emp테이블에서 업무별로 월급이 평균 월급보다 높은사원을 부서번호, 이름, 급여 출력.
select job, empno, ename, sal
from EMP E1
where sal < (
        select avg(sal)
    from EMP E2
        where E2.job = E1.job
       )
order by job
--★★ 교제 165PAGE EXISTS ★★-- 중요
--// exists : 조건에 만족하는 행이 존재하면 참이고, 존재하지 않으면 거짓
select *
from EMP
where exists(select * from dept where deptno = 10); -- 참인 예제

select *
from EMP
where exists(select * from dept where deptno = 50); -- 거짓 예제

--// not exists : 조건에 만족하는 행이 존재하면 거짓, 존재하지 않으면 참
select *
from EMP
where not exists(select * from dept where deptno = 10); -- 거짓 예제

select *
from EMP
where not exists(select * from dept where deptno = 50); -- 참 예제
---||| 주소지가 DALLAS 와 CHICAGO에 근무하는 사원의 부서번호, 사원번호, 사원명, 급여를 출력.
------// 서브쿼리에 in이 나오면 쿼리 처리 속도를 위해 exists를 써주는게 좋다.
select deptno, empno, ename, sal
from emp
where deptno in (select deptno
        from DEPT
        where loc in('DALLAS','CHICAGO') );
       
select deptno, empno, ename, sal
from emp E
where exists(select deptno
      from DEPT D
      where loc in('DALLAS','CHICAGO')
      and D.deptno = E.deptno);
--|=| 참고사항 : 서브쿼리보다는 join이 처리속도가 더 빠르다.
select D.dname E.empno, E.ename, E.sal
from emp E, dept D
where E.deptno = D.deptno and D.loc in ('DALLAS','CHICAGO');

select D.dname, E.empno, E.ename, E.sal
from
(select empno, ename, sal, deptno from EMP) E,
(select dname, deptno from DEPT where loc in ('DALLAS','CHICAGO')) D
where E.deptno = D.deptno;
-- 위 두개의 쿼리문은 출력값은 같으나 위쪽쿼리는 emp,dept테이블의 column, row의 모든값을
-- 표시하기때문에 아래쿼리문처럼 미리 필요한 column, row만(필요없는 부분 출력안함) 뽑아서
-- 쿼리문을 작성하면 처리 속도가 빨라진다. (대용량 DB에 매우 유용하다.)

--// 서브쿼리는 where절에 또하나의 select 문이 들어가는것을 말한다.
---|| 30번 부서중에서 sal가 가장 최고인 사원의 모든정보를 추출하시오
select *
from emp
order by sal desc;

update emp set sal=2850  -- 원래는 2975
where empno = 7566;

select *
from emp
where sal = (select max(sal) from emp -- 쿼리문에서 실행된값이 다른 부서에도 있으면 출력이 된다.
        where deptno = 30);      -- 이유는 전체문장에서 30번 부서만뽑는 조건이 없기때문이다.
       
select *
from emp
where deptno = 30 and
   sal = (select max(sal) from emp
          where deptno = 30);
-----------------------------------------------------------------------------------------------
---|| adams 가 근무하는 부서에서 그 부서의 평균급여보다 많은 사원의 모든정보를 추출
select *
from emp
where deptno = (select deptno from emp where ename='ADAMS') and
   sal > (select avg(sal) from emp
         where deptno = (select deptno from emp where ename='ADAMS'));
---|| JONES와 CLARK이 근무하는 부서와 동일한 부서에 근무하는 사원들의 모든정보를 추출.
select *
from emp
where deptno = any (select deptno from emp where ename in ('JONES','CLARK'));
---|| 각 부서별로 급여가 최저인 사원들의 부서번호, 사원명, 급여를 추출하여라.
----/// 아래 문장은 부서번호가 정의되지 않았기 때문에 잘못된 표현이다.
select deptno, ename, sal, empno
from emp
where sal in (select min(sal) from emp group by deptno)
order by deptno;

update emp set sal=1300  -- 원래는 2975
where empno = 7566;
update emp set sal=1300  -- 원래는 1250
where empno = 7654;
---/// 두개의 조건을 동시에 비교해서 해야 정확한 답이 나온다.
---/// 이런경우를 다중열 서브쿼리라 하여 Pairwise되었다고 한다.
select deptno, ename, sal
from emp
where (deptno, sal) in (select deptno, min(sal) from emp group by deptno)
order by deptno;
--|| EMP 테이블에서 관리자로 등록되어진 사원의 정보를 추출하시오.
select *
from emp
where empno in (select mgr from emp);

select *
from emp
where empno = any (select mgr from emp);
--|| EMP 테이블에서 관리자로 등록되어지지 않은 사원의 정보를 추출하시오.
select *
from emp
where empno not in (select mgr from emp); -- 이렇게하면 서브쿼리에 null값때문에 값이 나오질 않는다.
--/// 다음과 같이 풀이가 된다.
select *
from emp
where empno ^= 7566 and empno ^= 7698 and empno ^= 7782 and
   empno ^= 7788 and empno ^= 7839 and empno ^= 7902 and empno = null
   -- null은 존재하지 않는 값이기때문에 부정을 해도 null이 된다. 그래서 null부분을 빼야한다.
select *
from emp
where empno not in (select mgr from emp
          where mgr is not null); -- 다음과 같이 null부분을 없애줘야 정확한 값이 나온다.
select *
from emp
where empno ^= all (select mgr from emp     -- not in 은 ^= all 과 같다.
          where mgr is not null);

--/// 누적 예제 ///--
create table baljeon
(balday date, balyang number) tablespace users;

insert into baljeon values('2007-02-01',30);
insert into baljeon values('2007-02-02',25);
insert into baljeon values('2007-02-03',20);
insert into baljeon values('2007-02-04',15);
insert into baljeon values('2007-02-05',10);

--/// 날짜별 순차적인 누적량을 구하기 위해선 더해야할 행수는 해당 테이블의 총행수(count값)을 넣어주면된다.
--/// 또는 rows 부터 그이하를 생략하면 된다.
select count(*) from baljeon;

select balday "발전일자", balyang "발전량",
    sum(balyang) over(order by balday) "누적량"
from baljeon;
-----------------------------------------------------------------------------------------
insert into baljeon values('2007-03-01',10);
insert into baljeon values('2007-03-02',20);
insert into baljeon values('2007-03-03',30);
insert into baljeon values('2007-03-04',40);
insert into baljeon values('2007-03-05',50);
-----------------------------------------------------------------------------------------
select * from baljeon;

select balday "발전일자", balyang "발전량",
    sum(balyang) over(partition by to_char(balday,'yyyy-mm') order by balday) "누적량"
    -- partition by to_char(balday,'yyyy-mm') 원하는 날짜별로 누계를 구한다.
from baljeon;

---- cross join
select *
from emp, dept;

select *
from emp cross join dept; -- SQL 1999 CODE

---- natural join (inner join과 같고, Oracle에만 존재한다.)
---- natural join 테이블에 allias(별칭) 를 주면 안된다. (A,B 두테이블에 공통 컬럼이 한개만 존재할때 가능하다.)
select deptno, dname, ename, job
from emp natural join dept;

---- using 절 : 테이블에 Allias를 쓰면 안된다. (Oracle에만 존대한다.)
select deptno, dname, ename, job
from emp join dept
using (deptno);  -- 공통 컬럼을 지정해준다. // using(공통컬럼) //

----   [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;