IT/데이터베이스 / / 2015. 2. 18.

오라클 수업 요약1

포스팅 목차

    ▒ DATABASE

    DBMS - 계층형, 네트워크형, 관계형(RDBMS)

    RDBMS(관계형 DBMS) - oracle, mysql, mssql, DB2 (가장 널리 상용화된 모델)

             ① 데이터 중복 최소화

             ② 데이터 일관성

             ③ 보안

             ④ 공유

     

          - 논리적인 구조, 물리적인 구조 설계가 어렵다.

          - 관리가 어려움 (복구, 백업) 

          - 한쪽 부분에 문제가 생기면 전체에 문제가 발생된다.

          ==> 이런 부분은 DBA의 업무

     

    ▒ DATABASE 연결 

    [user 들]  -----------------------> [Web] ----------------------> [DBserver]

                                   WAS서버(web application server)

     

    [user 들]  ----------------------->[application]------------------> [DBserver]

                                          Middle ware 서버 (통신회사, 은행등에서 많이 씀)

     

    cf) 일반유저가 DBserver가 알아들을수 있는 명령어를 사용하기 위해 필요한 프로그램이 필요한데, 이것을 클라이언트 프로그램이라고 함. (sqlplus, isqlplus)

     

    ▒ SQL 함수

    ▶ DDL : DB 논리적 구조, 물리적 구조 명시

    ▶ DML : DB 검색, 수정, 삭제

    ▶ DCL : DB 제어

     

     

    ▒ ORACLE 접속및 끊기

    # su - oracle

    sqlplus /nolog    (로그인하지 않고 sqlplus를 실행시킴)

    sql> conn /as sysdba

    sql> startup (오라클 가동 확인)     cf) shutdown immediate (오라클 내리기)

    sql> create user username

           identified by passwd; (숫자+문자)

    sql> grant dba to username;   (사용자에게 권한 부여)

    sql> host

    $ cd /oracle/product/10g/bin

    $ ./lsnrctl start

    $ ./isqlplusctl start 

     

    http://본인아이피:5560/isqlplus

    -----------------------------------------------------------------------------

    ★ 사용자로 접속

    SQL> conn

    Enter user-name : spy 

    Enter password : *****

    Connected.

    SQL> show user

    USER is "spy"

     

    SQL> select * from employees

          r : 다시 방금 전 sql문 실행

          ed : 실행 했던 sql문 보기

    -----------------------------------------------------------------------------

    ★ sqlplus에서 테이블 사이즈 조절

    set linesize 200

         pagesize 200

    테이블의 구조를 보고 맞춰 줌.

     

    SQL> col 컬럼명 format 문자일 경우 (a3)

                                      숫자 일경우 (999 이용)

    ------------------------------------------------------------------------------

    ♣ select 문 (DB검색)

    - 원본에는 영향을 주지 않는다.

    - 컬럼구분은 ,로 구분함.

    - 형식 : select 컬럼명 from 테이블명

     

    <Example>

    - select * from employees    

         (*는 모든것을 검색)

    - select LAST_NAME,SALARY from employees 

         (LAST_NAME,SALARY 컬럼 검색)   

    - select LAST_NAME as a,SALARY as b from employees 

         (LAST_NAME,SALARY 컬럼이름을 a,b로 바꿔서 검색, as는 생략가능) 

    -  select LAST_NAME ,SALARY+300 from employees 

        (산술연산을 이용한 검색, SALARY에 300씩 더해서 보여줌)

    - select last_name, department_id from employees where department_id=80

        (where를 통해 조건을 줄수 있음.)

    - select last_name,salary,job_id from employees where job_id='SA_REP' order by salary desc

    - select distinct last_name,salary,job_id,department_id from employees where department_id=80

       (중복 행은 제거)

    - select last_name,salary,job_id,department_id from employees where salary between 5000 and 10000

       (salary가 5000~10000 검색)

    -  select  last_name,salary,job_id,department_id from employees where salary in (5000,10000)

       (salary가 5000,10000 검색)

    -  select  last_name,salary,job_id,department_id from employees where last_name like '%a%'   

       (last_name에 a가 들어간 사람 검색)

    - select distinct last_name,hire_date from employees where hire_date between '96/12/31' and '98/01/01'

       (97년에 입사한 사람 검색)

    - select last_name,salary from employees where department_id in(50,80,90) and salary not between 5000 and 10000

       (부서ID가 50,80,80이고 salary가 5000~10000이 아닌 것 검색)

    - select  concat(last_name,first_name),lpad(salary,6,'0') from employees where substr(job_id,4)='REP'

       (job_id에 REP가 들어가는 것중에, 이름이 다나오게 하고, 연봉을 6자리로 표현하고, 빈부분은 '0'으로 채움)


    cf) desc 테이블명  : 테이블의 구조를 볼수 있음

    cf) 단일 비교 연산자 (= > < >= <=)

         다중 비교 연산자 ( between A and B , in (A,B), Like 특정문자열)

         is null(null값) , not (제외하고 검색)

     

    cf) order by (정렬)

     

    cf) Null 값 : 사용할수 없는 값

        Salary * null => null

        특정 COLUMN 에 값이 입력되어 있지 않을 때, 그 값을 NULL 이라 부른다.
        NULL 값은 0 이나 공백과 같지 않다. 
        NULL 값이 산술 연산식에 포함되면 그 결과도 NULL 이다. 
        그러므로 NVL FUNCTION 을 사용하여 NULL 값을 다른 값으로 대체하여야 한다.
        NULL 값을 다른 값으로 대체한다.
           NVL (number_column, 9)
           NVL (date_column, '01-JAN-95')
           NVL (character_column, 'ABCDE')
        [ 예제 ]
         1. S_EMP TABLE 에서 LAST_NAME, COMMISSION 값을 검색하시오.
         2. COMMISSION 은 SALARY * COMMISSION_PCT /100 으로 계산하시오.
             SELECT LAST_NAME, SALARY * NVL(COMMISSION_PCT,0) /100 COMMISSION
             FROM S_EMP ;


     cf) select last_name || first_name from employees 

          select first_name || last_name || ' is a ' || salary from employees

          (하나의 컬럼안에 두가지를 같이 나오게 할때, 연결연산자)

          

    -------------------------------------------------------------------------------------------------

    ▒ 단일행 함수 : 데이터 조작 함수

    - 형식 : function_name (arg1, arg2)

    arg : 상수,문자열,colum

    - lower (소문자)

        ex) select lower ('I Love J') from dual 

             dual(더미테이블:가상의 테이블,테스트용)

    - upper (대문자)

    - initcap (앞자리만 대문자)

    concat (결합함수)

        ex) concat ('World','Hello')  => WorldHello

    substr

       ex) substr ('WorldHello',1,5)  => World (1부터 5까지 추출)

             select substr ('dfaadsfa',1,5) from dual => dfaad

    instr

       ex) substr ('WorldHello','w')  => w가 몇번째 있는지

    length

        ex) length ('WorldHello') => 10 (문자의 길이)

    Lpad | rpad

       ex) lpad ('salary',10,'*') => salary 데이터를 10자리까지 나타내고 남을 경우 왼쪽에 '*'로 채움 

             select lpad(salary,10,'*') from employees       =>  ******2500 

        ex) rpad ('salary',10,'*') => salary 데이터를 10자리까지 나타내고 남을 경우 오른쪽에 '*'로 채움 

             select lpad(salary,10,'*') from employees       =>  2500 ******

    trim

        ex) trim ('H' from 'Hello')

              select  trim ('H' from 'Hello') from dual   (H를 잘라네네요)

     

    ▶ 숫자함수

    Round (반올림)

       ex) round (78.923,2)  => 78.92  (소수셋째자리에서 반올림)

    trunc (버림) 

       ex) trunc (78.923,2) => 78.92 (소수셋째자리부터 버림)

    Mod (나머지)

    Width_bucket (구간)

       ex) width_bucket (salary,0,100000,10)  => 0부터 100000까지를 10등분을 한 salary의 등급표시.

     

    ▶ 날짜함수

    sysdate

       ex) select sysdate from dual

    add_months (날짜)

        select add_months('97/01/01',6) from dual = > 97/07/01 
        select add_months(sysdate,6) from dual => 07/12/26 

    last_day (현재날짜에서 마지막 날짜까지 남은 날)

         select last_day('99/02/01') from dual  => 99/02/28 

         select last_day(sysdate)-sysdate from dual => 현재날에 마지막날까지 남은 날수계산

     

    ▶ null함수

    - nvl (expr,A) : expr가 null이 나오면 A로 바꾸라

    nvl2 (expr,A,B) : expr이 null이 아니면 A로 , null이 아니면 B로

    nullif (expr1,expr2) : expr1과 expr2를 비교한것이 같으면 null을 반환, 다르면 expr1을 반환하라

    coalesce (expr1, expr2, expr3......) : expr1이 null이면 다음 expr2로 또 null이면 expr3으로 null이 아니면 반환 

     

    -select last_name,salary 연봉,salary+nvl(salary*COMMISSION_PCT,0) 총연봉 from employees

    -select last_name,salary 연봉,nvl2(COMMISSION_PCT,salary+salary*COMMISSION_PCT,salary) 총연봉 from employees

    -select first_name,length(first_name),last_name,length(last_name), nullif(length(first_name),length(last_name)) from  

     employees

    ▶ 기타함수

    - case문

    select last_name,department_id,
    CASE department_id
        when 90 then '임원'
        when 60 then '관리부'
        when 50 then '자재부'
        when 80 then '영업부'
    else '신입사원'
    end CASE
    from employees

     

    - decode 문

    select last_name,department_id,
    decode(department_id,
        when 90 then '임원'
        when 60 then '관리부'
        when 50 then '자재부'
        when 80 then '영업부'
    else '신입사원')
    from employees

     

    ▶ 데이터타입

    - number : 숫자

    - varchar : 가변형 문자

    - char : 고정형 문자

    - date : 날짜

     

    ① 암시적변환 : 오라클 자동변환

    ② 명시적 변환 : 수동 변환

    To_char : 다른 데이터타입을 문자형으로 변환

        ex) select To_char(sysdate,'YYYY-MON-DAY')

    To_number :다른 데이터타입을 숫자형으로 변환

    To_date : 다른 데이터타입을 날짜형으로 변환

     

    select To_char(sysdate,'YYYY-MON-ddspth-HH:MM:SS') from dual

    ---------------------------------------------------------------------------------------------

    ▒ sql쿼리 처리 과정

    1. 오라클은 제일 먼저 sql 쿼리문이 맞는지 검사, 틀리면 에러

    2. 테이블이 있는지 검사

    3. 테이블안에 컬럼이 있는지 확인

    4. where문 분석해서 실행(서로 조건과 맞는지 매칭)

     

    ---------------------------------------------------------------------------------------------

    ▒ JOIN

    형식 : select colum from tables

                                       employees,departments      (테이블들을 하나로 인식)

     

    ① 카타시안곱 조인 방식

        employees의 각 행이 departments의 각 행과 1:1로 대응해서 조인.

        ex) select last_name, department_name from employees, departments 

     

    ② 등가 조인 방식

         각테이블에 기준을 가지고 그 기준에 맞추어 대응시키는 방식.

        ex) -select last_name, department_name from employees, departments 

               where employees.department_id = departments.department_id 

             - select last_name, department_name,department_id from employees, departments 

               where employees.department_id = departments.department_id  => error 

                (department_id가 employees에 있는 것인지 departments에 있는 것인지 불명확하기 때문에) 

              - select last_name, department_name,employees.department_id from employees, departments 

               where employees.department_id = departments.department_id  =>good!

             - select last_name, department_name,employees.department_id from employees a, departments b 

               where a.department_id = b.department_id  =>별칭 사용 good!

             - select last_name, department_name from employee e,departments d,location 

                where e.department_id=l.department_id and d.location_id=l.location_id

                (employee ,departments ,location 3개의 테이블을 등가 조인)

     

    ③ 비등가 조인 방식

           동등하지 않은 연산자를 사용하여 조인.

         ex) select e.last_name,e.salary,j.grade_level from employees e,job_grades j

               where e.salary between j.lowest_sal and j.highest_sal

               (각 연봉별 등급을 표시)

     

    ④ 포괄 조인

         ex) select last_name,salary from employees e, departments d

               where e.department_id=d.department_id(+)

     

    ⑤ 자체 조인

         ex) - select w.employee_id, w.last_name, w.manager_id, s.last_name from employees w, employees s

               where w.manager_id=s.employee_id

               
              - select w.last_name, w.hire_date, s.last_name, s.hire_date from employees w, employees s
                 where w.manager_id=s.employee_id and w.hire_date < s.hire_date 

                 (관리자보다 먼저 입사한 사원의 이름, 입사일과, 관리자 이름, 입사일을 표시)

     

    ⑥ 교차 조인

        :카타시안곱 조인 방식과 동일.

          ex) - select * from employees cross join departments

     

    ⑦ 자연 조인

         : 오라클이 알아서 조인함->오라클이 자신이 기준을 찾아서 조인함.

          ex) - select * from employees natural join departments

                  공통된 컬럼,여기선 2개, department_id, manage_id 두개를 기준으로 조인하기 때문에, 

                  department_id와 manage_id 두개가 같은것만 조인하게됨.

     

    ⑧ using을 사용한 조인

          ex) select * from departments join locations using(location_id)

     

    ⑨ on절을 사용한 조인

          ex) select * from departments d join locations l on(d.location_id=l.location_id)

          ex) select * from departments d 

                join locations l on d.location_id=l.location_id 

                join employees e on e.department_id=d.department_id

                (테이블 3개를 조인)

     

    ⑩ outer join :포괄 조인과 동일

           ex) select * from departments d

                 left outer join locations l

                 on d.location_id=l.location_id 

     

                 left (왼쪽) ,right(오른쪽) ,full(전체)

     

    -------------------------------------------------------------------------------------------------

    ▒ 그룹함수

    AVG,SUM,MAX,MIN

    ex) select avg(salary), sum(salary), Max(salary),min(salary) from employees

          select avg(salary), sum(salary), Max(salary),min(salary) from employees where department_id=80 

           (조건문이 있을 경우, 조건을 먼저 해석한뒤, 함수를 처리함.)

           select department_id, sum(salary) from employees group by department_id 부서별 합계를 나타냄.

    - 그룹함수는 null값을 무시함!!!

          select avg(nvl(commission_pct,0)) from employees 

     

    ▒ count 함수

    형식 : count (expr)

    expr : *, 컬럼

    ex ) select count(*) from employees

           select count(commission_pct) from employees (null을 무시)

           select count(nvl(commission_pct,0)) from employees (null 까지 포함)

    ---------------------------------------------------------------------------------------------

    select department_id, sum(salary)

    from employees

    group by department_id

    having sum(salary) > 10000   (그룹에 대한 조건을 써줄수 있음)

     

    ex) 부서 id가 50,60,80인 사원의 업무별 평균 연봉이 5000보다 큰 것을 출력

    select job_id,avg(salary) from employees
    where department_id in(50,60,80)
    group by job_id
    having avg(salary) > 5000 

    -----------------------------------------------------------------------------------------------

    ▒ 분석함수

     

    avg

    sum             (컬럼)          over               order by 컬럼           : 정렬을 하고 분석

    count                                                   partition by  컬럼      : 그룹화한 다음에 분석함수 적용

    rank

     

    ex) - select employee_id, department_id, salary, first_value(salary) over (order by department_id) "aa" from employees

           cf) first_value : 정렬된후 첫번째 컬럼으로 할 것.

          - select employee_id, department_id, salary, first_value(salary) over (partiton by department_id) "aa" from employees

             그룹화하여 그 부서의 가장 첫번째 값을 반환함.

           - select employee_id, department_id, salary, sum(salary) over (group by department_id) "aa" from employees

             누적하여 합계가 나옴.

           - select employee_id, department_id, salary, sum(salary) over (partition by department_id order by employee_id) "aa"

             from employees

           - select employee_id, department_id, salary, count(*) over (order by salary) from employees

           - select employee_id, department_id, salary, count(*) over (order by salary desc) from employees

           - select employee_id, department_id, salary, avg(salary) over (partition by department_id) from employees

               부서별로 그룹화하여 그부서별 평균을 냄.

           - select employee_id, department_id, salary, rank() over (order by salary desc) from employees  (연봉순위)

           - select employee_id, department_id, salary, rank() over (partition by department_id order by salary desc

              from employees  (부서별 연봉 순위를 보여줌, 동일한 값은 같은 순위로 보여줌)

           - select employee_id, department_id, salary, row_number() over (partition by department_id order by salary desc

              from employees  (부서별 연봉 순위를 보여줌, 동일한 값도 순위로 보여줌)

              cf) dense_rank() : row_number 와 비슷함

     

    ------------------------------------------------------------------------------------------------

    ▒ 서브쿼리

     

    ex) Abel이라는 사원보다 급여를 많이 받는 사원을 찾아보자.

    1. Abel의 급여 확인 (서브쿼리-메인쿼리를 이용하기 위해 보조적으로 사용, 메인쿼리보다 서브쿼리가 먼저 실행)

    2. salary에서 Abel보다 큰 급여를 받는 사람 검색 (메인쿼리

        select last_name,salary from emplyees where salary > Abel 급여 (select salary from employees where last_name='Abel')

     

    ex) 사원 ID 144와 같은 업무를 하는 사원 연봉및 부서를 표시

    select last_name,job_id,department_id,salary 

    from employees 

    where job_id=(select job_id from employees where employee_id=144)

     

    ex) Rajs와 같은 업무를 하며 사원 ID 142인 사원보다 연봉이 큰 사원의 연봉, 업무, 부서를 표시

    select last_name,salary, job_id,department_id 

    from employees 

    where salary > (select salary from employees where employee_id=142) and job_id= (select job_id from employees where last_name='Rajs')

     

    ex) Rajs와 같은 업무를 하며 사원 ID 142인 사원보다 연봉이 큰 사원의 연봉, 업무, 부서이름, 근무지를 표시

    select e.last_name,e.salary,e.job_id, d.department_name,l.city 
    from employees e, departments d, locations l
    where e.department_id=d.department_id and d.location_id=l.location_id 
    and salary > (select salary from employees where employee_id=142) and job_id= (select job_id from employees where last_name='Rajs')

     

    ex) 부서 ID가 80인 사원중 연봉이 가장 작은 사원보다 연봉을 많이 받는 사원의 연봉및 업무를 표시

    select last_name,salary,job_id from employees
    where salary > (select min(salary) from employees where department_id=80)

     

    ex) 부서 ID가 80인 사원중 연봉이 가장 작은 사원보다 부서ID가 50,60인 사원들 중 연봉을 많이 받는 사원의 연봉및 업무를 표시

    select last_name,salary,job_id from employees
    where salary > (select min(salary) from employees where department_id=80) and department_id in (50,60)

     

    ex) 회사 내 전체 평균연봉보다 적게 받는 사원

    select last_name,salary,job_id, from employees
    where salary < (select avg(salary) from employees)

     

    ex) 부서별 평균연봉보다 적게 받는 부서별 사원

    select last_name,salary,job_id, from employees
    where salary < (select avg(salary) from employees group by department_id)

    < : 단일조건 연사자라서 뒤에 서브쿼리가 여러개의 값을 갖기 때문에 error.

    -첫번째 방법 : 

    select * from employees a where a.salary <
    (select avg(b.salary) from employees b
    where b.department_id = a.department_id)

     

    -두번째 방법:

    select * from (select last_name, department_id,
    employee_id, salary, avg(salary) over
    (partition by department_id )
    as salary2 from employees)
    where salary < salary2;

     

    <any : 최대값보다 작음

    >any : 최소값보다 큼

    >all : 최대값보다 큼

    <all : 최소값보다 작음

    ----------------------------------------------------------------------------------------------------------

    ▒ set 연산자

    : select문의 결과를 결합하는 연산자

     

    ① union 연산자

        A의 결과값↘

                          중복  ->  1번 출력 (합집합)

        B의 결과값↗

    ex)

    - select employee_id, department_id from employees

    union select employee_id , department_id from job_history

    업무부서를 바꾼 사람들을 표사, 중복되어 두번 표시됨

     

    - select employee_id, job_id from employees

    union select employee_id , job_id from job_history

    업무를 바꾼 사람들을 표시, 중복되어 두번 표시됨.

     cf) union all : 중복된것도 또 한번 출력함. A결과, B결과 모두 뽑아져 나옴.

    - select employee_id, department_id from employees

    union all select employee_id , department_id from job_history

     

    ② intersect 연산자

     

        A의 결과값↘

                          중복된 것만 출력 (교집합)

        B의 결과값↗

     

    ex) - select employee_id, department_id from employees  

          intersect select employee_id , department_id from job_history

     

    ③ minus 연산자

         A의 결과값 - B의 결과값  (차집합)

    ex) - select employee_id, department_id from employees

          minus select employee_id , department_id from job_history

    ------------------------------------------------------------------------------------------------------

    ▒ group by 확장 함수

    ① rollup

    group by rollup (a,b)   => (a,b) (a) (  )  이 3개의 쿼리를 union으로 결합한것과 같은 효과.

    group by rollup (a,b,c)  => (a,b,c) (a,b) (a) (  )

    a,b : colum

    ex) - select department_id,avg(salary) from employees group by rollup(department_id)

            (department_id로 하나로 묶어서 평균연봉값)

     

         DEPARTMENT_ID   AVG(SALARY)
         -------------       ---------------
               10                  4400
               20                  9500
               50                  3500
               60                  6400
               80               10033.3333
               90               19333.3333      -> 부서별 평균
              110                 10150 
                                    7000            -> department_id가 없는 사람의 평균
                                     8775           -> 부서를 다 합한 평균

     

    ex) - select department_id,job_id,avg(salary) from employees group by rollup(department_id,job_id)

            부서별, 업무별평균, 부서별평균, 전체평균 이렇게 3개가 나와야 함.

     

    ② cube

    group by cube (a,b,c)  => (a,b,c)  (a,b)  (a,c)  (b,c)  (a) (b) (c) (  )

    ex) - select department_id, job_id, avg(salary) from employees group by cube (department_id,job_id)

     

    ex) - select job_title, department_name, avg(salary) Avg_salary 

            from employees e Join departments d on d.department_id=e.department_id Join jobs j on j.job_id=e.job_id 

            group by cube (department_name,job_title)

     

    JOB_TITLE DEPARTMENT_NAME AVG_SALARY 
        8717.64706 
    Preesident   24000 
    Programmer   6400 
    Stock Clerk   2925 
    Sales Manager   10500 
    Stock Manager   5800 
    Marketing Manager   13000 
    Sales Representative   9800 
    Administration Assistant   4400 
    Marketing Representative   6000 
    Administration Vice President   17000 
      IT 6400 
    Programmer IT 6400 
      Sales 10033.3333 
    Sales Manager Sales 10500 
    Sales Representative Sales 9800 
      Shipping 3500 
    Stock Clerk Shipping 2925 
    Stock Manager Shipping 5800 
      Executive 19333.3333 
    Preesident Executive 24000 
    Administration Vice President Executive 17000 
      Marketing 9500 
    Marketing Manager Marketing 13000 
    Marketing Representative Marketing 6000 
      Administration 4400 
    Administration Assistant Administration 4400 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    ③ grouping sets 연산

       내가 원하는 그룹을 만들어 줌.

       group by grouping sets ((a,b), (a))    => (a,b) (a) 의 그룹 결과만 얻을수 있다.

    ex) - select job_title, department_name, avg(salary) Avg_salary 

            from employees e Join departments d on d.department_id=e.department_id Join jobs j on j.job_id=e.job_id 

            group by grouping sets ((department_name,job_title),(department_name))

     

    JOB_TITLE DEPARTMENT_NAME AVG_SALARY 
    Programmer IT 6400 
      IT 6400 
    Sales Manager Sales 10500 
    Sales Representative Sales 9800 
      Sales 10033.3333 
    Stock Clerk Shipping 2925 
    Stock Manager Shipping 5800 
      Shipping 3500 
    Preesident Executive 24000 
    Administration Vice President Executive 17000 
      Executive 19333.3333 
    Marketing Manager Marketing 13000 
    Marketing Representative Marketing 6000 
      Marketing 9500 
    Administration Assistant Administration 4400 
      Administration 4400 

     

     

    ④ 계층적 질의

    employee_id과  manager_id 의 관계

     

     (4)                (3)                   (2)                 (1)

    gietz →  thggins,whaien →  kochhar  ┓

                            Hunold →  dehaan   ┫    →  king

                                            zlotkey    ┫

                                            hartstein  ┛

     

    ex) - select employee_id, last_name, manager_id from employees order by employee_id

    EMPLOYEE_ID LAST_NAME MANAGER_ID 
    100 King   
    101 Kochhar 100 
    102 Dehaan 100 
    103 Hunold 102 
    104 Ernst 103 
    107 Lorentz 103 
    124 Mourgos 100 
    141 Rajs 124 
    142 Davies 124 
    143 Matos 124 
    144 Vargas 124 
    149 Zlotkey 100 
    174 Abel 149 
    176 Taylor 149 
    178 Grant 149 
    200 Whaien 101 
    201 Hartstein 100 
    202 Fay 201 
    205 Higgins 101 
    206 Gietz 205 

     

     

    - select employee_id, last_name, manager_id, prior last_name 

       from employees start with employee_id=100 connect by prior employee_id=manager_id;

                                                                                                부모             자식

    EMPLOYEE_ID LAST_NAME MANAGER_ID PRIORLAST_NAME 
    100 King     
    101 Kochhar 100 King 
    200 Whaien 101 Kochhar 
    205 Higgins 101 Kochhar 
    206 Gietz 205 Higgins 
    102 Dehaan 100 King 
    103 Hunold 102 Dehaan 
    104 Ernst 103 Hunold 
    107 Lorentz 103 Hunold 
    124 Mourgos 100 King 
    141 Rajs 124 Mourgos 
    142 Davies 124 Mourgos 
    143 Matos 124 Mourgos 
    144 Vargas 124 Mourgos 
    149 Zlotkey 100 King 
    174 Abel 149 Zlotkey 
    176 Taylor 149 Zlotkey 
    178 Grant 149 Zlotkey 
    201 Hartstein 100 King 
    202 Fay 201 Hartstein 

     

     

    - select w.employee_id, lpad(last_name,length(last_name)+(level*2-2),'*') from employees

       start with last_name='KING' connect by prior employee_id=manager_id

     

    EMPLOYEE_ID LPAD(LAST_NAME,LENGTH(LAST_NAME)+(LEVEL*2-2),'*') 
    100 King 
    101 **Kochhar 
    200 ****Whaien 
    205 ****Higgins 
    206 ******Gietz 
    102 **Dehaan 
    103 ****Hunold 
    104 ******Ernst 
    107 ******Lorentz 
    124 **Mourgos 
    141 ****Rajs 
    142 ****Davies 
    143 ****Matos 
    144 ****Vargas 
    149 **Zlotkey 
    174 ****Abel 
    176 ****Taylor 
    178 ****Grant 
    201 **Hartstein 
    202 ****Fay 

     

     

    - select employee_id, lpad(employee_id,level*3,'*'), sys_connect_by_path(last_name,'/')path 
       from employees start with last_name='King' connect by prior employee_id=manager_id

     

    EMPLOYEE_ID LPAD(EMPLOYEE_ID,LEVEL*3,'*') PATH 
    100 100 /King 
    101 ***101 /King/Kochhar 
    200 ******200 /King/Kochhar/Whaien 
    205 ******205 /King/Kochhar/Higgins 
    206 *********206 /King/Kochhar/Higgins/Gietz 
    102 ***102 /King/Dehaan 
    103 ******103 /King/Dehaan/Hunold 
    104 *********104 /King/Dehaan/Hunold/Ernst 
    107 *********107 /King/Dehaan/Hunold/Lorentz 
    124 ***124 /King/Mourgos 
    141 ******141 /King/Mourgos/Rajs 
    142 ******142 /King/Mourgos/Davies 
    143 ******143 /King/Mourgos/Matos 
    144 ******144 /King/Mourgos/Vargas 
    149 ***149 /King/Zlotkey 
    174 ******174 /King/Zlotkey/Abel 
    176 ******176 /King/Zlotkey/Taylor 
    178 ******178 /King/Zlotkey/Grant 
    201 ***201 /King/Hartstein 
    202 ******202 /King/Hartstein/Fay 

     

     

     

    --------------------------------------------------------------------------------------------------------

    ▒ 고급 서브 쿼리

     

    ① 비교 서브 쿼리 - 데이터의 일관성을 확인할 때 사용

        [메인쿼리의 결과] <-- 서로 비교 --> [서브쿼리의 결과]

     

    ex1) select employee_id, manager_id, department_id from employees 

          where (manager_id, department_id)

          in (select manager_id, department_id from employees where employee_id in (174,178))

               174  149   80

               178  149    X

    *서브쿼리에 null이 들어있으면 안나오게 됨.

    EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID 
    176 149 80 
    174 149 80 

     

     

    ex2) select employee_id, manager_id, department_id from employees 

           where (manager_id, department_id)

           in (select manager_id, department_id from employees where employee_id in (174,101))

     

    EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID 
    102 100 90 
    101 100 90 
    176 149 80 
    174 149 80

     

     

    ex3) 쌍비교 서브쿼리

           select employee_id, manager_id, department_id from employees 

           where (manager_id, department_id)

           in (select manager_id, department_id from employees where employee_id in (174,101))

           and employee_id not in (174,101) 

           => 비교하는 자기 자신은 제외하고 나오게 하게 할때.

     

    ex4) 근무지가 토론토에서 일을 하는 사원과 옥스포드에서 일하는 사원의 department_id, location_id 가 같은 사원을 출력

           select e.employee_id,e.last_name, d.department_id, l.location_id from employees e, departments d, locations l

           where e.department_id=d.department_id and d.location_id=l.location_id and (d.department_id,l.location_id)

           in (select d.department_id,l.location_id from departments d, locations l where d.location_id=l.location_id and

           l.city in ('Toronto' ,'Oxford')) 

     

    EMPLOYEE_ID LAST_NAME CITY DEPARTMENT_ID LOCATION_ID 
    149 Zlotkey Oxford 80 2500 
    174 Abel Oxford 80 2500 
    176 Taylor Oxford 80 2500 
    201 Hartstein Toronto 20 1800 
    202 Fay Toronto 20 1800 

     

     

    ex5) 비쌍비교 서브쿼리

           select employee_id, manager_id, department_id from employees

           where manager_id in (select manager_id from employees where employee_id in (142,101))

           and department_id in (select department_id from employees where employee_id in (142,101))

     

           142 (100) , 101 (124)   <----->  142 (90),  101 (50) 

            manager_id 100번과 124번에 대해 department_id 90 또는 50이 각각 대응되어 모두 출력.

    EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID 
    124 100 50 
    101 100 90 
    102 100 90 
    141 124 50 
    142 124 50 
    143 124 50 
    144 124 50 

     

     

    ② 인라인 뷰 - 가상의 테이블을 만들어 줌

    ex1) select e.last_name, e.department_id, e.salary, b.sal 

          from employees e, (select department_id, avg(salary) as sal from employees group by department_id) b

          where e.salary > b.sal

     

    LAST_NAME DEPARTMENT_ID SALARY SAL 
    Lorentz 60 4200 3500 
    Whaien 10 4400 3500 
    Mourgos 50 5800 3500 
    Ernst 60 6000 3500 
    Fay 20 6000 3500 
    Grant   7000 3500 
    Gietz 110 8300 3500 
    Taylor 80 8600 3500 
    Hunold 60 9000 3500 
    Zlotkey 80 10500 3500 
    Abel 80 11000 3500 
    Higgins 110 12000 3500 
    Hartstein 20 13000 3500 
    Kochhar 90 17000 3500 
    Dehaan 90 17000 3500 
    King 90 24000 3500 
    Mourgos 50 5800 4400 
    Ernst 60 6000 4400 
    Fay 20 6000 4400 
    Grant   7000 4400 
    Gietz 110 8300 4400 
    Taylor 80 8600 4400 
    Hunold 60 9000 4400 
    Zlotkey 80 10500 4400 
    Abel 80 11000 4400 
    Higgins 110 12000 4400 
    Hartstein 20 13000 4400 
    Kochhar 90 17000 4400 
    Dehaan 90 17000 4400 
    King 90 24000 4400 
    Grant   7000 6400 
    Gietz 110 8300 6400 
    Taylor 80 8600 6400 
    Hunold 60 9000 6400 
    Zlotkey 80 10500 6400 
    Abel 80 11000 6400 
    Higgins 110 12000 6400 
    Hartstein 20 13000 6400 
    Kochhar 90 17000 6400 
    Dehaan 90 17000 6400 
    King 90 24000 6400 
    Gietz 110 8300 7000 
    Taylor 80 8600 7000 
    Hunold 60 9000 7000 
    Zlotkey 80 10500 7000 
    Abel 80 11000 7000 
    Higgins 110 12000 7000 
    Hartstein 20 13000 7000 
    Kochhar 90 17000 7000 
    Dehaan 90 17000 7000 
    King 90 24000 7000 
    Zlotkey 80 10500 9500 
    Abel 80 11000 9500 
    Higgins 110 12000 9500 
    Hartstein 20 13000 9500 
    Kochhar 90 17000 9500 
    Dehaan 90 17000 9500 
    King 90 24000 9500 
    Zlotkey 80 10500 10033.3333 
    Abel 80 11000 10033.3333 
    Higgins 110 12000 10033.3333 
    Hartstein 20 13000 10033.3333 
    Kochhar 90 17000 10033.3333 
    Dehaan 90 17000 10033.3333 
    King 90 24000 10033.3333 
    Zlotkey 80 10500 10150 
    Abel 80 11000 10150 
    Higgins 110 12000 10150 
    Hartstein 20 13000 10150 
    Kochhar 90 17000 10150 
    Dehaan 90 17000 10150 
    King 90 24000 10150 
    King 90 24000 19333.3333 

     

     

    ex2) 부서별 평균보다 연봉을 많이 받는 사원

            select e.last_name, e.salary, e.department_id, b.sal
            from employees e, (select department_id,avg(salary) as sal from 
            employees group by department_id) b 
            where e.salary > b.sal and e.department_id=b.department_id;

    LAST_NAME SALARY DEPARTMENT_ID SAL 
    King 24000 90 19333.3333 
    Hunold 9000 60 6400 
    Mourgos 5800 50 3500 
    Zlotkey 10500 80 10033.3333 
    Abel 11000 80 10033.3333 
    Hartstein 13000 20 9500 
    Higgins 12000 110 10150 

     

     

    ③ 상호관련 서브쿼리

    [메인쿼리의 결과]가  [서브쿼리]로 들어가서 조건에 맞는 것을 검색

    ex1) select employee_id, department_id, salary from employees a

           where salary > (select avg(salary) from a.employees  where department_id=a.department_id)

     

    EMPLOYEE_ID DEPARTMENT_ID SALARY 
    100 90 24000 
    103 60 9000 
    124 50 5800 
    149 80 10500 
    174 80 11000 
    201 20 13000 
    205 110 12000 

     


    • 네이버 블로그 공유
    • 네이버 밴드 공유
    • 페이스북 공유
    • 카카오스토리 공유