'프로그래밍/ORACLE'에 해당되는 글 9건

제목 날짜
  • 9일차! 2015.05.20
  • 8일차! 2015.05.18
  • 7일차! 2015.05.15
  • 6일차! 2015.05.12
  • 5일차! 2015.05.11
  • 4일차! 2015.05.08
  • 3일차! 2015.05.07
  • 2일차! 2015.05.06
  • 1일차! 2015.05.04

9일차!

 계층형 쿼리.

 잘못만들면 무한 루프를 돌수도 있음. 주의.
 

계층 구조를 가진 데이터
     ㄴ 조직도, 카테고리

 조직도가 보통의 모습.

오라클의 계층형 쿼리.
     Start with     - 계층형 데이터를 조회할 때 시작 행(위치)를 지정함.

     Connect by     - 부모행과 자식행의 관계를 지정함.
                    - prior 연산자를 사용해서 연결방향 지정 가능.
                    connect by prior 부모키 = 자식키 <-- top-down  방식, 위 아래.
                    connect by prior 자식키 = 부모키 <-- bottom-up 방식, 아래 위.                   

                    connect by prior 자식컬럼 = 부모컬럼
                    CONNECT BY PRIOR employee_id = manager_id 

                                       ^
                                요자리가 먼저나옴.

     level          - 계층구조의 depth를 표현.
          
          connect by isleaf
                    - 자식행이 없으면 1을 반환
          order siblings by
                    - 계층형 데이터를 트리구조로 유지하면서, 정렬할때 사용한다.


 

SELECT DCODE as ah, DNAME, PDEPT as bumo, LEVEL

FROM DEPT2

  START WITH DCODE = '0001'

    CONNECT BY PRIOR DCODE = PDEPt and LEVEL <= 2;


    




 

SELECT lpad('  ', level *3, '-') || DCODE as ah, DNAME, PDEPT as bumo, LEVEL

FROM DEPT2

  START WITH DCODE = '0001'

    CONNECT BY PRIOR DCODE = PDEPT;




이걸 응용하면...




-- 원래는 이럼.

sELECT lpad(' ', (LEVEL-1)*3, ' ') || LAST_NAME,

       employee_id,

       MANAGER_ID,

       LEVEL

FROM EMPLOYEES

START WITH employee_id = 101

      CONNECT BY PRIOR employee_id = manager_id;










-- 얘 하나만 빼기.

SELECT lpad(' ', (LEVEL-1)*3, ' ') || LAST_NAME,

       employee_id,

       MANAGER_ID,

       LEVEL

FROM EMPLOYEES

WHERE LAST_NAME != 'Greenberg'

START WITH employee_id = 101

      CONNECT BY PRIOR employee_id = manager_id;










-- 가지치기.


SELECT lpad(' ', (LEVEL-1)*3, ' ') || LAST_NAME,

       employee_id,

       MANAGER_ID,

       LEVEL

FROM EMPLOYEES

START WITH employee_id = 101

      CONNECT BY PRIOR employee_id = manager_id and LAST_NAME != 'Greenberg';





1이면 아래가 없음 잎사귀
0이면 아래가 있음 나뭇가지
가지와 나무.




저작자표시

트랙백

※ 스팸 트랙백 차단중 ...{ ? }

8일차!

무결성 제약조건    - 테이블에 유효하지않는 데이터가 입력되는것을 방지.

 

 

종류

  not null     - 열에 null이 포함되지 않도록 지정.

 

  unique       - 테이블 전체에서 고유한 값을 가지도록 지정.

 

  primary key  - 테이블에서 각 행을 고유하게 식별하도록 지정. 즉 각 행을 대표할수 있게 함.

 

  foreign key  - 열에 값이 입력될때 참조 테이블(다른 테이블)의 지정된 열을 참조.

               - 참조되는 칼럼은 반드시 프라임키나 유닉 제안조건이 지정된 컬럼이어야함.

 

  check        - 열에 입력가능한 값을 미리 지정함.

 

제약조건 정의

 

     컬럼 레벨에서 제약조건 정의하기.

       - 각각의 컬럼이 가져야 될 제약조건을 정의할 수 있다.

  CREATE TABLE 테이블명 (

     컬럼명 데이터타입 CONSTRAINT 제약조건이름 제약조건 타입

);

 

 

     테이블 레벨에서 제약조건 정의하기.

       - 하나 이상의 컬럼을 조합해서 제약조건을 정의할 수 있다.

  CREATE TABLE 테이블명 (

     컬럼명 테이터타입

     컬럼명 테이터타입

     ...

     CONSTRAINT 제약조건이름 제약조건타입 (컬럼명, ...),

)

     ㄴ 컬럼들을 미리 지정해놓고. 하나 이상일때니까 1개도 됨.

 

 

