Bohemian life

select sum(sal)/count(*), avg(sal)

  from emp;

oj_SQL.txt

 drop table t1 purge;

  create table t1 (col1 number);

  insert into t1 values (1000);

  insert into t1 values (null);

  insert into t1 values (1000);

  insert into t1 values (2000);

  insert into t1 values (2000);

  insert into t1 values (null);

  commit;

  select count(*)

       , count(col1)

       , count(distinct col1)

  from t1;

- All group functions ignore null values. 

예외) count(*)

  select count(comm) from emp;

  select count(*) from emp;

  SQL> insert into t1 values (null,null ,null );

  SQL> commit;

  SQL> select * from t1;

  SQL> select count(a), count(b), count(c), count(*) from t1;

  select avg(comm), avg(nvl(comm, 0))

  from emp;

- 사원의 총급여

  select sum(sal)

  from emp;

- 부서별 급여합 구하기

  select sum(sal)

  from emp

  where deptno = 10

  union all

  select sum(sal)

  from emp

  where deptno = 20

  union all

  select sum(sal)

  from emp

  where deptno = 30;

      ↓ ↓ ↓

- GROUP BY 절 사용

  select sum(sal)

  from emp

  group by deptno;

  select deptno, sum(sal)

  from emp; --> 오류: ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

===> select-list에 단일 컬럼 또는 수식을 그룹 함수와 같이 사용하려면

             반드시 group by 절에 해당 단일 컬럼 또는 수식을 명시해 주어야 합니다.

      ↓ ↓ ↓

  select deptno, sum(sal)

  from emp

  group by deptno;

  select substr(ename, -1, 1), sum(sal)

  from emp

  group by ename; --> 엉터리

  select substr(ename, -1, 1), sum(sal)

  from emp

  group by substr(ename, -1, 1); --> 제대로

  * 그러나 group by 절에 명시되었다고 반드시 select-list 에 명시될 필요는 없습니다.

select sum(sal)

from emp

group by deptno;

===> group by 절의 단일컬럼 또는 수식은 반드시 select-list에

            명시될 필요는 없습니다.

  select deptno, job, sum(sal)

  from emp

  group by deptno, job;

  select to_char(hiredate, 'YYYY') as Year, sum(sal)

  from emp

  group by to_char(hiredate, 'YYYY');

  ==> 9i 까지는 sort group by가 수행되었으나

   10g부터는 hash group by가 수행됩니다. ==> 

Group Functions는 group 당 하나의 결과를 주기 위해 로우의 세트에 작용한다. 

이들 세트는 테이블 전체이거나 그룹으로 나누어진 테이블이 될 수 있다.

Types of Group Functions

AVG([DISTINCT|ALL]n)

          ## Average value of n, ignoring null values

COUNT({*|DISTINCT|ALL]expr})

          ## Number of rows, where expr evaluates to something other than null

              (count all selected rows using *, including duplicates and rows with nulls)

MAX([DISTINCT|ALL]expr)

          ## Maximum value of expr, ignoring null values

MIN([DISTINCT|ALL]expr)

          ## Minimum value of expr, ignoring null values

STDDEV([DISTINCT|ALL]x)

          ## Standard deviation of n, ignoring null values

SUM([DISTINCT|ALL]n)

          ## Sum values of n, ignoring null values

VARIANCE([DISTINCT|ALL]x)

          ## Variance of n, ignoring null values

Group Functions Syntax

SELECT      [column,] group_function(column), ...

FROM         table

[WHERE      condition]

[GROUP BY column]

[GROUP BY column];

DISTINCT는 중복된 값은 하나만을 사용한다. ALL은 중복된 값을 포함한

모든 값을 사용한다.

expr 인수의 데이터 타입은 CHAR, VARCHAR2, NUMBER, DATE가 될 수 있다.

모든 group functions는 null value를 무시한다. null value를 치환하기 위해서는

NVL, NVL2, COALESCE functions를 사용한다.

Oracle Server는 GROUP BY절이 사용될 때 묵시적으로 오름차순으로 결과를

정렬한다.

COUNT function

COUNT function은 세가지 포맷을 갖는다.

- COUNT(*)

- COUNT(expr)

