[SQL] Advanced SQL - 날짜 함수 활용

Advanced SQL - 날짜 함수 활용

01. emp테이블에서, 입사 일자(hiredate)컬럼을 이용하여 연도, 월/일, 요일, 분기를 검색하시오. 단, 입사 일자는 월요일 부터 일요일 순으로 정렬합니다.

해답 SQL문

SELECT empno, ename,

    TO_CHAR(hiredate,'YYYY') Year,

    TO_CHAR(hiredate,'DD, Month') AS "Date",

    TO_CHAR(hiredate,'Day') Day,

    TO_CHAR(hiredate, 'Q') Quarter

FROM emp

ORDER BY TO_CHAR(hiredate-1,'D');

-- 실행 결과--

14 rows selected.

02. emp 테이블에서 20번 부서에 근무하는 사원들을 입사 일자를 기준으로 검색 하시오.

START_DATE : 입사 일자가 포함된 한 주의 시작일 (일요일)

END_DATE : 입사 일자가 포함된 한 주의 종료일 (토요일)

해답 SQL문

SELECT

    empno, ename, hiredate,

    TO_CHAR(hiredate, 'Day') DAY,

    TRUNC(hiredate,'DY') START_DATE,

    TRUNC(hiredate,'DY')  END_DATE

FROM 

    emp

WHERE 

    deptno = 20

ORDER BY 

    hiredate;

-- 실행 결과 --

5 rows selected.

03. sales 테이블에서, time_id 컬럼의 값이 '1998/05/01'일을 포함한 한 주(일요일-토요일)의     판매 내역을 요일 별로 금액(amount_sold)합계를 검색 하시오.

   단, 검색 결과는 일요일 부터 토요일 까지 정렬합니다.

해답 SQL문

SELECT 

    TO_CHAR(time_id,'DAY','NLS_DATE_LANGUAGE=AMERICAN') AS "Day",

    SUM(amount_sold)

FROM 

    sales

WHERE 

    time_id 

        BETWEEN 

            TRUNC(TO_DATE('19980501','YYYYMMDD'),'D') 

        AND 

            TRUNC(TRUNC(TO_DATE('19980501','YYYYMMDD'),'D')+7) - 1/86400

GROUP BY 

    time_id ;

-- 실행 결과 --

7 rows selected.

04. emp 테이블에서, 입사 일자(hiredate)의 주차를 검색 하시오.

   한 주의 시작일은 일요일이며, 달력을 기준으로 주차를 검색합니다.

해답 SQL문

SELECT 

    empno, ename, hiredate,

    (TRUNC(hiredate,'DY')

        - TRUNC(TRUNC(hiredate,'MM'),'DY'))/7+1 Week 

FROM 

    emp

ORDER BY 

    hiredate;

-- 실행 결과 --

14 rows selected.

05. emp_date 테이블에서, 잘못된 입사일자(hiredate)를 갖는 사원을 검색 하시오.

해답 SQL문

-- 현제 emp_date에서 hiredate 컬럼의 데이터 형식은 varchar2 형임

-- TO_DATE(hiredate, 'YYYYMMDD')로 형 변환을 시키면 에러 출력

-- 함수를 정의해서 사용 해야 함

-- PL/SQL이라는 언어를 사용하여 변수선언, 로직선언, 예외처리 등을 가능하게 해 줌

-- ->는 초급자에게 너무 어려움

-- 함수선언 사용 --------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION ck_date (p_val VARCHAR2)

RETURN VARCHAR2 IS

  v_ck DATE ;

BEGIN

  v_ck := to_date(p_val,'YYYYMMDD') ;

  RETURN 'Valid' ;

EXCEPTION

  WHEN OTHERS THEN

    RETURN 'Invalid' ;

END;

/

SELECT empno, ename, hiredate, ck_date(hiredate) AS CK_DATE

FROM emp_date

WHERE ck_date(hiredate) = 'Invalid' ;

-- CONNECT BY절을 사용 하는 방법이 있음 ------------------------------------------------------

-- 도, 너무 어려움...

SELECT *

FROM emp_date c

WHERE NOT EXISTS (SELECT * 

                  FROM (SELECT TO_CHAR(base,'YYYYMMDD') AS START_DATE,

                               TO_CHAR(last_day(base),'YYYYMMDD') AS END_DATE

                        FROM (SELECT ADD_MONTHS(

TO_DATE('1980/01/01','YYYY/MM/DD'),level - 1) AS base

                              FROM dual

                              CONNECT BY level <= 500)

  )

                  WHERE c.hiredate BETWEEN start_date AND end_date

) ;

-- VALIDATE_CONVERSION 사용 -----------------------------------------------------------------

-- 함수를 사용 WHERE절에 VALIDATE_CONVERSION이라는 함수가 있음

-- 문제는 DB버전 12.2부터 사용 가능 함

SELECT *

FROM emp_date

WHERE VALIDATE_CONVERSION(hiredate AS DATE, 'YYYYMMDD') = 0 ;

-- 실행 결과 --

2 rows selected.