싸미~*

SQL)강의- 제 6 장 GROUP 함수

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s06.htm

 

1. 그룹함수

    ◈그룹함수는 그룹당 하나의 결과를 return한다.
    ◈그룹함수들은 SELECT 및 HAVING절에 쓸 수 있다.
    ◈SELECT 문장상의 GROUP BY절은 행들을 그룹화한다.
    ◈ HAVING절은 그룹을 제외한다.
     

Syntax

      SELECT      column,group_function
      FROM        table
      [WHERE      조건]
      [GROUP BY   group_by_expression]
      [ORDER BY   column];

     

  그룹함수

    함       수

    설           명

    AVG(distinct|all|n)

    NULL값을 제외한 n개 행의 평균값

    COUNT(distinct|all|expr|*)

    행의 개수. expr은 NULL값이 아닌 것을 평가한다.
    사용하여 중복및 NULL값을 갖는 행들도 계산한다.

    MAX(distinct|all|expr)

    최대값

    MIX(distinct|all|expr)

    최소값

    STDDEV(distinct|all|n)

    NULL값을 제외한 n의 표준편차

    SUM(distinct|all|n)

    NULL값을 제외한 n의 합계

    VAR(distinct|all|n)

    NULL값을 제외한 n의 분산

    ◈DISTINCT는 해당함수로 하여금 오직 중복되지 않는 값만 출력한다.
    ◈그러나 all은 해당함수로 하여금 중복된 것을 포함한 모든 값을 출력한다.(기본값은 all이다.)
    ◈expr이 있는 인수들의 datatype은 CHAR, VARCHAR2, NUMBER 또는 DATE형이 될 수 있다.
    ◈COUNT(*)를 제외한 모든 그룹함수들은 NULL값을 무시한다.

    실습 : 모든 영업사원에 대해 평균 월급여액, 최고 월급여액, 최저 월급여액 및 합계를 출력하시오. 

    SQL> SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
              FROM   s_emp;

    Note : 숫자 datatype column에 대해서는 AVG와 SUM함수의 사용이 가능하다.
     

    실습 : 부서번호 31에 근무하는 사원의 수를 출력하시오. 

    SQL> SELECT COUNT(*)  FROM   s_emp   WHERE  dept_id = 31;

     

2. Group BY절

Syntax

      SELECT      column,group_function
      FROM        table
      [WHERE      조건]
      [GROUP BY   group_by_expression]
      [ORDER BY   column];

    ◈Group BY절을 사용하여 한 테이블의 행들을 원하는 그룹으로 나눈다.
    ◈Column명을 Group함수와 SELECT절에 사용하고자 하는 경우 Group BY뒤에 Column명을 추가한다.
    ◈ORDER BY절을 사용하여 기본 정렬 순서를 바꾼다.
    ◈기본적으로 행들은 GROUP BY에 명시된 column의 순서대로 올림차순 정렬이 일어난다.
    ◈Group BY뒤에 오는 column이 반드시 SELECT절에 있어야 되는 것은 아니다.
    ◈SELECT list 상에서 어떠한 컬럼이나 표현식도 그룹 함수가 아닌 경우에는 Group BY절에 와야 한다.
    ◈그룹을 제한하기 위해 WHERE절을 사용할 수 없다.(그룹을 제한하기 위해 HAVING절을 사용한다
    ◈Group BY 다음에 1개 이상의 column을 나열함으로써 그룹 or sub그룹에 대한 결과를 얻을 수 있다.
     

    실습 : 가능한 고객신용도 및 각 신용도 범주 내에 있는 고객들의 수를 출력하시오.

    SQL> SELECT credit_rating, COUNT(*) "고객신용도"  FROM   s_customer
              GROUP BY credit_rating

     

    실습 : 직급과 직급별 총 월급여액을 출력하되 부사장은 제외하고 총 월급여액순으로 출력하시오.

    SQL> SELECT title,SUM(salary) PAYROLL    FROM   s_emp
             WHERE  title NOT LIKE 'VP%'
             GROUP BY title   ORDER BY SUM(salary);

     

    실습 : 각 직급 그룹별로 최고 급여액과 직급을 출력하시오.

    SQL> SELECT title,MAX(salary)  FROM   s_emp
             GROUP BY title;

     

    실습 : 각 지역과 각 지역의 부서 수를 출력하시오.

    SQL> SELECT region_id,COUNT(name)  FROM   s_dept
             GROUP BY region_id

     

    실습 : 각 부서 내에서 각 직급 범주별로 직원 수를 출력하는 예

    SQL> SELECT dept_id,title,COUNT(*)      FROM   s_emp
             GROUP BY dept_id,title;

     

3. GROUP함수를 잘못 사용한 Query

    ◈SELECT상에서 어떤한 column이나 표현식도 그룹함수가 아닌 경우에는 GROUP BY절에 와야 한다.
    ◈그룹을 제한하기 위해 WHERE절을 사용할 수 없고 HAVING절을 사용하여야 한다.
    ◈SELECT문장에서 각각의 항목(아래에서 REGION_ID)과 그룹함수(아래 예의 COUNT)를복합해서
       사용시 각 항목을 명시하는 GROUP BY절을 포함해야 한다.
    ◈GROUP BY절 생략시 "not a single-group group funtion"이란 error message가 나타나고
        애스테리스크(*)가 위반한 Line을 지적해 준다.

    실습 : 각 지역내에서 부서의 수를 Display한다.

    SQL> SELECT   region_id, COUNT(name)     FROM     s_dept
              GROUP BY  region_id;

     

    실습 : 평균 급여액이 2000이 넘는 부서에 대해 부서번호 및 평균급여액을 Display한다.

    SQL> SELECT   dept_id, AVG(salary)     FROM     s_emp
              GROUP BY dept_id
              HAVING   AVG(salary) > 2000

     

4. 그룹내의 그룹

    실습 : 각 부서내에서 각 직급 범주별로 직원수를 Display한다.

    SQL> SELECT dept_id, title, COUNT(*)     FROM   s_emp
              GROUP BY dept_id, title;

     

    실습 : 평균 급여액이 2000이 넘는 부서에 대해 부서번호 및 평균급여액을 Display한다.

    SQL> SELECT title, dept_id, COUNT(*)    FROM   s_emp
             GROUP BY title, dept_id;

     

 5. HAVING절

    Syntax

      SELECT      column,group_function
      FROM        table
      [WHERE      조건]
      [GROUP BY   group_by_expression]
      [HAVING     group_condition]
      [ORDER BY   column];
       

    ◈여기서, group_condtion은 그룹을 제한하기 위한 조건이다.
    ◈그룹 제한시 HAVING절을 사용한다.
        -- Step 1 : 행들이 그룹핑된다.
        -- Step 2 : 그룹함수가 그룹에 적용된다.
        -- Step 3 : HAVING조건을 만족하는 그룹들이 display된다.


    ◎ HAVING절을 사용하면 SELECT상에서 그룹함수 없이 Group BY절을 사용할 수 있다.
    ◎ 그룹함수의 결과에 근거한 그룹 제한시 GROUP BY절 및 HAVING절을 사용한다.

    실습 : 월급여액이 4000이 넘는 부서들에 대해 모든 부서번호를 출력하시오. 

    SQL> SELECT dept_id,AVG(salary)
              FROM   s_emp
              GROUP BY dept_id
              HAVING AVG(salary) > 2000;

     

    실습 : 총 월급여액이 5000이 넘는 직급에 대해 직급명과 총 월급여액을 출력하시오.
                단, 부사장은 포함시키지 말고 결과를 총월급여액 순으로 정렬하시오.

    SQL> SELECT title,SUM(salary) "급여"    
             FROM   s_emp
             WHERE  title NOT LIKE 'VP%'
             GROUP BY title
             HAVING SUM(salary) > 5000
             ORDER BY SUM(salary);

     

신고

SQL)강의- 제 5 장 복수 테이블로부터 데이터 조회

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s05.htm

1. Join이란?

    ◈DB상에서 한 개 이상의 테이블로부터 데이터가 요구될 시 조인 조건이 사용된다.
    ◈한 테이블의 행들은 타 테이블의 column에 존재하는 공통된 값, 일반적으로 Primary key와
       Foreign key 값을 사용하여 조인한다.

     

Join 방법

        ◈주로 사용되는 두 개의 조인 방법

             1. Equijoin     :  = 로 조인한 경우
             2. Non-equijoin :  = 을 쓰지 않고 조인한 경우

        ◈추가적인 조인 방법

             1. Outer join  : 데이터가 한 쪽 테이블에만 있을 경우에 전체를 출력한다.
             2. Self join   : 자기 자신과 조인
             3. Set operators : union 등

     

Cartesian product

    ◈Cartesian product는 다음과 같은 경우에 발생된다.
    ◈조인 조건을 생략한 경우와 조인 조건이 잘못된 경우
    ◈첫번째 테이블의 모든 행들이 두번째 테이블의 모든 행과 조인이 되는 경우
    ◈Cartesian product를 피하기 위해서는 WHERE절에 정확한 조인 조건을 쓰도록 한다.
    ◈Cartesian product란?

      ◈모든 가능한 행들의 조합이 display된다.
      ◈첫번째 테이블의 모든 행들은 두번째 테이블의 모든 행들과 조인 된다.
       

    실습 : Catesian Product가 발생된 잘못된 조인 예

    SQL> SELECT name, last_name   FROM   s_dept, s_emp;

    ※ 현재 s_dept 테이블은 12개의 행, s_emp 테이블은 25개의 행으로 구성되어 있다.(12*25 = 300)

     

