* SQL 시작
|
1
|
sqlplus scott/tiger
|
cs |
* 연산자
SELECT COL1, COL2, FUNCTION, ALIAS, SAL*12, ‘ 문자 ‘, *, DISTINCT, ||
FROM TAB1
WHERE CONDITION (연산자)
1. 비교연산자 : >, <, >=, <=, =, !=, <>
2. BETWEEN 작은값 AND 큰값
3. IN
4. LIKE
5. IS NULL
-> 2~4는 앞에 NOT 붙이면 부정 가능, IS NULL은 IS NOT NULL이 부정
* BETWEEN AND와 IN
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE SAL BETWEEN 1500 AND 3000;
두 코드는 같다. 근데 아래로 쓰는 게 더 좋음. 왜??
> 코드가 너무 길면 구간의 의미를 갖는 것이 눈에 잘 안들어옴
> BETWEEN AND라는 연산자를 쓰면 사이값이라는 뜻이 있기 때문에 한 줄만 써도 되어 가독성이 좋음
>> BETWEEN AND와 IN은 데이터베이스에서만 쓰이는 특별한 연산자
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> -- 급여가 1500 이상 3000 이하를 받는 사원들의 사번, 이름, 급여?
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE SAL BETWEEN 1500 AND 3000;
EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7844 TURNER 1500
7902 FORD 3000
|
cs |
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> -- 수당을 300, 500, 1400 받는 사원의
SQL> -- 사번, 이름, 수당을 출력
SQL> SELECT EMPNO, ENAME, COMM
2 FROM EMP
3 WHERE COMM IN (300, 500, 1400);
EMPNO ENAME COMM
---------- ---------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
|
cs |
* NULL값과 비교하면 모두 참, 거짓이 아닌 '?'
> NULL의 값은 ?과 마찬가지이기 때문에
> WHERE은 항상 참이 나오는 값을 구하기 때문에,
SQL> SELECT EMPNO, ENAME, MGR
2 FROM EMP
3 WHERE MGR = 'NULL';
WHERE MGR = 'NULL'
*
3행에 오류:
ORA-01722: 수치가 부적합합니다
>> 이와 같은 결과가 나옴
* 따라서, MGR = NULL인 값을 구하기 위해서는?
SQL> SELECT EMPNO, ENAME, MGR
2 FROM EMP
3 WHERE MGR IS NULL;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
>> IS NULL 까지가 연산자
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
-- 직속상사가 없는, 대표가 누군지 구하려면?
SQL> SELECT EMPNO, ENAME, MGR
2 FROM EMP
3 WHERE MGR = 'NULL';
WHERE MGR = 'NULL'
*
3행에 오류:
ORA-01722: 수치가 부적합합니다
-- 따라서, 연산자인 IS NULL 사용
SQL> SELECT EMPNO, ENAME, MGR
2 FROM EMP
3 WHERE MGR IS NULL;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
|
cs |
* 데이터베이스 와일드 카드 문자
- 대표 문자 : _, %
- % : 0, 1, N (글자 안와도 되고, 하나 와도 되고, 여러 글자 와도 됨)
- _ : 반드시 1(반드시 한 글자는 있어야 함)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
SQL> -- 문자열의 패턴으로 검색
SQL>
SQL> -- 이름에 S라는 글자가 들어간 사람
SQL>
SQL> -- LIKE
SQL>
SQL> SELECT EMPNO, ENAME, MGR
2 FROM EMP
3 WHERE ENAME LIKE 'S%';
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7788 SCOTT 7566
SQL> -- 0, 1, N : %
SQL>
SQL> SELECT EMPNO, ENAME, MGR
2 FROM EMP
3 WHERE ENAME LIKE '%S%';
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7900 JAMES 7698
SQL> -- 두 번째 글자에 C가 포함되어 있는 사원의 이름을 출력
SQL> SELECT ENAME
2 FROM EMP
3 WHERE ENAME LIKE '_C%';
ENAME
----------
SCOTT
-- 이름에 A가 포함되어 있는 사원의 사번, 이름, 급여?
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE ENAME LIKE '%A%';
EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7876 ADAMS 1100
7900 JAMES 950
SQL> -- 3번째 글자가 O인 사원의 사번 이름을 출력
SQL>
SQL> SELECT EMPNO, ENAME
2 FROM EMP
3 WHERE ENAME LIKE '__O%';
EMPNO ENAME
---------- ----------
7788 SCOTT
SQL> -- SCOTT만 빼고 출력
SQL> SELECT EMPNO, ENAME, MGR
2 FROM EMP
3 WHERE ENAME != 'SCOTT';
|
cs |
* WHERE 연산자
1. 비교연산자 : >, <, >=, <=, =, <>, !=
2. BETWEEN 작은값 AND 큰값
3. IN
4. LIKE
5. IS NULL
+ 1번~ 4번 부정 표현은 앞에 ‘NOT’이라는 키워드 넣으면 됨
+ IS는 BE동사, 따라서 동사의 부정은 ‘IS NOT NULL’
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
SQL> -- 급여가 1500 이상인 사원의
SQL> -- 사번, 이름, 급여?
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE SAL >= 1500;
EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7902 FORD 3000
SQL> -- 부정?
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE SAL < 1500;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7521 WARD 1250
7654 MARTIN 1250
7876 ADAMS 1100
7900 JAMES 950
7934 MILLER 1300
SQL> -- 부정?
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE NOT SAL >= 1500;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7521 WARD 1250
7654 MARTIN 1250
7876 ADAMS 1100
7900 JAMES 950
7934 MILLER 1300
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE NOT COMM IN (300, 500, 1400);
EMPNO ENAME SAL
---------- ---------- ----------
7844 TURNER 1500
SQL> SELECT ENAME
2 FROM EMP
3 WHERE NOT ENAME LIKE '%A%';
ENAME
----------
SMITH
JONES
SCOTT
KING
TURNER
FORD
MILLER
SQL>-- NULL이 아닌 값
SQL> SELECT EMPNO, ENAME, MGR
2 FROM EMP
3 WHERE MGR IS NOT NULL;
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
EMPNO ENAME MGR
---------- ---------- ----------
7902 FORD 7566
7934 MILLER 7782
|
cs |
* FUNCTION (많이 쓰는 기능을 함수로 만든 것)
1. SINGLE ROW FUNCTION
: 하나의 행마다 한 번씩 실행되는 함수
2. MULTIPLE ROW FUNCTION
: 여러 개 값을 받아들여서 한 번씩 실행되는 함수
* SINGLE ROW FUNCTION
> 함수명(컬럼명, 숫자, 문자, ..)
1. 문자대소: UPPER, LOWER, INITCAP
- UPPER: 문자를 대문자로 바꾸어 줌
- LOWER: 문자를 소문자로 바꾸어 줌
- INITCAP: 이니셜만 대문자로 바꾸어 줌
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>-- UPPER, LOWER, INNITCAP
SQL>-- 함수명(컬럼명, 숫자, 문자, ..)
SQL> SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
2 FROM EMP;
ENAME UPPER(ENAM LOWER(ENAM INITCAP(EN
---------- ---------- ---------- ----------
SMITH SMITH smith Smith
ALLEN ALLEN allen Allen
WARD WARD ward Ward
JONES JONES jones Jones
MARTIN MARTIN martin Martin
BLAKE BLAKE blake Blake
CLARK CLARK clark Clark
SCOTT SCOTT scott Scott
KING KING king King
TURNER TURNER turner Turner
ADAMS ADAMS adams Adams
ENAME UPPER(ENAM LOWER(ENAM INITCAP(EN
---------- ---------- ---------- ----------
JAMES JAMES james James
FORD FORD ford Ford
MILLER MILLER miller Miller
|
cs |
2. 문자조작: SUBSTR, INSTR, LENGTH, LPAD, RPAD, CONCAT
- SUBSTR(문자열/컬럼, n, m): 문자열이나 컬럼의 n번째 글자부터 m번째 글자까지 자름
> SUBSTR(문자열/컬럼, 1, 3): 문자열이나 컬럼의 첫 글자부터 세번째 글자 까지만 자름
> SUBSTR(ENAME, 4): ENAME 컬럼의 데이터를 4번째 글자부터 끝까지 자름
- LENGTH(문자열): 문자열의 길이
- CONCAT(문자열1, 문자열2): 앞의 문자열1과 뒤의 문자열2을 연결
- INSTR(문자열, ‘A’): 앞의 문자열에서 ‘A’라는 글자가 몇 번째에 위치해 있는지 찾음(없으면 0)
- LPAD(문자열, n, ‘*’): 전체 n만큼 문자 영역을 확보한 후, 문자열을 오른쪽을 기준으로 정렬한 다음에 왼쪽으로 남는 공간에 ‘*’로 채워서 출력 (ex. ******3000)
- RPAD(문자열, n, ‘*’): 전체 n만큼 문자 영역을 확보한 후, 문자열을 왼쪽을 기준으로 정렬한 다음에 오른쪽으로 남는 공간에 ‘*’로 채워서 출력 (ex. 3000******)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
SQL> SELECT SUBSTR(ENAME, 1, 3), SUBSTR(ENAME, 4), LENGTH(ENAME), CONCAT(ENAME, JOB), INSTR(ENAME, 'A'), LPAD(SAL, 10, '*'), RPAD(SAL, 10, '*')
2 FROM EMP;
SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) CONCAT(ENAME,JOB) INSTR(ENAME,'A')
------ -------------- ------------- ------------------- ----------------
LPAD(SAL,10,'*') RPAD(SAL,10,'*')
-------------------- --------------------
SMI TH 5 SMITHCLERK 0
*******800 800*******
ALL EN 5 ALLENSALESMAN 1
******1600 1600******
WAR D 4 WARDSALESMAN 2
******1250 1250******
SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) CONCAT(ENAME,JOB) INSTR(ENAME,'A')
------ -------------- ------------- ------------------- ----------------
LPAD(SAL,10,'*') RPAD(SAL,10,'*')
-------------------- --------------------
JON ES 5 JONESMANAGER 0
******2975 2975******
MAR TIN 6 MARTINSALESMAN 2
******1250 1250******
BLA KE 5 BLAKEMANAGER 3
******2850 2850******
SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) CONCAT(ENAME,JOB) INSTR(ENAME,'A')
------ -------------- ------------- ------------------- ----------------
LPAD(SAL,10,'*') RPAD(SAL,10,'*')
-------------------- --------------------
CLA RK 5 CLARKMANAGER 3
******2450 2450******
SCO TT 5 SCOTTANALYST 0
******3000 3000******
KIN G 4 KINGPRESIDENT 0
******5000 5000******
SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) CONCAT(ENAME,JOB) INSTR(ENAME,'A')
------ -------------- ------------- ------------------- ----------------
LPAD(SAL,10,'*') RPAD(SAL,10,'*')
-------------------- --------------------
TUR NER 6 TURNERSALESMAN 0
******1500 1500******
ADA MS 5 ADAMSCLERK 1
******1100 1100******
JAM ES 5 JAMESCLERK 2
*******950 950*******
SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) CONCAT(ENAME,JOB) INSTR(ENAME,'A')
------ -------------- ------------- ------------------- ----------------
LPAD(SAL,10,'*') RPAD(SAL,10,'*')
-------------------- --------------------
FOR D 4 FORDANALYST 0
******3000 3000******
MIL LER 6 MILLERCLERK 0
|
cs |
3. 숫자: ROUND, TRUNC, MOD
- ROUND(숫자, n): 소수점을 기준으로 n번째 자리까지 반올림
- TRUNC(숫자, n): 소수점을 기준으로 n번째 자리까지 절삭
>> ROUND와 TRUNC은 돈 계산 할 때 많이 씀
- MOD(N1, N2): 숫자 N2으로 N1을 나눈 나머지를 구해줌
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
SQL> -- ROUND, TRUNC 함수
SQL> SELECT ROUND(45.129, 2), TRUNC(45.129, 2)
2 FROM EMP;
ROUND(45.129,2) TRUNC(45.129,2)
--------------- ---------------
45.13 45.12
45.13 45.12
45.13 45.12
45.13 45.12
45.13 45.12
45.13 45.12
45.13 45.12
45.13 45.12
45.13 45.12
45.13 45.12
45.13 45.12
ROUND(45.129,2) TRUNC(45.129,2)
--------------- ---------------
45.13 45.12
45.13 45.12
45.13 45.12
SQL>-- SINGLE ROW FUNCTION이기 때문에 각 행별로 한 번씩 수행되어 12번 수행
SQL> DESC DUAL
이름 널? 유형
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL> SELECT * FROM DUAL;
D
-
X
SQL> SELECT ROUND(45.129, 2), TRUNC(45.129, 2)
2 FROM DUAL;
ROUND(45.129,2) TRUNC(45.129,2)
--------------- ---------------
45.13 45.12
SQL> SELECT ROUND(45.129, 0), TRUNC(45.129, -1)
2 FROM DUAL;
ROUND(45.129,0) TRUNC(45.129,-1)
--------------- ----------------
45 40
SQL> SELECT MOD(101, 2)
2 FROM DUAL;
MOD(101,2)
----------
1
|
cs |
| SQL에서 지원되는 함수를 조회할 때, 한 개의 결과값만 도출하기 위해 DUAL 테이블 함수를 사용 |
4. 날짜
- SYSDATE: 오늘 날짜, 현재 시간 -> 연산 가능
> 년/월/일, 시/분/초까지 가지고 있지만 년/월/일만 보여줌
- MONTHS_BETWEEN(날짜1, 날짜2): 날짜1와 날짜2 사이의 개월 수 계산
- ADD_MONTHS(날짜, N): 날짜로부터 N개월 후의 날짜
- NEXT_DAY(날짜, 요일): 날짜로부터 가장 가까운 특정 요일의 날짜 계산
- LAST_DAY(날짜): 해당 날짜가 포함된 월의 마지막 날
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
SQL> SELECT SYSDATE
2 FROM DUAL;
SYSDATE
--------
23/02/21
SQL> SELECT SYSDATE-1
2 FROM DUAL;
SYSDATE-
--------
23/02/20
SQL> SELECT SYSDATE+1
2 FROM DUAL;
SYSDATE+
--------
23/02/22
SQL> SELECT SYSDATE - HIREDATE
2 FROM EMP;
SYSDATE-HIREDATE
----------------
15406.5311
15341.5311
15339.5311
15300.5311
15121.5311
15271.5311
15232.5311
13092.5311
15071.5311
15141.5311
13058.5311
SYSDATE-HIREDATE
----------------
15055.5311
15055.5311
15004.5311
14 개의 행이 선택되었습니다.
SQL> -- 며칠 근무했는지 근속 일수 계산 가능
SQL> SELECT SYSDATE + HIREDATE
2 FROM EMP;
SELECT SYSDATE + HIREDATE
*
1행에 오류:
ORA-00975: 날짜와 날짜의 가산은 할 수 없습니다
SQL> SELECT HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE), ADD_MONTHS(HIREDATE, 6), NEXT_DAY(HIREDATE, '금'), LAST_DAY(HIREDATE)
2 FROM EMP;
HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE) ADD_MONT NEXT_DAY LAST_DAY
-------- -------------------------------- -------- -------- --------
80/12/17 506.147917 81/06/17 80/12/19 80/12/31
81/02/20 504.051142 81/08/20 81/02/27 81/02/28
81/02/22 503.986626 81/08/22 81/02/27 81/02/28
81/04/02 502.631788 81/10/02 81/04/03 81/04/30
81/09/28 496.793078 82/03/28 81/10/02 81/09/30
81/05/01 501.664046 81/11/01 81/05/08 81/05/31
81/06/09 500.405981 81/12/09 81/06/12 81/06/30
87/04/19 430.083401 87/10/19 87/04/24 87/04/30
81/11/17 495.147917 82/05/17 81/11/20 81/11/30
81/09/08 497.438239 82/03/08 81/09/11 81/09/30
87/05/23 428.954368 87/11/23 87/05/29 87/05/31
HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE) ADD_MONT NEXT_DAY LAST_DAY
-------- -------------------------------- -------- -------- --------
81/12/03 494.59953 82/06/03 81/12/04 81/12/31
81/12/03 494.59953 82/06/03 81/12/04 81/12/31
82/01/23 492.954368 82/07/23 82/01/29 82/01/31
|
cs |
5. 형변환
- NUMBER(숫자), CHAR(문자), DATE(날짜) 간의 형변환
> NUMBER: 20 / CHAR: ‘20’, ‘1980/01/01’ / DATE: 1980/01/01
- NUMBER <-> CHAR 간의 형변환 가능
- CHAR <-> DATE 간의 형변환 가능
1) 암시적 형변환: 자동
> 더 편리하지만, 버전이 바뀌면 자동으로 되던 것이 자동으로 되지 않아 오류가 발생할 수도
2) 명시적 형변환: 수동(함수)
- TO_반환할형(변경전값, 형식포맷)
1. TO_NUMBER
2. TO_CHAR
3. TO_DATE
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE DEPTNO = 20;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000
SQL>
SQL>
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE DEPTNO = '20';
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000
SQL> -- 형식이 숫자/문자로 다르지만, 오라클이 알아서 문자를 숫자로 형변환 해서 실행해 주는 것
SQL> --> 암시적 형변환
SQL> SELECT SYSDATE - '1980/01/01'
2 FROM DUAL;
SELECT SYSDATE - '1980/01/01'
*
1행에 오류:
ORA-01722: 수치가 부적합합니다
SQL> --SYSDATE는 날짜, '1980/01/01'는 문자이기 때문에 XX > 암시적 형변환은 항상 일어나진 않음
SQL> SELECT SYSDATE - TO_DATE('1980/01/01', 'YYYY/MM/DD')
2 FROM DUAL;
SYSDATE-TO_DATE('1980/01/01','YYYY/MM/DD')
------------------------------------------
15757.6075
SQL> SELECT SYSDATE - TO_DATE('1996/05/03', 'YYYY/MM/DD')
2 FROM DUAL;
SYSDATE-TO_DATE('1996/05/03','YYYY/MM/DD')
------------------------------------------
9790.60837
SQL> SELECT TO_CHAR(SYSDATE, 'CC YYYY/MM/DD:HH:MI:SS')
2 FROM DUAL;
TO_CHAR(SYSDATE,'CCYYY
----------------------
21 2023/02/21:02:42:52
SQL> --HH24로 하면 24시 기준으로 표시
SQL> -- $2,975형식의 문자로 SAL 표시
SQL> SELECT ENAME, TO_CHAR(SAL, '$999,999.99')
2 FROM EMP;
ENAME TO_CHAR(SAL,
---------- ------------
SMITH $800.00
ALLEN $1,600.00
WARD $1,250.00
JONES $2,975.00
MARTIN $1,250.00
BLAKE $2,850.00
CLARK $2,450.00
SCOTT $3,000.00
KING $5,000.00
TURNER $1,500.00
ADAMS $1,100.00
ENAME TO_CHAR(SAL,
---------- ------------
JAMES $950.00
FORD $3,000.00
MILLER $1,300.00
SQL> SELECT ENAME, TO_CHAR(SAL, 'L999,999.99')
2 FROM EMP;
ENAME TO_CHAR(SAL,'L999,999
---------- ---------------------
SMITH ￦800.00
ALLEN ￦1,600.00
WARD ￦1,250.00
JONES ￦2,975.00
MARTIN ￦1,250.00
BLAKE ￦2,850.00
CLARK ￦2,450.00
SCOTT ￦3,000.00
KING ￦5,000.00
TURNER ￦1,500.00
ADAMS ￦1,100.00
ENAME TO_CHAR(SAL,'L999,999
---------- ---------------------
JAMES ￦950.00
FORD ￦3,000.00
MILLER ￦1,300.00
|
cs |
6. 일반: NVL, DECODE
- NVL(COLUMN, n): 만약에 COLUMN의 값이 NULL이라면 n을 반환
- NVL2(COMM, n, m): 만약에 COLUMN의 값이 NULL이라면 n을, 아니라면 m을 반환
- DECODE(COLUMN, ‘nn’, mm): 만약에 COLUMN의 값이 nn이라면 mm(수식도 가능)을 변환
> DECODE(COLUMN, ‘nn’, mm, ‘nnn’, mmm, mmmm)
+ CASE문
> DECODE는 오라클 함수, CASE는 표준(다른 데이터베이스도 가능)
|
SQL
- 대소문자 구분X - 언어형식 - 변수 사용할 수 없음 - 제어문(if)이 없음 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
SQL> -- NVL (NULL VALUE)
SQL>
SQL> SELECT ENAME, SAL, COMM, NVL(COMM, 0)
2 FROM EMP;
ENAME SAL COMM NVL(COMM,0)
---------- ---------- ---------- -----------
SMITH 800 0
ALLEN 1600 300 300
WARD 1250 500 500
JONES 2975 0
MARTIN 1250 1400 1400
BLAKE 2850 0
CLARK 2450 0
SCOTT 3000 0
KING 5000 0
TURNER 1500 0 0
ADAMS 1100 0
ENAME SAL COMM NVL(COMM,0)
---------- ---------- ---------- -----------
JAMES 950 0
FORD 3000 0
MILLER 1300 0
SQL> SELECT ENAME, SAL, COMM, SAL*12 + NVL(COMM,0), NVL2(COMM, 100, 200)
2 FROM EMP;
ENAME SAL COMM SAL*12+NVL(COMM,0) NVL2(COMM,100,200)
---------- ---------- ---------- ------------------ ------------------
SMITH 800 9600 200
ALLEN 1600 300 19500 100
WARD 1250 500 15500 100
JONES 2975 35700 200
MARTIN 1250 1400 16400 100
BLAKE 2850 34200 200
CLARK 2450 29400 200
SCOTT 3000 36000 200
KING 5000 60000 200
TURNER 1500 0 18000 100
ADAMS 1100 13200 200
ENAME SAL COMM SAL*12+NVL(COMM,0) NVL2(COMM,100,200)
---------- ---------- ---------- ------------------ ------------------
JAMES 950 11400 200
FORD 3000 36000 200
MILLER 1300 15600 200
|
cs |
++ 활용
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
SQL> -- JOB이 MANAGER인 사람들은 임금 10% 인상
SQL> -- JOB이 CLERK인 사람들은 임금 5% 인상
SQL> -- 그 외 나머지 임금 3% 인상
SQL>
SQL> SELECT ENAME, JOB, SAL, DECODE(JOB, 'MANAGER', SAL*1.1,
2 'CLERK', SAL*1.05,
3 SAL*1.3) "UPSAL"
4 FROM EMP;
ENAME JOB SAL UPSAL
---------- --------- ---------- ----------
SMITH CLERK 800 840
ALLEN SALESMAN 1600 2080
WARD SALESMAN 1250 1625
JONES MANAGER 2975 3272.5
MARTIN SALESMAN 1250 1625
BLAKE MANAGER 2850 3135
CLARK MANAGER 2450 2695
SCOTT ANALYST 3000 3900
KING PRESIDENT 5000 6500
TURNER SALESMAN 1500 1950
ADAMS CLERK 1100 1155
ENAME JOB SAL UPSAL
---------- --------- ---------- ----------
JAMES CLERK 950 997.5
FORD ANALYST 3000 3900
MILLER CLERK 1300 1365
SQL> SELECT ENAME, JOB, SAL, DECODE(JOB, 'MANAGER', SAL*1.1, 'CLERK', SAL*1.05, SAL*1.03) "UPSAL2"
2 FROM EMP;
ENAME JOB SAL UPSAL2
---------- --------- ---------- ----------
SMITH CLERK 800 840
ALLEN SALESMAN 1600 1648
WARD SALESMAN 1250 1287.5
JONES MANAGER 2975 3272.5
MARTIN SALESMAN 1250 1287.5
BLAKE MANAGER 2850 3135
CLARK MANAGER 2450 2695
SCOTT ANALYST 3000 3090
KING PRESIDENT 5000 5150
TURNER SALESMAN 1500 1545
ADAMS CLERK 1100 1155
ENAME JOB SAL UPSAL2
---------- --------- ---------- ----------
JAMES CLERK 950 997.5
FORD ANALYST 3000 3090
MILLER CLERK 1300 1365
SQL> -- SQL92, SQL99 <==
SQL> -- SQL문은 계속 발전됨
SQL>
SQL> SELECT ENAME, JOB, SAL, DECODE(JOB, 'MANAGER', SAL*1.1, 'CLERK', SAL*1.05, SAL*1.03) "UPSAL2",
2 CASE
3 WHEN JOB = 'MANAGER' THEN SAL*1.1
4 WHEN JOB = 'CLERK' THEN SAL*1.05
5 ELSE
6 SAL*1.03
7 END "UPSAL3"
8 FROM EMP;
ENAME JOB SAL UPSAL2 UPSAL3
---------- --------- ---------- ---------- ----------
SMITH CLERK 800 840 840
ALLEN SALESMAN 1600 1648 1648
WARD SALESMAN 1250 1287.5 1287.5
JONES MANAGER 2975 3272.5 3272.5
MARTIN SALESMAN 1250 1287.5 1287.5
BLAKE MANAGER 2850 3135 3135
CLARK MANAGER 2450 2695 2695
SCOTT ANALYST 3000 3090 3090
KING PRESIDENT 5000 5150 5150
TURNER SALESMAN 1500 1545 1545
ADAMS CLERK 1100 1155 1155
ENAME JOB SAL UPSAL2 UPSAL3
---------- --------- ---------- ---------- ----------
JAMES CLERK 950 997.5 997.5
FORD ANALYST 3000 3090 3090
MILLER CLERK 1300 1365 1365
SQL> SELECT ENAME, JOB, SAL,
2 CASE
3 WHEN JOB = 'MANAGER' THEN SAL*1.1
4 WHEN JOB = 'CLERK' THEN SAL*1.05
5 ELSE
6 SAL*1.03
7 END "UPSAL3"
8 FROM EMP;
ENAME JOB SAL UPSAL3
---------- --------- ---------- ----------
SMITH CLERK 800 840
ALLEN SALESMAN 1600 1648
WARD SALESMAN 1250 1287.5
JONES MANAGER 2975 3272.5
MARTIN SALESMAN 1250 1287.5
BLAKE MANAGER 2850 3135
CLARK MANAGER 2450 2695
SCOTT ANALYST 3000 3090
KING PRESIDENT 5000 5150
TURNER SALESMAN 1500 1545
ADAMS CLERK 1100 1155
ENAME JOB SAL UPSAL3
---------- --------- ---------- ----------
JAMES CLERK 950 997.5
FORD ANALYST 3000 3090
MILLER CLERK 1300 1365
SQL> SELECT HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE)
2 FROM EMP;
HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE)
-------- --------------------------------
80/12/17 506.151214
81/02/20 504.05444
81/02/22 503.989923
81/04/02 502.635085
81/09/28 496.796375
81/05/01 501.667343
81/06/09 500.409278
87/04/19 430.086698
81/11/17 495.151214
81/09/08 497.441536
87/05/23 428.957665
HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE)
-------- --------------------------------
81/12/03 494.602827
81/12/03 494.602827
82/01/23 492.957665
14 개의 행이 선택되었습니다.
SQL> -- 함수가 중첩되면 안쪽에 있는 함수가 먼저 실행, 그 뒤에 밖의 함수가 실행
SQL> -- 함수의 중첩
SQL> -- 사원명 입사일 6개월 후 금요일의 날짜
SQL> -- ------ ------ ---------------
SQL> SELECT ENAME, HIREDATE, NEXT_DAY(ADD_MONTHS(HIREDATE, 6), '금')
2 FROM EMP;
ENAME HIREDATE NEXT_DAY
---------- -------- --------
SMITH 80/12/17 81/06/19
ALLEN 81/02/20 81/08/21
WARD 81/02/22 81/08/28
JONES 81/04/02 81/10/09
MARTIN 81/09/28 82/04/02
BLAKE 81/05/01 81/11/06
CLARK 81/06/09 81/12/11
SCOTT 87/04/19 87/10/23
KING 81/11/17 82/05/21
TURNER 81/09/08 82/03/12
ADAMS 87/05/23 87/11/27
ENAME HIREDATE NEXT_DAY
---------- -------- --------
JAMES 81/12/03 82/06/04
FORD 81/12/03 82/06/04
MILLER 82/01/23 82/07/30
SQL> SELECT ENAME "사원명", HIREDATE "입사일", NEXT_DAY(ADD_MONTHS(HIREDATE, 6), '금') "6개월후금"
2 FROM EMP;
사원명 입사일 6개월후
---------- -------- --------
SMITH 80/12/17 81/06/19
ALLEN 81/02/20 81/08/21
WARD 81/02/22 81/08/28
JONES 81/04/02 81/10/09
MARTIN 81/09/28 82/04/02
BLAKE 81/05/01 81/11/06
CLARK 81/06/09 81/12/11
SCOTT 87/04/19 87/10/23
KING 81/11/17 82/05/21
TURNER 81/09/08 82/03/12
ADAMS 87/05/23 87/11/27
사원명 입사일 6개월후
---------- -------- --------
JAMES 81/12/03 82/06/04
FORD 81/12/03 82/06/04
MILLER 82/01/23 82/07/30
|
cs |
더보기
++ 응용
1. 이름에 K가 들어있는 사원들의 사번, 이름, 급여 , 부서번호?
SQL> SELECT EMPNO, ENAME, SAL, DEPTNO
2 FROM EMP
3 WHERE ENAME LIKE '%K%';
11. COMM 을 받지 않는 사원(0도 포함)들의 사번, 이름, 급여, COMM ?
SQL> SELECT EMPNO, ENAME, SAL, COMM
2 FROM EMP
3 WHERE COMM IS NULL
4 OR COMM = '0';
12.입사일이 82년2월1일 이후인 사원의 모든 정보를 출력하라.
SQL> SELECT * FROM EMP
2 WHERE HIREDATE >= '82/02/01';
13.연봉이 2500만원 이상이면서 5000만원이하인 직원의 이름과 부서명,입사날짜를 출력하라. (xxxxx )
14.이름에 S 또는 I를 포함한 사원의 이름, JOB, 부서번호, 사원번호, 연봉을 출력하라
SQL> SELECT ENAME, JOB, DEPTNO, EMPNO, SAL*12
2 FROM EMP
3 WHERE ENAME LIKE '%S%I%';
15.급여가 2500 달러 이상 3000 달러 이하인 사원의 사원번호, 이름, 부서번호, 입사일을 출력하라
SQL> SELECT EMPNO, ENAME, DEPTNO, HIREDATE
2 FROM EMP
3 WHERE SAL BETWEEN 2500 AND 3000;
16.이름이 s자로 시작하고 마지막 글자가 h인 사람의 모든정보를 출력?
SQL> SELECT * FROM EMP
2 WHERE ENAME LIKE 'S%H';
17.부서 번호가 20인 부서의 일일당 임금을 계산 하여 출력? (단 1달은 30일로하자)
SQL> SELECT ENAME, SAL/30, DEPTNO
2 FROM EMP
3 WHERE DEPTNO = 20;18. 입사일이 빠른순서대로 이름과 월급을 출력하여라 (xxxxx)
19. 이름이 다섯자인 사람의 리스트를 출력하여라
SQL> SELECT ENAME
2 FROM EMP
3 WHERE LENGTH(ENAME) = 5;
20. 메니저(MGR)가 7566과 7782인 사원번호, 사원명, 메니저, 부서번호를 출력하세요.
SQL> SELECT EMPNO, ENAME, MGR, DEPTNO
2 FROM EMP
3 WHERE MGR IN ('7566', '7782');
21. 급여가 1100보다 크고 2000보다 작은 사원번호, 사원명, 급여를 출력하세요.
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE SAL BETWEEN 1100 AND 2000;
* 활용 문제
Step1. 이름과 근속기간 (월단위 절사)
SQL> SELECT ENAME "사원명", TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE), 0) "근속기간"
2 FROM EMP;
step2 . 이름 근속기간
SQL> SELECT ENAME "사원명", TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12, 0) "근속년",
2 MOD(TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE),0),12) "근속월"
3 FROM EMP;
step3 . 이름 근속기간
SQL> SELECT ENAME "사원명", TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12, 0) || '년' ||
2 MOD(TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE),0),12) || '개월' "근속기간"
3 FROM EMP;
+)
SQL> -- 출력된 컬럼의 길이를 조절
SQL> COL 근속기간 FORMAT A25
++ SQL문 파일 저장 / 저장 경로 확인 / 스풀(SPOOL) / SET TIMING
| ** C드라이브에 파일 생김 ** SQL>-- 방금 전에 실행했던 SQL 문을 파일로 저장 SQL> save B1 file B1.sql(이)가 생성되었습니다 SQL> host Microsoft Windows [Version 10.0.22000.1455] (c) Microsoft Corporation. All rights reserved. C:\Users\tinyt> SQL>-- 잠깐 빠져나옴 C:\Users\tinyt>dir b1.sql C 드라이브의 볼륨에는 이름이 없습니다. 볼륨 일련 번호: 585B-C433 C:\Users\tinyt 디렉터리 2023-02-21 오전 11:46 153 B1.sql 1개 파일 153 바이트 0개 디렉터리 102,520,942,592 바이트 남음 -- 어디 저장되었는지 C:\Users\tinyt>notepad b1.sql SQL>-- 파일 열기 C:\Users\tinyt>exit SQL>-- 끝낼 때는 exit SQL>-- 저장된 파일 불러올 수도 있음 SQL> @b1.sql SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) CONCAT(ENAME,JOB) INSTR(ENAME,'A') ------ -------------- ------------- ------------------- ---------------- LPAD(SAL,10,'*') RPAD(SAL,10,'*') -------------------- -------------------- SMI TH 5 SMITHCLERK 0 *******800 800******* ALL EN 5 ALLENSALESMAN 1 ******1600 1600****** WAR D 4 WARDSALESMAN 2 ******1250 1250****** SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) CONCAT(ENAME,JOB) INSTR(ENAME,'A') ------ -------------- ------------- ------------------- ---------------- LPAD(SAL,10,'*') RPAD(SAL,10,'*') -------------------- -------------------- JON ES 5 JONESMANAGER 0 ******2975 2975****** MAR TIN 6 MARTINSALESMAN 2 ******1250 1250****** BLA KE 5 BLAKEMANAGER 3 ******2850 2850****** SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) CONCAT(ENAME,JOB) INSTR(ENAME,'A') ------ -------------- ------------- ------------------- ---------------- LPAD(SAL,10,'*') RPAD(SAL,10,'*') -------------------- -------------------- CLA RK 5 CLARKMANAGER 3 ******2450 2450****** SCO TT 5 SCOTTANALYST 0 ******3000 3000****** KIN G 4 KINGPRESIDENT 0 ******5000 5000****** SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) CONCAT(ENAME,JOB) INSTR(ENAME,'A') ------ -------------- ------------- ------------------- ---------------- LPAD(SAL,10,'*') RPAD(SAL,10,'*') -------------------- -------------------- TUR NER 6 TURNERSALESMAN 0 ******1500 1500****** ADA MS 5 ADAMSCLERK 1 ******1100 1100****** JAM ES 5 JAMESCLERK 2 *******950 950******* SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) CONCAT(ENAME,JOB) INSTR(ENAME,'A') ------ -------------- ------------- ------------------- ---------------- LPAD(SAL,10,'*') RPAD(SAL,10,'*') -------------------- -------------------- FOR D 4 FORDANALYST 0 ******3000 3000****** MIL LER 6 MILLERCLERK 0 ******1300 1300****** SQL>-- 이렇게 경로 지정해서 저장할 수 있음. 안되면 시작할 때 관리자 권한으로 해야함! SQL> save c:\b2.sql SP2-0110: 저장 파일 "c:\b2.sql"(를)을 생성할 수 없습니다. SQL>-- 지금부터 실행하는 모든 명령어, 출력 결과, 에러 메시지까지 를 B3에 저장 SQL> SPOOL B3 SQL> SPOOL B3.LST에 스풀 중입니다. SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> SPOOL OFF SQL>-- 꼭 SPOOL OFF해야 파일이 저장됨! SQL>-- 그리고 SPOOL은 보통 덮어쓰기 SQL>-- 메모장에 LST파일을 드래그 앤 드랍해서 확인할 수 있음 SQL>-- 따로 메모할 필요 없음! SQL>-- 주석으로 코드의 설명까지 추가 가능 SQL>-- 해당 명령어 실행하는 데에 얼마나 시간이 소요되는지 확인 SQL> SET TIMING ON SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 경 과: 00:00:00.01 SQL>-- 시간 기록 SQL> SET TIME ON 12:21:49 SQL> SET TIME OFF |