[ORACLE 2일차] select/NVL/where/오름,내림차순/1,2차정렬/연산자

select 의 처리 순서

 

어떠한 테이블(또는 뷰)에서 데이터 정보를 꺼내와 보는 명령어인 select 의 처리순서 

   
   select 컬럼명, 컬럼명         --- 5  컬럼명 대신에 *(아스테리크)을 쓰면 모든 컬럼을 뜻하는 것이다.

   from 테이블명(또는 뷰명)   --- 1  :  전체 테이블의 결과를 갖고 옵니다.

   where 조건절                      --- 2  where 조건절이 뜻하는 것은 해당 테이블명(또는 뷰명)에서 조건에 만족하는 행(row)                                                            을 메모리(RAM)에 로딩(퍼올리는것) 해주는 것이다.
   group by 절                          --- 3
   having 그룹함수조건절        --- 4 
   order by 절                           --- 6

 

 

NVL

 

NVL 함수는 값이 NULL인 경우 지정값을 출력하고, NULL이 아니면 원래 값을 그대로 출력한다
함수  :  NVL("값", "지정값") 

 

1.  select 1+2, 1+null, 3*0, null*0, 2-1, 2-null, 5/2, 5/null 
      from dual;  (dual 은 select 다음에 나오는 값들을 화면에 보여주기 위한 용도로 쓰이는 가상테이블이다) 

 

 

2.  select nvl(7,3), nvl(null,3),
           nvl('이순신','거북선'), nvl(null,'거북선') 

         from dual;

   

 

NYL2

NVL2 함수는 NULL이 아닌 경우 지정값1을  출력하고, NULL인 경우 지정값2를 출력한다.
함수 :  NVL2("값", "지정값1", "지정값2") // NVL2("값", "NOT NULL", "NULL") 

    

select nvl2(7,3,2), nvl2(null,3,2),
           nvl2('이순신','거북선','구국영웅'), nvl2(null,'거북선','구국영웅') 
from dual;

 

 

where

 where 조건절이 뜻하는 것은 해당 테이블명(또는 뷰명)에서 조건에 만족하는 행(row) 을 메모리(RAM)에 로딩(퍼올리는것) 해주는 것이다.

<예시문제>

employees 테이블에 저장되어진 데이터중 부서번호(department_id) 60번에 근무하는 직원들에 대해 사원번호 (employee_id), 이름(first_name), 성(last_name), 직종ID(job_id), 기본급여(salary), 수당퍼센티지(commission_pct), 
월급, 부서번호(department_id) 를 출력하세요. 월급은 기본급여(salary) + 수당(salary * commission_pct) 이다.

 select employee_id, first_name, last_name, job_id, salary, commission_pct, salary * commission_pct, 
            NVL(salary + (salary * commission_pct), salary),
            NVL2(commission_pct, salary + (salary * commission_pct), salary),
            department_id
 from     employees
 where  department_id = 60; 

 

 

1. as로 별칭 주기

 

 select employee_id AS "사원번호"               -- 별칭(별명) alias 
         , first_name "이름"                                     -- 별칭(별명) alias 에서 AS 는 생략가능함.
         , last_name 성                                              -- 별칭(별명) alias 에서 "" 는 생략가능함.
         , salary "기본     급여"                                 -- 별칭(별명) alias 에서 공백을 주고자 한다라면 반드시 "" 로 해주어야 한다.
         , commission_pct "수당퍼센티지"
         , NVL(salary + (salary * commission_pct), salary) 월급
         , NVL2(commission_pct, salary + (salary * commission_pct), salary) month_salary
         , department_id 부서번호
    from employees;

 

 

2. 비교연산자

   1) 같다                    = 
   2) 같지않다                !=  <>  ^= 
   3) 크다. 작다              >   <
   4) 같거나크다. 같거나작다    >=       <= 
   5) NULL 은 존재하지 않는 것이므로 비교대상이 될 수가 없다.
      그러므로 비교연산( =  != <> ^= >  <  >=  <= )을 할수가 없다.
      그래서 비교연산을 하려면 nvl()함수, nvl2()함수를 사용하여 처리한다. 


 2.1 오라클에서 컬럼들을 붙일때(연결할때)는 문자타입 이든, 숫자타입 이든, 날짜타입 이든 관계없이 || 를 쓰면 된다. 

   select '대한민국', '서울시', 1234, sysdate,
          '대한민국' || '서울시' || 1234 || sysdate
   from dual;

