Oracle SQL - Select 개발일지(4)

Nam-Soomin·2022년 8월 29일
sql
0

Oracle SQL

목록 보기
4/7

📝 Oracle SQL

3장

📌 변환 함수

자바에서

int a = Integer.parseInt("200");

문자열을 정수로 바꾸기

  • 오토박싱
int a = 10;
Integer b = a;
  • 오토 언박싱
Integer x = new Integer(100);
int y = x;
  • 형태 지정
    날짜와 시간 출력
select to_char(sysdate, 'yy/mm/dd hh:mm:ss') from dual;

  • '2022년 8월 29일' -> 테이블에 삽입
    문자열 날짜형
    to_date('2022년 8월 29일',XXXX)
    XXXX : 형태요소

📌 날짜에 TO_CHAR 함수 사용

TO_CHAR (date, 'format_mode')
  • 형식 모델(format_mode)
  • 작은 따옴표로 묶어야 하며 대소문자를 구분
  • 쉼표로 날짜 값 구분
select employee_id, to_char(hire_date, 'MM/YY') "입사한 월"
from employees
where last_name = 'Higgins';

📌 날짜 형식 모델 요소

-YYYY : 연도(숫자)
-YEAR : 연도(문자)
-MM : 두 자리 달
-MONTH : 달 전체 이름
-MON : 세 자 약어 달
-DY : 세 자 약어 요일
-DAY : 요일 전체 이름
-DD : 숫자로 나타낸 달의 일

※대한민국은 MONTH와 MON의 값이 같다.

여러가지 타입의 날짜 형식 모델을 작성해 변화를 눈으로 확인해보자.
아까 작성했던 코딩을 변경해주면,

select employee_id, to_char(hire_date, 'MON/YY') "입사한 월"
from employees
where last_name = 'Higgins';

또 형식 모델을 변경해보자.

select employee_id, to_char(hire_date, 'DY MON-YY') "입사한 월"
from employees
where last_name = 'Higgins';


한번 더,

select employee_id, to_char(hire_date, 'DY DD-MON-YY') "입사한 월"
from employees
where last_name = 'Higgins';

날짜를 어떻게 지정해 줄 것이냐? 그것이 날짜 형식 모델이다.

그리고.
날짜가 DD인데, DDD는 일년 중의 일(day)

select to_char(sysdate, 'ddd') "1년 중 날짜" from dual;

오늘이 이번주에서 몇번째 인가?

select to_char(sysdate, 'd') "이번주 중 날짜" from dual;


(작성하는 날이 월요일이므로 2!)

날짜 형식은 사실 굉장히 많은데, 대표적인 몇가지만 알고 넘어가자!😉

📌 시간 형식

  • 시간 요소는 날짜 중 시간 부분의 형식을 지정한다.

-hh:mi:ss

select to_char(sysdate, 'hh:mi:ss') from dual;

-hh24:mi:ss

select to_char(sysdate, 'hh24:mi:ss') from dual;

select to_char(sysdate, 'hh:mi:ss am') from dual;

  • 문자열은 큰 따옴표로 묶어서 넣는다.

-문자열을 넣기 전

select to_char(sysdate, 'month dd') from dual;

-문자열을 넣은 후

select to_char(sysdate, 'month"의" dd') from dual;

  • 숫자 접미어는 숫자를 문자로 표기한다.
    미국은 5월 3일이다. 그런데 미국은 5월의 셋째날이다.(서수)로 출력하기도 한다. 그것을 ddspth 로 표현한다.

-먼저 오늘 날짜를 ddspth로 출력.

select to_char(sysdate,'ddspth') from dual;

-mm ddspth

select to_char(sysdate,'mm ddspth') from dual;

-입사일을 확인해보자.

select last_name, to_char(hire_date, 'YYYY MON DD')
as "입 사 일" from employees;

📌 'fm'은 뭘까?

*채워진 공백을 제거하거나, 선행 제로를 제거하는 요소이다.(쓸 때 없는 공백 제거)

📌 숫자에 to_char 함수 사용

-to_char 함수에는 숫자 값을 문자로 표시할 수 있는 몇 가지 형식 요소가 있다.

TO_CHAR(number, 'format_model')
  • 9 : 숫자를 표시
  • 0 : 0을 강제로 표시
  • $ : 부동 $기호를 표시
  • L : 부동 지역 통화 기호를 사용
  • . : 소수점
  • , : 천 단위 구분자 출력

-언스트라는 애가 받은 월급을 요렇게 출력해줘라 문

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';

그러나 우리는 대한민국이다.
그러므로 통화표시를 바꿔보자!

SELECT TO_CHAR(salary, 'L99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';


...물론 6000달러와 6000원은 전혀 다르지만💦

📌 TO_NUMBER 및 TO_DATE 함수

예제)

SELECT last_name, hire_date from employees
WHERE hire_date = TO_DATE('May  24, 2005', 'fxMonth DD, YYYY');

