DB /day17 / 23.09.14(목) / (핀테크) Spring 및 Ai 기반 핀테크 프로젝트 구축
DAY03
02_FUNCTION_2
-- INSTR : 특정 문자가 있는 위치 반환
-- INSTR ( 대상, 검색글자, 시작위치, 몇번째 검색 )
-- 검색 글자가 여러 개 있을 때 첫번째 위치를 반환
-- 시작위치는 그 위치를 포함하여 뒤의 문자를 검색
SELECT INSTR('STEP BY STEP', 'T')
FROM DUAL;
-- 시작위치(3)로부터 처음 나오는 T의 인덱스 반환
SELECT INSTR('STEP BY STEP', 'T', 3)
FROM DUAL;
-- 시작위치(2)로부터 2번째 나오는 E의 인덱스를 반환
SELECT INSTR('STEP BY STEP', 'E', 2, 2)
FROM DUAL;
SELECT INSTR('데이터베이스', '이',3)
FROM DUAL;
-- LPAD : 대상 문자열을 명시된 자릿수에서 오른쪽에 표시,
-- > 남은 왼쪽 자리들은 기호로 채움
-- LAPD ( 대상, 자릿수, 기호 )
-- 글자수 보다 자릿수가 길면 왼쪽에 기호 추가
SELECT LPAD('PADDING', 10, '#')
FROM DUAL;
-- LTRIM : 문자열의 왼쪽(앞) 의 공백 문자를 제거
SELECT LTRIM(' ㅋㅋㅁㅋㅁㅋ ')
FROM DUAL;
-- RTRIM : 문자열의 오른쪽(뒤) 의 공백 문자를 제거
SELECT RTRIM(' ㅋㅋㅁㅋㅁㅋ ')
FROM DUAL;
-- TRIM : 문자열의 양쪽(앞, 뒤) 의 공백 문자를 제거
SELECT TRIM(' ㅋㅋㅁㅋㅁㅋ ASDASD ASDDAS ')
FROM DUAL;
-- # 날짜 함수
-- SYSDATE : 시스템의 현재 날짜를 반환
SELECT SYSDATE
FROM DUAL;
-- 날짜 연산
-- 날짜 + 숫자 : 해당 날짜로부터 그 기간만큼 지난 날짜를 계산
-- 날짜 - 숫자 : 해당 날짜로부터 그 기간만큼 이전 날짜를 계산
-- 날짜 - 날짜 : 두 날짜 사이의 기간을 계산 : 일 (NUMBER)
SELECT SYSDATE -1 어제, SYSDATE 오늘, SYSDATE +2 모레
FROM DUAL;
-- ROUND 에 포맷 모델 날짜를 사용해서, 날짜를 반올림 할 수 있음
-- : 포맷 모델 단위
-- DDD 일을 기준
-- HH 시간 기준
-- MONTH 월을 기준 ( 16일을 기준 )
-- EMP 테이블의 입사일자를 월을 기준으로 반올림
SELECT ENAME, HIREDATE, ROUND(HIREDATE, 'MONTH')
FROM EMP;
-- TRUNC 함수에 포맷 형식을 사용해서, 날짜를 잘라낼 수 있음
-- EMP 테이블의 입사일자의 월을 기준으로 날짜 자르기
SELECT ENAME, HIREDATE, TRUNC(HIREDATE, 'MONTH')
FROM EMP;
-- MONTHS_BEWEEN : 날짜와 날짜 사이의 개월수를 반환
-- MONTHS_BEWEEN ( DATE_1, DATE_2 ) : 1-2 로 계산
SELECT ENAME, HIREDATE, FLOOR(MONTHS_BETWEEN(SYSDATE, HIREDATE)) 개월수
FROM EMP;
-- ADD_MONTHS : 특정 개월수를 더한 날짜 반환
-- ADD_MONTHS ( DATE, NUMBER )
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 6)
FROM EMP;
-- NEXT_DAY : 날짜를 기준으로 최초로 돌아오는 요일에 해당하는 날짜를 반환
-- NEXT_DAY ( DATE, 요일 )
-- 오늘 기준으로 최초로 돌아오는 화요일
SELECT SYSDATE, NEXT_DAY(SYSDATE, '화요일')
FROM DUAL;
-- LAST_DAY : 해당 날짜가 속한 달의 마짐가 날짜를 반환
-- LAST_DAY ( DATE )
SELECT HIREDATE, LAST_DAY(HIREDATE)
FROM EMP;
/*
# 형변환 함수
- 숫자, 문자, 날짜의 데이터 타입을 다른 데이터 타입으로 변환
- TO_CHAR : 날짜 도는 숫자 타입을 문자형으로 변환
- TO_DATE : 문자 타입을 날짜 타입으로 변환
- TO_NUMBER : 문자 타입을 숫자 타입으로 변환
*/
-- # TO_CHAR ( DATE, '출력 형식' )
-- 출력형식 종류 의미
-- YYYY 년도(4자리)
-- YY 년도(2자리)
-- MM 월을 숫자로 표현
-- MON 월을 알파벳으로 표현
-- DAY 요일 표현
-- 현재 날짜를 다른 형태로 출력
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD(DAY, MON)') "날짜 - "
FROM DUAL;
-- EMP 테이블의 사원 입사일 요일 출력
SELECT HIREDATE || TO_CHAR(HIREDATE, '(DAY)') "입사일+요일"
FROM EMP;
/*
# 시간 종류 출력 의미
AM OR PM 오전(AM), 오후(PM)
HH OR HH12 시간( 1 ~ 12 )
HH24 24시간
MI 분
SS 초
*/
-- 현재 날짜와 시간 출력 / ' ' 안에 문자열 쓸려면 "" 사용
SELECT SYSDATE, TO_CHAR(SYSDATE, 'HH"시" MI"분"') 지금시간
FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD (DAY) AM HH:MI:SS ') 현재시간
FROM DUAL;
/*
# 숫자 출력 형식
- 구분 의미
0 자릿수를 의미, 자릿수가 맞지 않을 경우 0으로 채움
9 자릿수를 의미, 자릿수가 맞지 않을 경우 출력되지 않고 빈공간 반환 / 부족하면 #처리
L 통화 기호를 앞에 표시
. 소숫점
, 천단위 자리 구분
*/
-- 숫자를 문자 형태로 변환
SELECT TO_CHAR(12300) "숫-문"
FROM DUAL;
-- 자리 채우기
SELECT TO_CHAR(123456, '0000000')"0쓰기", TO_CHAR(123456,'99999999') "9쓰기"
FROM DUAL;
-- 통화기호를 붙이면서, 천단위마다 ',' 출력
SELECT ENAME, SAL, LTRIM(TO_CHAR(SAL, 'L999,999,999,999')) 통화
FROM EMP;
/*
# TO_DATE : 문자열을 날짜 형식으로 변환
- TO_DATE ( '문자', 'FORMAT' )
*/
--
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE = TO_DATE(19801217,'YYYYMMDD'); -- 날짜 비교시 TO_DATE를 사용하여 비교
/*
# TO_NUMBER : 데이터를 숫자형으로 변환
- TO_NUMBER ( '문자', 'FORMAT' )
*/
SELECT TO_NUMBER('20,000', '999,999') - TO_NUMBER('12,000', '999,999') 연산
FROM DUAL;
-- #1 QUIZ
SELECT * FROM EMP;
SELECT * FROM DEPT;
-- emp 테이블에서 사원번호가 홀수인 사원을 출력하세요
SELECT ENAME, EMPNO
FROM EMP
WHERE MOD(EMPNO,2)=1;
-- 소문자 manager 로 직급 검색해서 출력하세요
SELECT ENAME,JOB, LOWER(JOB) "소문자 매니저"
FROM EMP
WHERE JOB = 'MANAGER';
-- emp 테이블에서 조회하는 이름을 소문자로 사용해서, 사원번호, 이름, 직급, 부서번호를 출력하세요
SELECT EMPNO, ENAME,LOWER(ENAME) "소문자 이름", JOB, DEPTNO
FROM EMP
WHERE LOWER(ENAME) = 'smith';
-- dept 테이블에서 첫글자만 대문자로 변환하여 모든 정보를 출력하세요
SELECT INITCAP(DNAME), INITCAP(LOC)
FROM DEPT;
-- emp 테이블의 ename 컬럼의 마지막 문자 하나만 추출해서 이름이 E 로 끝나는 사원을 출력하세요
SELECT ENAME
FROM EMP
WHERE SUBSTR(ENAME,-1)='E';
-- emp 테이블에서 이름의 세번째 자리가 R 인 사원을 출력하세요
SELECT ENAME
FROM EMP
WHERE SUBSTR(ENAME,3,1)='R';
-- emp 테이블에서 20번 부서의 사원번호, 이름, 이름의 글자수, 급여, 급여의 자릿수를 출력하세요
SELECT DEPTNO, EMPNO, ENAME, LENGTH(ENAME) "이름 글자수", SAL, LENGTH(SAL) "급여 자릿수"
FROM EMP
WHERE DEPTNO = 20;
-- emp 테이블에서 현재까지 근무일수가 몇일 인지를 구하고, 근무일수가 많은 순서로 출력하세요
SELECT ROUND(SYSDATE - HIREDATE) || '일' "근무일", ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE;
/*
# DECODE / JAVA switch 문과 유사
- DECODE ( 표현식, 조건_A, 결과_A,
조건_B, 결과_B,
......
기본 결과 ( DEFAULT )
*/
SELECT * FROM DEPT;
--10 ACCOUNTING NEW YORK
--20 RESEARCH DALLAS
--30 SALES CHICAGO
--40 OPERATIONS BOSTON
-- EMP 테이블에서 부서번호에 해당되는 부서명을 구하기
SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES'
)AS "부서명"
FROM EMP
ORDER BY "부서명"; -- 정렬시 별칭을 사용 해도 됨
/*
# CASE
- 여러가지 경우에 대해서 하나를 선택하는 함수
- 다양한 비교 연산자를 사용해서 조건을 적용
JAVA if ~ else if 와 유사
CASE WHEN 조건_A THEN 결과_A
조건_B THEN 결과_B
.....
ELSE 결과
END
*/
SELECT ENAME, DEPTNO,
CASE WHEN DEPTNO <= 10 THEN '회계'
WHEN DEPTNO <= 20 THEN '마케팅'
ELSE '영업'
END AS "부서명"
FROM EMP;
-- #2 QUIZ
SELECT * FROM EMP;
-- emp 테이블을 사용해서 직급(job)에 따라서 급여를 인상하는 쿼리문을 작성하세요
-- CLERK -> 20%
-- SALESMAN -> 15%
-- MANAGER -> 10%
-- ANALYST -> 5%
SELECT ENAME, JOB, SAL, NVL(DECODE(JOB, 'CLERK', SAL*1.2,
'SALESMAN', SAL*1.15,
'MANAGER', SAL*1.1,
'ANALYST', SAL*1.05),SAL) AS "인상 급여"
FROM EMP;
-- emp 테이블을 사용해서 급여액에 따라 고액, 보통, 저액을 출력하는 쿼리문을 작성하세요
-- 3000 이상 -> 고액
-- 2000 이상 -> 보통
-- 그외 -> 저액
SELECT ENAME, SAL, CASE WHEN SAL>=3000 THEN '고액'
WHEN SAL>=2000 THEN '보통'
ELSE '저액'
END AS "급여 등급"
FROM EMP;
03_그룹함수.SQL
-- 03_그룹함수.SQL
/*
# 그룹 함수
- 하나 이상의 행을 그룹으로 묶어서 결과를 출력
- SELECT 문 뒤에 작성, 여러 그룹 함수를 쉼표로 구분하여 함께 사용할 수 있음
Ex) SELECT GROUP_FUNCTION( COLUMN ), ......
FROM TABLE_NAME;
- 그룹 함수는 해당 컬럼 ㄱ밧이 NULL 인 것을 제외하고 계산
*/
-- # SUM : 해당 컬럼 값을에 대한 총합
SELECT SUM(SAL)
FROM EMP;
-- # AVG : 해당 컬럼에 대한 평균
SELECT ROUND(AVG(SAL),2) "평균 급여"
FROM EMP;
-- # MAX,MIN : 최대 최소
SELECT MAX(SAL) 최대, MIN(SAL) 최소
FROM EMP;
-- # COUNT : 행의 갯수 반환
SELECT COUNT(*) "전체 사원", COUNT(DISTINCT JOB) "업무 종류", COUNT(COMM) "커미션 지급" -- << NULL 값은 계산 X
FROM EMP;
-- # GROUP BY : 어떤 컬럼값을 기준으로 그룹 함수를 적용할 수 있음
-- Ex) SELECT 컬럼명, 그룹 함수
-- FROM 테이블명
-- WHERE 조건
-- GROUP BY 컬럼명;
-- 부서번호 그룹
SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO;
-- 부서별 평균 급여
SELECT DEPTNO, ROUND(AVG(SAL),1)
FROM EMP
GROUP BY DEPTNO;
-- 부서별 최대, 최소 급여
SELECT DEPTNO, MAX(SAL), MIN(SAL)
FROM EMP
GROUP BY DEPTNO;
-- 정렬은 가장 마지막에 붙음
SELECT DEPTNO, COUNT(*), COUNT(COMM), NVL(AVG(COMM),0)
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;
/*
# HAVING : 그룹의 결과를 제한할 때 HAVING 절을 사용
*/
-- 부서별 평균 급여 > 부서별 평균 급여 2000 이상인 부서만 출력
SELECT DEPTNO, TRUNC(AVG(SAL),1)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>=2000;
-- #1 QUIZ
-- 가장 최근에 입사한 사원의 입사일과 가장 오래된 사원의 입사일을 출력하세요
SELECT * FROM EMP;
SELECT MAX(HIREDATE) "가장 최근", MIN(HIREDATE) "가장 오래된 사원"
FROM EMP;
-- 부서별 커미션을 받는 사원의 수를 출력하세요
SELECT DEPTNO, NVL(COUNT(COMM),0)
FROM EMP
GROUP BY DEPTNO;
-- SALESMAN 의 급여에 대해서 평균, 최고액, 최저액, 합계를 구하세요
SELECT JOB, AVG(SAL), MAX(SAL), MIN(SAL), SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB LIKE 'SALESMAN';
-- 각 부서별로 인원수, 급여 평균, 최저 급여, 최고 급여, 급여의 합을 구하고
-- 급여의 합이 높은 순서로 출력하세요
SELECT DEPTNO, COUNT(*), ROUND(AVG(SAL),1), MIN(SAL), MAX(SAL), SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY SUM(SAL) DESC;
-- 직급별 급여의 평균이 3000 이상인 직급에 대해서 직급, 평균 급여, 급여의 합을 출력하세요
SELECT JOB, AVG(SAL), SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) >= 3000;
-- 전체 월급이 4000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하세요
-- 단, SALESMAN 은 제외하고 월급여 합계로 내림차순 정렬합니다
SELECT JOB, SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL)>4000 AND JOB != 'SALESMAN'
ORDER BY SUM(SAL) DESC;
SELECT JOB, SUM(SAL)
FROM EMP
WHERE JOB NOT LIKE 'SALES%'
GROUP BY JOB
HAVING SUM(SAL) > 4000
ORDER BY SUM(SAL) DESC;
04_JOIN.SQL
-- 04_JOIN.SQL
-- # JOIN : 하나 이상의 테이블에서 한 번의 질의문으로 원하는 자료를 검색할 때 사용
/*
# EQUI JOIN
- 가장 많이 사용되는 조인 방식, 조인 대상이 되는 두 테이블에서
공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결해서 결과를 생성
*/
SELECT * FROM DEPT;
SELECT * FROM EMP;
-- 사원 정보 출력시 각 사원들이 소속된 부서의 상세 정보 확인
-- WHERE 절에서 같은 컬럼을 '=' 를 사용해서 연결
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- 위 결과에서 특정 컬럼만 확인
SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY DNAME;
-- 이름이 'J'시작 인 놈 부서명
SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND ENAME LIKE 'J%';
-- 두 개의 테이블에서 동일하게 존재하는 컬럼 확인시 '테이블명.컬럼명' 작성
SELECT ENAME, DNAME, EMP.DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND ENAME LIKE 'J%';
-- 테이블에 별칭 지정
-- 컬럼명도 별칭 가능, AS는 아님
-- FROM 절 뒤에 테이블 이름을 명시하고, 공백을 작성한 다음에 별칭 지정
-- Ex) FROM 테이블명 별칭
SELECT ENAME, DNAME, E.DEPTNO
FROM EMP E, DEPT "D"
WHERE E.DEPTNO = D.DEPTNO
AND ENAME LIKE 'J%';
/*
# NON-EQUI JOIN
- 조인 조건에 특정 범위 내에 있는지를 비교연산자를 사용해서 JOIN
*/
-- SALGRADE : 급여 등급 테이블
DESC SALGRADE;
-- GRADE NUMBER
-- LOSAL NUMBER
-- HISAL NUMBER
-- 각 사원의 급여 등급 확인
SELECT ENAME, SAL, GRADE
FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;
-- 사원 이름과 소속 부서, 급여의 등급 확인
SELECT E.ENAME, D.DNAME, E.SAL, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
/*
# SELF JOIN
- 하나의 테이블에서 조인을 해서 원하는 결과를 얻을 수 있음
*/
-- 사원의 담당 MANAGER 확인
SELECT EMPLOYEE.ENAME || '의 MANAGER 는 ' || MANAGER.ENAME || ' 입니다.' ㅋㅋ
FROM EMP EMPLOYEE, EMP MANAGER
WHERE EMPLOYEE.MGR = MANAGER.EMPNO;
SELECT E.ENAME "부하 직원", M.ENAME "사수"
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO;
/*
# OUTER JOIN
- 조인 조건에 만족하지 않는 행도 나타내는 조인
- 조인될 때, 어느 한 쪽의 테이블에는 해당하는 데이터가 있지만,
다른쪽 테이블에는 데이터가 없을 경우 그 데이터가 출력되지 않는 것을 해결
- '+' 기호를 조인 조건에서 정보가 부족한 컬럼 이름 뒤에 붙이면 됨
Ex) 테이블1.컬럼 = 테이블2.컬럼(+) >> NULL 값으로 나옴
*/
-- 상사 없는 애 확인해보기
SELECT E.ENAME "부하 직원", NVL(M.ENAME,'왕고') "사수"
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO(+);
-- # QUIZ
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM SALGRADE;
-- 'NEW YORK' 에서 근무하는 사원의 이름과 급여를 출력하세요
SELECT E.ENAME, E.SAL, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND D.LOC = 'NEW YORK';
-- SMITH 사원과 동일한 근무지에서 근무하는 사원의 이름을 출력하세요
SELECT E.ENAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND D.LOC = 'DALLAS';
SELECT EA.ENAME, EB.ENAME, D.LOC
FROM EMP EA,EMP EB, DEPT D
WHERE EA.DEPTNO = EB.DEPTNO
AND D.DEPTNO = EA.DEPTNO
AND EA.ENAME = 'SMITH'
AND EA.ENAME != EB.ENAME; -- 왼쪽과 오른쪽에 같은 이름을 가진 컬럼 제거
Fintech