2.2 employees 테이블에서 부서번호가 null 인 직원들만 사원번호, 사원명, 월급, 부서번호를 추출하세요.

   select employee_id AS 사원번호
        , first_name || ' ' || last_name AS 사원명
        , nvl( salary + (salary * commission_pct), salary ) AS 월급
        , department_id AS 부서번호
   from employees
   where department_id = null;
데이터가 출력되지 않는다.
왜냐하면 NULL 은 존재하지 않는 것이므로 비교대상이 될 수가 없기 때문이다.

2.2-1 employees 테이블에서 부서번호가 null 인 직원들만 사원번호, 사원명, 월급, 부서번호를 추출하세요.

 
   select employee_id 사원번호
        , first_name || ' ' || last_name 사원명
        , nvl( salary + (salary * commission_pct), salary ) 월급
        , department_id 부서번호
   from employees
   where nvl(department_id, -9999) = -9999;

또는 

   select employee_id 사원번호
        , first_name || ' ' || last_name 사원명
        , nvl( salary + (salary * commission_pct), salary ) 월급
        , department_id 부서번호
   from employees
   where department_id is null;
  NULL 은 is 연산자를 사용하여 구한다.
  department_id 컬럼의 값이 null 인 행들만 RAM(메모리)에 퍼올리는 것이다.

 

 2.3 employees 테이블에서 부서번호가 30번이 아닌 직원들만 사원번호, 사원명, 월급, 부서번호를 추출하세요.  
   select employee_id 사원번호
        , first_name || ' ' || last_name 사원명
        , nvl( salary + (salary * commission_pct), salary ) 월급
        , department_id 부서번호
   from employees
-- where department_id != 30; 
-- where department_id <> 30;
   where department_id ^= 30;

   select employee_id 사원번호
        , first_name || ' ' || last_name 사원명
        , nvl( salary + (salary * commission_pct), salary ) 월급
        , department_id 부서번호
   from employees
   where nvl(department_id, -9999) != 30;


2.4 employees 테이블에서 부서번호가 NULL 아닌 직원들만 사원번호, 사원명, 월급, 부서번호를 추출하세요. 

   select employee_id 사원번호
        , first_name || ' ' || last_name 사원명
        , nvl( salary + (salary * commission_pct), salary ) 월급
        , department_id 부서번호
   from employees
   where nvl(department_id, -9999) != -9999;

   -- 또는
   select employee_id 사원번호
        , first_name || ' ' || last_name 사원명
        , nvl( salary + (salary * commission_pct), salary ) 월급
        , department_id 부서번호
   from employees
   where department_id is not null;

   -- 또는
   select employee_id 사원번호
        , first_name || ' ' || last_name 사원명
        , nvl( salary + (salary * commission_pct), salary ) 월급
        , department_id 부서번호
   from employees
   where not (department_id is null);

   -- 또는
   select employee_id 사원번호
        , first_name || ' ' || last_name 사원명
        , nvl( salary + (salary * commission_pct), salary ) 월급
        , department_id 부서번호
   from employees
   where not department_id is null;

 

   오름차순 / 내림차순

select 되어져 나온 데이터를 정렬(오름차순정렬, 내림차순정렬) 하여 보여주기   

1. 오름차순 :  salary 컬럼의 값을 오름차순 정렬하여 보이세요.
   select employee_id, first_name, last_name, salary, department_id
   from employees
   order by salary asc; 
   
   -- 또는 
   select employee_id, first_name, last_name, salary, department_id
   from employees
   order by salary; -- asc 은 생략가능하다.
   
   
2. 내림차순 :  salary 컬럼의 값을 내림차순 정렬하여 보이세요.
   select employee_id, first_name, last_name, salary, department_id
   from employees
   order by salary desc;  -- desc 은 내림차순인데 생략하면 안된다.

 

<예시> 월급의 오름차순 정렬하여 보이세요.
   select employee_id, first_name, last_name 
        , nvl( salary + (salary * commission_pct), salary ) AS month_salary 
        , department_id
   from employees
   order by nvl( salary + (salary * commission_pct), salary ) asc;
   
   -- 또는 
   select employee_id, first_name, last_name 
        , nvl( salary + (salary * commission_pct), salary ) AS month_salary 
        , department_id
   from employees
   order by month_salary asc;
   
   -- 또는 
   select employee_id, first_name, last_name 
        , nvl( salary + (salary * commission_pct), salary ) AS month_salary 
        , department_id
   from employees
   order by 4 asc;

 

 

3. NULL