이 코드는 데이터타입이 달라 에러가 난다.
그 이유는 공백이 두칸있어야 하는데, fxMonth DD 사이에 공백이 한 칸 밖에 없어서 오류가 났다.
참고로, fxMonth는 공백 하나까지도 같아야 한다는 의미이다. 

SELECT last_name, hire_date from employees
WHERE hire_date = TO_DATE('3월 11, 2005', 'fxMonth DD, YYYY');

select last_name, hire_date from employees
where hire_date = to_date('2005 3월 11', 'yyyy mon dd');

📌 중첩 함수

  • 함수 안에 함수 안에 함수.
  • 단일 행(row) 함수는 여러 번 중첩될 수 있다.
  • 중첩 함수는 가장 안쪽부터 바깥쪽 순으로 계산된다.
F3(F2(F1(col1, arg1), arg2), arg3)

예제)입사일로부터 여섯 달 경과 후 첫번재 금요일의 날짜를 표시.
날짜는 Friday, August 13th, 1999와 같은 형식으로 표시되어야 하며 입사일을 기준으로 결과를 정렬할 것.

입사한 후 6개월 -> add_months(hire_date, 6)
		next_day(add_months(hire_date,6),'금') TO_CHAR(next_day(add_months(hire_date,6),'금'),날짜,'YYYY-MM-DD')

📌 일반 함수

-모든 데이터 타입을 사용할 수 있다. NULL도 사용할 수 있다.
-함수 중에 몇가지는 NULL을 데이터로 바꿔준다.

  • NVL (1,2)
  • NVL2 (1,2,3)
  • NULLIF (1,2)
  • COALESCE (1,2, ..., exprn)

📌 NVL 함수

-널을 실제 값으로 변환해준다.
-데이터타입은 서로 같아야한다.
-NVL 함수를 사용하여 널 갑을 실제 값으로 변환할 수 있다.

예)커미션이 없는 사람은 0으로 바꾸기

NVL(commission_pct, 0)

📌 NVL2 함수

-NVL은 자바로 치면 if(조건(NULL일 때면 ~해라))이 있는이라면, NVL2는 IF(조건에), ELSE까지 있는 느낌.

NVL2(1, 2, 3)
  • 1이 NULL일 때, 3이 처리됨
  • 1이 NULL이 아닐 때, 2가 처리됨

📌 NULLIF 함수

-두 표현식을 비교해서 똑같을 때, NULL을 반환하고, 동일하지 않다면 첫번째 인자를 반환한다.

NULLIF(1, 2)
  • 1과 2가 같을 경우 NULL로 바뀜
  • 1과 2가 다를 경우 1로 바뀜

📌 COALESCE 함수

-콜리스 함수가 NVL함수보다 좋은 점은 여러 대체값을 사용할 수 있다는 점이다.
-NVL은 첫번째 인자가 NULL 일때 바뀌는데, 콜리스로 하면 바꿀수 있는 여러 인자가 많아진다.

coalesce(1,2,3)
  • 1이 null이 아닌 경우 ->1이 출력
  • 1이 null인 경우 ->2가 출력
  • 1이 null이고 2도 null인 경우 ->3이 출력
SELECT last_name,
COALESCE(commission_pct, salary, 10) comn
FROM employees
ORDER BY commission_pct;

📌 조건 표현식

-SQL 안에서 IF-THEN-ELSE 논리를 사용할 수 있다.
-방식은 CASE표현식과 DECODE함수가 있다.

📌 CASE 표현식

  • CASE WHEN... THEN... ~END로 작성

📌 DECODE 표현식

  • CASE 또는 IF-THEN-ELSE 문의 역할을 수행하여 조건부 조회를 손쉽게 수행할 수 있다.
  • DECODE는 CASE와 거의 같지만 코드를 생략한 느낌(내생각)

✎연습문제3-2)

7번 TO_CHAR함수를 써서 문제를 해결하여라 숫자를 문자열로 바꿔라
8번 LPAD 함수를 사용해서 해라
9번 본문에 나온 예제이다. 급여 검토일=연봉 협상일을 출력해라
출력형태는 요일,년,월,일(TO_CHAR)
10번 정렬은 어려우니 그 전까지만 해도 된당~
11번 사원의 이름과 커미션 출력.
12번은 생략 (연습문제를 같이 풀 때 같이 할거임) - 월급을 백설표로 출력하는 것임. 5천불이면 *가 5개라는거임
13번은 하는 업무에 따라 A부타 출력하는거 DECODE
14번은 CASE로 풀어라~ (13 OR 14 둘중에 하나만 풀어라)

7)각 사원에 대해 다음 항목을 생성하는 질의를 작성하시오.

SELECT last_name || ' earns '||to_char(salary,'$99,999.00')||' monthly but wants ' || TO_CHAR(salary*3,'$99,999.00') "꿈의 월급"
from employees;

8)모든 사원의 이름과 급여를 표시하는 질의를 작성하여라. 급여는 15자 길이로 왼쪽에 $기호가 채워진 형식으로 표기하고 열 레이블을 salary로 지정하여라.

