sqldeveloper-4

 

오늘 날짜, 시간 알려주는 명령어

SElECT SYSDATE, CURRENT_TIMESTAMP
FROM dual;

1) SYSDATE : 날짜

2) CURRENT_TIMESTAMP : 시간

 

시간표시 형식 바꾸기 : 도구> 환경설정>NLS>데이터베이스> 시간 기록 형식 

 

날짜 포맷팅

1) RR vs YY (RRRR vs YYYY)

2) MM, MON, N

3) DD, DAY

4) HH vs HH24, MI, SS

 

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'RR/MM/DD HH:MI:SS' ) AS TOCHAR,
    TO_CHAR(SYSDATE, 'RRRR/MM/DD HH24:MI:SS' ) AS TOCHAR2
FROM dual;

 

 

1. AWS

2. MS Azure (GCP)

3. Google Cloud Platform

...

국내. NHN Cloud (Naver) : 한국 2~3곳, 세계 1~2곳 리전을 확대해 나가고 있음.

 

Oracle 대신 MariaDB 사용할 예정

그밖에 MongoDB, Supabase등 모던 개발환경에서 사용할 수 있는 데이터베이스가 있음

 

-- 2. 변환 함수

-- 오라클에서 사용하는 세가지 자료형 : 숫자, 문자, 날짜 타입을 서로 변환하는 함수
-- 1) TO_CHAR(date | n ); 날짜-문자, 문자-숫자
-- 형식문자 | 뜻
-- RR, RRRR     | 19c 표기법
-- YY, YYYY     | 20c 표기법
-- MONTH, MON, MM | 월 --> 언어 설정에 따라 다름 (NLS_DATE_FORMAT="LANGUAGE")
-- DD, DAY, DY | 일
-- AM   | 오전/오후 (설정에 따름)

-- 2) TO_DATE();
-- 3) TO_NUMBER();
-- 데이터 타입의 변환을 묵시적(암묵적)으로 지원하기도 하지만, 경우에 따라 명시적으로 변환을 해야하는 경우가 있음
--      TO_CHAR   TO_DATE
-- 숫자 <-----> 문자 <------> 날짜


-- 1-1. 문자-날짜

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'RR/MM/DD HH:MI:SS' ) AS TOCHAR,
    TO_CHAR(SYSDATE, 'RRRR/MM/DD HH24:MI:SS' ) AS TOCHAR2,
    TO_CHAR(SYSDATE, 'MON')
FROM dual;

 

-- 1-2. 숫자-문자
-- 형식 문자
-- ------------
-- 9 | 숫자(하나)
-- , | 1000단위 구분기호 999,999,999
-- . | 소숫점 99,999.999
-- $ | $문자를 붙이는
-- L | 지역통화(한국-원)

 

-- 2) TO_DATE() : 문자-날짜
-- 숫자 --> 문자 --> 날짜 or 숫자 <-- 날짜 변환은 가능하지만, 숫자 --> 날짜, 날짜 --> 숫자
-- 3) TO_NUMBER() : 문자-숫자(거의 사용..x)

 

-- 3. 특수 함수

-- NULL 처리 함수
-- NULL인 데이터는 비교나 연산의 대상이 아니다! (제대로 된 결과가 나오지 않음)

-- 1) NVL(expr1, expr2) : expr1이 NULL이면 expr2를 반환하고 아니라면 expr1을 반환하는 함수
-- N/A : No Answer, Not Applicable 을 뜻함 (해당사항 없음)
-- expr1과 expr2는 타입이 같아야 한다.

 

SELECT last_name, NVL(TO_CHAR(commission_pct), 'N/A') commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name;

 

-- 2) NVL2(expr1, expr2, expr3) : expr1이 NULL이면 expr2를 반환하고 아니라면 expr3을 반환하는 함수

--Q. 사원들의 정보 중 급여와 수수료 정보를 조회하시오 (단, 수수료가 없다면 최종 수입은 급여수익만 있음)
SELECT last_name, salary,
        salary + (salary * commission_pct) as 수수료,
       NVL2(commission_pct, salary + (salary * commission_pct), salary) income
FROM employees
WHERE last_name like 'B%'
ORDER BY last_name;

 

-- 3) COALESCE(expr1, expr2, ... ) : 첫번째로 NULL이 아닌 값을 반환함
-- 무조건 NULL이 아닌 값(컬럼)이 하나는 있어야 함.
-- expr1 ~ expr3 : 데이터 타입은 아무거나

 

Database(오라클)

-- 4) DECODE(expr, search1, return1,
--                      search2, return2
--                 ....and...
--                  ,  ) AS Alias
-- 오라클의 DECODE()는 프로그래밍 언어의 IF ~ ELSE와 같다.

 

-- Q.  사원들 중 보너스(또는 인센티브)를 받지 않는 사원들에 대해 부서번호가 10번이면 급여의 10%,
-- 부서번호가 20이면 20%, 부서번호가 30이면 30% 나머지는 5%를 성과급으로 지급하고자 할 때,
-- 해당하는 사원들의 정보와 보너스로 지급될 급액을 조회하시오

 

SELECT
    employee_id,
    first_name,
    salary,
    department_id,
    DECODE(department_id, 10, salary * 0.1,
                          20, salary * 0.2,
                          30, salary * 0.3,
                          salary * 0.05) AS bonus
FROM
    employees
WHERE
    department_id IS NOT NULL;

 

 

-- 5) CASE ~ END : IF ~ THEN ~ ELSE 로직 ==> DECODE처럼 동등비교(equals)
-- DECODE() vs CASE (~WHEN THEN)
-- 주된 용도는 함수나 프로시저 등에서 특정한 조건에 따라 값을 반환하는 서브프로그램을 만들때 사용
-- 5-1) Simple CASE ~ END : DECODE() 처럼
-- DECODE()와 다르게 각 행을 콤마(,) 구분하지 않으므로 가독성이 좋지 못함..

 

