[구디아카데미 후기]DAY 06 내장함수

▶ 한줄평 : 함수는 외운다기 보다는 시간 날때마다 봐서 익숙해지게 외우는 것이 좋을 것 같다. 민경태 강사님이 실무에서 쓰일만한 것들만 뽑아서 잘 알려주셔서 감사하다.

• 삼발이 있는 쪽 : M(외래키 품고 있는 쪽)
• 빨간 점 : not null(필수)
• 외래키 not null 유무 : 실선, 점선

-예제문제-

---

1. 폴라리스 오피스(무료) 설치하세요.

2. 내장함수(날짜,수학,문자열, 기타 등)

3. 데이터베이스 구현 능력단위 평가

• 원래) 글 내용은 '필수'로 하면 안됨

1. 

-- DUAL 아무 의미없는 테이블, 문법의 필요성에 의해서 추가함 (실제 어떤 데이터를 가져오지는 않음)

절대값을 4로 바꿈(최종)
어떤 서비스에 좋을까? 좋아요 -> 1값 저장, 싫어요 -> -1값 저장(절대값으로)

ABS(Absolute Number)

2. SQRT : SQURE ROUTE

• 다음 중 잘못 된 답안을 찾으시오. SQRT(5) = 25 (X) - 자격증 시험

3. 부호 - 싸인

4. 제곱
   ★ 2의 제곱 값 공부하기! (~ 2의 10제곱까지 = 1024)

(EX) 초창기 핸드폰 - 6만 5천 COLOR지원
화면을 구성하는 것 - 네모난 점(픽셀, Pixel)
-> 픽셀 하나가 나타낼 수 있는 색깔 : 6만 5천개 중 하나를 고를 수 있었음
-> 픽셀 하나의 크기 : 16bit(2BYTE) = 2의 16제곱의 역할 수행가능 = 약 6만 5천
-> 6만 5천 COLOR

TRUE COLOR (최소 24bit 이상 - 2의 24제곱) : 편안하다고 느끼는 color

5. 나머지 - 값을 순환시킬때 많이 쓰임
   1 .... 100
   n = n+1 n = MOD(n,100) 100으로 나눈 나머지 한번 더 계산
   101 을 100으로 나누면 -> 1로 돌아감 (순환, 반복)

-> 시계, 날짜, 시간

6. 정수로 올림, 정수로 내림
   천장 CELI(씰)
   바닥 FLOOR(바닥)

• 정수 자릿수 지정 X -> 정수 단위로만 가능함

7. 정수로 내림(FLOOR)

16. EMPLOYEES 테이블에서 SALARY 평균이 10000 이상인 부서의 DEPARTMENT_ID와 SALARY 평균을 조회하시오. 평균은 정수로 내림처리하시오.
    SELECT DEPARTMENT_ID
    , FLOOR(AVG(SALARY)) AS 부서평균연봉
    FROM EMPLOYEES
    GROUP BY DEPARTMENT_ID
    HAVING AVG(SALARY) > = 1000;

7. 반올림
   반올림은 값이 커질수도 작을수도 있음 -> 서비스 구현할 때 별로 사용하지 않음
   

8. 절사(반올림보다 많이 사용)
   TRUCATE 줄임말(-> TRUC)
   

-정수로 절사 : 수도요금
-일의 자리에서 절사 : 핸드폰 요금(원단위절사)

---

• 오라클이 설치된 서버 기준
  

• 세션타임존(대,소문자 구분해서 알아두기)
  
  -> 오라클 설치를 해외 서버에 두면 - SYSDATE가 다른나라 시간이 나올 수 있음

• UTC(그리니치 시간_) 기준 + 9시간(우리나라)

  TO_CHAR

  12. EMPLOYEES 테이블에서 DEPARTMENT_ID가 80인 사원들을 높은 SALARY순으로 조회하시오. SALARY는 9,000처럼 천 단위 구분기호를 표기해서 조회하시오.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, TO_CHAR(SALARY, '99.999') AS SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
ORDER BY SALARY DESC;

(X) TO_DATE(이형식으로 해석하는 방식이지 원하는 형식으로 조회하는 것이 아님)

일반적으로 '3자리' 보여줌

• 계산은 오라클에게 맡기면 됨

• 날짜 지정할때 시간 지정안하면 0시(자정이 됨)
• 4일 지나고 몇시간이 지났다
  -> 자르면 됨( FLOOR or TRUC 둘 중 아무거나 쓰면 됨)