2. 단순 조인 Query(Equijoin)

    두개 이상의 관련 테이블로 부터 데이터를 출력 시키기 위하여 WHERE 절에 단순한 조인 조건문을 작성한다.

♣ Syntax

    SELECT   table.column, table.column          - 데이터를 검색할 테이블 column
    FROM     table1, table2
    WHERE    table1.column1 = table2.column2;    - 조인 조건


    ◈Guidelines

      ◈같은 column명이 한개 이상의 테이블에 존재하는 경우 column명은 해당 테이블명을
         붙여 사용한다.
      ◈테이블들을 함께 조인하기 위해서는 최소한 테이블 개수 1 조건의 개수가 필요하다.
         즉 4개의 테이블을 조인하기 위해 최소 3개의 조인 조건이 필요하다.
      ◈두 테이블 상에서 각 column의 값이 일치하여야 한다.
       

    실습 : s_emp테이블과 s_dept테이블을 조인하여 출력하시오.

    SQL> SELECT  s_emp.last_name, s_emp.dept_id, s_dept.name
             FROM    s_emp, s_dept
             WHERE   s_emp.dept_id = s_dept.id;

     

테이블명 사용

    ◈두개의 테이블상에서 같은 이름이 존재하지 않는 경우는 column명을 테이블명과 같이 쓸 필요가
       없으나 테이블명을 접두사로 사용함으로써 향상된 성능을 얻을 수 있다. 
     

    실습 : 모든 부서에 대해 부서번호, 지역번호 및 지역명을 출력하시오.

    SQL> SELECT s_dept.id, s_region.id, s_region.name
            FROM   s_dept, s_region
            WHERE  s_dept.region_id = s_region.id;

     

추가적인 검색 조건

    ◈조인 이외에 WHERE절에 추가적인 조건을 가질 수 있는데, AND연산자 등을 이용한다.
    ◈테이블 Alias는 SQL코드를 더 적게하므오 더 적은 메모리를 사용하게 해준다.

    실습 : Menchu라는 사원의 성, 부서번호 및 부서이름을 출력하시오.

    SQL> SELECT s_emp.last_name, s_emp.dept_id, s_dept.name
            FROM   s_emp, s_dept
            WHERE  s_emp.dept_id = s_dept.id
               AND INITCAP(s_emp.last_name) = 'Menchu';

     

테이블 Alias 사용

    ◈테이블 이름을 사용하여 column명을 명확히 할때 테이블 이름이 긴 경우 매우 많은 시간을 요할 때
       테이블의 alias로 column을 명확히 할 수 있다.
    ◈현재의 SELECT 문장에 대해서만 유효하다.
    ◈Alias를 작성한 경우 테이블 이름 대신에 테이블의 alias를 사용하여 columnm참조시 error발생을
       방지한다.

    실습 : 모든 고객에 대해 고객 이름, 지역번호 및 지역이름을 display 하고, 테이블 참조를 단축하기
               위해 테이블 alias를 지속적으로 사용해야 한다.

    SQL> SELECT  c.name, c.region_id, r.name
             FROM    s_customer c, s_region r
             WHERE   c.region_id = r.id;

     

    실습 : commission을 받는 모든 사원들의 성, 지역 이름 및 commission_pct를 출력하시오.

    SQL> SELECT  e.last_name, r.name, e.commission_pct
             FROM    s_emp e, s_dept d, s_region r
             WHERE   e.dept_id = d.id
                  AND  d.region_id = r.id  AND  e.commission_pct > 0;

     

3. Non-Equijoin

    ◈Non-Equijoin은 한 테이블의 어떤 column도 join할 테이블의 한 column에 직접적으로 일치하지
       않을 시 사용한다.
    ◈조인조건은 등등(=)이외의 연산자를 갖는다.
    ◈예를들어 EMP와 SALGRADE 사이의 관련성은 EMP 테이블의 어떠한 column도 직접적으로
       SALGRADE 테이블의 한 column에 상응하지 않기 때문에 Non-Equijoin이다.
    ※ <= 및 >= 같은 다른 연산자를 사용가능하나 BETWEEN이 가장 단순하다.
        BETWEEN 사용시 하한값을 먼저 명시하고 상한값을 나중에 명시한다는 것을 명심하라.


    실습 : 사원의 급여 등급을 평가하기 위한 Non-Equijoin을 사용하라.급여는 하한값과 상한값의
               사이에 있어야 한다.

    SQL> SELECT e.ename, e.sal, s.grade
             FROM   emp e, salgrade s
             WHERE  e.sal BETWEEN s.losal AND s.hisal;

     

4. Outer Join

    ◈정상적으로 조인 조건을 만족하지 못하는 행들을 보기위해 outer join을 사용한다.
    ◈조인시킬 값이 없는 조인측에서 "(+)"를 위치 시킨다. 즉, 정보가 부족한 조인측에 위치한다.
    ◈Outer join을 포함하는 조건은 IN 연산자를 사용할 수 없고, OR 연산자에 의해 다른 하나의 조건에
       연결 불가능하다.
    ◈(+)연산자는 한 개 이상의 NULL 행을 생성하고 정보가 충분한 테이블의 한 개 이상의 행들이 이런
       NULL 행에 조인된다.

♣ Syntax

      SELECT   table.column, table.column
      FROM     table1.table2
      WHERE    table1.column = table2.column(+);

        또는

      SELECT   table.column, table.column
      FROM     table1.table2
      WHERE    table1.column(+) = table2.column;

    ◈ 여기서, (+)는 Outer join 기호이다. 이 기호는 WHERE절 조건의 어느측에 올 수 있으나
        양측 모두에는 올 수 없다. Outer join 연산자를 일치하는 행이 없는 테이블쪽에 두라. 
     

♣ Outer join의 제약사항

    ◈Outer join은 표현식의 한 측에만 올수 있는데 정보가 부재한 쪽에 둔다.
       다른 테이블의 어떠한column과도 일치하지 않는 한 테이블의 행을 돌려준다.
    ◈Outer join을 포함하는 조건은 IN 연산자를 사용할 수가 없고 OR 연산자에 의해 다른 하나의 조건에
       연결 불가능하다. 
     

    실습 : 모든 고객에 대해 담당 영업사원 이름과 사번 및 고객 이름을 출력하라.
               이때 고객을 담당하는 담당 영업사원이 없더라도 고객 이름을 출력하도록 하라.

    SQL> SELECT e.last_name, e.id, c.name
             FROM   s_emp e, s_customer c
             WHERE  e.id(+) = c.sales_rep_id
             ORDER BY e.id;

     

5. Self Join

    ◈Self join을 사용하여 한 테이블의 행들을 같은 테이블의 행들과 조인한다.
    ◈한개의 테이블을 두 개의 별도의 테이블처럼 사용하기 위해 테이블 alias를 사용하여 한 테이블을
       자체적으로 조인가능하다.
    ◈FROM절 상에서 두 개의 테이블을 시뮬레이트하기 위해 아래 예는 같은 테이블인 S_EMP 테이블에
       대해 alias를 사용하고 있다.
    ◈이 예에서 WHERE절은 사원의 관리자 번호가 관리자에 대한 직원번호와 일치하는것을 의미하는 조인
       조건을 포함하고 있다.
     

    실습 : 사원의 이름과 각자의 관리자를 출력하시오.

    SQL> SELECT  worker.last_name||' 관리자 '||manager.last_name
             FROM    s_emp worker, s_emp manager
             WHERE   worker.manager_id = manager.id;

     

신고

SQL)강의- 제 4 장 단일 행에 대한 SQL 함수

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s04.htm

 

♣ SQL 함수의 개요

    함수는 SQL의 매우 강력한 기능으로 아래와 같은 일을 할 경우에 사용한다.
    ◈데이터에 대해 계산을 수행할 경우
    ◈각각의 데이터 항목을 변경할 경우
    ◈그룹의 결과 출력
    ◈출력할 날짜형식을 변경할 경우
    ◈column datatype을 변경할 경우
     

♣ 2가지의 SQL함수

    ◈단일행 함수 : 이 함수는 단일행에 대해서만 적용 가능하고 행 당 한 개의 결과를 돌려준다.
          -문자, 숫자, 날짜, 변환

    ◈복수행 함수 : 이 함수는 복수의 행을 조작하여 복수의 행에 대해 하나의 결과를 돌려준다.

 

1. 단일 행 함수

    ◈단일 행 함수는 데이터 항목을 조작하기 위해 사용된다.
    ◈데이터에 관한 계산을 수행할 경우나 데이터 항목을 조작하기 위해 사용.
    ◈한 개 이상의 인수(Argument)를 받아들여 한 개의 행 당 하나의 값을 Return한다.
    ◈인수 - 상수, 가변적인 값, 변수, 표현식
    ◈참조시 사용한 datatype과 다른 datatype의 데이터 값을 돌려줄 수 있다.
    ◈함수를 중첩하여 사용할 수 있다.
    ◈SELECT, WHERE 및 ORDER BY절에도 함수를 사용할 수 있다.
    Syntax : Function_name (column|expression, [arg1,arg2,…])

    항목

    설명

    function_name

    함수의 이름

    Column

    데이터베이스에 존재하는 column명

    Expression

    어떤 문자스트링 또는 계산된 표현식

    Arg1, arg2

    함수에 의해 사용될 인수(들)

     

