NULL은 존재하지 않는 값이므로 연산에 사용할 수 없다.
SELECT 1+2+3+4+5, 5-2, 5*2, 5/2
FROM DUAL;
SELECT 1+2+3+4+5, 5-NULL, 5*NULL, 5/1, 5/NULL
FROM DUAL;
SELECT NAME, COMM
FROM TBL_NEWSAWON;
SELECT NAME, COMM
FROM TBL_NEWSAWON
WHERE COMM =0;
SELECT NAME, COMM
FROM TBL_NEWSAWON
WHERE COMM IS NULL; -- = 대신 IS 해서 쓴다.
SELECT NAME, COMM
FROM TBL_NEWSAWON
WHERE NOT COMM IS NULL;
SELECT NAME, COMM
FROM TBL_NEWSAWON
WHERE COMM IS NOT NULL; -- 위의 것과 같은 결과 값
SELECT NAME AS "사원명", SAL "급여", COMM 커미션,
--연봉 => 12달 급여 + 커미션
-- SAL*12 + COMM 이것이 아니라
NVL(COMM,0) "연봉", -- NULL이라면 0로 나타내라
NVL2(COMM, 999, 888), -- NULL이라면 999, NULL이 아니라면 888
NVL2(COMM, COMM, 0)
FROM TBL_NEWSAWON;
SELECT NAME,SAL, COMM, -- 위와 똑같은 것
SAL*12+ NVL(COMM, 0),
NVL2(COMM, SAL*12 + COMM, SAL*12)
FROM TBL_NEWSAWON;
SELECT NAME,SAL, COMM,
SAL*12+ NVL(COMM, 0),
NVL2(COMM, SAL*12 + COMM, SAL*12),
COALESCE(SAL*12 + COMM, SAL*12, COMM,0)
-- NULL이면 그 다음 값을, 그 다음 값을 ..., NULL 이 안 나올 때까지 나온다.
FROM TBL_NEWSAWON;
<<급여와 보너스를 합해서 연봉을 구하기>>
SELECT FIRST_NAME AS "성",
SALARY AS "급여",
COMMISSION_PCT AS "보너스%",
(SALARY + NVL(SALARY*COMMISSION_PCT,0) ) * 12 AS "연봉1",
NVL2(COMMISSION_PCT,
(SALARY + SALARY*COMMISSION_PCT) * 12,
SALARY * 12) AS "연봉2",
COALESCE((SALARY + SALARY*COMMISSION_PCT) * 12,
SALARY * 12, 0) AS "연봉3"
FROM EMPLOYEES;
문자, 날짜, 숫자, 어떤 것이든 연결시켜주는 연결 연산자인 || 연산자
SELECT *
FROM TBL_NEWSAWON;
SELECT '사원번호가'|| SANO || '인' || NAME || HIREDATE ||'입니다.'
FROM TBL_NEWSAWON;
<<값>>
사원번호가1001인한석규90/01/02입니다.
사원번호가1002인두석규92/01/03입니다.
사원번호가1003인세석규94/01/04입니다.
사원번호가1004인네석규96/01/05입니다.
사원번호가1005인오연수96/01/06입니다.
사원번호가2001인일용이00/01/07입니다.
사원번호가2002인이미자01/01/08입니다.
TBL_NEWSAWON 테이블에서 급여가 2000 이상 4000 이하
직원들의 정보를 사원번호, 사원명, 직급, 급여로 나타내세요.
SELECT DEPTNO, SANO, NAME, JIK, SAL -- 이것을 권장
FROM TBL_NEWSAWON
WHERE SAL>=2000 AND SAL <=4000;
SELECT DEPTNO, SANO, NAME, JIK, SAL -- (위의 값과 같음)
FROM TBL_NEWSAWON
WHERE SAL BETWEEN 2000 AND 4000; -- 2000 이상 4000이하
SELECT DEPTNO, SANO, NAME, JIK, SAL
FROM TBL_NEWSAWON
WHERE SAL < 2000 OR SAL > 4000; --2000 작고 4000크다
SELECT DEPTNO, SANO, NAME, JIK, SAL --위의 값과 같음
FROM TBL_NEWSAWON
WHERE NOT (SAL BETWEEN 2000 AND 4000);
정렬함수 (ORDER BY)
SELECT DEPTNO, SANO, NAME, JIK,
NVL2(COMM,SAL*12 + COMM, SAL *12)
AS "YEARPAY"
FROM TBL_NEWSAWON
WHERE SAL >= 2000 AND SAL <= 4000
ORDER BY NVL2(COMM, SAL*12 + COMM, SAL*12) DESC;
SELECT DEPTNO, SANO, NAME, JIK,
NVL2(COMM,SAL*12 + COMM, SAL *12)
AS "YEARPAY"
FROM TBL_NEWSAWON
WHERE SAL>=2000 AND SAL <=4000
ORDER BY 5 DESC; -- 내림차순 (5:다섯번째 컬럼을 내림차순으로)
<값>
DEPTNO SANO NAME JIK YEARPAY
10 1002 두석규 부장 48600
30 3002 노주현 과장 48600
20 2002 이미자 과장 48600
20 2001 일용이 부장 48500
10 1003 세석규 과장 36500
20 2003 삼미자 사원 36500
30 3001 고소영 부장 36400
30 3003 도지원 사원 36200
30 3009 이자연 대리 36000
20 2004 사미자 사원 36000
10 1004 네석규 사원 24000
SELECT DEPTNO, SANO, NAME, JIK,
NVL2(COMM,SAL*12 + COMM, SAL *12)
AS "YEARPAY"
FROM TBL_NEWSAWON
WHERE SAL>=2000 AND SAL <=4000
ORDER BY 5 ASC; -- 오름차순 (생략가능)
<값>
DEPTNO SANO NAME JIK YEARPAY
30 3005 마광수 사원 24000
10 1004 네석규 사원 24000
10 1005 오연수 사원 24000
30 3004 류시원 사원 24000
20 2002 이미자 과장 48600
10 1002 두석규 부장 48600
30 3002 노주현 과장 48600
SELECT DEPTNO, SANO, NAME, JIK,
NVL2(COMM,SAL*12 + COMM, SAL *12)
AS "YEARPAY"
FROM TBL_NEWSAWON
WHERE SAL>=2000 AND SAL <=4000
ORDER BY 4,5 DESC;-- 같은 차순에 연봉을 내림차순으로 보고 싶다.
<값>
DEPTNO SANO NAME JIK YEARPAY
30 3002 노주현 과장 48600
20 2002 이미자 과장 48600
10 1003 세석규 과장 36500
10 1005 오연수 사원 24000
10 1004 네석규 사원 24000
30 3004 류시원 사원 24000
30 3005 마광수 사원 24000
SELECT DISTINCT DEPTNO, JIK -- DISTINCT는 중복되는 값을 하나만 출력
FROM TBL_NEWSAWON
ORDER BY 1; --NULL을 오라클은 가장 큰 값으로 간주
<값>
DEPTNO JIK
10 과장
10 사원
10 사장
20 과장
20 사원
30 과장
30 사원
NULL 사원
단일행함수
문자 함주, 숫자함수, 날짜함수
1. 문자함수
A. UPPER -- 모든 문자를 대문자로 변환시켜주는 함수
B. LOWER -- 모든 문자를 소문자로 변환시켜주는 함수
C. INITCAP -- 첫글자만 대문자로 변환시키고 나머지 글자는
-- 모두 소문자로 변환시켜주는 함수
SELECT JOB,UPPER(JOB), LOWER(JOB),INITCAP(JOB)
FROM EMP
WHERE JOB ='sAleSMan'
<<값>>
JOB UPPER(JOB) LOWER(JOB) INITCAP(JOB)
sAleSMan SALESMAN salesman Salesman
D. CONCAT
-- 두개의 문자열을 연결시켜주는 함수
SELECT NAME,JIK,
CONCAT(NAME,JIK) -- 두개만 붙일 수 있다.
FROM TBL_NEWSAWON;
<값>
NAME JIK CONCAT(NAME,JIK)
한석규 사장 한석규사장
두석규 부장 두석규부장
세석규 과장 세석규과장
네석규 사원 네석규사원
오연수 사원 오연수사원
SELECT NAME,JIK,JUBUN,
--CONCAT(NAME,JIK || JUBUN),
CONCAT(CONCAT(NAME,JIK), JUBUN )
FROM TBL_NEWSAWON;
<값>
NAME JIK JUBUN CONCAT(CONCAT(NAME,JIK),JUBUN)
한석규 사장 7110031234567 한석규사장7110031234567
두석규 부장 7210031234567 두석규부장7210031234567
세석규 과장 7310031234567 세석규과장7310031234567
네석규 사원 7410031234567 네석규사원7410031234567
오연수 사원 7510032234567 오연수사원7510032234567
일용이 부장 8110031234567 일용이부장8110031234567
DESC TBL_SAWON;
E. SUBSTR
--특정 문자열에서 특정한 일부부만 추출할 때 사용 하는 함수이다.
SELECT 'KH정보교육원',
SUBSTR('KH정보교육원',1,4), -- 첫번째 글자에서 4번째까지 뽑아라
SUBSTR('KH정보교육원',2,4),
SUBSTR('KH정보교육원',3) -- 세번째 글자부터 끝까지 뽑아라
FROM DUAL;
<값>
KH정보교육원 KH정보 H정보교 정보교육원
SELECT NAME,SUBSTR(JUBUN,1,6),JIK
FROM TBL_NEWSAWON;
<퀴즈> TBL_NEWSAWON 테이블에서 여자만 사원번호, 사원명, 직급을 나타내세요.
<값>
SELECT JUBUN,SANO,NAME, JIK
FROM TBL_NEWSAWON
WHERE SUBSTR (JUBUN,7,1)=2 OR SUBSTR (JUBUN,7,4)=4; -- IN (2,4)
SELECT 'KH정보교육원',
SUBSTRB('KH정보교육원',1,4), -- B는바이트를 뜻한다. 첫~4바이트까지
SUBSTRB('KH정보교육원',2,4),
SUBSTRB('KH정보교육원',4)
FROM DUAL;
<값>
'KH정보교육원' SUBSTRB('KH정보교육원',1,4) SUBSTRB('KH정보교육원',2,4)
KH정보교육원 KH정
SUBSTRB('KH정보교육원',4)
H정보교육원
F. REVERSE
-- 행당 문자열로 거꾸로 보여 주는 함수이다.
SELECT 'ORACLE','KH정보교육원',
REVERSE('ORACLE'), -- 거꾸로 돌린다는 것
REVERSE ('KH정보교육원')
FROM DUAL;
<값>
ORACLE KH정보교육원 ELCARO 貶걋낢맏ㅑHK
SELECT JUBUN, REVERSE(JUBUN)
FROM TBL_NEWSAWON;
<값>
7110031234567 7654321300117
7210031234567 7654321300127
7310031234567 7654321300137
G. INSTR
-- 어떤 문자열에서 특정글자가 나오는 위치값을 알려주는 함수이다.
SELECT 'KH정부교육원 통신정보학',
INSTR('KH정부교육원 통신정보학', '정보',1,1),
INSTR('KH정부교육원 통신정보학', '정보',4,1),
INSTR('KH정부교육원 통신정보학', '정보',1,2),
INSTR('KH정부교육원 통신정보학', '정보',1,3),
INSTR('KH정부교육원 통신정보학', '정보',2),
INSTR('KH정부교육원 통신정보학', '정보',4)
FROM DUAL;
<값>
KH정부교육원 통신정보학 11 11 0 0 11 11
CREATE TABLE TBL_FILEINFO –테이블 생성
(FILENO NUMBER
,FILENAME VARCHAR2(500)
);
<<값 넣기>>
INSERT INTO TBL_FILEINFO VALUES
(1, 'C:\MYDOCUMENT\SALES\W010년11월영업계획.DOC');
INSERT INTO TBL_FILEINFO VALUES
(2, 'D:\ORACLESTUDY.TXT');
INSERT INTO TBL_FILEINFO VALUES
(3, 'C:\MUSIC\MP3\KOREA\DONGYO\학교종이땡땡땡.MP3');
COMMIT;
SELECT *
FROM TBL_FILEINFO;
<문제>
1 C:\MYDOCUMENT\SALES\W010년11월영업계획.DOC
2 D:\ORACLESTUDY.TXT
3 C:\MUSIC\MP3\KOREA\DONGYO\학교종이땡땡땡.MP3
최종 파일 네임만 나타내 주세요.
<<답>>
SELECT SUBSTR(FILENAME,1,1),
REVERSE (SUBSTR (REVERSE (FILENAME),1, INSTR (REVERSE (FILENAME),'\',1,1)-1))
FROM TBL_FILEINFO;
<값>
C W010년11월영업계획.DOC
D ORACLESTUDY.TXT
C 학교종이땡땡땡.MP3
H. LENGTH
-- 문자열의 길이를 나타내는 함수
SELECT LENGTH('KH정보교육원'), LENGTH('KH 정보교육원')
FROM DUAL;
--> 7 (문자열의 길이를 나타남) , 8
SELECT LENGTHB('KH정보교육원'), LENGTHB('KH 정보교육원')
FROM DUAL;
--> 12 , 13
I. LPAD -- PAD는 채운다는 의미(LEFT PAD 약자)
-- 왼쪽부터 채워라
J. RPAD -- RIGHT PAD 약자
-- 오른쪽부터 채워라
SELECT LPAD('KH정보교육원', 20,'S')
FROM DUAL; --
-- 1. 20Byte를 확보해라(20글자가 아니다.)
-- 2. 확보한 20 byte 공간 안에 KH정보교육원(12BYTE)D을 넣어라.
-- 3. KH정보교육원(12BYTE)을 넣고 남은 왼쪽 빈공간에
-- 'S'를 넣어 채워라.
--> SSSSSSSSKH정보교육원
SELECT LPAD('KH정보교육원', 20,'한국')
FROM DUAL;
--> 한국한국KH정보교육원
SELECT LPAD('*',6,'*')
FROM DUAL;
-- --> ******
K. TRIM
-- :TRIM은 잘라내는 것
-- LTRIM / RTRIM
SELECT 'BDABBABCAAACCDAABBCC',
LTRIM('BCABBABCAAACCDAABBCC','BAC')
-– 왼쪽부터 잘라나가다가 D 를 만나서 그만
FROM DUAL;
<< 결과 값>>
--> BDABBABCAAACCDAABBCC DAABBCC
-- ==> LTRIM 은 출발점이 왼쪽
-- BAC 가 나오면 TRIM(삭제)
-- 그러므로 ABC에 포함되지 않은 D 가 나올 때까지
-- 왼쪽부터 삭제한 후 그 결과 값을 보여준다.
--> BDABBABCAAACCDAABBCC BCABBABCAAACCD (RTRIM의 결과 값)
SELECT ' 김성훈 멋지다. ',
'KH정보교육원' || ' 김성훈 멋지다. '
FROM DUAL;
<<결과 값>>
-- > 김성훈 멋지다. KH정보교육원 김성훈 멋지다.
SELECT LTRIM(' 김성훈 멋지다. '),
'KH정보교육원' ||
LTRIM(' 김성훈 멋지다. ')
FROM DUAL;
<<결과 값>>
-- > 김성훈 멋지다. KH정보교육원김성훈 멋지다.
SELECT '대'||' 한민국 '||'짝짝짝짝짝~!',
'대'||LTRIM(' 한민국 ')||'짝짝짝짝짝~!',
'대'||RTRIM(' 한민국 ')||'짝짝짝짝짝~!'
FROM DUAL;
<<결과 값>>
--> 대 한민국 짝짝짝짝짝~!
--> 대한민국 짝짝짝짝짝~!대 한민국짝짝짝짝짝~!
--> 대 한민국짝짝짝짝짝~!
'IT. 컴퓨터' 카테고리의 다른 글
| [오라클] 오라클 설치 및 SQL Gate 설치 접속 (0) | 2020.04.29 |
|---|---|
| [오라클] 여러함수들 (0) | 2020.04.26 |
| [오라클] 괄호, AND, OR, NOT, =, !=,(^=, <>) 연산 (0) | 2020.04.22 |
| 제품 테이블, 판매 테이블 생성 및 백업 (1) | 2020.04.21 |
| STATUS 제약조건 (0) | 2020.04.20 |