아래껀 두개이상의 제약조건을 만들때.

NOT NULL, PRIMARY 제약조건이면 위에거밖에 안됨.

 아래거는두개이상을 조합해서 하나의 제약조건으로 걸어버림.

그 위에껀 1개에 1개.

 

 

제약조건을 걸때 학생 테이블이 있어, 거기 학번, 이름이있겠지.

근데, 100번도 101도 102도 3번학생 홍길동 김유신 강감찬 이순신이 있다하면,

 

과목 테이블이 있는거야 과목테이블엔

과목코드, 과목 번호 같은게 있겠지.

 S_001 002 003

 전자기학, 재료역학, 전자실험1 이런게 있다고 하자고, 이런 과목들이 있어

 

수강 테이블이 있는거지.

수강에는 어떤 학생이 어떤 과목을 듣냐는거

학번, 과목번호가 있는 테이블. 그러면, 여기엔 어떤 값이 가야하지?

이런게 들어가면 안되니까. 이런경우는 절대 안되는게

100번학생이 S001 을 수강했어 근데, 100번이 또 S001 을 수강하면 안되니까.

101학생이 S001을 들어도 되지만, 한 학생이 두개를 중복해서 들으면 안되니까.

 

그런 경우에는 아래걸 쓴다는거지. 그런 방법일때 아래껄 쓴다는거지.

두개이상의 제약을 조합.

 

학번이랑 과목번호 둘다 고유 해야하니까. 두개가 합침(조합)을 하려면 아래껄 쓴다.

 

 

 

나중에 프로젝트를 하면, 테이블 명세서를 받게되는데 테이블의 데이터 타입 각 컬럼의 간단한 설명

제약조건이 어떤게 걸렸는지 그게 엑셀로 되어있는 명세서를 받게됨.

 

 

NOT NULL은 체크 제약조건임.

포린키, 유니크, 체크 제약조건은 많아도 되지만

프라이머리키 제약조건은 무조건 1개.

제약조건 추가/삭제

 

     - 추가

          alter table 테이블명

          add contraint 제약조건명 제약조건타입 (컬럼명)

 

     - 삭제

          alter table 테이블명

          drop contraint 제약조건명;

 

 

제약조건 정의하기

 -- 회원 테이블 (회원번호, 이름, 연락처, 이멜, 생일, 가입일, 프로필사진, ...)

 

 -- 회원번호       -> 그 회원을 대표하는 컬럼

 -- 이름           -> 반드시 입력되야함

 -- 연락처         -> 중복되는 값 x

 -- 이메일         -> 중복되는 값 x, 반드시 입력되야함

 -- 포인트         -> 0보다 큰 값이어야함.

 

  CREATE TABLE SAMPLE_MEMBER (

     no      number          constraint mem_no_pk      primary key,

     namk    varchar2(50)    constraint mem_name_nn    not null,

     gender  char(1)         constraint mem_gender_ck  check (gender in ('F', 'M') ),

     tel     varchar2(20)    constraint mem_tel_uk     unique,

     email   varchar2(100)   constraint mem_email_nn   not null,

     point   number          constraint mem_point_ck   check(point >0),

     regdate date            defalut    sysdate,

 

     constraint mem_email_uk unique (email)

);

 

 

-- 차일드 테이블에서 참조하고 있는 값을 삭제 할수 없다.

-- 차일드 테이블에서 참조하고 있는 테이블은 삭제 불가.

-- 차일드 테이블을 먼저 삭제후 부모 테이블을 삭제 가능.

 

 

 -- 회원들이 작성하는 게시판 테이블 (글번호, 제목, 작성자, 내용, 조회수, 작성일, ...)

  CREATE TABLE SAMPLE_BOARD (

 

)

 

 

-- 정의된 제약조건 정보가 있는 Dictionary -> USER_CONSTRAINTS

SELECT *

FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'EMPLOYEES';

 

-- 정의된 시퀀스 정보가 있는 딕셔너리.

SELECT *

FROM USER_SEQUENCES;

 

-- 정의된 테이블 정보가 있는 딕셔너리

SELECT *

FROM USER_TABLES;

 

-- 정의된 인덱스 정보가 있는 딕.

SELECT *

FROM USER_INDEXES;

 