2. 문자 함수

    ◈단일 행 문자 함수들은 문자데이터를 입력으로 받아들여 문자나 숫자 결과 값을 돌려 준다.

    함수

    목 적

    LOWER(column\expression)

    알파벳 문자를 소문자로 바꿈.
    예> LOWER(‘SQL Course’)→sql course

    UPPER(column\expression)

    알파벳 문자를 대문자로 바꿈
    예> UPPER(‘SQL Course’)→SQL COURSE

    INITCAP(column\expression)

    알파벳 각 단어의 첫자는 대문자로, 나머지는 소문자로 바꿈
    예> INITCAP(‘SQL Course’)→Sql Course

    CONCAT(column1\expression1,
    Column2\expression2)

    첫번째의 문자값과 두번째의 문자값을 연결한다.('||'와 동일)
    예> CONCAT(‘Good’,’String’)→GoodString

    SUBSTR(column\expression,m,[,n])

    문자값 중에서 m위치에서 n문자길이의 문자를 돌려줌.
    예>SUBSTR(‘STRING’,1,3)→STR

    LENGTH

    문자갯수를 값으로 돌려 줌.
    예>LENGTH(‘’) →6

    NVL(column1\expression1,
    olumn2\expression2)

    첫번째 값이 NULL 이면 두번째 값으로 바꾸어 준다.
    예> SELECT ename,NVL(to_char(bonus),’NO BONUS’)FROM emp;

     

    실습 : 성이 Patel인 모든 사원의 성과 이름을 출력 하시오.

    SQL> SELECT first_name, last_name FROM s_emp
             WHERE UPPER(last_name) = 'PATEL'

     
    실습 : 성명은 소문자로, USERID의 첫 자만 대문자로 그리고 직급은 대문자로 출력

    SQL> SELECT LOWER(first_name||' '||last_name) VP,
             INITCAP(userid) USER_ID, UPPER(title) TITLE
             FROM s_emp   WHERE title LIKE 'VP%';

     
    실습 : 신용도가 “GOOD”인 모든 고객의 이름과 Country를 합성시켜 출력 하시오.

    SQL> SELECT CONCAT(name,country) CUSTOMER
             FROM s_customer
             WHERE credit_rating = 'GOOD';

     

3. 숫자 함수

    ◈숫자 함수는 숫자값을 받아들여 숫자값을 되돌려 준다.

    함수

    목적

    ROUND(columm/expression,n)

    Column.expression의 값을 소수점 n자리까지 반올림 한다. 만약 n이 없다면 소숫점은 없어진다. 또한 n값이 음수라면 소수점의 왼쪽 자리수만큼 반올림된다.

    TRUNC(columm/expression,n)

    Column.expression의 값을 소수점 n자리까지 절삭한다.
    만약 n이 없다면 소숫점은 나타나지 않는다. 또한 n값이 음수라면 소숫점의 왼쪽 자리수만큼 절삭한다

    MOD (m,n)

    m값을 n값으로 나누고 남은 나머지를 Return


    실습 : 45.923을 소수점 2자리, 0, -1자리까지 반올림한 값을 출력 하시오.

    SQL> SELECT  ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)
             FROM    dual;

     
    실습 : 45.923을 소수점 2자리, 0, -1자리까지 절삭한 값을 출력 하시오.

    SQL> SELECT  TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1)
             FROM    dual;


    실습 : 월급여가 1400보다 큰 모든 사원에 대해 월급여를 상여금으로 나누고 난 나머지를 계산하라.

    SQL> SELECT last_name, MOD(salary,commission_pct)
             FROM s_emp
             WHERE salary > 1400;

     

4. 날짜 산술 연산

    ◈결과로 날짜 값이 나오게 날짜에 숫자를 더하거나 날짜로부터 숫자를 뺀다.
    ◈두 개의 날짜 사이의 일수를 알기 위해 날짜에서 날짜를 뺀다.
    ◈시간을 날짜에 더한다.
     

♣ 날짜에 산술 연산자 사용

    ◈데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술연산자를 사용하여 계산을 할 수 있다.
    ◈날짜뿐만 아니라 숫자상수를 더하거나 뺄 수 있다.

    연산

    결과

    설명

    Date + number

    Date

    날수를 날짜에 더한다.

    Date - number

    Date

    날짜에서 날수를 뺀다.

    Date ? date

    Number of days

    다른 하나의 날짜에서 하나의 날짜를 뺀다.

    Date + number/24

    date

    시간을 날짜에 더한다.


    실습 : 부서번호 43에 속하는 사원의 성과 입사일부터 지금까지 근무한 근무주 수를 출력하라.

    SQL> SELECT last_name, (SYSDATE - start_date) / 7 WEEKS
             FROM s_emp
            WHERE dept_id = 43;

     

SYSDATE

    ◈SYSDATE는 현재의 날짜와 시각을 돌려주는 날짜 함수이다.(소수점 이하는 시간이다.)
    ◈예를 들어 Dummy테이블인 DUAL에서 SYSDATE를 선택할 수 있다.
     

DUAL 테이블

    ◈DUAL은 SYSDATE를 보기 위해 사용하는 Dummy 테이블이다.
    ◈DUAL 테이블은 사용중인 테이블에서 유래하지 않는 상수값, Pseudo column, 표현식등의 값을
       단 한번만 돌려 받기를 원할 경우에 유용하다,
    실습 : 현재 날짜를 출력 하시오.

    SQL> SELECT SYSDATE      FROM DUAL;

     

5. 날짜 함수

    ◈날짜 함수는 오라클의 날짜에 대해 연산을 한다.
    ◈모든 날짜 함수는 DATE 자료형의 값을 돌려주는데 MONTHS_BETWEEN은 예외적으로 숫자값을
       돌려준다.

    함수

    목적

    MONTHS_BETWEEN(date1,date2)

    두 날짜 사이의 달 수.
    결과의 소수점부분은월이며, 음수 값을 가질 수 있다.
    예> MONTHS_BETWEEN(‘01-SEP-98’,’11-JAN-97’)→16.6774194

    ADD_MONTHS(date,n)

    날짜에 n달 수를 더한다.
    n은 정수이어야 하며 음수가 가능하다.
    예> ADD_MONTHS(’11-JAN-98’,6)→ ‘11-JUL-98’

    NEXT_DAY(date,’char’)

    명시한 날짜이후의 첫번째 해당요일(‘char’) 일자.
    예>NEXT_DAY(‘04-FEB-98’,’FRIDAY’) →’06-FEB-98’

    LAST_DAY(date)

    date를 포함하고 있는 달의 마지막 날을 돌려 준다.
    DAY(‘04-FEB-98’) → ‘28-FEB-98’

    ROUND(date[,’fmt’])

    정오를 기준으로 날짜 반올림.
    예> ROUND(‘25-MAY-98’,’MONTH’) → ‘01-JUN-98’
    예> ROUND(‘25-MAY-98’) →‘25-MAY-98’

    TRUNC(date[,’fmt’])

    날짜 절삭(날짜에서 시간부분을 제거).
    예> TRUNC(‘25-MAY-98’,’YEAR’)→’

    ※ Note : 위는 이용 가능한 날짜 함수 중 일부분이다. 형식 모델은 이 장 나중에 다루어진다.
                 형식 모델의 예는 달 또는 년이다.
    실습 : 근무경력이 48개월 미만인 사원들의 사번,입사일,근무월수 및 입사 6개월되는 날짜를 출력 하시오.

    SQL> SELECT  id,start_date,  MONTHS_BETWEEN(SYSDATE,start_date) TENURE,
                      ADD_MONTHS(start_date,6) REVIEW
             FROM    s_emp   
             WHERE   MONTHS_BETWEEN(SYSDATE,start_date) < 80;

     

★ 전환 함수

    ◈전환 함수는 많은 요소들로 구성된 형식 모델(FORMAT)을 사용할 수 있다.

    함수

    목적

    TO_CHAR(number\date, [‘fmt’])

    숫자 및 날짜 스트링을 문자스트링으로 바꾸어 준다.
    예> TO_CHAR(sysdate,’YYYY/MM/DD’)→1998/2/4

    TO_NUMBER(char)

    숫자를 포함하고 있는 문자스트링을 숫자로 바꾸어 준다.

    TO_DATE(char[,‘fmt’])

    날짜의 문자스트링을 날짜값으로 바꾸어 준다.
    예> TO_DATE(’04-FEB-1998’,’YYYY/MM/DD’)

     

6. TO_CHAR 함수

Syntax

    TO_CHAR(date, 'fmt')

    ◈단일 인용부호(single quotation)로 에워싸야 하며 대소문자 구분이 있다.
    ◈어떤 유효한 날짜 형식 요소도 포함가능하다.
    ◈덧붙여진 공백이나 선행제로를 없애기 위한 fm 형식을 갖고 있다.
    ◈콤마에 의해 날짜값과 구분한다.
     

