날짜계산함수
ADD_MONTHS(date, integer) : 매개변수 DATE에 매개변수 INTEGER만큼의 월을 더한 날짜를 리턴
LAST_DATE(date) : 현재 월의 마지막 일자를 리턴
NEXT_DAY(date, char) : 매개변수 date의 다음 주 char(요일)의 날짜를 리턴
MONTHS_BETWEEN(date1, date2) : 매개변수 date1과 date2 사이의 개월수를 리턴
SELECT ADD_MONTHS(SYSDATE,7) 칠개월후,
LAST_DAY(SYSDATE) 금월마지막일,
NEXT_DAY(SYSDATE, '일요일') 담주일요일,
ROUND(MONTHS_BETWEEN(SYSDATE, SYSDATE-100)) 삼개월,
ROUND(MONTHS_BETWEEN(SYSDATE, '2020-02-25')) 이개월
FROM DUAL;
시, 분, 초 계산하기
SELECT ROUND((TO_DATE('16:30','HH24:MI') - TO_DATE('15:10','HH24:MI')) * 24, 1) 시간으로차이계산
FROM DUAL;
SELECT ROUND((TO_DATE('16:30','HH24:MI') - TO_DATE('15:10', 'HH24:MI')) 24 60, 1) 분으로시간차이계산
FROM DUAL;
SELECT ROUND((TO_DATE('16:30','HH24:MI') - TO_DATE('15:10','HH24:MI')) 24 60 * 60, 1) 초로시간차이계산
FROM DUAL;
요일 표시, 년월일 절삭하기
SELECT TO_CHAR(SYSDATE, 'YEAR') FROM DUAL; --결과 : 연도
SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL; --결과 : 화요일
SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL; --결과 : 화
--결화 : 3 (1:일, 2:월, 3:화, 4:수, 5:목, 6:금, 7:토)
SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL;
--오늘 날짜 20230623 기준 절삭시
SELECT TRUNC(SYSDATE, 'Y') FROM DUAL; --결과 2023/01/01 00:00:00
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL; --결과 2023/03/01 00:00:00
SELECT TRUNC(SYSDATE, 'DD') FROM DUAL; --결과 2023/03/22 00:00:00
AM, PM 세기 표시
--nls_date_language=american : AM, PM
--nls_date_language=KOREAN : 오전, 오후
SELECT TO_CHAR(SYSDATE, 'AM', 'nls_date_language=american') AS AMERICAN
, TO_CHAR(SYSDATE, 'AM', 'nls_datE_language=korean') AS KOREAN FROM DUAL;
SELECT TO_CHAR(TO_DATE('20230623100511', 'YYYYMMDDHH24MISS'),
'YYMMDD HH:MM:SS AM') 오전오후
FROM DUAL;
SELECT TOCHAR(TO_DATE('20230623','YYYYMMDD'), 'YYMMDD') || 'P'||
'0001' 순서계약번호산출
FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'AD YYYY, CC"세기"')
FROM DUAL;
활용
--올해 몇 칠이 지났는지 출력하시오.
--현재날짜에서 올해 1월 1일을 뺀 결과를 출력하고 TO_DATE 함수를 사용하여 데이터 형을 일치 시키시오.
SELECT TO_DATE('2023-04-25','YYYY-MM-DD')FROM DUAL;
SELECT TO_DATE(SYSDATE) - TO_DATE('2023-01-01', 'YYYY-MM-DD') FROM DUAL;
전부 조회
DESC ALL_OBJECTS;
DESC USER_TABLES;
SELECT * FROM ALL_TABLES;
SELECT * FROM ALL_VIEWS;
NVL 함수
NVL(c, r) : c가 Null값이 아니면 c값으로, Null이면 r 반환
NVL("값", "지정값")
NVL2(c, r1, r2) : c가 Null값이 아니면 r1값으로, Null이면 r2 반환
활용
--사원들의 사번, 이름, 상관사번을 출력하되 상관이 없는 사원에 대해서는 NULL 값 대신 0으로 출력하시오.
SELECT EMPNO 사번
, ENAME 이름
, NVL(MGR, 0) 상관사번
FROM EMP;
DECODE / CASE WHEN 함수
IF문 기능
DECODE(컬럼, 조건1, 걸과1, 조건2, 결과2, default_result)
연속적인 조건문(표준)
CASE WHEN [조건식1] THEN [조건 1 만족 시 리턴 값]
WHEN [조건식2] THEN [조건 2 만족 시 리턴 값]
ELSE [조건 불만족 시 리턴 값] END
AS [컬럼명]
대상 값과 값 비교 시 ELSE AS 없이 THEN 하고 END
활용
--직급이 ‘분석가’인 사원은 200, ‘영업사원’인 사원은 180, ‘관리자’인 사원은 150, ‘점원”인 사원은 130을 인상하시오.
SELECT ENAME 이름, JOB 직무, SAL 급여
, DECODE(JOB, '분석가', SAL+200, '영업사원', SAL+180, '관리자', SAL+150, '점원', SAL+130)
FROM EMP;
ROUND 함수
활용
--모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하시오. 평균에 대해서는 정수로 반올림하시오.
SELECT MAX(SAL) 급여최고액
, MIN(SAL) 최저액
, SUM(SAL) 총액
, ROUND(AVG(SAL),0)평균
--, ROUND(SUM(SAL) / COUNT(SAL)) 평균급여
FROM EMP;
--각 담당 업무 유형별로 급여 최고액, 최저액, 총액 및 평균 액을 출력하시오. 평균에 대해서는 정수로 반올림 하시오.
SELECT JOB 업무
, MAX(SAL) 급여최고액
, MIN(SAL) 최저액
, SUM(SAL) 총액
, ROUND(AVG(SAL))평균
FROM EMP
GROUP BY JOB;
TABLE JOIN
RDB 핵심
INNER JOIN : 각각 테이블에서 동등한 조건 있을 때 원하는 컬럼 가져오기
OUTER JOIN : INNER JOIN에서 원하는 부분 추가로 가져오기
종류
Cartesian Product : 모든 가능한 행들의 조합
Equi Join : 조건이 일치하는 컬럼을 매칭(주로 PK 와 FK)
ㄴ(Simple Join)=내부조인(INNER JOIN)
Non-Equi Join : 조건절에서 JOIN 조건이 '='이 아닌 다른 연산기호로 주어지는 경우('<', BETWEEN a AND b)
Outer Join : 조건이 일치하지 않더라도 모든 행들을 검색하고자 할 때 사용, (+) 로 표시
Self Join : 한 테이블 내에서 Join 하는 경우
EQUI 조인(INNER JOIN)
내부 조인 : 조인하는 테이블에서 조인 조건이 일치하는 것만 조회
Oracle->EQUI 조인 / 표준SQL->INNER JOIN
NULL값 있는 경우 처리 못해서 제외됨
활용
--김민욱 사원의 부서번호와 부서 이름을 출력하시오.
SELECT E.ENAME 사원이름
, D.DEPTNO 부서번호
, D.DNAME 부서이름
FROM DEPT D, EMP E
WHERE E.ENAME = '김민욱' AND D.DEPTNO = E.DEPTNO;
--INNER JOIN과 ON 연산자를 사용하여 사원 이름과 함께 그 사원이 소속된 부서이름과 지역 명을 출력하시오.
SELECT E.ENAME 사원이름
, D.DNAME 부서이름
, D.LOC 지역명
FROM EMP E
INNER JOIN DEPT D
ON D.DEPTNO = E.DEPTNO;
표준화
SELECT a, b, c, d
FROM A INNER B
ON A.a = B.a;
NATURAL JOIN
활용
--NATURAL JOIN을 사용하여 커미션을 받는 모든 사원의 이름, 부서이름, 커미션, 지역 명을 출력하시오.
SELECT EMP.ENAME 사원이름
, DEPT.DNAME 부서이름
, EMP.COMM 커미션
, DEPT.LOC 지역명
FROM EMP
INNER JOIN DEPT
ON NOT EMP.COMM IS NULL;
USING
컬럼 이름 다를 때 ON 같을 때 USING
활용
--INNER JOIN과 USING 연산자를 사용
--10번 부서에 속하는 모든 담당 업무의 고유 목록(한 번씩만 표시)을 부서의 지역명을 포함하여 출력 하시오.
SELECT EMP.EMPNO 사원번호
, EMP.JOB 담당업무
, DEPT.LOC 지역명
FROM EMP
INNER JOIN DEPT USING (DEPTNO)
WHERE DEPTNO = 10;
COUNT, MIN, MAX, GROUP BY 활용
--COUNT() 함수를 이용하여 담당업무가 동일한 사원 수를 출력하시오.
SELECT JOB 담당업무
, COUNT(EMPNO) 사원_수
FROM EMP
GROUP BY JOB;
--급여 최고액, 급여 최저액의 차액을 출력하시오.
SELECT MAX(SAL) 급여최고액
, MIN(SAL) 급여최저액
, MAX(SAL)-MIN(SAL) 차액
FROM EMP;
--업무를 표시한 다음 해당 업무에 대해 부서 번호별 급여 및 부서 10, 20, 30의 급여 총액을 각각 출력하시오.
SELECT E.DEPTNO 부서번호, D.DNAME 부서명, SUM(E.SAL) 총액
FROM EMP E, DEPT D
WHERE E.DEPTNO= D.DEPTNO AND E.DEPTNO IN(10,20,30)
GROUP BY E.DEPTNO, D.DNAME;
