DUAL
오라클 자체에서 제공하는 테이블, 간단한 함수를 이용해 결과값을 확인가능
Null 값
한 행의 특정 열에 데이터 값이 없으면 NULL값이라고 한다
널 값은 알 수 없는 값으로 0과 공백과는 결과가 다르다
널 값을 포함하는 연산의 경우 결과도 널이다
모르는 데이터에 숫자를 더하거나 빼도 결과는 모르는 데이터인 것과 같다
함수
UPPER, LOWER(대문자 소문자), INITCAP(첫글자 대문자 이후 소문자)
SQL> SELECT EMPNO, LOWER(ENAME), LOWER(JOB), DEPTNO
2 FROM EMP
3 WHERE ENAME = 'KING'
4 ;
EMPNO LOWER(ENAME) LOWER(JOB) DEPTNO
----- -------------------- ------------------ ------
7839 king president 10
사원 테이블에서 이름의 첫글자가 K보다 크고 Y보다 작은 사원의 정보를 출력하되 이름순으로 정렬해보자
SQL> SELECT *
2 FROM EMP
3 WHERE SUBSTR(ENAME,1,1) > 'K' AND SUBSTR(ENAME,1,1) <'Y'
4 ORDER BY ENAME;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- ------------------ ----- -------- ----- ----- ------
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7934 MILLER CLERK 7782 82/01/23 1300 10
7369 SMITH CLERK 7902 80/12/17 800 20
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30#SUBSTR(ENAME,1,1) : ENAME의 첫글자부터 첫글자까지
SQL> SELECT 'ABCDE', INSTR('ABCEF','A') RES
2 FROM DUAL;
'ABCDE' RES
---------- -----
ABCDE 1# INSTR(값 안에 내가 원하는 값이 있는지 찾는 함수)
# LPAD(expr, n, expr1) : 지저된 자리수 n만큼 우측에서 채우고 남은 공간 좌측에 expr1을 채움
# RPAD(반대)
#LTRIM(EXPR, EXPR1) : 문자열 EXPR에 대해 좌측부터 EXPR1과 동일한 문자 제거
# REPLACE: '단어'를 치환하기때문에 'SC'가 붙은 것만 치환
# TRANSLATE : 한글자씩 치환한다
SQL> SELECT ENAME, REPLACE(ENAME,'SC','*?')
2 FROM EMP;
ENAME REPLACE(ENAME,'SC','*?')
-------------------- ----------------------------------------
SMITH SMITH
ALLEN ALLEN
WARD WARD
JONES JONES
MARTIN MARTIN
BLAKE BLAKE
CLARK CLARK
KING KING
TURNER TURNER
JAMES JAMES
FORD FORD
MILLER MILLER
TRANSLATE(ENAME,'SC','*?')
--------------------------------------------------------------------------------
SMITH
*MITH
ALLEN
ALLEN
WARD
WARD
JONES
JONE*
MARTIN
MARTIN
BLAKE
BLAKE
CLARK
?LARK
KING
KING
TURNER
TURNER
JAMES
JAME*
FORD
FORD
MILLER
MILLER
숫자함수
ROUND : 반올림
TRUNK : 버림
MOD(M.N) : M 을 N으로 나눈 나머지
ABS : 절대값
FLOOR: 해당 수보다 작거나 같은 정수 중 큰 정수를 리턴
CEIL : 해당 수보다 크거나 같은 정수 중 가장 작은 정수를 반환
SIGN: 1, -1, 0
POWER(M,N) : M의 N승
SQL> SELECT ROUND(124.82412) RES1, ROUND(4124.512,0) RES2, ROUND(1.125215,2) RES3, ROUND(1412.555512,-2) RES4
2 FROM DUAL;
RES1 RES2 RES3 RES4
----- ----- ----- -----
125 4125 1.13 1400SQL> SELECT TRUNC(-55.3) RES1, FLOOR(-55.3) RES2
2 FROM DUAL;
RES1 RES2
----- -----
-55 -56
SQL> SELECT TRUNC(55.3) RES1, FLOOR(55.3) RES2
2 FROM DUAL;
RES1 RES2
----- -----
55 55# TRUNC, FLOOR 둘 다 버림이지만, TRUNC는 0을 향해 버리는 것이고 FLOOR은 음수더라도 무조건 아래로 버린다.
날짜데이터
기본 DATA FORMAT : 'RR/MM/DD'. /'DD-MON-RR'
RR = Y2K를 고려하여 년도를 표기함(00~49 : 2000년대, / 50~99 : 1990년대)
DATE1 - DATE2 : 두 날짜 사이의 경과일수
SYSTIMESTAMP: TIMESTAMP WITH TIME ZONE 형식의 시스템 날짜를 리턴
CURRENT-DATE : 현재 세션의 날짜
CURRENT-TIMESTAMP: TIMESTAMP WITH TIME ZONE의 형식으로 리턴
EXTRACT: DATETIME 또는 INTERVAL의 값표현형식에 지정된 DATETIE FIELD의 값을 추출해서 리턴
EXTRACT(YEAR OR MONTH OR DAY FROM SYSDATE)
SQL> SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
EXTRACT(YEARFROMSYSDATE)
------------------------
2021사원테이블에서 사원의 이름 입사일자에서 입사한 월 정보 조회
SQL> SELECT ENAME, EXTRACT(MONTH FROM HIREDATE)
2 FROM EMP;
ENAME EXTRACT(MONTHFROMHIREDATE)
-------------------- --------------------------
SMITH 12
ALLEN 2
WARD 2
JONES 4
MARTIN 9
BLAKE 5
CLARK 6
KING 11
TURNER 9
JAMES 12
FORD 12
MILLER 1# 사원테이블에서 근무일수, 주와 일로 구하여라
SQL> SELECT ENAME, HIREDATE, SYSDATE, TRUNC((SYSDATE - HIREDATE)+1) AS TotalDays, ROUND(TRUNC((SYSDATE-HIREDATE)+1)/7) AS WEEK, ROUND(MOD((SYSDATE-HIREDATE),7),0) AS DAYS
2 FROM EMP;
ENAME HIREDATE SYSDATE TOTALDAYS WEEK DAYS
-------------------- -------- -------- --------- -------- --------
SMITH 80/12/17 21/05/18 14763 2109 7
ALLEN 81/02/20 21/05/18 14698 2100 5
WARD 81/02/22 21/05/18 14696 2099 3
JONES 81/04/02 21/05/18 14657 2094 6
MARTIN 81/09/28 21/05/18 14478 2068 2
BLAKE 81/05/01 21/05/18 14628 2090 5
CLARK 81/06/09 21/05/18 14589 2084 1
KING 81/11/17 21/05/18 14428 2061 1
TURNER 81/09/08 21/05/18 14498 2071 1
JAMES 81/12/03 21/05/18 14412 2059 6
FORD 81/12/03 21/05/18 14412 2059 6
MILLER 82/01/23 21/05/18 14361 2052 4
# 사원테이블에서 10번 부서 사원의 현재까지 근무월수를 구하여라
SQL> SELECT DEPTNO,ENAME,HIREDATE,SYSDATE, MONTHS_BETWEEN(SYSDATE,HIREDATE) AS M_BETWEEN, TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) AS T_BETWEEN
2 FROM EMP WHERE DEPTNO=10;
DEPTNO ENAME HIREDATE SYSDATE M_BETWEEN T_BETWEEN
-------- -------------------- -------- -------- --------- ---------
10 CLARK 81/06/09 21/05/18 479.3122 479
10 KING 81/11/17 21/05/18 474.0541 474
10 MILLER 82/01/23 21/05/18 471.8605 471
# 사원테이블에서 10번 부서 사원의 입사일로부터 5개월이 지난 날짜를 계산하여 출력
SQL> SELECT ENAME, HIREDATE, SYSDATE, ADD_MONTHS(HIREDATE,5)
AS A_MONTH FROM EMP WHERE DEPTNO = 10 ORDER BY 4;
ENAME HIREDATE SYSDATE A_MONTH
-------------------- -------- -------- --------
CLARK 81/06/09 21/05/18 81/11/09
KING 81/11/17 21/05/18 82/04/17
MILLER 82/01/23 21/05/18 82/06/23
# 사원테이블에서 10번 부서 사원의 입사일로부터 돌아오는 금요일을 계산하라
SQL> SELECT ENAME, HIREDATE, SYSDATE, NEXT_DAY(HIREDATE, '금요일') AS 금요일
2 FROM EMP
3 WHERE DEPTNO = 10;
ENAME HIREDATE SYSDATE 금요일
-------------------- -------- -------- --------
CLARK 81/06/09 21/05/18 81/06/12
KING 81/11/17 21/05/18 81/11/20
MILLER 82/01/23 21/05/18 82/01/29
DATE ROUND
- 일(DAY)를 반올림 할 때, 정오를 넘으면 다음날 자정을 출력하고, 넘었다면 그 날 자정을 출력한다
- 월(MONTH)를 반올림 할 때, 15일 이상이면 다음날 1일, 그 이하라면 현재 1일 출력한다
- 년(YEAR)를 반올림 할 때, 6월을 넘으면 다음해 1월 1일, 그 이하라면 올해의 1월 1일을 출력한다
DATE TRUNC
- 일(DAY)를 버릴 때, 그날 자정을 출력하고 월(MONTH)을 버릴 때, 그 달의 1일을 출력하고 년(YEAR)을 버릴 때 그 년도의 1월 1일을 출력한다
- 단 시간정보가 중요하지 않을 경우, 시간정보를 생략하고 입력하거나 연산할 때 편리하다
Format Models
The total length of a datetime format model cannot exceed 22 characters. The default datetime formats are specified either explicitly with the NLS session parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT, or implicitly with the
docs.oracle.com
TO_CHAR
- 숫자 FORMAT : 숫자(9,0) 그룹 구분자(,),G, 소수점, 통화 $, L, 공백제거FM,
- 표기법 EEEE(지수), RN(로마표기자), rn, X(16진수),
- 기호표시 MI : 숫자가 음수일 경우 마지막에, PR : 음수인경우<>안에 표시, S: 음수인 경우 숫자 앞에
SQL> SELECT TO_CHAR(1000,'00009999') FROM DUAL;
TO_CHAR(1000,'0000
------------------
00001000
SQL> SELECT TO_CHAR(12.3, '009.90') FROM DUAL;
TO_CHAR(12.3,'
--------------
012.30
SQL> SELECT TO_CHAR(12.3, '00009.90000') FROM DUAL;
TO_CHAR(12.3,'00009.9000
------------------------
00012.30000- 날짜 FORMAT
- 기원: AD/BC, A.D/B.C
- 년도: YYYY, YYY, YY,Y,YEAR,SYYYY
- 분기: Q
- 월: MM, MON,MONTH,RM
- 주: W, WW, IW(W: 해당 월의 몇주차, WW: 해당년도 1월1일기준으로 지금이 몇주차)
- 일자: D,DD,DDD,J
- 요일: DY, DAY
# 사원테이블에서 20번 부서 중 급여 앞에 $을 삽입하고 천단위 구분자를 표시하면서 조회하자.
사원번호, 이름, 직업, 봉급, 봉급포맷을 출력하자
SQL> SELECT EMPNO, JOB,SAL,TO_CHAR(SAL,'$999,999')
2 FROM EMP
3 WHERE DEPTNO = 20
4 ORDER BY SAL DESC;
EMPNO JOB SAL TO_CHAR(SAL,'$999,
----- ------------------ ----- ------------------
7902 ANALYST 3000 $3,000
7566 MANAGER 2975 $2,975
7369 CLERK 800 $800
SQL> SELECT EMPNO, JOB,SAL,TO_CHAR(SAL,'L999,999')
2 FROM EMP
3 WHERE DEPTNO = 20
4 ORDER BY SAL DESC;
EMPNO JOB SAL TO_CHAR(SAL,'L999,999')
----- ------------------ ----- ------------------------------------
7902 ANALYST 3000 ￦3,000
7566 MANAGER 2975 ￦2,975
7369 CLERK 800 ￦800SELECT TO_CHAR(TO_DATE('98','RR'),'YYYY') TEST1,
TO_CHAR(TO_DATE('05','RR'),'YYYY') TEST2,
TO_CHAR(TO_DATE('98','YY'),'YYYY') TEST3,
TO_CHAR(TO_DATE('05','YY'),'YYYY') TEST4
FROM DUAL;
TEST1 TEST2 TEST3 TEST4
-------- -------- -------- --------
1998 2005 2098 2005YY : 날자의 년도부분을 현재의 세기로 해석
RR: 현재 년도와 지정된 년도의 두자리를 비교하여 50년을 기준으로 해석한다. 현재의 년도가 2000~2050 사이라면 RR 01 ~ 49까지는 2000년대, 50~99까진 1900년대로 해석한다.
NVL2
SQL> SELECT ENAME, NVL2(COMM, SAL*12 + COMM, SAL*12) AS "AS" FROM EMP;
ENAME AS
-------------------- -----
SMITH 9600
ALLEN 19500
WARD 15500
JONES 35700
MARTIN 16400
BLAKE 34200
CLARK 29400
KING 60000
TURNER 18000
JAMES 11400
FORD 36000
MILLER 15600#직업이 매니저인 사람들의 연봉보다 연봉이 높은 사원들을 구하여라
SQL> SELECT *
2 FROM EMP
3 WHERE SAL>
4 (SELECT AVG(SAL) FROM EMP WHERE JOB = 'MANAGER') AND JOB <> 'MANAGER';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------- ------------------ ----- -------- ----- ----- ------
7839 KING PRESIDENT 81/11/17 5000 10
7902 FORD ANALYST 7566 81/12/03 3000 20SQL> SELECT * FROM EMP WHERE HIREDATE > TO_DATE('1980/10/15','YYYY/MM/DD');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------- ------------------ ----- -------- ----- ----- ------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10SQL> SELECT TO_NUMBER('1,300','9999') - TO_NUMBER('1,500','9999') FROM DUAL;
TO_NUMBER('1,300','9999')-TO_NUMBER('1,500','9999')
---------------------------------------------------
-200
'IT > DataBase' 카테고리의 다른 글
| SQL - SUBQUERY (0) | 2021.05.25 |
|---|---|
| SQL - SET (0) | 2021.05.25 |
| SQL - JOIN (0) | 2021.05.24 |
| SQL - 그룹함수 (0) | 2021.05.21 |
| SQL - DML(SELECT) (0) | 2021.05.17 |