-- 정의된 뷰 정보가 있는 딕.

SELECT *

FROM SYS.USER_VIEWS;

 

 

제약조건 추가/삭제

     - 추가

          alter table 테이블명

          add contraint 제약조건명 제약조건타입 (컬럼명)

 

     - 삭제

          alter table 테이블명

          drop contraint 제약조건명;

 

뷰 (View)

     - 뷰는 가상의 테이블임

     - 하나 or 두개 이상의 테이블을 사용, 가상의 테이블(뷰)를 만들 수 있음.

 

뷰 쓰는 이유?

     - 데이터에 대한 접근을 제한하기 위해.

     - 복잡한 쿼리를 쉽게 작성하려고.

     - 동일한 데이터로 다양한 결과를 얻기 위해.

뷰의 생성

     Create or replace 뷰

          as SELECT구문

          with read only;

뷰 삭제

     Drop view 뷰이름

 

인라인 뷰

     - SQL문에서 사용되는 서브쿼리

     - SELECT문의 FROM절에 있는 서브쿼리를 인라인뷰 라고 부름.

     -

 

 

껐다 켜도, 뷰는 살아있음.

 

 

-- 부서번호가 80번인 사원의 정보를 가진 뷰

 

CREATE VIEW EMP_80

  as SELECT employee_id, last_name, salary

  FROM EMPLOYEES

  where DEPARTMENT_ID = 80;

 

SELECT *

FROM EMP_80

WHERE SALARY > 10000;

 

 

-- 서재수의 사은품?

SELECT A.GNO, A.GNAME, POINT, B.GNAME as gift_name

FROM GOGAK A, gift B

where A.POINT >= B.G_START and A.POINT <= B.G_END

and A.GNAME = '서재수';

 

-- 고객 정보, 포인트에 해당하는 사은품 정보 주는 뷰.

CREATE or REPLACE VIEW gogak_gift -- 새로 만들거나, 바꾸거나

  as select a.gno, A.gname as name,

    point, b.gname as gift_name, SUBSTR(A.JUMIN, 1, 6) || '*******' as jumin

    FROM GOGAK A, GIFT B

    WHERE a.point >= B.G_START and a.point <= B.G_END

    with read only; -- 읽기전용.

  

    -- 서재수의 사은품?

SELECT GNO, NAME, POINT, GIFT_NAME

FROM GOGAK_GIFT

where NAME = '서재수';

 

 

SELECT DEPARTMENT_ID as id,  round(avg(SALARY)) as avg_sal

FROM EMPLOYEES

group by  DEPARTMENT_ID

ORDER BY AVG(SALARY) DESC;

 

 

SELECT DEPARTMENT_ID, DEPARTMENT_NAME

FROM DEPARTMENTS;

 

 

SELECT a.id, d.DEPARTMENT_NAME, a.avg_sal

FROM DEPARTMENTS D, (SELECT DEPARTMENT_ID as id,

                      round(avg(SALARY)) as avg_sal

                      FROM EMPLOYEES

                      group by  DEPARTMENT_ID)  a

WHERE a.id = D.DEPARTMENT_ID

ORDER BY a.avg_sal DESC;

 

-- 사번, 이름, 급여, 급여등급을 표시하는 SQL

 

--SELECT E.EMPLOYEE_ID, E.last_name, E.SALARY, G.Grade

--FROM EMPLOYEES E, (SELECT GRADE_LEVEL as Grade

--                   FROM JOB_GRADES) as G

 

SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.SALARY, G.GRADE_LEVEL as Grade

FROM EMPLOYEES E, JOB_GRADES G

where E.SALARY >= G.LOWEST_SAL

and E.SALARY <= G.HIGHEST_SAL;

 

-- 사원 테이블의 모든 사원들의 사원번호, 이름,

-- 부서번호, 부서이름, 급여 급여등급 정보를 포함한 뷰 만들기.

 

create or REPLACE VIEW EMP_SALARY_DETAIL as

SELECT E.DEPARTMENT_ID, e.last_name,

       D.DEPARTMENT_NAME, E.SALARY, G.GRADE_LEVEL

FROM EMPLOYEES E, JOB_GRADES G, DEPARTMENTS D

where E.SALARY >= G.LOWEST_SAL

and E.SALARY <= G.HIGHEST_SAL

and E.DEPARTMENT_ID = D.DEPARTMENT_ID

WITH READ ONLY;

 

SELECT *

FROM EMP_SALARY_DETAIL

where last_name = 'King';

 

-- 인라인뷰에서 순위 계산.