• 경과일수 쓰임
  : 비밀번호 변경 안내 창 : 비번 바꾼 날짜를 DB에 저장하고
  SYSDATE 오늘 날짜 -> '23/07/01'(비번이 변경된 날짜가 저장된 칼럼 저장) > = 30
  30일 이상이다 -

경과한 기간이 TIMESTAMP 형식으로 반환(답이 날짜였다)

• EXTRACT
  

-> 함수는 외우는 것 아니라 오류없이 돌아가는지 정리만 하기!
-> 회사갔을때) 1. DATE 2. TIMESTAMP 3. (제일많음) VARCHAR(문자로 되어있는 경우) 많음
-> 함수랑 친해져야 함

-소수점 버리고 싶을때 -> FLOOR 나 TRUNC 씀
-TRUNC 안에 있는 것 먼저 사용 - ((EXTRACT(SECOND FROM SYSTIMESTAMP))
-, TO_CHAR(SYSDATE, 'YYYY') - 기존에 알고 있는 함수 활용(추출)

5. 필요한 단위 추출하기(EXTRACT FROM )
   SELECT EXTRACT(YEAR FROM SYSDATE) -- 년
   , EXTRACT(MONTH FROM SYSDATE) -- 월
   , EXTRACT(DAY FROM SYSDATE) -- 일
   , EXTRACT(HOUR FROM SYSTIMESTAMP) -- 시(UTC 표준시)
   , EXTRACT(MINUTE FROM SYSTIMESTAMP) +9 -- 시(Asia/Seoul)
   , EXTRACT(SECOND FROM SYSTIMESTAMP) -- 분
   , TRUNC((EXTRACT(SECOND FROM SYSTIMESTAMP)) -- 초
   , TO_CHAR(SYSDATE, 'YYYY') -- TO_CHAR 함수를 추출용도로 사용
   FROM DUAL;
   

EXTRACT : 필요한 단위 추출하기
6. EMPLOYEES 테이블에서 HIRE_DATE가 2005년도인 사원을 조회하시오.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE EXTRACT(YEAR FROM HIRE_DATE) = 2005;

다음 일요일 : 7월 9일

-지난 일요일 : 일주일 전으로 빼놓고 날짜 구하기
(당일 처리 원리 : -8 해야함)
SYSDATE +_ 가능하고, 1일 = 하루전

• 지난 수요일
  
  

• 개월 수 ; 함수
• 1년 : + 12
  

-> 1월 1일 부터 : 6개월 몇일 지남

---

1.INIT 첫번째, 초기화

2. 글자수
   

3. 바이트 수 (1BYTE - 영어한글자, 글잣수 같음)
   (한글 한글자 : 최소 2BYTE ~ 많으면 3BYTE)
   

4. 가능하면 표준 문법이랑 맞게 ~
   1) || 함수
   2) CONCAT (CONCATNATE: 연결하다) - 나중에 쿼리를 바꾸지 않을 수 있음
   CONCAT(A,B) : 인수를 2개만 전달할 수 있다.
   연결하고 싶은것이 3개여도 안됨!
   

CONCAT(CONCAT(A,B),C) : 인수를 3개 이상은 CONCAT 함수 여러 개로 해결한다.

-> 일반적인 호환성이 높은 쿼리문 작성가능 LIKE CONCAT('515','%');

5. SUBSTRING 일부만 반환하기
   (첫 3글자)
   

6. INSTR(STRING 안에서)

2개이상있을때 :첫번째로 발견된 A의 위치
안들어가있을때 : O

7. 바꾸기
   A를 찾아서 $달러로 변환

8. PAD(패드)

모든 부서번호를 3자리로 만들고 싶다

LPAD(왼쪽에 전체 3글자로 0채우기!)

RPAD (개인정보 마스킹해서 추출 -> PAD쓰면 편함)
이메일의 첫2글자만 추출 -> 전체 5자리 추출 -> 모자르면 **로

• 중간 마스킹은 잘 안됨
  

9. 공백 제거 (왼, 오 -중간에 있는건 X) - 시작과 마무리만

-> 간단히 TRIM으로 해결하기
데이터가 넘어왔을때 데이터가 앞,뒤로 붙어서 넘어올때

HELLO(5자리)WORLD

---

1. 사원 테이블의 JOBID에서 밑줄() 앞/뒷 부분 조회하기
   -- 예시) IT_PROG -> IT / PROG
   -- 글자수 : 7
   -- 밑줄위치 : 3
   -- 밑줄 앞 글자수 : 2(밑줄위치 -1)
   -- 밑줄 뒤 글자수 : 4(글자수 - 밑줄위치)
   밑줄의 위치 : INSTR

JOBID, '' -> 밑줄의 위치

