혼자 공부하는 공간

[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #6 본문

자격증/SQLD

[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #6

god_z 2020. 8. 21. 16:01

<목차>

 

<< 함수 (FUNCTION) >>

* 함수는 아래와 같이 분류할 수 있다.

  1. 벤더에서 제공하는 내장 함수 (Built-In Function)

    * 벤더별로 차이점이 있지만, 핵심적인 기능들은 대부분의 벤더에서 제공.

    1. 함수의 입력 값이 단일행인 단일행 함수 (Single-Row Function)
      * 다중행 함수에서는 단일행 함수 사용 불가능
      * 단일행 함수에서는 다중행 함수 사용 가능

    2. 함수의 입력 값이 다중행인 다중행 함수 (Multi-Row Function)

      * 다중행 함수는 이후에 다룰 예정 : 추후 링크달겠습니다.

      1. 집계 함수 (Aggregate Function) : 링크

      2. 그룹 함수 (Group Function) : 링크

      3. 윈도우 함수 (Window Function) : 링크

  2. 사용자가 정의할 수 있는 사용자 정의 함수 (User Defined Function)

* 단일행 함수 (Single-Row Function)

  • 처리하는 데이터의 형식에 따라 문자형, 숫자형, 날짜형, 변환형, NULL 관련 함수로 나뉜다.

  • 특징

    * SELECT, WHERE, ORDER BY, UPDATE의 SET 절 등에 사용이 가능.

    * 각 행들에 대해 개별적으로 작용, 각 행에 대한 결과를 리턴.

    * 여러 인자를 입력해도 단 하나의 결과만을 리턴 ---> 다중행 함수도 하나의 결과만을 리턴

    * 함수의 인자로 상수, 변수, 표현식 사용 가능.

    * 함수의 인자로 함수 사용 가능.

  • 종류

단일행 함수의 종류

 

1. 문자형 함수

* 문자형 함수는 문자 데이터를 매개변수로 받아서 문자나 숫자 값의 결과를 리턴하는 함수.

  1. 종류

    단일행 문자형 함수의 종류

  2. 사례

    단일행 문자형 함수 사례

  3. 예시 - 문제 (문자열 길이)

    * 'SQL Expert' 라는 문자형 데이터의 길이를 구하라.

  4. 예시 - 결과 (문자열 길이 ; Oracle)

    1
    2
    3
    4
    5
    6
    7
    SELECT LENGTH('SQL Expert') len
    FROM DUAL;
    /*
    len
    ---
    10
    */
    		 cs

    * DUAL : Oracle의 SELECT문에서는 FROM절까지를 필수 절로 지정했기 때문에 테이블이 필요 없는 SQL문에도 DUAL이라는 일종의 더미 테이블을 지정해야 한다. 

  5. 예시 - 결과 (문자열 길이 ; SQL Server)

    1
    2
    3
    4
    5
    6
    SELECT LEN('SQL Expert') AS ColumnLength; 
    /*
    ColumnLength 
    ---------- 
    10
    */
    		 cs

    * SQL Server에서는 FROM절이 필수가 아님.

  6. 예시 - 문제 (문자열 연결)

    * 선수 테이블에서 CONCAT을 사용하여 선수이름 뒤에 '축구선수' 문자를 연결하라.

  7. 예시 - 결과 (문자열 연결)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    /* 모두 같은 기능 */
    /* Oracle, SQL Server 둘 다 허용 */
    SELECT PLAYER_ID, CONCAT(PLAYER_NAME, ' 축구선수') 선수명 
    FROM PLAYER;
     
    /* Oracle 에서만 허용 */
    SELECT PLAYER_ID, PLAYER_NAME || ' 축구선수' AS 선수명 
    FROM PLAYER;
     
    /* SQL Server 에서만 허용 */
    SELECT PLAYER_ID, PLAYER_NAME + ' 축구선수' AS 선수명 
    FROM PLAYER;
     
    /*
    PLAYER_ID 선수명 
    --------- ------------ 
    2011075   김성환 축구선수 
    2012123   가비 축구선수 
    2010089   강대희 축구선수 
    2007051   고종수 축구선수 
    2012015   고창현 축구선수 
    2009089   정기범 축구선수 
    2009083   정동현 축구선수 
    2011071   정두현 축구선수
    .....      ....
    480개의 행이 선택되었다.
    */
    		 cs

  8. 예시 - 문제 (문자열 연결 응용)

    * 경기장의 지역변호(DDD)와 전화번호(TEL)를 합친 번호의 길이를 구하시오.

  9. 예시 - 결과 (문자열 연결 응용)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    /* 모두 같은 기능 */
    /* Oracle 에서만 허용 */
    /* CONCAT 대신에 DDD||TEL 허용 */
    SELECT LENGTH(CONCAT(DDD, TEL)) AS T_LEN
    FROM STADIUM;
     
    /* SQL Server 에서만 허용 */
    /* CONCAT 대신에 DDD+TEL 허용 */
    SELECT LEN(CONCAT(DDD, TEL)) AS T_LEN
    FROM STADIUM;
     
    /*
    T_LEN 
    -----
    11
    11
    11
    11
    11
    12
    12
    11
    3
    ...
    20개의 행이 선택되었다.
    */
    		 cs

 

2. 숫자형 함수

* 숫자 데이터를 입력받아 처리하고 숫자를 리턴.

  1. 종류
    단일행 숫자형 함수 종류
  2. 사례
    단일행 숫자형 함수 사례

  3. 예시 - 문제 (반올림, 내림, 올림)

    * 사원의 연봉(SAL)에서 월급을 소수점 이하 한 자리까지 반올림 및 내림, 정수 기준으로 반올림 및 올림하여 출력하라.

  4. 예시 - 결과 (반올림, 내림, 올림)

     

    1
    2
    3
    /* 차례대로 소수점 한 자리까지 반올림, 소수점 한 자리까지 내림, 정수 기준 반올림, 정수 기준 올림 */
    SELECT ROUND(SAL/12,1), TRUNC(SAL/12,1), ROUND(SAL/12), CEIL(SAL/12)
    FROM EMP;
    		 cs

     

 

3. 날짜형 함수

* DATE 타입의 값을 연산하는 함수.

  1. 종류

    단일행 날짜형 함수 종류

  2. 연산

    * DATE 변수는 세기, 년, 월, 일, 시, 분, 초와 같은 숫자형식으로 변환되어 저장되기 때문에 산술 연산자로도 계산이 가능.

    단일행 날짜형 데이터 연산

  3. 예시 - 현재 날짜 확인

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    /* Oracle */
    SELECT SYSDATE FROM DUAL;
    /*
    SYSDATE
    -------- 
    12/07/18
    */
     
    /* SQL Server */
    SELECT GETDATE() AS CURRENTTIME; 
    /*
    CURRENTTIME 
    ----------------------- 
    2012-07-18 13:10:02.047
    */
    		 cs

  4. 예시 - 문제 (년, 월, 일 추출)
    * 사원(EMP) 테이블의 입사일자(HIDEDATE)에서 년, 월, 일 데이터를 각각 출력하라.

  5. 예시 - 결과 (년, 월, 일 추출)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    /* Oracle */
    SELECT EXTRACT(YEAR FROM HIDEDATE) 입사년도, EXTRACT(MONTH FROM HIDEDATE) 입사월, EXTRACT(DAY FROM HIDEDATE) 입사일
    FROM EMP;
     
    SELECT TO_NUMBER(TO_CHAR(HIDEDATE, 'YYYY')) 입사년도, TO_NUMBER(TO_CHAR(HIDEDATE, 'MM')) 입사월, TO_NUMBER(TO_CHAR(HIDEDATE, 'DD')) 입사일
    FROM EMP;
     
     
    /* SQL Server */
    SELECT DATEPART(YEAR, HIDEDATE) 입사년도, DATEPART(MONTH, HIDEDATE) 입사월, DATEPART(DAY, HIDEDATE) 입사일
    FROM EMP;
     
    SELECT YEAR(HIDEDATE) 입사년도, MONTH(HIDEDATE) 입사월, DAY(HIDEDATE) 
    FROM EMP;
     
    /*
    입사년도  입사월  입사일
    -------- ------  -----
    1980     11      6
    1991     7       13
    1994     1       28
    1995     8       31
    1999     12      10
    1998     10      15
    */
    		 cs

 

4. 변환형 함수

* 특정 데이터 타입을 다양한 형식으로 출력할 때 사용되는 함수.
* 두 가지의 방식이 존재.

데이터 유형 변환의 종류

* 암시적 변환의 경우 성능 저하가 발생할 수 있고, 자동적으로 변환하지 않는 경우가 있어 에러 발생의 요인이 되므로 명시적 변환 방식을 사용하는 것이 바람직하다.

  1. 명시적 변환에 사용되는 함수 종류

    단일행 변환형 함수의 종류

    * 변환형 함수를 사용할 때, 숫자형/날짜형의 경우 상당히 많은 포맷이 벤더별로 제공.

  2. 예시 (날짜 데이터를 문자 데이터로 변환)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    /* Oracle */
    SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') 날짜, TO_CHAR(SYSDATE, 'YYYY. MON, DAY') 문자형 
    FROM DUAL; 
    /* 
    날짜        문자형 
    ---------   ---------------- 
    2012/07/19  2012. 7월, 월요일 
    */
     
    /* SQL Server */
    SELECT CONVERT(VARCHAR(10),GETDATE(),111) AS CURRENTDATE 
    /*
    CURRNETDATE 
    ---------- 
    2012/07/19
    */
    		 cs

  3. 예시 (금액의 표현 변환)

    1
    2
    SELECT TO_CHAR(123456789/1200,'$999,999,999.99') 환율반영달러, TO_CHAR(123456789,'L999,999,999') 원화 
    FROM DUAL;
    		 cs

 

5. CASE 표현

* IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할을 한다.

단일행 CASE 표현의 종류

  1. 예시 - 문제

    * 부서 정보에서 부서 위치를 미국의 동부, 중부, 서부로 구분하라.

  2. 예시 - 결과

    * SIMPLE_CASE_EXPRESSION : CASE 다음에 바로 조건에 사용되는 칼럼이나 표현식(LOC)을 표시, 다음 WHEN 절에서 앞에서 정의한 칼럼이나 표현식과 같은지 다른지 판단하는 형식.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    SELECT LOC, 
    CASE LOC 
        WHEN 'NEW YORK' THEN 'EAST' 
        WHEN 'BOSTON' THEN 'EAST' 
        WHEN 'CHICAGO' THEN 'CENTER' 
        WHEN 'DALLAS' THEN 'CENTER' 
        ELSE 'ETC' 
    END as AREA 
    FROM DEPT;
     
    /*
    LOC       AREA 
    --------- -------- 
    NEW YORK  EAST 
    DALLAS    CENTER 
    CHICAGO   CENTER 
    BOSTON    EAST 
    4개의 행이 선택되었다.
    */
    		 c

  3. 예시 - 문제

    * 사원 정보에서 급여가 3000 이상이면 상등급, 1000 이상이면 중등급, 1000 미만이면 하등급으로 분류하라.

  4. 예시 - 결과 

    * SEARCHED_CASE_EXPRESSION : CASE 다음에는 칼럼이나 표현식을 표시하지 않고, 다음 WHEN 절에서 여러 조건을 이용한 조건절을 사용할 수 있기 때문에 SIMPLE_CASE_EXPRESSION 보다 더 다양한 조건을 적용할 수 있다.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    SELECT ENAME, 
    CASE 
        WHEN SAL >= 3000 
        THEN 'HIGH' 
        WHEN SAL >= 1000 
        THEN 'MID' 
        ELSE 'LOW' 
    END AS SALARY_GRADE 
    FROM EMP;
     
    /*
    ENAME  SALARY_GRADE 
    ------ ------------- 
    SMITH  LOW 
    ALLEN  MID 
    WARD   MID 
    JONES  MID 
    MARTIN MID 
    BLAKE  MID 
    CLARK  MID 
    SCOTT  HIGH 
    KING   HIGH  
    14개의 행이 선택되었다.
    */
    		 cs

  5. 예시 - 문제 (CASE 중첩)

    * 사원 정보에서 급여가 2000 이상이면 보너스를 1000으로, 1000 이상이면 500으로, 1000미만이면 0으로 계산하라.

  6. 예시 - 결과 (CASE 중첩)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    SELECT ENAME, SAL, 
    CASE 
        WHEN SAL >= 2000 THEN 1000 
        ELSE (CASE 
                WHEN SAL >= 1000 THEN 500 
                ELSE 0 
            END) 
    END as BONUS 
    FROM EMP;
     
    /*
    ENAME   SAL  BONUS 
    ------- ---- ------ 
    SMITH   800  0 
    ALLEN   1600 500 
    WARD    1250 500 
    JONES   2975 1000 
    MARTIN  1250 500 
    BLAKE   2850 1000 
    CLARK   2450 1000 
    SCOTT   3000 1000 
    KING    5000 1000 
    14개의 행이 선택되었다.
    */
    		 cs

 

6. NULL 관련 함수

  1. NVL/ISNULL 함수

    * NULL 값은 정의되지 않은 값으로 0 또는 공백과는 다름.

    * TABLE 생성 시, NOT NULL의 제약조건이 없거나 기본키로 정의되지 않은 데이터 유형이 NULL 값을 가질 수 있음.

    * NULL 값을 포함하는 연산의 경우, 결과 값도 NULL 값이다.

    * 결과 값이 NULL 값이 아닌 다른 값을 얻고자 할 때, NVL/ISNULL 함수를 사용.

    NULL 포함 연산의 결과

    * 종류

    단일행 NULL 관련 함수의 종류

    * 예시 (Oracle)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    SELECT NVL(NULL, 'NVL-OK') NVL_TEST 
    FROM DUAL;
    /*
    NVL_TEST 
    ------- 
    NVL-OK 
    1개의 행이 선택되었다.
    */
     
    SELECT NVL('Not-Null', 'NVL-OK') NVL_TEST 
    FROM DUAL; 
    /*
    NVL_TEST 
    ------- 
    Not-Null 
    1개의 행이 선택되었다.
    */
    		 cs

    * 예시 (SQL Server)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT ISNULL(NULL, 'NVL-OK') ISNULL_TEST ; 
    /*
    ISNULL_TEST 
    --------- 
    NVL-OK 
    1개의 행이 선택되었다.
    */
     
    SELECT ISNULL('Not-Null', 'NVL-OK') ISNULL_TEST ;
    /*
    ISNULL_TEST 
    --------- 
    Not-Null 
    1개의 행이 선택되었다.
    */
    		 cs

    * 예시 - 문제 (NVL, ISNULL)

      : 선수 테이블에서 K08의 TEAM_ID를 가지는 선수의 이름과 포지션을 출력하라. 포지션이 없는 경우 '없음'으로 표시

    * 예시 - 결과 (NVL, ISNULL)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    /* Oracle */
    SELECT PLAYER_NAME 선수명, POSITION, NVL(POSITION,'없음') 포지션 
    FROM PLAYER 
    WHERE TEAM_ID = 'K08'
     
    /* SQL Server */
    SELECT PLAYER_NAME 선수명, POSITION, ISNULL(POSITION,'없음') 포지션 
    FROM PLAYER 
    WHERE TEAM_ID = 'K08'
    		 cs

    * 예시 - 문제 (NVL, ISNULL)

      : 급여와 커미션을 포함한 연봉을 계산.

    * 예시 - 결과 (NVL, ISNULL)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    SELECT ENAME 사원명, SAL 월급, COMM 커미션, (SAL * 12) + COMM 연봉A, (SAL * 12) + NVL(COMM,0) 연봉B 
    FROM EMP;
     
    /*
    사원명   월급  커미션  연봉A  연봉B 
    ------- ----- ------ ------ ----- 
    SMITH    800                9600 
    ALLEN    1600  300   19500  19500 
    WARD     1250  500   15500  15500 
    JONES    2975               35700 
    MARTIN   1250  1400  16400  16400 
    BLAKE    2850               34200 
    CLARK    2450               29400 
    SCOTT    3000               36000 
    KING     5000               60000 
    TURNER   1500  0     18000  18000 
    ADAMS    1100               13200 
    JAMES    950                11400 
    FORD     3000               36000 
    MILLER   1300               15600 
    14개의 행이 선택되었다.
    */
    		 cs

    * 월급 * 12에 커미션을 더해서 연봉을 계산하는 식에서 커미션에 NULL 값이 있다.

      : NVL함수를 사용하지 않고 연봉A를 계산하면 산술연산에 NULL이 포함되어 연봉A가 NULL이 됨.

      : NVL함수를 사용하여 연봉B를 계산하면 커미션이 NULL일 때, 0으로 바꾸어 의도한 대로 계산됨.

  2. NULL 과 공집합

    * SELECT 1 FROM DUAL WHERE 1 = 2; 에서 조건에 맞는 결과가 없으므로 공집합을 발생시킨다. NULL과는 다르게 이해해야 한다.

    * 공집합은 NVL/ISNULL 함수를 이용해도 공집합이 그대로 출력된다.

    * 적절한 집계함수를 사용하여 NULL 값을 추출해낼 수 있다.

  3. NULLIF

    * NULLIF(EXPR1, EXPR2) : EXPR1과 EXPR2와 같으면 NULL, 같지 않으면 EXPR1 반환.

  4. 기타 NULL 관련 함수 (COALESCE)

    * COALESCE(EXPR1, EXPR2, ...) : 인수의 개수가 한정되어 있지 않음. EXPRn에서 NULL이 아닌 최초의 EXPRn을 출력한다. 모든 EXPRn이 NULL이라면 NULL 반환.

출처

http://www.dbguide.net/db.db?cmd=view&boardUid=148194&boardConfigUid=9&categoryUid=216&boardIdx=134&boardStep=1

 

데이터 전문가 지식포털 DBGuide.net

관계형 데이터베이스 개요 DDL DML TCL WHERE 절 함수(FUNCTION) GROUP BY, HAVING 절 ORDER BY 절 조인(JOIN) 1. 내장 함수(BUILT-IN FUNCTION) 개요 함수는 다양한 기준으로 분류할 수 있는데, 벤더에서 제공하는 함수�

www.dbguide.net

질문은 댓글로 남겨주시면 되겠습니다. 감사합니다.

저작자표시

'자격증 > SQLD' 카테고리의 다른 글

[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #8  (0) 2020.08.22
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #7  (0) 2020.08.22
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #5  (0) 2020.08.20
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #4  (0) 2020.08.16
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #3  (0) 2020.08.16
'자격증/SQLD' Related Articles more
Comments