-- 급여를 가장 많이 받는 사원3.

SELECT ranking, last_name, salary

FROM (SELECT ROW_NUMBER() OVER (ORDER BY salary desc) as ranking,

             last_name,

             salary

        FROM EMPLOYEES)

where ranking <=3;

 

 

-- 1997년 이후, Clerk.

SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE, JOB_Id

FROM EMPLOYEES

WHERE JOB_ID = 'ST_CLERK'

and HIRE_DATE > '1997-01-01'

ORDER BY HIRE_DATE;

 

--...????

 

--2번 커미션 사람 표시.

 

SELECT LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT

FROM EMPLOYEES

WHERE COMMISSION_PCT is not null

ORDER BY SALARY DESC;

 

-- 3번.

SELECT LAST_NAME, SALARY as before, salary*1.1 as after

FROM EMPLOYEES

WHERE COMMISSION_PCT is null

ORDER BY SALARY;

 

-- 4번 일한 년 월.

SELECT LAST_NAME, round(months_between (SYSDATE, hire_date)) as month,

      round(months_between (SYSDATE, hire_date)/12) as years

FROM EMPLOYEES;

 

-- 5번 J K L M 시작 이름 사원

SELECT LAST_NAME

FROM EMPLOYEES

WHERE substr(LAST_NAME, 1, 1) in ('J', 'K', 'L', 'M');

 

-- 6번

SELECT LAST_NAME, SALARY, COMMISSION_PCT as com,

  CASE

    WHEN commission_pct is null then 'no'

    when commission_pct is not null THEN 'yes'

   end as yes_or_no

FROM employees;

-- 2005년 이후

SELECT *

FROM EMPLOYEES

WHERE HIRE_DATE > TO_DATE('2006/01/01', 'yyyy-mm-dd')

and JOB_ID like '%CLERK';

 

-- 2번.

SELECT LAST_NAME, JOB_ID, TO_CHAR(SALARY, '$999,999') as sal,

                                                    to_char(COMMISSION_PCT) as comm

FROM EMPLOYEES

WHERE COMMISSION_PCT is not null

ORDER BY SALARY DESC;

 

-- 3번.

SELECT LAST_NAME || '의 급여가 10% 인상되어 ' || SALARY *1.1 ||

                                              ' 달러가 되었심심'as salsal

FROM EMPLOYEES

WHERE COMMISSION_PCT is null;

 

-- 4번.

SELECT LAST_NAME, trunc(months_between (SYSDATE, hire_date)) as month,

       trunc(months_between (SYSDATE, hire_date)/12) as years

FROM EMPLOYEES

ORDER BY HIRE_DATE;

 

-- 4번 정확.

SELECT LAST_NAME, trunc(months_between (SYSDATE, hire_date)/12) as years,

                  trunc(mod(months_between (SYSDATE, hire_date), 12)) as dal

FROM EMPLOYEES;

 

 

with MONTHS_TABLE as (

SELECT LAST_NAME, MONTHS_BETWEEN(SYSDATE, hire_date) months

FROM EMPLOYEES

)

SELECT last_name, trunc(months/12) nyeon, trunc(mod(months, 12)) dal

FROM MONTHS_TABLE; -- 임시의 테이블.

 

 

-- 5번.

SELECT LAST_NAME

FROM EMPLOYEES

WHERE substr(LAST_NAME, 1, 1) in ('J', 'K', 'L', 'M');

 

-- 6번

SELECT LAST_NAME, SALARY,

  CASE

    WHEN commission_pct is null then 'no'

    when commission_pct is not null THEN 'yes'

   end as yes_or_no

FROM employees;

 

 

SELECT LAST_NAME, SALARY,

  CASE

    WHEN commission_pct is not null then 'yes'

    ELSE 'no'

  end comm

from EMPLOYEES;

 

SELECT LAST_NAME, SALARY,

       decode(commission_pct, null, 'no', 'yes') comm

FROM EMPLOYEES;

 

SELECT LAST_NAME, SALARY,

       nvl2 (to_char(commission_pct), 'yes', 'no') comm

FROM EMPLOYEES;

 

-- 7번 1800위치 부서정보 찾고, 부서아디와, 사원테이블 조인.

SELECT D.DEPARTMENT_ID, D.LOCATION_ID, E.LAST_NAME, E.JOB_ID, E.SALARY

FROM DEPARTMENTS D, EMPLOYEES E

WHERE D.LOCATION_ID = 1800

and D.DEPARTMENT_ID = E.DEPARTMENT_ID;

 