- COUNT(DISTINCT expr)

COUNT(*)은 값이 로우의 컬럼들의 값이 중복되거나, null 값을 포함하고 있는

테이블의 모든 로우의 수를 반환한다. 만약 WHERE 절이 포함되어 있으면, 그

조건을 만족하는 로우의 수를 반환한다.

COUNT(expr)은 expr로 기술된 컬럼에서 null 값이 아닌 로우의 수만을 반환한다.

COUNT(DISTINCT expr)은 expr로 기술된 컬럼에서 null이 아니고, 중복된 값은

하나로 처리한 로우의 수를 반환한다.

Group Functions는 null을 무시한다

SELECT AVG(commission_pct)

FROM employees;

이 샘플에서 averagesms COMMITION_PCT 컬럼에 유효한 값이 저장된 로우에

대해서만 계산을 수행한다. 즉 average는 commition을 받는 직원의 수로 직원들

에게 지불된 commition의 총합을 나누어 계산된다.

SELECT AVG(NVL(commission_pct, 0))

FROM employees;

NVL function은 group functions가 null value를 포함하도록 한다. 이 샘플에서

average는 COMMISSION_PCT 컬럼에 저장된 null 값을 무시하고 모든 로우에

대해서 계산을 수행한다. 즉 average는 회사의 모든 직원들의 수로 모든 직원들

에게 지급된 commission의 총합을 나누어 계산된다.

Group functions를 사용한 잘못된 질의

SELECT list에서 Group function이 아닌 column이나 expression은 반드시

GROUP BY 절에 있어야 한다.

SELECT department_id, COUNT(last_name)

FROM employees;

이렇게 하면 오라클은 '아악~, 안돼, 안돼!'라는 메시지를 날린다.

WHERE 절에서 그룹을 제한할 수 없다.

그룹을 제한하기 위해서는 HAVING 절을 사용해야 한다.

WHERE 절에 group functions를 사용할 수 없다.

SELECT department_id, AVG(salary)

FROM    employees

WHERE  AVG(salary) > 8000

GROUP BY department_id;

이렇게 하면 오라클은 역시 '아악~, 안돼, 안돼!'라는 메시지를 날린다.

Group Results를 제외하기(Excluding Group Results) : HAVING 절

Groups를 제한하기 위해 HAVING 절을 사용한다.

1. Rows가 분류된다.

2. Group function이 적용된다.

3. HAVING 절에 맞는 groups가 표시된다.

WHERE절이 선택된 rows를 제한하듯이, HAVING 절은 groups를 제한한다.

SELECT          column, group_function

FROM             table

[WHERE          condition]

[GROUP BY     group_by_expression]

[HAVING         group_by_condition]

[ORDER BY     column];

예)

SELECT        department_id, MAX(salary)

FROM           employees

GROUP BY    department_id

HAVING        MAX(salary)>10000;

출처:http://blog.naver.com/john1227?Redirect=Log&logNo=60010245414

 - short-circuit evaluation

  * CASE expression: ANSI SQL - simple, searched

  * DECODE function: Oracle proprietary

접기

 

COALESCE

문법

coalesce::=

그림 설명

 

목적

COALESCE 함수는 나열된 값을 순차적으로 체크하여 NULL이 아닌 첫번째 인수를 반환한다. 모든 인수가 NULL인경우에는 NULL을 반환한다.

만약 expr 전체가 수치 데이터형이거나 암묵적으로 수치 데이터 형으로 변환 가능한 수치 외의 데이터형이라면, 오라클 데이터 베이스는 가장 높은 수치 순위를 결정하여 나머지 인수를 암묵적으로 그 데이터형으로 변환하고, 그 데이터 형을 반환한다.

이 함수는 NVL함수의 일반화된것이다.

COALSESCE함수는 CASE식의 표현으로 사용 할수 있다.예를 들어,

COALESCE (expr1, expr2)
는 아래와 같다.

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

는 동일하게,

COALESCE (expr1, expr2, ..., exprn), for n>=3

이것은 아래와 동일하다.

CASE WHEN expr1 IS NOT NULL THEN expr1 
   ELSE COALESCE (expr2, ..., exprn) END

참 조 : NVL and "CASE Expressions "

 