♣ 날짜형식 사용시 TO_CHAR함수(특정 형식으로 날짜 Display 하기)

    ◈이전에는 모든 오라클의 날짜값은 DD-MON-YY 형식으로 보여졌다.
    ◈TO_CHAR 함수는 이런 기본 형식의 날짜값을 명시한 형식으로 전환해 준다.
    ◈형식 모델은 단일 인용부호로 에워싸야 하고 대소문자 구분이 있다.
    ◈형식 모델은 어떠한 유효한 날짜 형식 요소를 포함할 수 있고, 날짜값은 콤마에 의해 구분한다.
    ◈결과에 일,월의 이름은 자동적으로 공백으로 덧붙여진다.
    ◈덧붙여지는 공백 및 선행 제로를 없애기 위해서는 자릿수채움 모드 "fm"을 사용하시오.
    ◈COLUMN명령을 사용하여 결과로 나오는 문자필드의 Display폭을 재결정할 수 있다.
    ◈결과로 나오는 column의 폭은 기본적으로 80자이다.
    실습 : 영업사원 11에 의해 받은 모든 주문에 대해 주문번호, 주문날짜를 출력 하시오.
                (날짜형식 "08'92")

    SQL> SELECT  id,TO_CHAR(date_ordered,'MM/YY') ORDERED
             FROM    s_ord
             WHERE   sales_rep_id = 11;

     

    ◈ 실제 날짜Type형태는거의 사용하지 않고 있다.(시스템 날짜만 사용하고 있음)

7. 숫자 사용시 TO_CHAR 함수

♣ Syntax

    TO_CHAR(number, 'fmt')

    number의 값에 있어 앞에 0이 있으면 9을 제거하고 출력한다.
     

♣ 숫자 사용할 때 TO_CHAR함수

    문자 스트링 같은 숫자값으로 일을 할 때 그런 숫자를 TO_CHAR함수를 사용하여 문자 자료형으로 전환해야 하는데 TO_CHAR함수는 NUMBER 자료형을 VARCHAR2자료형의 값으로 전환해준다. 이런 기법은 특히 합성시(concatenation) 유용하다.
     

♣ 숫자 형식 요소(total 값이 1234인 경우)

    ◈문자를 숫자로 변환하기를 원한다면 아래와 같은 숫자형식 요소를 사용할 수 있다.

    요소

    기술

    예(값이 1234인 경우)

    결과

    9

    숫자(9는 Display 폭을 결정한다)

    TO_CHAR(total,'999999')

    1234

    0

    0을 출력 하시오.

    TO_CHAR(total,'099999')

    001234

    $

    달러 기호를 나타낸다.

    TO_CHAR(total,'$999999')

    $1234

    L

    지역 화폐 기호를 나나낸다.

    TO_CHAR(total,'L999999')

    \1234

    .

    명시한 위치에 소수점을 출력한다.

    TO_CHAR(total,'999999.99')

    1234.00

    ,

    명시한 위치에 코마를 표시한다.

    TO_CHAR(total,'999,999')

    1,234

    ◈오라클7 서버는 형식 모델에 의해 제공하는 자릿수를 넘는 숫자에 대해서는 파운드 기호(#) 스트링을 보여준다.

    ◈오라클7 서버는 저장된 소숫값을 형식 모델에서 제공 하는 소숫점 자리수로 반올림 시켜준다.

    실습 : 1992년 9월 21일날 선적되어야 하는 주문이 완료되었다는 메시지를 출력하라.
    각 주문별 합계와 주문번호를 반드시 포함하라.

    SQL> SELECT '주문번호 '||TO_CHAR(id)||' 선적 완료 합계 '
                 ||TO_CHAR(total,'$9,999,999') NOTE
             FROM   s_ord   WHERE  date_shipped = '21-SEP-92';
     
    or
     
    SQL> SELECT '주문번호 '||TO_CHAR(id)||' 선적 완료 합계 '
                ||TO_CHAR(total,'fm$9,999,999') NOTE
         FROM   s_ord
       WHERE  date_shipped = '21-SEP-92';

     

8. TO_NUMBER와 TO_DATE 함수

    ◈문자 스트링을 TO_NUMBER와 TO_DATE 함수를 사용하여 숫자 형식으로 전환한다.
           TO_DATE(char[, 'fmt'])
    ◈문자 스트링을 TO_NUMBER와 TO_DATE 함수를 사용하여 날짜 형식으로 전환한다.

      - TO_DATE('10 September 1992', 'dd Month YYYY')
      - Use format elements
      TO_DATE(char[, 'fmt'])
       

♣ TO_NUMBER와 TO_DATE 함수

    ◈문자스트링을 숫자나 날짜형식으로 변환하기를 원할 수도 있다.
    ◈이러한 일을 수행하기 위해 TO_NUMBER 또는 TO_DATE 함수를 각각 사용할 수 있다.
       선택할 형식은 앞페이지에서 보여진 형식의 형태를 따른다.
    실습 : 1992년 9월 7일날 받은 모든 주문을 보여라. 여기서 보고자 하는 날짜 스트링을 날짜 형식으로
                전환하라.

    SQL> SELECT id, total, date_ordered     FROM   s_ord
        WHERE date_ordered = TO_DATE ( 'September 7, 1992', 'Month dd, YYYY' );

     

9. 단일행 함수의 중첩

    ◈단일행 함수들은 여러 레벨에 걸쳐 중첩이 가능하다.
    ◈중첩된 함수들은 가장 하위 레벨에서 가장 상위 레벨 순으로 진행된다.

    실습 : 직급이 부사장인 사원의 성을 부서명과 합성하여 출력한다. 이때 column heading은 Vice Presidents로 한다.

    SQL> SELECT CONCAT(UPPER(last_name),SUBSTR(title,3)) "부사장"    
             FROM s_emp
             WHERE title LIKE 'VP%'

     

    실습 : 자기위에 관리자가 없는 회사의 사장을 출력하라.

    SQL> SELECT last_name, NVL(TO_CHAR(manager_id),'관리자 없음')   
         FROM   s_emp
         WHERE  manager_id IS NULL;

     

    실습 : 주문일로부터 6개월이 지난 달의 다음 금요일의 날짜를 출력하라.
               결과로 나올 날짜는 "Friday, March 12th, 1993"와 같은 형태로 보여져야 한다.

    SQL> SELECT   
    TO_CHAR(NEXT_DAY(ADD_MONTHS(date_ordered,6),'FRIDAY'),
                  'fmDay, Month ddth, YYYY') "New 6 Month Review"
         FROM     s_ord
         ORDER BY date_ordered

     

    ◈내부 함수를 수행한다.: Result1=ADD_MONTHS(date_ordered,6)
    ◈그 다음 함수를 평가한다. : Result2=NEXT_DAY(Result1, 'FRIDAY')
    ◈외부 함수를 평가한다. : Result3=TO_CHAR(Result2, 'fm Day, Month ddth, YYYY')

신고

SQL)강의- 제 3 장 SQL *Plus 명령어

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s03.htm
 

♣ 테이블 구조 조회(DESC 명령)

    ◈SQL*Plus의 DESCRIBE명령은 테이블의 구조를 표시한다.
          SQL>DESCRIBE 테이블명
    ◈NOT NULL인 column은 반드시 값을 가져야 한다.
    ◈Column, Datatype 및 길이(length)
    ◈명령의 결과는 column명, datatype 및 column이 NULL값을 허용하는지를 알 수 있다.

    실습 : S_DEPT 테이블의 구조에 관한 정보를 출력 하시오.

    SQL> DESCRIBE s_dept;

     

1. SQL *Plus편집 명령어

    ◈SQL문장을 수행하고 나면 SQL buffer에 마지막으로 수행된 SQL문장이 저장된다.
    ◈SQL buffer에 저장되어 있는 SQL문장은 SQL*PLUS의 EDIT command를 이용하여 EDIT를 한다.
    ◈SQL buffer에는 맨마지막으로 수행된 단 1개의 SQL문장만이 남아있다.

    Command

    설    명

    A[PPEND] text

    현재 Line의 마지막에 text를 추가한다

    C[HANGE]/old_text/new_text

    old_text를 new_text로 변경한다.

    C[HANGE]/text/

    현재 행으로부터 Text를 삭제한다.

    CL[EAR] BUFF[ER]

    SQL버퍼로부터 모든 라인을 삭제한다.

    DEL

    SQL buffer에 남아있는 SQL문장을 삭제한다.

    I[NPUT]

    SQL buffer의 마지막에 새로운 Line을 추가

    L[IST]

    SQL buffer에 남아있는 모든 SQL문장을 보여준다.

    L[IST] m n

    m에서 n라인까지의 내용을 보여 준다.

    R[UN]

    SQL buffer의 내용을 display하고 수행한다.

    n

    n라인으로 현재 위치를 변경시켜 준다.

    n text

    n 으로 지정된 Line을 text로 변경한다.

    0 text

    1 라인 전에 하나의 라인을 삽입한다.

    ◈명령어 작성중간에 [RETURN]키를 누르면 SQL*Plus는 라인번호와 함께 프람프트를 내어 준다.
    ◈터미네이터 문자(세미콜론 또는 슬레쉬)중 하나를 입력하거나 (RETURN)키를 두 번 누름으로
       SQL 명령어 입력을 끝낼 수 있다. 이때 SQL 프람프트를 볼 수 있다.

     

 2. SQL *Plus 파일 명령어

    ◈SQL*Plus는 환경제어, Query결과 Format설정 및 파일관리를 위해 아래 명령어들을 제공한다.

    Command

    설 명

    SAVE filename
    [REPLACE]

    SQL buffer의 내용을 filename으로 FILE에 저장한다.
    (REPLACE 명령어를 사용하면 저장하고자 하는 filename이 있으면 file의 내용을 SQL buffer의 내용으로 변경하고, 없으면 새로운 file을 만든다)

    GET filename

    filename.sql로 저장된 내용을 출력 하시오

    START(/) filename

    filename.sql로 저장된 file을 수행한다

    @filename

    filename.sql로 저장된 file을 수행한다(START와 동일)

    RUN

    SQL buffer의 내용을 display하고 수행한다.

    ED(IT) filename

    저장된 파일의 내용을 편집하기 위해 편집기를 실행한다.

    EXIT

    SQL *Plus를 중단한다.

    SPOOL filenamel[off]

    향후 수행될 sql 문의 결과를 저장한다

    HOST

    SQL *Plus를 종료하지 않고도 HOST 운영체제의 명령어를 실행한다.

    ◈Note : SQL *Plus 변수 _EDITOR의 값을 DEFINE명령을 사용하여 변경함으로써
                Text 편집기를 변경할 수 있다.
    ◈파일명을 쓰지 않고 EDIT명령어를 사용할 때, 기본적인 파일명은 afiedt.buf이다.
        이 파일은 버퍼를 편집할 때마다 이 파일에 겹쳐쓰게 된다.
        현재의 디렉토리에 없는 파일명을 명시하면 SQL*Plus는 파일 이름을 묻는다.

    실습 : 따라하기

    SQL> SELECT first_name, last_name, title
         FROM s_emp
         WHERE dept_id = 41;
    SQL> save a
    SQL> GET a
    SQL> START a

     