-- 8번. 이름이 n으로 끝나는 놈. 몇?

SELECT COUNT(*)

FROM EMPLOYEES

WHERE LAST_NAME like '%n';

 

SELECT COUNT(*)

FROM EMPLOYEES

where substr(LAST_NAME, length(last_name), 1) = 'n';

 

-- 9

SELECT DEPARTMENT_ID, COUNT(*)

FROM EMPLOYEES E

GROUP BY DEPARTMENT_ID;

 

SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID, nvl(e.cnt, 0)

FROM (SELECT DEPARTMENT_ID, COUNT(*) as cnt

      FROM EMPLOYEES

      GROUP BY DEPARTMENT_ID ) E, DEPARTMENTS D

WHERE e.DEPARTMENT_ID(+) = D.DEPARTMENT_ID;

 

-- 10, 부서번 10,20, 50의 업무 표시.

SELECT DISTINCT JOB_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID in (10, 20, 50);

 

-- 11

SELECT E.JOB_ID, COUNT(*) as cnt

FROM DEPARTMENTS D, EMPLOYEES E

WHERe D.DEPARTMENT_ID = E.DEPARTMENT_ID

AND D.DEPARTMENT_NAME in ('Administration', 'Executive')

GROUP BY E.JOB_ID

ORDER BY cnt desc;

 

-- 12, 입사일이 16일 이전의 사원명 입사일 표시.

SELECT LAST_NAME, to_char(HIRE_DATE, 'yyyy-mm-dd') as ippsa

FROM EMPLOYEES

WHERE to_char(HIRE_DATE, 'dd') < '16';

 

-- 13.

 

-- 14.

SELECT sa.LAST_NAME as sawon, ma.LAST_NAME as "s'manager",

                            ma.SALARY, G.GRADE_LEVEL

FROM EMPLOYEES sa, EMPLOYEES ma, JOB_GRADES G

WHERE sa.MANAGER_ID = ma.EMPLOYEE_ID

and ma.SALARY > 15000

 

AND ma.SALARY >= G.LOWEST_SAL

and ma.SALARY <= G.HIGHEST_SAL;

 

-- 16. 평높은 부서의 최저급여

 

SELECT DEPARTMENT_ID, min(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING avg(SALARY) = (SELECT max(avg(SALARY))

                      FROM EMPLOYEES

                      GROUP BY DEPARTMENT_ID);

 

-- 17 SA_REP 없는 부서번, 이름, 위치.

SELECT *

FROM EMPLOYEES E, DEPARTMENTS D

WHERE JOB_ID <> 'SA_REP';

 

 

 

-- 18 모든 사원번호, 이름, 급여, 부서번, 부서별 평균 급여

 

-- 3인 미만.

SELECT D.DEPARTMENT_ID as Dn, D.DEPARTMENT_NAME as DNAME, COUNT(*)

FROM DEPARTMENTS D, EMPLOYEES E

WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID

GROUP by D.DEPARTMENT_ID, D.DEPARTMENT_NAME

HAVING count(*) < 3;

 

SELECT B.DEPARTMENT_ID, B.DEPARTMENT_NAME

FROM (SELECT d.DEPARTMENT_ID, COUNT(*) cnt

      FROM DEPARTMENTS D, EMPLOYEES E

      WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID

      GROUP by D.DEPARTMENT_ID

      HAVING count(*) <3) A, DEPARTMENTS B

wHERE a.DEPARTMENT_ID = B.DEPARTMENT_ID;

 

-- 사원 가장많은.

 

 

-- 사원 가장 적은 부서

SELECT a.DEPARTMENT_ID, B.DEPARTMENT_NAME, cnt

FROM (SELECT DEPARTMENT_ID, COUNT(*) cnt

      FROM EMPLOYEES

      GROUP BY DEPARTMENT_ID

      having COUNT(*) = (SELECT min(count(*))

                         FROM EMPLOYEES

                         GROUP BY DEPARTMENT_ID)) A, DEPARTMENTS B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;

 

--19 패스

 

 

 

 

--20

SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'DAY')

FROM EMPLOYEES;

--WHERE to_char(HIRE_DATE, 'DAY') in (SELECT to_char(HIRE_DATE, 'DAY') day;

                                  

                                   

                                  

                                  

SELECT *

FROM EMPLOYEES

WHERE to_char(HIRE_DATE, 'DAY')

              in (SELECT to_char(HIRE_DATE, 'DAY'), count(*)

                    FROM employees

                    GROUP