(JOBID, '') + 1 -> 밑줄 다음 번호부터

2. FIRST_NAME과 LAST_NAME을 연결해서 모두 대문자로 바꾼 FULL_NAME 조회하기
   -- 예시) FIRST_NAME : Steven
   -- LAST_NAME : King
   -- FULL_NAME : STEVEN KING

공백1개

-> 글자수의 지장이 없으면 끝까지 반환
-> 밑줄 다음 글자부터 끝까지 가져오시오

밑줄 뒤 글자수 : 4(글자수 - 밑줄위치)(사실은 계산이 필요하지 않다.)

• 경험의 문제 (비슷한 상황이 닥치면 비슷하게 해결하려고 함)
  ♥ 오류가 안보이면 지우고 다시쓰기 !(BLOCK단위로 통째로 날리기)
  ♥ 이상한 부호가 포함되어있음

• 당첨자 조회 -> MASKING(마스킹) 조회
  (EX) 홍길동 홍동
  (X) TOM_CRUISE -> TM_CRUISE

//(1)

---

1. 순위 구하기

연봉 내림차순 정렬문법이 ORDER안에 들어가있음 -> ORDER BY SALARY DESC

고용일 오름차순 정렬 후 순위 매기기 -> 옛날에 입사한 사람이 맨위로

RANK() OVER(ORDER BY 칼럼 ASC) : 낮은 값이 1등 -> 자주 쓰지는 않음

2. 행 번호 구하기
   
   t123/post/33adab04-da40-46ee-baee-4fb8d9c50c69/image.png)

-> ★기능 구현 시 ROW_NUMBER가 훨씬 많이 필요함 (DB에서 가지고 오는걸 못함)
★ ROW_NUMBER 중요!

3. 암호화 함수

'SHA1'(secure hash 1)
2의 8제곱 = 256

-> 회원가입 구현할때) DB 관리자 암호화 과정을 거쳐서 바꿔서 넣어줘야 함
(이전단계에서는 비번이 노출되어 있음
-log 로그 기록을 찍어놓음)
-특정 파일에 비번 써있음)

-> 바로 바꾸고 이렇게 바꾸지는 않을것

---

연습문제 ) 11번 ) PK 아니더라도 UNIQUE COLUMN 은 1:다 관계 맺을 수 있음

디코드(DECODE) : 조인의 과정없이 조인을 대체할 수 있는 함수

//(1) 분기처리

5. 분기 처리 함수 (DECODE, 디코드)

14. EMPLOYEES 테이블에서 PHONE_NUMBER에 따른 지역(REGION)을 조회하시오.
    -- PHONE_NUMBER가 011로 시작하면 'MOBILE', 515로 시작하면 'EAST', 590으로 시작하면 'WEST', 603으로 시작하면 'SOUTH', 650으로 시작하면 'NORTH'로 조회하시오.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
, DECODE(SUBSTR(PHONE_NUMBER, 1, 3)
, '011', 'MOBILE'
, '515', 'EAST'
, '590', 'WEST'
, '603' , 'SOUTH'
, '650', 'NORTH') AS REGION
FROM EMPLOYEES;

EMPLOYEE_ID DEPARTMENT_ID 사원 TABLE
DEPARTMENT_NAME 부서 TABLE
조회 할 수 없음 -> '조인'으로 써야함
ELSE 'Unknown' : 나머지 모든 경우

> 6. 분기 처리 표현식(CASE) = 오라클의 IF문 수행하는 것

EMPLOYEES 테이블에서 근무 개월 수가 240개월 이상이면 '퇴직금정산대상', 아니면 빈 문자열('')을 조회하시오.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
, FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) AS 근무개월 수
, CASE
WHEN MONTHS_BETWEEN(SYSDATE, HIRE_DATE) > = 240 THEN '정산대상'
ELS ..
END AS 퇴직금정산대상유무
FROM EMPLOYEES;

IF문) ORACLE IF의 역할을 수행하는 것

• 캡쳐 : 로고 + SHIFT + S

• 평가시작
  서명 - 문제열기 - 다운로드 압축 풀어서 사용
  답안작성란은 비운다.
  파일첨부 : 답안 파일 첨부(파일명 자유롭게)
  시험 종료하면 "저장하기"버튼 - "시험종료" 버튼 -> 저장은 한번이상하기
  시험 종료하면 "시험종료" 버튼

  10문제(2시간) , 30점 -> 부분점수 있음
  모르는 문제 2문제(5점)(인터넷 조회하기)
  일부 문제는 풀려있음(~DDL까지) - CREATE TABLE