CASE expr WHEN comparison_expr1 THEN return_expr1
          WHEN comparison_expr2 THEN return_expr2
          ...and...
          ELSE return_expr
END AS Alias

SELECT employee_id,
      first_name,
      salary,
      department_id,
      CASE department_id WHEN 10 THEN salary * 0.1
                         WHEN 20 THEN salary * 0.2
                         WHEN 30 THEN salary * 0.3
                         ELSE salary * 0.05
      END AS bonus
FROM employees
WHERE department_id != 80;

 

 

-- 5-2) Searched CASE ~ END : Condition(조건)을 사용해 동등 비교 이외의 크다, 작다 등을 비교..(범위비교)
-- condition : expr > value, expr = value, expr <= value...등이 = 이외의 비교(>,>=,<,<=)를 사용

CASE WHEN condition1 THEN return_expr1
     WHEN condition2 THEN return_expr2
     ...and...
     ELSE return_expr
END AS Alias

-- DECODE() --> CASE ~ END로 변경 가능!

 

-- 함수 연습문제

--1. 사원 테이블에서 30번 부서원의 사번, 성명, 급여, 근무 개월 수를 조회하는 쿼리문을 작성한다.
-- 단, 근무 개월 수는 오늘 날짜를 기준으로 날짜 함수를 사용하여 계산한다.
-- 단, 성명은 이름과 성을 합쳐서 full_name이라는 컬럼으로 조회한다.
-- 날짜 함수 : SYSDATE, MONTHS_BETWEEN(오늘 날짜, 입사일자)

SELECT
    employee_id,
    first_name
    || ' '
    || last_name                      AS full_name,
    TO_CHAR(salary,'999,999') AS salary,
    ROUND(month_between(sysdate, hire_date)) AS work_months,
    TRUNC(month_between(sysdate, hire_date / 12)) AS work_months
--만약 근속년수가 20년 이상이면 ~ 20년근속,
--만약 근속년수가 15 ~ 20년이상이면 15년근속,
--만약 근속년수가 10 ~ 15년 이상이면 ~ 10년근속,
FROM
    employees
WHERE
    department_id = 30;

 

    
--2. 급여가 12000 달러 이상인 사원의 사번, 성명, 급여를 조회하여 급여순으로 결과를 정렬하는 쿼 리문을 작성한다.
-- 단, 급여는 천 단위 구분 기호를 사용하여 표시하도록 한다.
-- 정렬 : ORDER BY절, 변환함수(컬럼, 문자열형식)

SELECT employee_id, first_name
    || ' '
    || last_name                      AS full_name,
    TO_CHAR(salary,'999,999') AS salary
FROM employees
WHERE salary >= 1200
ORDER BY salary;

--3. 2005년 이전에 입사한 사원들의 사번, 성명, 입사일, 업무 시작 요일을 조회하는 쿼리문을 작성 한다.
-- 단, 업무 시작 요일이란 입사일에 해당하는 요일을 지칭한다.

SELECT employee_id, hire_date, TO_CHAR(hire_date, 'DAY') AS hire_day
FROM employees
WHERE hire_date < TO_DATE('05/01/01', 'RR/MM/DD');

 

--4. 그룹함수(집계함수)
-- 여러 행(row)에서 결과괎을 집계해서 반환하는함수
--ex> 사원 테이블의 급여(정보)를 합계를 구하는 함수 : SUM()

-- 1) SUM() : 합계
SELECT
    to_char(SUM("A1"."SALARY"),
            '999,999') "SUM_SALARY"
FROM
    "HR"."EMPLOYEES" "A1";

-- 2) AVG() : 평균
SELECT
    to_char(AVG("A1"."SALARY"),
            '999,999') "SUM_SALARY"
FROM
    "HR"."EMPLOYEES" "A1";

-- 3) COUNT() : 행의 갯수를 세는 함수 (null 제외)
SELECT
    COUNT("A1"."EMPLOYEE_ID") "CNT"
FROM
   employees; -- 전체 사원수 : 107
   
SELECT
    COUNT("A1"."EMPLOYEE_ID") "CNT"
FROM
   employees
WHERE department_id =80;

-- 4) MIN() / MAX() : 최대, 최소
-- 숫자 아닌 컬럼에도 적용할 수 있음 ==> 활용도가 높다.
-- ex> MAX(hire_date): 가장 나중에 입사한.., MIN(hire_date): 가장 먼저 입사한..
-- ex> MAX(first_name) : A~Z 순서 (역순)
SELECT MAX(first_name),
       MIN(first_name)
FROM employees;

SELECT MAX(salary) AS max_sal,
       MIN(salary) AS min_sal
FROM employees;

SELECT employee_id, SUM(salary), ROUND(AVG(salary))
FROM employees
GROUP BY employee_id;

-- ORA-000937: 그룹 함수 옆에 일반 컬럼을 나열했으면,GROUP BY 절에 반드시 표시

SELECT SUM(salary), ROUND(AVG(salary))
FROM employees
GROUP BY employee_id;

   
-- Q. 이 회사의 전체 사원에 대해 급여 합계, 평균, 최대, 최소, 사원 수 등을 집계하여 조회하시오

SELECT 'Oracle' As company_name,
       SUM(salary) AS sum_salary,
       TO_CHAR(ROUND(AVG(salary)), '999,999') AS avg_salary,
       MAX(salary) AS max_salary,
       MIN(salary) AS min_salary,
       COUNT( employee_id) AS cnt
FROM employees group by 'Oracle';

 -- RANK() : 동순위 표기 o, 다음 순위 표기x

-- DENSE_RANK() : 다음 순위 표기 o