3. SET 환경 명령어

SQL *Plus SET 명령어 Syntax

      SET 시스템변수값

    ◈SET명령어는 현재의 세션 환경을 제어한다.
    ◈로그인시 login.sql에는 실행되는 표준 SET 명령과 그 외의 SQL*Plus명령이 들어 있다.
    ◈login.sql을 수정하여 부가적인 SET 명령을 쓸 수 있다.
    ◈여기서 시스템변수는 세션 환경을 제어하는 변수이고, 값은 시스템변수의 값임

    ♠실습 : Login.sql 확인(C:\orawin95\doc\Login.sql)

    SQL> GET Login.sql    

         set numwidth 9
         set linesize 100
         set pagesize 24
         set timing on

     

  login.sql 파일을 이용한 기본값 설정

    ◈login.sql 파일에는 접속 때마다 필요한 표준 SET 명령과 그 외의 SQL*Plus 명령어들이 들어 있다. 접속할 때에 이 파일을 읽어서 명령어가 수행된다. 로그 아웃을 하면 사용자 정의 설정이 상실된다.
     

기본값 설정 변경

    ◈login.sql로 수행된 설정은 현재의 세션에서 변경될 수 있다. 해당 세션에 대해서만 변경된다. 로그 아웃을 하자마자 설정값이 상실된다. 설정값을 영구적으로 변경하려면 login.sql 파일에 추가한다.
     

♣ 유용한 SET 명령어

    SET 변수와 값

    설              명

    ARRAYSIZE {20|n}

    데이터베이스의 데이터 인출 크기 설정

    COLSEP    {|text}

    Column사이에 출력될 문자를 설정

    ECHO      OFF|ON

    파일이 수행될 때 SQL *Plus명령어의 표시 여부를 결정

    FEEDBACK  {6|n|OFF|ON}

    Feedback 설정

    HAEDING   {OFF|ON}

    Column의 Hading 출력여부를 결정

    LINESIZE  {80|n}

    보고서의 라인당 문자의 수를 설정

    LONG      {80|n}

    LONG값을 표시하는 최대 폭을 결정

    PAGESIZE  {24|n}

    출력 페이지당 라인의 수를 명시

    PAUSE     {OFF|ON|text}

    단말기의 화면 이동을 제어함

    ◈ 예를 들어, SHOW PAUSE와 같이 현재의 설정 값을 보려면 SHOW 명령문을 사용한다.
    ◈ 모든 SET 변수값을 보려면, SHOW ALL 명령어를 사용한다.
    ◈ n값은 숫자값을 나타낸다.
    ◈ 밑줄 그은 값은 기본값을 나타낸다. 만일 변수에 값이 없으면, SQL*Plus는 기본값을 사용한다.

     

4. SQL *Plus 포멧 명령어

◈보고서의 특성을 제어할 수 있다.

    명  령  어

    옵  션                       설        명

    COLUMN [Column][옵션]

    CLEAR              Column의 형식을 WLDNA

    FORMAT형식        Column의 데이터 표시형식 설정

    HEADING text       Column의 Heading 설정

    JUSTIFY {정렬}     Column의 Headin을 왼쪽, 중앙, 오른쪽으로 정렬

    예> COL  last_name HEADING 'Employee|Name ' FORMAT A15
    예> COL  salary JUSTIFY LEFT FORMAT $99,999.00

    TTITLE [text|OFF|ON]

    보고서의 각 페이지 상단에 나타나는 머리글을 명시
    예> TTITLE 'Salary|Report'

    BTITLE [text|OFF|ON]

    보고서의 각 페이지 하단에 나타나는 머리글을 명시

    BREAK [ON]
       element[action]]

    라인 넘김을 써서 중복값을 배제

    COLUMN        Break가 일어날 column명을 명시한다.

    REPORT      총계를 내도록 보고서 레벨의 그룸을 생성 한다.

    SKIP n|PAGE  Break값의 변경시 n개의 행을 건너뛰거나 새로운 페이지로 이동한다.

    DUPLICATES    중복값을 출력한다.

    NODUPLICATES      중복값을 출력하지 않는다.

    예> BREAK ON title SKIP 1 ON RREPORT

    COMPUTE 함수 [LABEL]…
       OF  column…
       ON  break column

    합계을 계산함.
    column명, 식 또는 column의 alias
    column명, 식, column의 alias, REPORT키워드

    예> BREAK ON title SKIP 1 ON REPORT
        COMPUTE SUM LABEL 'Subtotal' OF salary ON title

     

스크립트 파일

    ◈명령어의 값을 포맷하고 재설정하는 데에 사용되는 SQL SELECT 문장과 Plus 명령어로 구성된
       스크립터 파일(또는 명령어 파일)을 만들 수 있다.
    ◈스크립터 파일은 문장에 대한 특정한 SQL 문장과 Plus 설정 값을 저장하는 데 유용하다.
    ◈추가적으로, 기본적인 Plus 명령어를 갖는 파일을 생성할 수 있는데 언제든지 실행하여 설정 값을
       재설정할 수 있다. 이 파일은 명령어 파일 안에서 실행 될 수 있다.
    ◈모든 형식 명령어는 Plus 세션이 끝나거나 형식 설정이 변경될 때까지 유효하다.
    ◈보고서를 작성한 후에는 Plus의 설정을 기본값으로 재설정하라.
    ◈SQL*Plus 변수 값을 기본값으로 설정하기 위한 명령은 없다.
    ◈특정 값을 알지 못하면 로그아웃을 하고 나서 다시 로그인해야 한다.
    ◈ Column에 대한 alias를 주는 경우, Column 이름이 아닌 alias를 참조해야 한다.
     

COLUMN 명령

    ◈COLUMN 명령을 사용하여 보고서의 Column의 표시 형식을 제어한다. 예를 들어, Heading,폭,
       형식을 변경할 수 있다.
          COL[UMN] [{Column|alias} [옵션]]

    ◈COLUMN명령어 옵션

    옵션

    설명

    CLE[AR]

    Column의 형식을 지움.

    FOR[MAT] 형식

    Column 데이터의 표시 형식을 설정.
    예:COLUMN last_name
    예:COLUMN last_name CLEAR

    HEA[DING] 텍스트

    Column의 Heading 설정.
    예:COL last_name HEADING 'Employee|Name' FORMAT A15
    예:COL salary JUSTIFY LEFT FORMAT $99,990.00

    JUS[TIFY] {정렬}

    Column의 Heading을 왼쪽,중앙,또는 오른쪽에 정렬함.

    ※ 명령이 길다면, 현재의 행 끝에 하이픈(-)을 써서 다름 행에 계속 쓸 수 있다.
     