: 정렬(오름차순정렬, 내림차순정렬)을 할 때 NULL 은 존재하지 않는 것이므로  오라클 에서는 NULL 을 가장 큰것으로 간주를 하고, 마이크로소프트사의 MS-SQL 에서는 NULL 을 가장 작은것으로 간주한다.

 

 

1,2차 정렬

 

<예시>
 employees 테이블에서 부서번호별 오름차순 정렬을 한 후에 동일한 부서번호내에서는
 월급의 내림차순으로 정렬하여 사원번호, 사원명, 월급, 부서번호를 나타내세요.
   
   select employee_id
        , first_name
        , last_name 
        , nvl( salary + (salary * commission_pct), salary ) AS month_salary 
        , department_id
   from employees
   order by 5 asc, 4 desc;              ->asc는 생략가능
               1차 정렬, 2차 정렬

 

 

AND    OR    IN()     NOT   연산자

 

<예시>

employees 테이블에서 부서번호가 30, 50, 60번 부서에 근무하는 사원들중에 
연봉(월급*12)이 20000 이상 60000 이하인 사원들만 
사원번호, 사원명, 연봉(월급*12), 부서번호를 나타내되 
부서번호의 오름차순으로 정렬한 후 동일한 부서번호내에서는 연봉의 내림차순으로 나타내세요.
   
아래는 틀린 풀이 이다.

   select employee_id AS 사원번호
        , first_name || ' ' || last_name AS 사원명
        , nvl( salary + (salary * commission_pct), salary ) * 12 AS 연봉
        , department_id AS 부서번호
   from employees
   where department_id = 30 OR department_id = 50 OR department_id = 60 AND nvl( salary + (salary * commissio     n_pct), salary ) * 12 >= 20000 AND nvl( salary + (salary * commission_pct), salary ) * 12 <= 60000  
   order by 4 asc, 3 desc;
   
아래가 올바른 풀이 이다.

 

<중요>
AND 와 OR 가 혼용되어지면 우선순위는 AND 가 먼저 실행한다.
연산자에 있어서 가장 최우선은 괄호( )가 제일 우선한다.

 

 

범위 연산자 

 

>             <             >=            <=             between A and B
범위 연산자에 사용되는 데이터는 숫자 뿐만 아니라 문자, 날짜 까지 모두 사용된다.

 

   select employee_id AS 사원번호
        , first_name || ' ' || last_name AS 사원명
        , nvl( salary + (salary * commission_pct), salary ) * 12 AS 연봉
        , department_id AS 부서번호
   from employees
   where department_id IN(30,50,60) AND nvl( salary + (salary * commission_pct), salary ) * 12 BETWEEN 20000 AND 60000 
   order by 4 asc, 3 desc;

 

현재 시각을 알려주는 것

 

1. sysdate, current_date, localtimestamp, current_timestamp, systimestamp

 select sysdate, current_date, localtimestamp, current_timestamp, systimestamp
   from dual;

     날짜 타입은 date 이다.
      date 타입의 기본적인 표현방식은 'RR/MM/DD' 으로 나타내어진다.
      RR 은 년도의 2자리만 나타내어주는데 50 ~ 99 는 1950 ~ 1999 을 말하는 것이다.
      RR 은 년도의 2자리만 나타내어주는데 00 ~ 49 는 2000 ~ 2049 을 말하는 것이다.
      MM 은 월이고, DD 는 일이다.

 

2. to_char

 : 오라클에서 쿼리문을 작성할 때 TO_CHAR() 함수는 날짜, 숫자 등의 값을 문자열로 변환하는 함수이다

  select sysdate
        , to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
        , to_char(sysdate, 'yyyy-mm-dd hh:mi:ss')
        , to_char(sysdate, 'yyyy-mm-dd am hh:mi:ss')
        , to_char(sysdate, 'yyyy-mm-dd pm hh:mi:ss')
   from dual;

 

   select employee_id AS 사원번호
        , first_name || ' ' || last_name AS 사원명
        , to_char(hire_date, 'yyyy-mm-dd') AS 입사일자
        , to_char(hire_date, 'yyyy')||'년 '||to_char(hire_date, 'mm')||'월 '||to_char(hire_date, 'dd')||'일' AS 입사일자
   from employees;

 

 

   select hire_date
   from employees
   where employee_id = 154;
   -- 06/12/09

 update employees set hire_date = to_date('2006-12-31 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
   where employee_id = 154;
   -- 1 행 이(가) 업데이트되었습니다.
   
   commit;
   -- 커밋 완료.