SELECT LAST_NAME, LPAD(SALARY, 15, '$') 월급
FROM EMPLOYEES;

9)사원의 이름, 입사일 및 급여 검토일을 표시하시오. 급여 검토일은 여섯 달이 경과한 후 첫번재 월요일이다. 열 레이블을 REVIEW로 지정하고 날짜는 "요일,년,월,일"과 같은 형식으로 표시되도록 지정할 것.

SELECT LAST_NAME, HIRE_DATE, TO_CHAR(NEXT_DAY(ADD_MONTHS(HIRE_DATE, 6), '월'),'DAY, YY/MM/DD') AS "REVIEW"
FROM EMPLOYEES;

  • 입사 후 여섯달 경과 : ADD_MONTHS(HIRE_DATE, 6)
  • 지정된 날짜에 가장 가까운 요일인 월요일이 언제인지 : NEXT_DAY(지정된 날짜, '월')
    *종합 : 입사 후 여섯달이 경과된 가장 가까운 월요일이 언제인지?
  • 그러므로 이 두가지를 합치면 NEXT_DAY(ADD_MONTHS(HIRE_DATE, 6), '월')
  • 위의 결과(데이터타입:DATE)를 월요일, 연도/연/날 로 출력해라(출력할 데이터의 형태는 문자열) - 따라서, 날짜를 문자열로 바꿔서 출력 :TO_CHAR(날짜,'형태요소')

10)이름, 입사일 및 업무 시작 요일을 표시하고 열 레이블을 day로 지정하시오. Monday를 시작으로 해서 요일을 기준으로 결과를 정렬하시오.

SELECT LAST_NAME, HIRE_dATE, TO_CHAR(hire_Date, 'day') AS "DAY"
FROM EMPLOYEES
ORDER BY to_char(hire_Date,'d') asc;

  • 날짜를 요일로 바꾼다 : TO_CHAR(hire_Date, 'day')
  • 일요일이 1, 월요일이 2이다. 그런데 월요일을 가장 처음으로 정렬하고 싶다면, 월요일 숫자를 하나 빼주면 된다. : order by to_char(hire_date-1,'d');

11)사원의 이름과 커미션 합계를 표시하는 질의를 작성하시오. 커미션을 받지 않는 사원일 경우 "No Commission"을 표시하시오. 열 레이블은 COMM으로 지정할 것.(변환함수 문제)

SELECT LAST_NAME, NVL(to_char(commission_pct), 'NO Commission') AS "커 미 션"
FROM EMPLOYEES;


  • 커미션이 없으면 "no commission"으로 출력. (NULL을 데이터로 바꾸는 함수 NVL사용)
  • to_char(commission_pct) : 커미션의 데이터타입이 숫자이므로 문자열로 바꿔주기(바꿔주지 않으면 nvl에서 만나는 노 커미션과 타입이 달라서 에러)

12)사원의 이름을 표시하고 급여 총액을 별표로 나타내는 질의를 작성하시오. 각 별표는 1,000달러를 나타냅니다. 급여를 기준으로 데이터를 내림차순으로 정렬하고 열 레이블을 EMPLOYEES_AND_THEIR_SALARIES 로 지정하시오.

SELECT LAST_NAME, RPAD(' ', salary/1000 + 1,'*') "EMPLOYEES_AND_THEIR_SALARIES" FROM EMPLOYEES;

  • 급여가 5000 -> *
    rpad(1,2,3)
    1번을 2의 자리 수로 출력, 남는 부분을 3으로 채운다.

    	rapd(' ', salary/1000 + 1,'*') -> 공백을 5자리로 출력. 남는 부분을 *

    rpad(salary, 10, '') -> 월급을 10자리로 출력. 남는 부분을

13)DECODE함수를 사용하여 다음 데이터에 따라 JOB_ID열의 값을 기준으로 모든 사원의 등급을 표시하는 질의를 작성하시오.

SELECT JOB_ID,DECODE(JOB_ID, 'ST_CLERK','E','SA_REP','D','IT_PROG','C','ST_MAN','B','AD_PRES','A','0') "등 급"
FROM EMPLOYEES;

  • 등급 DECODE(JOB_ID, 'ST_CLERK','E','SA_REP','D','IT_PROG','C','ST_MAN','B','AD_PRES','A','0')

14)13번의 문제를 명령문 CASE를 사용하여 재작성하시오.

SELECT JOB_ID,
FROM EMPLOYEES

(결과)

(ง˙∇˙)ว 오늘도 나는 열심히 달려나간다.
오늘 알게된 사실 : WIN + . = 이모지
일일히 사이트에서 복사해오지 않아도 된다! 유후~😉🤞

profile
Nam-Soomin
🇰🇷Dreaming Full-Stack WEB Developer
이전 포스트

Oracle SQL - Select 개발일지(3)

다음 포스트

Oracle SQL - Select 개발일지(5)

0개의 댓글

관련 채용 정보