예제

다음 예제는 oe.prodct_information 테이블을 이용하여, 상품의 재고 판매를 기획한다. 10% 할인 판매를 한다. 만약 표시 가격이 없다면, 세일 가격은 최소가격이 된다. 만약 최소가격이 없다면, 세일 가격은 5이다.

SELECT product_id, list_price, min_price,
   COALESCE(0.9*list_price, min_price, 5) "Sale"
   FROM product_information
   WHERE supplier_id = 102050;

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      2382        850        731        765
      3355                                5
      1770                    73         73
      2378        305        247      274.5
      1769         48                  43.2

출처:http://www.statwith.pe.kr/ORACLE/functions019.htm#i1001341

접기

- short-circuit evaluation

  drop table t1 purge;

  create table t1 as select comm a, mgr b, sal c from emp;

  select * from t1;

  select a, b, c, nvl(nvl(nvl(a, b), c), 0) d from t1;

  select a, b, c, coalesce(a, b, c, 0) d from t1;

select last_name, first_name, nullif(substr(last_name, -1, 1), substr(first_name, -1, 1)) 

  from employees;

접기

NULLIF

문법

nullif::=

그림 설명
 

목적

NULLIF는 expr1과 expr2를 비교한다.만약 expr1과 expr2가 같으면, NULL값을 반환한다. 만약 expr1과 expr2가 같지 않으면, expr1을 반환한다. expr1에 대하여는 NULL을 지정할수 없다.

만약 양쪽 인수가 수치형이라면, 오라클 데이터 베이스는 더 높은 수치 순위를 가지는 인수를 판단하여, 나머지 인수를 그 데이터형으로 암묵적으로 변환하여 반환한다. 만약 인수가 수치가 아니라면, 두 인수는 같은 데이터형이여야 하며, 그렇지 않은 경우 에러가 발생한다.

NULLIF함수는 다음의 CASE문장과 논리적으로 동등하다.

CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

참 조: "CASE Expressions "

예제

다음 예제는 샘플 스키마 HR에서 고용된 이후 직종이 변경된 사람을 선택하는데, 현재 job_id와 job_history의 job_id를 비교하여 확인한다.

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
   FROM employees e, job_history j
   WHERE e.employee_id = j.employee_id
   ORDER BY last_name;

LAST_NAME                 Old Job ID
------------------------- ----------
De Haan                   AD_VP
Hartstein                 MK_MAN
Kaufling                  ST_MAN
Kochhar                   AD_VP
Kochhar                   AD_VP
Raphaely                  PU_MAN
Taylor                    SA_REP
Taylor
Whalen                    AD_ASST 

Whalen

접기

접기

NVL

문법

nvl::=

그림 설명
 

목적

NVL함수는 쿼리의 결과에서 NULL(공백으로 반환)값을 치환한다. 만약 expr1이 NULL이면, NVL함수는 expr2를 반환한다. 만약 expr1이 NULL이 아니면, NVL함수는 expr1을 반환한다.

인수 expr1과 expr2는 임의의 데이터 형을 가진다. 만약 데이터 형이 다르면,

• 만약 expr1이 문자 데이터 이면, 오라클 데이터 베이스는 비교하기 전에 expr2의 데이터형은 expr1의 데이터형으로 변환하여, expr1의 문자 세트에 VARCHAR2를 반환한다.

• 만약 expr1이 숫자형이면, 오라클 데이터 베이스는 더 높은 수치 순위를 가지는 인수를 판단하여, 다른 인수의 데이터형을 암묵적으로 변환하여 반환한다.

 

예제

다음 예제는 종업원이 수수료를 받지 않은 경우 "Not Applicable"를 표시한다.

SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')
   "COMMISSION" FROM employees
   WHERE last_name LIKE 'B%'
   ORDER BY last_name;
 
LAST_NAME                 COMMISSION
------------------------- ----------------------------------------
Baer                      Not Applicable
Baida                     Not Applicable
Banda                     .1
Bates                     .15
Bell                      Not Applicable
Bernstein                 .25
Bissot                    Not Applicable
Bloom                     .2 

Bull Not Applicable

접기

접기

NVL2

문법

nvl2::=

그림 설명
 

목적