Column 형식 모델 요소의 예

    요소

    설명

    결과

    An

    문자와 날짜 Column에 대해 n만큼의 폭으로 출력 설정

    N/A

    N/A

    9

    자리표현수

    999999

    1234

    0

    앞에 0을 붙임

    909999

    01234

    $

    달러 부호의 표시

    $9999

    $1234

    L

    국내 통화 표시

    L9999

    L1234

    .

    소숫점의 위치를 표현

    9999.99

    1234.00

    ,

    천 단위 구분자 표시

    9,999

    1,234

    ※ Oracle7 Server는 결과 값이 형식 모델에서 제공하는 정수의 자리수를 초과하면
        파운드 부호(#) 문자열을 출력한다.
     

TTITLE과 BTITLE 명령

    ◈페이지 머리글을 형식화하는 데에는 TITLE 명령을 사용하고, BTITLE 명령은 바닥글을 형식화하는
       데에 사용한다.  바닥글은 PAGESIZE값에 따라 페이지의 끝에 출력된다.
          TTI[TLE] [[텍스트|변수]  [OFF|ON]  또는 BTTI[TLE] [[텍스트|변수]  [OFF|ON]
    ◈날짜는 왼쪽, 텍스트는 중앙, 페이지번호는 오른쪽끝에 기본 설정값을 출력한다.
    ◈여러 행에 결쳐 제목에 대한 텍스트를 쓰려면 ( | )를 사용한다.
    ◈설정값의 출력과 해제

    명령어

    설명

    TTITLE

    머리글에 대한 현재의 설정값을 출력.
    예: SQL> TTI 'Salary | Report'

    TTITLE OFF

    머리글을 출력하지 않음.

    HEA[DING] BTITLE

    바닥글에 대한 현재의 설정값을 출력.

    BTITLE OFF

    바닥글을 출력하지 않음.


BREAK 명령

    ◈BREAK 명령을 사용하여 보고서를 보기좋게 할 수 있다.
    ◈BREAK 명령어는 column에 대해 중복값을 감추고,변경할때마다 한 행씩 건너뛴다.
    ◈COMPUTE 명령에 사용될 시 그룹을 생성한다.
         BRE[AK]  [ON element [action]]
    ◈BREAK명령어 옵션

    단위요소 또는 작업

    설명

    COLUMN

    Break가 일어날 Column명을 명시한다.

    REPORT

    총계를 내도록 보고서 레벨의 그룹을 생성한다.

    SKI[P] n|PAGE

    Break값의 변경시 n개의 행을 건너뛰거나 새로운 페이지로 이동한다.

    DUP[LICATES]

    중복값을 출력한다.

    NODUP[LICATES]

    중복값을 출력하지 않는다.


♣ COMPUTE 명령

    ◈COMPUTE 명령을 써서 합계와 같은 함수를 수행할 수 있다.
    ◈함수 :  AVG, COUNT, MAXIMUM, MINIMUM, STD
    ◈COMPUTE 명령에 사용될 시 그룹을 생성한다.

      COM[PUTE]  함수  [LAB[EL] 텍스트]…

             OF   column….    ON   break cloumn
       

    실습 : 직급별로 상원의 급여 합계를 계산한다.

    SQL> compute  sum of  SALARY on title


    실습 : 부서번호별로 직원수 및 평균급여를 계산한다.

    SQL> comp  count avg of  SALARY on dept_id report

     

보고서 수행하기 위한 스크립트 파일 생성 단계

    1. SQL 프롬프트에서 SQL SELECT문을 생성한다.
    2. SELECT 문장을 스크립트 파일에 저장한다.
    3. SQL *Plus 명령을 추가하기 위해 스크립트 파일을 저장한다.
    4. SELECT문장 전에 필요한 Format을 설정한다.
    5. SELECT문장 뒤에 ";"이나 "/" 같은 실행문자를 입력한다.
    6. SELECT문장 이후에 포멧팅 명령을 해제하는 SQL *Plus명령을 추가한다.
    7. 변경된 스크립트 파일을 저장한다.
    8. 스크립트를 실행하기 위해 START파일명이나 @파일명을 입력하여 스크립트 파일을 실행한다.
     

♣ 보고서 생성

    ◈머리글과 바닥글 생성
    ◈그룹 사이에 페이지 구분과 라인 넘김 추가
    ◈데이터 출력형식 변경
    ◈중복 데이터 제거
    ◈소계와 총계의 표시
    ◈Column의 Heading변경

    실습 : 부사장이나 창고 관리자가 아닌 모든 사원의 직급,성 및 월급을 출력하는 보고서를 만드는
               스크립트 파일을 작성한다.
               동일한 직급은 1번만 출력하고 직급별 그룹마다 한 행을 띄울 수 있도록 명령 파일을 편집하고,
                각 직급별 급여 소계및 모든 사원 급여의 총계를 계산.

    REM ** This report covers all employees who are not
    REM ** Vice presidents or warehouse manages.
    SET ECHO OFF
    SET PAGES 37                                  -- 1페이지에 37줄 출력
    SET LINES 60                                    -- 1줄에 60자 출력
    SET FEEDBACK OFF
    TTI 'Employee|Report'  
    BTI 'Confidential'
    COL title          HEA 'Job Category' FORMAT A22
    COL last_name HEA 'Employee'      FORMAT A22
    COL salary      HEA 'Salary'           FORMAT $99,999.99
    BREAK ON title SKIP 1 ON REPORT
    COMPUTE SUM LABEL 'Subtotal'        OF salary ON title
    COMPUTE SUM LABEL 'Grand Total' OF salary ON REPORT
    REM **Insert SELECT statement
    SELECT title, last_name, salary
    FROM s_emp
    WHERE title NOT LIKE 'VP%'
    AND title <> 'Warehouse Manager'
    ORDER BY title, last_name, salary
    /

     

신고

SQL)강의- 제 2 장 데이터베이스로부터 자료검색

.Tech/SQL

출처 : http://naver.kaist.ac.kr/oracle_sql/s02.htm 

1. 기본적인 Query문

Syntax

    SELECT      [DISTINCT] {*,column [alias]…..}
    FROM        테이블명
    WHERE       Query조건(들)
    [ORDER BY   {column, 표현식}  {ASC|DESC}];


    4개의 기본적인 절로 구성

    설  명

    SELECT절

    적어도 한 개의 column으로 구성된 리스트이다.
    DISTINCT : 중복 행을 1개 만 출력한다.
    *      : 모든 Column을 선택한다.
    Alias : 선택된 column에 대해 다른 이름을 부여한다.

    FROM절

    대상 테이블을 적는다.

    WHERE절

    Query조건을 만족하는 행들만을 Return하도록 한다.
    (column, 표현식, 상수 및 비교 연산자들로 구성된다.)

    ORDER BY

    Query된 행들이 Display되는 순서를 명시한다.
    ASC|DESC : 올림차순|내림차순으로 행들을 정렬한다


SQL 명령 작성 규칙

    ◈SQL 명령어들은 여러 라인에 걸쳐 입력 가능하다.
    ◈절들은 보통 판독성과 편집의 용이를 추구하기 위해 각기 다른 라인에 적는다.
    ◈명령어는 여러 라인에 걸쳐 분리할 수 없으며 약식표기 할 수 없다.
    ◈키워드 및 명령어는 전형적으로 대문자로 입력하며 모든 다른 단어들은 예를 들자면 테이블 명,
        column 명 등은 소문자로 입력한다.
    ◈SQL 명령어들은 대소문자 구별이 불필요하다.
    ◈SQL 명령은 SQL 프롬프트에서 입력하며 뒤에 오는 라인들은 번호가 부여되는데
       이를 SQL 버퍼라고 부른다.
     

버퍼내에서 SQL 문장을 실행시키는 방법

    ◈마지막 절 끝에 세미콜론(;)을 찍는다.
    ◈버퍼 내에 있는 마지막 라인에 세미콜론을 찍거나 슬래쉬(/)를 입력한다.
    ◈프롬프트에서 슬래쉬를 입력한다.
    ◈SQL 프롬프트에서 SQL *Plus의 RUN 명령을 입력한다.
     

모든 column, 모든 행 선택

    ◈가장 기본적인 SELECT 문장은 아래사항을 포함해야 한다.
    ◈Display 시킬 column들을 명시하기 위한 SELECT절
    ◈SELECT절에서 명시한 column들을 갖고 있는 테이블을 명시하기 위한 FROM절.
     

    ♠실습 : DEPT테이블로부터 모든 행과 모든 column을 출력 하시오

      SQL> SELECT * FROM S_DEPT;

     

특정 column 선택

    ◈SELECT절에서 column명들을 명시하고 콤마에 의해 분리 함으로서 특정 column들만 출력되게
       Query를 제한할 수 있다.
    ◈테이블의 column명을 알기 위해 SQL*Plus의 DESCRIBE명령을 사용한다.
    ◈DESCRIBE 명령을 사용하여 column명을 확인할 수 있다.
    ◈SELECT 절에 column명들을 입력한다.
    ◈콤마를 사용하여 column들을 분리한다.
    ◈보기 원하는 순서대로 column명을 적는다.

    Default column Heading
    ◈ 레이블 정렬
        -좌측 정렬 : 날짜 및 문자 데이터
        -우측 정렬 : 숫자 데이터
    ◈ 레이블은 기본적으로 대문자로 보여진다.
     

    실습 : DEPT테이블를 DESCRIBE 명령으로 column들을 조회한 후 부서명과 부서번호를 출력 하시오.

    SQL> desc s_dept;
    SQL> select id, name   from s_dept;

     

2. 산술표현식

    ◈데이터가 출력 되는 방식을 변경하거나, 계산을 수행하고자 할 때 사용할 수 있다.
    ◈산술 표현식은 column명, 상수 숫자 값 및 산술 연산자를 포함한다.
     

산술 연산자

    ◈SQL에서 이용 가능한 산술 연산자는 +, -, *, / 이며
    ◈FROM절을 제외한 모든 SQL 문장에서 산술연산자를 사용할 수 있다

    실습 : 모든 사원의 연봉액수 및 commission percentage를 보여주기 위해 곱셈 연산자를 사용하라.

    SQL> select last_name, salary*12, commission_pct      from s_emp;

    ※Column SALARY*12의 계산 결과는 원래 테이블의 새로운 column이 아니라 단지 출력 용도로만
       사용되고 있음을 주시하라.
     

연산자 우선순위

    ◈곱셈 및 나눗셈이 덧셈 및 뺄셈보다 우선한다.
    ◈괄호는 우선순위 평가를 변경 시 또는 문장들을 명확하게 하기 위해 사용된다.
    ◈같은 우선순위의 연산자들은 왼쪽에서 오른쪽으로 수행된다.

     

    실습 : 사원의 이름, 급여, 연봉(급여의 12달분과 $100의 보너스의 합)을 출력 하시오.

    SQL> select last_name, salary, 12*salary+100     from s_emp;

     

    실습 : 사원의 이름, 급여, 연봉을 출력 하시오. 단, 월 보너스가  $100이다.

    SQL> select last_name, salary, 12*(salary+100)      from s_emp;

 

♣ 합성 연산자

    ◈사용자는 합성 연산자( || )를 사용하여 column을 다른 column, 산술 표현식 또는 상수 값에
        연결하여 문자 형태로 표현할 수 있다.
    ◈연산자 양편의 column들은 단일 결과 column으로 출력된다.
    ◈문자 표현식의 결과에 의해 새로운 column을 생성한다.

    실습 : Column Heading Employees로 하여 사원의 완전한 이름(Full name)을 Display 하라.

    SQL> select first_name||last_name AS "사원이름"       from s_emp;

♣ 리터럴 문자열

    ◈리터럴이란 column명, alias가 아닌 SELECT리스트에 포함된 문자,표현식 또는 숫자.
    ◈리터럴이란 column명, alias가 아닌 SELECT 리스트에 포함된 문자, 표현식 또는 숫자이다.
    ◈날짜 및 문자 값은 단일 인용부호(‘‘)로 둘러 쌓여야 하지만, 숫자 리터럴은 그럴 필요가 없다.
    ◈각 문자 column은 되돌려지는 각 행 당 하나씩 출력된다.

    ♠실습 : Column heading을 ’로 하고 사원의 이름(Full name)과 직급을 출력 하시오. 
               이때 구두점을 추가하여 이름과 직급이 구별되도록 하라.

    SQL> select first_name||last_name||'.'||title "사원명/직급"       from s_emp;

3. Column Aliases

    ◈Column alias는 column의 Heading을 새로이 변경한다.
    ◈column명 바로 뒤에 온다.(column명과 alias사이에 AS키워드를 사용할 수도 있다.)
    ◈이중 인용부호(Double quotation)는 alias가 공백, 특수문자를 포함할 경우와 대소문자 구분이
       필요할 경우 사용된다.
    ◈검색결과를 Display할 때, SQL*Plus는 보통 선택한 column명을 heading으로 사용한다.
    ◈column alias을 사용하여 column heading을 변경할 수 있다.
    ◈alias는 SELECT 리스트상에서 column명 뒤 공백을 사용하여 구분하고 alias를 적는다.
    ◈기본값으로 alias heading은 대문자로 출력되고 공백을 포함할 수 없는데 alias가 이중 인용부호
       안에 적혀 있을 시는 예외이다.

    실습 : 사원의 성,월급여 및 연봉을 Display 하라. 연봉은 월급여에다 월보너스금액인 $100을
               더한 데다 12를 곱하여 구하라. 그 column의 heading을 ANNUAL_SALARY로 출력하라

    SQL> select last_name, salary, 12*(salary+100) AS ANNUAL_SALARY
              from s_emp;

    ※ Note 사용자는 ANSI SQL92의 표준을 준수하기 위해 alias앞에 AS 키워드를 포함시킬 수 있다.
     

이중인용 부호(“ “)를 갖고 있는 column alias

    ◈alias가 공백,특수문자(예 #또는_)를 포함하거나 또는 대소문자를 구분해 한다면
       alias은 이중 인용부호(" ")로 에워싸라.

    실습 : 위 예에서 heading을 ’로 출력 하시오.(이중인용 부호 사용)

    SQL> select last_name, salary, 12*(salary+100) "Annual Salary"
         from s_emp;

 

4. NULL 값 관리와 NVL 함수

    ◈column에 데이터 값이 없으면 그 값 자체가 NULL 또는 NULL 값을 포함하고있다.
    ◈NULL 값은 이용할 수 없거나 할당되지 않은 값이며, NULL 값이란 공백이란 문자이다.{0은 숫자}
    ◈NULL 값은 1바이트의 내부저장 장치를 오버헤드로 사용하고 있으며 어떠한 datatype column들이라도
       NULL 값을 포함할 수 있다
    ◈ [column이 not NULL로 정의되어 있거나 테이블 생성시 primary key로 정의되어 있을 시는 예외]
    ◈산술 표현식에서의 NULL 값 표현식에서 column값이 NULL 값이면 결과는 NULL이 된다.

    실습 : 성, 월급여, 직급, 및 계산된 상여금을 출력.

    SQL> select last_name, title, salary*commission_pct/100 COMM
         from s_emp;

NVL함수

    ◈null값을 다른 값으로 대치하기 위해 NVL함수를 사용한다.
    ◈Syntax
        NVL  (expr1, expr2)
              expr1  : NULL값을 포함할 수 있는 값이거나 표현식이다.
                 expr2  : NULL값을 대치 시 사용할 값
    ◈NULL값을 전환하기 위한 NVL함수 예

      datatype

      전환 예

      NUMBER

      NVL(number_column,9)

      DATE

      NVL(date_column, '01-JAN-95')

      CHAR or VARCHAR2

      NVL(characrter_column, 'Unavailable')

       

    실습 : 위의 예에서 사원에 대한 상여금 계산시  NULL값을 0으로 대치하시오.

    SQL> select last_name,title, salary*NVL(commission_pct,0)/100 COMM
         from s_emp;

5. 중복 행 선택 방지(DISTINCT 키워드)

    ◈기본적으로 질의문은 중복된 행을 출력.
    ◈SELECT 절에서 SELECT 명령어 바로 뒤에 DISTINCT 키워드를 입력하면 중복 행을 모두 제거한다.
    ◈DISTINCT는 SELECT 모든 선택된 column에 영향을 준다.

    실습 : s_emp테이블의 모든 직급을 출력.

    SQL> select DISTINCT title FROM s_emp;


    실습 : S_EMP 테이블에서 직급과 부서번호가 다른 모든 사원을 출력 하시오.

    SQL> SELECT DISTINCT dept_id, title   FROM s_emp;

6. ORDER BY 절에 의한 사용한 행들의 정렬

    ◈ORDER BY절은 SELECT 명령에서 마지막에 위치.
    ◈기본적인 정렬순서는 올림차순.
    ◈사용자는 표현식 또는 alias에 의하여 정렬.
    ◈NULL값은 다음과 같이 출력.

      - 올림차순시 가장 마지막에 옴.
      - 내림차순시 가장 먼저옴.

    ◈Syntax
        SELECT expr
        FROM table Default
        [ORDER BY {column, expr} [ASC||DESC]];
     

기본적인 데이터의 정렬

    실습 : 모든 사원의 이름 부서 번호, 입사일의 정보를 사원의 이름을 기준으로 출력.

    SQL> SELECT last_name, dept_id, start_date         FROM s_emp  
             ORDER BY last_name;


    ◈나열한 column의 위치를 이용하여 결과를 정렬할 수 있다.()
    ◈이는 긴 표현식에 의한 정렬 시 유용하다. 즉, SELECT 상에서 표현식의 위치만 명시하면 된다.

    실습 : 사원명세를 Display 시키되 가장 연봉이 적은 순으로 정렬하라.

    SQL> SELECT last_name, salary*12          FROM s_emp  
             ORDER BY 2;

♣ Multiple Column에 의한 정렬

    ◈한 개 이상의 column에 의해 Query 결과를 정렬할 수 있다.
    ◈이는 ORDER BY절에서 column명을 명시하고 콤마를 사용하여 column명을 구분한다

    실습 : 모든 사원을 부서번호부터 정렬하고, 급여가 제일 많은 사람부터 되도록 하라.

    SQL> SELECT last_name, dept_id, salary          FROM s_emp
             ORDER BY dept_id, salary DESC;

 

7. 조건 Query(선택할 행의 제한)

    ◈WHERE절을 사용하여 행들을 제한할 수 있다
    ◈WHERE절은 FROM절 다음에 온다.
    ◈조건은 아래의 것으로 구성된다.
         - column 명, 표현식, 상수, 비교 연산자, 문자(Literal)

    ♣ Syntax

      SELECT expr
      FROM table
      [ORDER BY condition]
      [ORDER BY {column, expr} [ASC||DESC]];

    ◈ condition은 column명, 표현식, 상수 및 비교연산자로 구성된다
     

비교연산자

    ◈비교 연산자들은 2가지 범주로 나누어지는데 즉 논리 연산자와 SQL 연산자이다.
    ◈이런 비교 연산자들은 하나의 표현식을 다른 하나의 표현식과 비교하기 위해 WHERE절에서 사용한다.
    Syntax
        ...WHERE expr operator value
    ◈논리 연산자는 =,    >,    >=,    <,  <=  조건을 check한다.
     

SQL 연산자

    연산자

    의미

    BETWEEN...AND...

    두 개의 값 사이(두개의 값도 포함)

    IN(list)

    리스트의 값중 어느 하나라도 일치

    LIKE

    문자 형태와 일치

    IS NULL

    널값인 경우

    ◈모든 Datatype에 대해 연산가능한 4가지의 SQL연산자가 있다.
    ◈LINK연산자는 어떤 경우 BETWEEN비교 연산자와 유사하게 이용된다.
     

논리 연산자 : AND,   OR,  NOT
 

부정 논리 연산자

    연산자

    설명

    !=

    같지 않는(VAX,UNIX,PC)

    ^=

    같지 않는(IBM)

    <>

    같지 않는(모든 운영체제)

    NOT colname =

    같지 않는

    NOT colname >

    ∼보다 크지 않는


부정 SQL 연산자

    연산자

    설명

    NOT BETWEEN...AND...

    두 개의 명시된 값 사이가 아닌

    NOT IN(list)

    명시된 값의 리스트에 없는

    NOT LIKE

    비교 스트링과 같지 않는

    IS NOT NULL

    널값이 아닌

    ◈ 만약 사용자가 알려진 값을 널값과 비교하자면 IS 또는 IS NOT NULL 비교 연산자를 사용한다.
        널값을 다른 연산자를 사용해 비교하면 결과는 항상 FALSE이다.
    ◈예를 들자면 COMM!=NULL은 항상 거짓이다. 왜냐하면 널값은 어떤 다른값과 같거나 같지 않을
       수가 없기 때문이다.
     

    실습 : WHERE 조건의 예

    연산자 사용 예

    설 명

    WHERE BR_CODE 〓 501

    BR_CODE가 501인것

    WHERE BR_CODE != 501 또는(<> 501)

    BR_CODE가 501이 아닌것

    WHERE BR_CODE 〉 501

    BR_CODE가 501보다 큰것

    WHERE BR_CODE 〈〓 501

    BR_CODE가 501이하인것

    WHERE BR_CODE BETWEEN 510 AND 520

    BR_CODE가 510과 520사이

    WHERE BR_CODE NOT BETWEEN 510 AND 520

    BR_CODE가 510과 520사이가 아닌것

    WHERE BR_CODE IN (510, 520)

    BR_CODE가 510과 520인것

    WHERE JOB_DATE LIKE '%95'

    JOB_DATE가 '95년도 인것※ LIKE 는 CHAR나 DATE TYPE에만 사용이 가능하다

    WHERE CHUL_ID IS NULL

    CHUL_ID가 NULL인것

    WHERE CHUL_ID IS NOT NULL

    CHUL_ID가 NULL이 아닌것

    ◈WHERE문 내에서 AND와OR를 조합하여 여러가지 조건문을 만들어 사용할수 있다.
    ◈LINK연산자는 어떤 경우 BETWEEN비교 연산자와 유사하게 이용된다.
    ◈만약 사용자가 알려진 값을 널값과 비교하자면 IS 또는 IS NOT NULL 비교 연산자를 사용한다.
    널값을 다른 연산자를 사용해 비교하면 결과는 항상 FALSE이다.
       예를 들자면 COMM!=NULL은 항상 거짓이다.
       왜냐하면 널값은 어떤 다른값과 같거나 같지 않을 수가 없기 때문이다.
     

문자 스트링과 날짜

    ◈문자 스트링과 날짜는 단일 인용부호(Single quotation)에 의해 둘러 쌓인다.
    ◈숫자값은 인용부호가 필요 없다.
    ◈문자값은 대소문자 구분이 필요하다.
    ◈기본 날짜 형신은 'DD-MON-YY'이다.
    ◈문자 스트링과 날짜는 WHERE절을 단일 인용 부호('')에 의해 에워 쌓여야 한다.

    실습 : 사원 성이 ‘김’인 사원의 이름과, 성, 직책을 출력 하시오.

    SQL> SELECT first_name, last_name, title FROM s_emp
              WHERE first_name = '김';

 

BETWEEN 연산자

    ◈어떤 범위내의 값에 포함되었는지를 CHECK하기 위해 BETWEEN 연산자를 사용한다.
    ◈사용자는 BETWEEN 연산자를 사용하여 어느 범위의 값을 가진 행들을 Display 할 수 있다.
    ◈사용자가 명시하는 범위는 하한값과 상한값을 포함한다.
     

    실습 : 입사일자가 1991년 5월 9일에서 1991년 6월 17일 사이에 있는 사원의 성,이름 및 입사일자를
               Display 한다.

    /* DATE type이 영문인 경우 */
    SQL> SELECT first_name, last_name, start_date  
             FROM s_emp
             WHERE start_date BETWEEN ‘09-may-91’ AND ‘17-jun-91’;
     
    /* DATE type이 한글인 경우 */
    SQL> SELECT first_name, last_name, start_date  
             FROM s_emp
             WHERE start_date BETWEEN '91/05/09' AND '91/06/17';

IN 연산자

    ◈리스트 상의 값에 포함되었는지를 CHECK하기 위해 IN을 사용한다.

    실습 : 지역번호가 1이거나 3인 부서들의 부서번호,부서명,지역번호를 출력 하시오.

    SQL> SELECT id,name,region_id FROM s_dept
              WHERE region_id IN (1,3);

 LIKE 연산자

    ◈유효한 Query 스트링값으로 와일드카드 사용시 LIKE 연산자를 사용한다.
    ◈LIKE 연산자는 BETWEEN 비교연산자와 유사하게 이용된다.
    ◈Query 조건은 리터럴 문자 또는 숫자를 포함할 수 있다.
    ◈%(와0개 이상의 어떤 문자를 의미한다.) “_”(단일문자를 의미한다.)
       기호는 리터럴 문자와 어떤 조합으로도 이용 가능하다.
    ◈사용자가 “_” 문자를 검색하고자할 경우에는 ESCAPE 명령어를 사용한다.

    실습 : 성이 “M”자로 시작하는 모든 사원의 성을 출력 하시오.

    SQL> SELECT DEPT_ID, FIRST_NAME, LAST_NAME  
             FROM S_EMP
             WHERE LAST_NAME LIKE '만%';


    실습 : 1991년도에 입사한 사원의 입사일자를 출력 하시오.

    SQL> SELECT last_name, start_date  
              FROM s_emp
              WHERE start_date LIKE '91%';


    실습 : 고객이름에 “X_Y”를 갖는 고객 이름을 출력 하시오.
               (Test를 위하여 다음에 배울 Update문을 먼저 사용하였다)

    SQL> update s_customer  
             set name = 'Unisports X_Y Test'
             where name LIKE 'Unispo%';

    SQL> SELECT name  
             FROM s_customer
             WHERE name LIKE '%X\_Y%' ESCAPE'\';

 

IS NULL 연산자

    ◈IS NULL 연산자를 사용해 NULL값을 Check한다.
    ◈반드시 = 연산자를 사용하여서는 안된다.

    실습 : 담당 영업사원를 갖고 있지 않는 모든 고객들의 고객번호, 고객이름 및 신용도를 출력 하시오.

    SQL> SELECT id,name,credit_rating  
             FROM s_customer
             WHERE sales_rep_id IS NULL;

 

8. 다중 조건에 의한 Query와 우선순위

    ◈여러개의 질문조건들을 묶어 복잡한 질문조건을 명시할 필요가 있는데 이때 AND와 OR연산자가
        복합논리 표현식을 만들기 위해 사용된다.
    ◈AND연산자는 모든 조건이 참일시 참이고 OR연산자는 어느 하나의 조건이라도 참일시 참이다.

    실습 : 부서번호 41에 근무하는 모든 재고 담당 사원의 성, 월급여, 부서번호 및 직급을 출력한다.

    SQL> SELECT last_name, salary, dept_id, title  
         FROM s_emp
         WHERE dept_id = 41 AND title = 'Stock Clerk'

     

    실습 : 부서번호 41에 근무하거나 직급이 Stock Clerk인 사원의 성, 월급여, 부서번호 및 직급을
               출력한다.

    SQL> SELECT last_name, salary, dept_id, title  
             FROM s_emp
             WHERE dept_id = 41 OR title = 'Stock Clerk';

     

 ♣ 우선 순위 규칙

    ◈여러분은 같은 논리 표현식에 AND와 OR연산자를 합성할 수가 있다.
    ◈모든 조건들의 결과는 연결연산자의 우선순위에 의해 결정되는 순서로 조합된다.
    ◈동등한 우선순위의 연산자가 바로 옆에 사용될시 좌측에서 우측으로 수행된다.    각 AND는 먼저
       수행되고 그 다음에 각 OR가 수행된다. AND는 OR보다 더 높은 우선순위를 가지고 있다.
    ◈괄호를 사용하여 우선 순위를 변경 할 수 있다.
    ◈여러 연산자들이 나열되었을때 우선순위 규칙
       1 순위 : 모든 비교 연산자(=, <>, >, >=, <. <=, in, LIKE, IS NULL, BETWEEN)
       2 순위 : AND
       3 순위 : OR

    실습 : 부서번호 44인 부서 사원 중에서 월급여가 1000이상이 되는 사원과 42부서에 속하는
               전사원을 출력 하시오.

    SQL> SELECT last_name,salary, dept_id  
             FROM s_emp
             WHERE salary >= 1000 AND dept_id = 44 OR dept_id = 42;

     

      ♠실습 : 44부서나 42부서 사원 중에서 월급여가 1000이상이 되는 사원들을 출력 하시오.

    SQL> SELECT last_name,salary, dept_id  
         FROM s_emp
         WHERE salary >= 1000 AND (dept_id = 44 OR dept_id = 42);

     

신고