NVL2함수를 사용하면, 지정한 표현이 NULL인지 여부에 근거하여 쿼리의 반환될 값을 판단할수 있다. 만약 expr1이 NULL이 아니라면, NVL2는 expr2를 반환한다. 만약 expr1인 NULL이라면, NVL2는 expr3을 반환한다.

인수 expr1은 임의의 데이터형을 가질수 있다. 인수 expr2와 expr3은 LONG이외의 임의의 데이터 형을 가질수 있다.

만약 expr2와 expr3의 데이터형이 다르다면,

• 만약 expr2가 문자 데이터형이라면, 오라클 데이터 베이스는 expr3이 NULL 상수가 아닌 한, 그것들을 비교하기 전에 expr3을 expr2의 데이터형으로 변환한다. expr3이 NULL상수인 경우에는, 데이터형 변환은 필요하지 않는다.  오라클은 expr2의 문자 세트에서 VARCHAR2를 반환한다.

 

• 만약 expr2가 수치인 경우에는, 오라클은 더 높은 수치 순위를 판단하여, 다른 인수의 데이터형을 암묵적으로 변환하여, 그 데이터형으로 반환한다.

예제

다음 예제는 employee테이블의 commission_pct 열이  NULL인지 여부에 의해서, 종업원의 수입이 급여와 수수료가 더해졌는지, 단지 급여로 구성되어 있는지 나타낸다.

SELECT last_name, salary, NVL2(commission_pct, 
   salary + (salary * commission_pct), salary) income
   FROM employees WHERE last_name like 'B%'
   ORDER BY last_name;

LAST_NAME                     SALARY     INCOME
------------------------- ---------- ----------
Baer                           10000      10000
Baida                           2900       2900
Banda                           6200       6882
Bates                           7300       8468
Bell                            4000       4000
Bernstein                       9500      11970
Bissot                          3300       3300
Bloom                          10000      12100
Bull                            4100       4100

접기

select empno, ename, comm, nvl(comm, 0)

  , nvl2(comm, comm, 0)

  from emp;

  select empno, ename, sal, comm

, sal*12 + nvl(comm, 0) as ann_sal

, decode(nvl(to_char(comm), 'null'), 'null', 'sal*12', 'sal*12+comm') as gubun

  from emp;

  select empno, ename, sal, comm, nvl2(comm, sal*12+comm, sal*12) as ann_sal, nvl2(comm, 'sal*12+comm', 'sal*12') as gubun

  from emp;

  select empno, ename, sal, comm

, sal*12 + nvl2(comm, comm, 0) as ann_sal, nvl2(comm, 'sal*12+comm', 'sal*12') as gubun

  from emp;

  * NVL2를 이용해서 NVL의 기능을 구현

select nvl(comm, 0)

    , nvl2(comm, comm, 0) 

from emp;

  * return type

    - NVL: expr1

    - NVL2: expr2

  - YY Date Format Element

  - RR Date Format Element

alter session set nls_language = american;

drop table df_test1 purge;

create table df_test1 (

 cen varchar2(5),

 naljja date);

alter session set nls_date_format = 'YYYY-MM-DD';

insert into df_test1 values ('20xx', '2012-01-09');

insert into df_test1 values ('00xx', '12-01-09');

insert into df_test1 values ('000x', '2-01-09');

insert into df_test1 values ('20xx', '01-09'); --> ORA-01840: 입력된 값의 길이가 날짜 형식에 비해 부족합니다

commit;

select * from df_test1;

insert into df_test1 values ('19xx', '1912-01-09');

insert into df_test1 values ('20xx', '2052-01-09');

insert into df_test1 values ('19xx', '1952-01-09');

commit;

select * from df_test1;

alter session set nls_date_format = 'YY-MM-DD';

select * from df_test1;

select * from df_test1

where naljja = '12-01-09';

select * from df_test1

where naljja = '52-01-09';

  - 1912년을 찾고 싶다.

select * from df_test1

where naljja = to_date('1912-01-09', 'YYYY-MM-DD');

    또는

select * from df_test1

where naljja = '1912-01-09';

    만약 애플리케이션에서 넘어온 날짜 값이 다음과 같다면: '09/01/1912'

select * from df_test1

where naljja = '09/01/1912';

↓ ↓ ↓ 

select * from df_test1

where naljja = to_date('09/01/1912', 'DD/MM/YYYY');

  - RR Format으로 변환

alter session set nls_date_format = 'RR-MM-DD';

select * from df_test1;

select * from df_test1

where naljja = '12-01-09';

select * from df_test1

where naljja = '52-01-09';

SQL> select * from df_test1

    where naljja = '2052-01-09';

==> 세기 정보를 함께 주면 RR date format이더라도 2052년을 찾을 수 있습니다.

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

drop table df_test2 purge;

create table df_test2 (

 d_format varchar2(5),

 naljja date);

alter session set nls_date_format = 'YY-MON-DD';

insert into df_test2 values ('YY', '12-JAN-09');

insert into df_test2 values ('YY', '77-SEP-09');

alter session set nls_date_format = 'RR-MON-DD';

insert into df_test2 values ('RR', '12-JAN-09');

insert into df_test2 values ('RR', '77-SEP-09');

commit;

select naljja from df_test2;

alter session set nls_date_format = 'RRRR-MM-DD'; --> RRRR은 YYYY와 똑같습니다.

select * from df_test2;

문제 1) 10번 및 20번 부서의 사원들의 정보를 아래와 같이 출력하세요. 

(단, 명시적 변환을 사용하세요)

사원번호 및 이름          급여            입사일

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

사번:7369 (SMITH)         800달러         1980년 12월 17일

사번:7566 (JONES)         2975달러        1981년 04월 02일

사번:7782 (CLARK)         2450달러        1981년 06월 09일

사번:7788 (SCOTT)         3000달러        1982년 12월 09일

사번:7839 (KING)          5000달러        1981년 11월 17일

사번:7876 (ADAMS)         1100달러        1983년 01월 12일

사번:7902 (FORD)          3000달러        1981년 12월 03일

사번:7934 (MILLER)        1300달러        1982년 01월 23일

해답)

select *

from emp

where deptno in (10, 20);

↓ ↓ ↓

column "사원번호 및 이름" format a25

col 급여 for a15

col 입사일 for a25

문제2) 위 결과에서 급여를 우측정렬하여 출력하세요

해답)

select '사번:'||to_char(empno)||' ('||ename||')' as "사원번호 및 이름"

, LPAD(to_char(sal)||'달러', 10) as 급여

, to_char(hiredate, 'YYYY"년 "MM"월 "DD"일"') as 입사일

from emp

where deptno in (10, 20);

SQL> select last_name, hire_date

    from employees

    where hire_date = to_date('June  7, 2002', 'fxMonth DD, YYYY');

==> ERROR : ORA-01858: a non-numeric character was found where a numeric was expected

SQL> l

 1    select last_name, hire_date

 2    from employees

 3*   where hire_date = to_date('June  7, 2002', 'fxMonth DD, YYYY')

SQL> c/June  7/June 7

 3*   where hire_date = to_date('June 7, 2002', 'fxMonth DD, YYYY')

SQL> /

 where hire_date = to_date('June 7, 2002', 'fxMonth DD, YYYY')

==> ERROR : ORA-01862: the numeric value does not match the length of the format item

SQL> l

 1    select last_name, hire_date

 2    from employees

 3*   where hire_date = to_date('June 7, 2002', 'fxMonth DD, YYYY')

SQL> c/7/07

 3*   where hire_date = to_date('June 07, 2002', 'fxMonth DD, YYYY')

SQL> /

↓ ↓ ↓ 

  * 결론: fx를 사용하면 format element 간의 공백과 자릿수를 맞춰주어야 합니다.

                    (June  7 => June 7, 7 => 07)

※으아...집에서 하니까 안되네...강사님도 여러번 하셨는데... 아무튼 자릿수를 잘 맞춰라~~

oj_SQL.txt

* format model : http://goo.gl/9fKed

    : a character literal that describes the format of datetime or numeric data stored in a character string

  * format elements

  * format model modifiers: toggle

    - fm (fill mode): no leading or trailing blanks in format elements

          <= TO_CHAR

    - fx (format exact): exact matching between the character data and the format model

          <= TO_DATE

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

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