티스토리 뷰
반응형
강사 : 김호진 강사님
Oracle SQL Developer (3일차)
20180109_scott
Test08
--○ TBL_EMP 테이블에서 모든 사원들의
-- 사원번호, 사원명, 급여, 커미션, 연봉 정보를 조회한다.
-- 단, 급여(SAL)는 매월 지급한다.
-- 수당(COMM)은 1회/년 지급한다.
SELECT EMPNO 사원번호, ENAME 사원명, SAL 급여, COMM 커미션
, (12*SAL) + COMM 연봉
FROM TBL_EMP;
--> 이와 같이 연봉을 연산하여 조회할 경우
-- 수당(COMM)이 NULL 인 직원들은 연봉까지도 NULL 로 연산되는 것을
-- 확인할 수 있다.
SELECT EMPNO 사원번호, ENAME 사원명, SAL 급여, COMM 커미션
, (12*SAL) 연봉
FROM TBL_EMP;
--> 그렇다고 수당(COMM)을 연산 대상에서 제외시키거나
-- 0 으로 가정하고 연산을 수행할 경우
-- 수당(COMM)이 존재하는 직원들의 연산 결과가 잘못되어 처리된다.
NVL()
--○ NVL()
SELECT NULL"①", NVL(NULL, 0)"②", NVL(2, 0)"③"
FROM DUAL;
--==>> (NULL) 0 2
--> 첫 번째 파라미터 값이 NULL 이면, 두 번째 파라미터 값을 적용한다.
-- 첫 번째 파라미터 값이 NULL 이 아니면, 그 값을 그대로 적용한다.
SELECT *
FROM TBL_EMP;
SELECT ENAME, NVL(COMM, 0)
FROM TBL_EMP;
SELECT EMPNO 사원번호, ENAME 사원명, SAL 급여, NVL(COMM, 0) 커미션
, (12*SAL) + NVL(COMM, 0) 연봉
FROM TBL_EMP;
--==>>
/*
7369 SMITH 800 0 9600
7499 ALLEN 1600 300 19500
7521 WARD 1250 500 15500
7566 JONES 2975 0 35700
7654 MARTIN 1250 1400 16400
7698 BLAKE 2850 0 34200
7782 CLARK 2450 0 29400
7788 SCOTT 3000 0 36000
7839 KING 5000 0 60000
7844 TURNER 1500 0 18000
7876 ADAMS 1100 0 13200
7900 JAMES 950 0 11400
7902 FORD 3000 0 36000
7934 MILLER 1300 0 15600
*/
NVL2()
--○ NVL2()
SELECT NVL2(NULL, 10, 20)"①", NVL2(0, 10, 20)"②"
FROM DUAL;
--==>> 20 10
--> 첫 번째 파라미터 값이 NULL 이 아닌 경우, 두 번째 파라미터 값을 반환하고,
-- 첫 번째 파라미터 값이 NULL 인 경우, 세 번째 파라미터 값을 반환한다.
Test09
--○ TBL_EMP 테이블에서 COMM 컬럼의 값이 NULL 이 아니면 3000,
-- NULL 이면 4000 으로 특별 수당을 지급하는 상황이다.
-- 이에 대한 연산 결과를 조회할 수 있도록 한다.
-- 사원명, 급여, 수당, 특별수당 항목을 조회한다.
SELECT ENAME"사원명", SAL"급여", NVL(COMM, 0)"수당", NVL2(COMM, 3000, 4000)"특별수당"
FROM TBL_EMP;
--○ TBL_EMP 테이블에서 모든 사원들의
-- 사원번호, 사원명, 급여, 커미션, 연봉 정보를 조회한다.
-- 단, 급여(SAL)는 매월 지급한다.
-- 수당(COMM)은 1회/년 지급한다.
-- 또한, NVL2() 함수를 활용하여 처리한다.
SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", NVL2(COMM, COMM, 0)"커미션"
--, (커미션이 NULL 이 아닌 사원들은 SAL*12 으로 처리하고... 커미션이 NULL 인 사원들은 SAL*12 로 처리한다.)"연봉"
, NVL2(COMM, SAL*12+COMM, SAL*12)"연봉"
FROM TBL_EMP;
COALESCE()
--○ COALESCE()
--> 맨 앞부터 차례로 값이 NULL 이 아니면 적용(반환)하고
-- NULL 인 경우에는 다음 파라미터를 확인하는 연산으로 넘어간다.
SELECT COALESCE(NULL, NULL, NULL, NULL, 10)"첫 번째 컬럼"
FROM DUAL;
--※ 모든 경우의 수를 확인할 수 있다.
-- TBL_EMP 테이블 데이터 수정
UPDATE TBL_EMP
SET SAL=NULL
WHERE EMPNO=7521;
--==>> 1 행 이(가) 업데이트되었습니다.
SELECT *
FROM TBL_EMP;
COMMIT;
--==>> 커밋 완료.
Test10
--○ TBL_EMP 테이블에서 연봉에 대한 연산 결과가 NULL 이 나오는 경우는
-- 급여(SAL)가 NULL, 혹은 수당(COMM)이 NULL, 또는 둘 다 NULL
-- 모든 경우를 고려하여 연봉을 조회할 수 있도록 쿼리문을 구성한다.
-- 사원번호, 사원명, 급여, 수당, 연봉을 조회한다.
-- 단, 연봉은 급여*12+수당 으로 연산처리한다.
-- COALESCE() 함수를 사용한다.
SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", COMM"수당"
, COALESCE(SAL*12+COMM, SAL*12, COMM, 0)"연봉"
FROM TBL_EMP;
-- NVL() 함수를 사용한다.
SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", COMM"수당"
, NVL(SAL*12+COMM, NVL(SAL*12, NVL(COMM, 0)))"연봉"
FROM TBL_EMP;
-- NVL2() 함수를 사용한다.
SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", COMM"수당"
, NVL2(SAL*12+COMM, SAL*12+COMM, NVL2(SAL*12, SAL*12, NVL2(COMM, COMM, 0)))"연봉"
FROM TBL_EMP;
날짜에 대한 세션 설정 변경
--※ 날짜에 대한 세션 설정 변경
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.
현재 시간(날짜)을 조회하는 함수
--○ 현재 시간(날짜)을 조회하는 함수
SELECT SYSDATE, CURRENT_DATE, LOCALTIMESTAMP
FROM DUAL;
--==>> 2018-01-09 10:05:52 2018-01-09 10:05:52 18/01/09 10:05:52.000000000
컬럼과 컬럼의 연결 (『||』)
--○ 컬럼과 컬럼의 연결 (『||』)
SELECT '김선규', '최진규'
FROM DUAL;
--==>> 김선규 최진규
SELECT '김선규' || '최진규'
FROM DUAL;
--==>> 김선규최진규
SELECT 10 || 20
FROM DUAL;
--==>> 1020
SELECT '김선규' || 10 || SYSDATE
FROM DUAL;
--==>> 김선규102018-01-09 10:11:21
--> 문자열 || 숫자 || 날짜
-- 결과적으로 문자열 타입으로 표현~!!!
--※ 오라클에서는 문자열로 형 변환의 과정 없이
-- 『||』만 넣어주면 간단히 컬럼과 컬럼을 연결하는 것이 가능하다.
-- MSSQL 에서는 모든 문자열을 CONVERT 해야 한다.
Test11
--○ TBL_EMP 테이블의 정보를 활용하여
-- EX) SMITH 의 현재 연봉은 9600 인데 희망 연봉은 19200 이다.
-- ALLEN 의 현재 연봉은 19500 인데 희망 연봉은 39000 이다.
-- :
-- 이와 같은 형식으로 조회할 수 있도록 한다.
SELECT ENAME || '의 현재 연봉은 ' || COALESCE(SAL*12+COMM, SAL*12, COMM, 0)
|| '인데 희망 연봉은 ' || COALESCE(SAL*12+COMM, SAL*12, COMM, 0)*2 || '이다.'
FROM TBL_EMP;
SELECT ENAME, HIREDATE
FROM TBL_EMP;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
SELECT ENAME, HIREDATE
FROM TBL_EMP;
SELECT ENAME || '의 입사일은 ' || HIREDATE || '이다. 그리고 급여는 ' || SAL || '이다.'
FROM TBL_EMP;
--==>>
/*
SMITH의 입사일은 1980-12-17이다. 그리고 급여는 800이다.
ALLEN의 입사일은 1981-02-20이다. 그리고 급여는 1600이다.
WARD의 입사일은 1981-02-22이다. 그리고 급여는 이다.
JONES의 입사일은 1981-04-02이다. 그리고 급여는 2975이다.
MARTIN의 입사일은 1981-09-28이다. 그리고 급여는 1250이다.
BLAKE의 입사일은 1981-05-01이다. 그리고 급여는 2850이다.
CLARK의 입사일은 1981-06-09이다. 그리고 급여는 2450이다.
SCOTT의 입사일은 1987-07-13이다. 그리고 급여는 3000이다.
KING의 입사일은 1981-11-17이다. 그리고 급여는 5000이다.
TURNER의 입사일은 1981-09-08이다. 그리고 급여는 1500이다.
ADAMS의 입사일은 1987-07-13이다. 그리고 급여는 1100이다.
JAMES의 입사일은 1981-12-03이다. 그리고 급여는 950이다.
FORD의 입사일은 1981-12-03이다. 그리고 급여는 3000이다.
MILLER의 입사일은 1982-01-23이다. 그리고 급여는 1300이다.
*/
Test12
/*
SMITH's의 입사일은 1980-12-17이다. 그리고 급여는 800이다.
ALLEN's의 입사일은 1981-02-20이다. 그리고 급여는 1600이다.
WARD's의 입사일은 1981-02-22이다. 그리고 급여는 이다.
JONES's의 입사일은 1981-04-02이다. 그리고 급여는 2975이다.
:
*/
/*
SELECT ENAME || ''s 입사일은 ' || HIREDATE || '이다. 그리고 급여는 ' || SAL || '이다.'
FROM TBL_EMP;
--==>> 에러 발생
*/
SELECT ENAME || '''s 입사일은 ' || HIREDATE || '이다. 그리고 급여는 ' || SAL || '이다.'
FROM TBL_EMP;
--※ 오라클은 문자열 영역 안에서 『'』(홑따옴표) 두 개가 하나를 표현하는 구조로 기능한다.
대소문자 구분
--○ 오라클에서 데이터의 값 만큼은 반드시 대소문자 구분을 엄격하게 처리한다.
sElECT EMpnO, EnAme, jOB
FROM TbL_eMp
WHere job='CLERK';
--==>>
/*
7369 SMITH CLERK
7876 ADAMS CLERK
7900 JAMES CLERK
7934 MILLER CLERK
*/
sElECT EMpnO, EnAme, jOB
FROM TbL_eMp
WHere job='CLeRK';
--==>> 조회 결과 없음
UPPER(), LOWER(), INITCAP()
--○ UPPER(), LOWER(), INITCAP()
SELECT 'oRAcLE', UPPER('oRAcLE'), LOWER('oRAcLE'), INITCAP('oRAcLE')
FROM DUAL;
--==>> oRAcLE ORACLE oracle Oracle
sElECT EMpnO, EnAme, jOB
FROM TbL_eMp
WHere UPPER(job) = UPPER('CLeRK');
sElECT EMpnO, EnAme, jOB
FROM TbL_eMp
WHere LOWER(job) = LOWER('CLeRK');
sElECT EMpnO, EnAme, jOB
FROM TbL_eMp
WHere INITCAP(job) = INITCAP('CLeRK');
Test13
--○ TBL_EMP 테이블에서 입사일이 1981년 9월 28일 입사한 직원의
-- 사원명, 직종명, 입사일 항목을 조회한다.
SELECT ENAME"사원명", JOB"직종명", HIREDATE"입사일"
FROM TBL_EMP
WHERE HIREDATE = '1981-09-28';
--==>> MARTIN SALESMAN 1981-09-28
SELECT ENAME"사원명", JOB"직종명", HIREDATE"입사일"
FROM TBL_EMP
WHERE HIREDATE = TO_DATE('1981-09-28', 'YYYY-MM-DD');
--==>> MARTIN SALESMAN 1981-09-28
-- '1981-09-28' → '1981-09-28'의 문자열
-- TO_DATE('1981-09-28', 'YYYY-MM-DD') → 1981년 09월 28일 날짜형
-- '2018-01-09 10:47:10' → '2018-01-09 10:47:10'의 문자열
-- TO_DATE('2018-01-09 10:47:10', 'YYYY-MM-DD HH24:MI:SS' → 2018년 1월 9일 10시 47분 10초 날짜형
--○ TBL_EMP 테이블에서 입사일이 1981년 9월 28일 이후로 입사한 직원의
-- 사원번호, 사원명, 입사일 항목을 조회한다.
SELECT EMPNO"사원번호", ENAME"사원명", HIREDATE"입사일"
FROM TBL_EMP
WHERE HIREDATE >= TO_DATE('1981-09-28', 'YYYY-MM-DD');
--==>>
/*
7654 MARTIN 1981-09-28
7788 SCOTT 1987-07-13
7839 KING 1981-11-17
7876 ADAMS 1987-07-13
7900 JAMES 1981-12-03
7902 FORD 1981-12-03
7934 MILLER 1982-01-23
*/
--○ TBL_EMP 테이블에서 입사일이 1981년 4월 2일 부터 1981년 9월 28일 사이에 입사한 직원의
-- 사원번호, 사원명, 입사일 항목을 조회한다.
-- 사원번호, 사원명, 입사일 항목을 조회한다.
SELECT EMPNO"사원번호", ENAME"사원명", HIREDATE"입사일"
FROM TBL_EMP
WHERE HIREDATE >= TO_DATE('1981-04-02', 'YYYY-MM-DD')
AND HIREDATE <= TO_DATE('1981-09-28', 'YYYY-MM-DD');
--==>>
/*
7566 JONES 1981-04-02
7654 MARTIN 1981-09-28
7698 BLAKE 1981-05-01
7782 CLARK 1981-06-09
7844 TURNER 1981-09-08
*/
BETWEEN ⓐ AND ⓑ
--○ BETWEEN ⓐ AND ⓑ
SELECT EMPNO"사원번호", ENAME"사원명", HIREDATE"입사일"
FROM TBL_EMP
WHERE HIREDATE BETWEEN TO_DATE('1981-04-02', 'YYYY-MM-DD')
AND TO_DATE('1981-09-28', 'YYYY-MM-DD');
--==>>
/*
7566 JONES 1981-04-02
7654 MARTIN 1981-09-28
7698 BLAKE 1981-05-01
7782 CLARK 1981-06-09
7844 TURNER 1981-09-08
*/
SELECT *
FROM TBL_EMP
WHERE SAL BETWEEN 2000 AND 3000;
--==>>
/*
7566 JONES MANAGER 7839 1981-04-02 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-07-13 3000 20
7902 FORD ANALYST 7566 1981-12-03 3000 20
*/
SELECT *
FROM TBL_EMP
WHERE ENAME BETWEEN 'C' AND 'S';
--==>>
/*
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 KING PRESIDENT 1981-11-17 5000 10
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10
*/
--※ BETWEEN ⓐ AND ⓑ 는 날짜, 숫자, 문자 모두에 적용된다.
-- 하지만 문자일 경우 아스키코드 순서를 따르기 때문에
-- 대문자가 앞에 있고 소문자가 뒤에 있다는 배열과정의 순서를 주의해야 한다.
-- 또한, BETWEEN ⓐ AND ⓑ 는 수행되는 시점에서
-- 오라클 내부적으로는 부등호 연산자의 형태로 바뀌어 연산된다.
-- 마지막으로 문자일 경우... 사전식 배열과정의 순서를 주의해야 한다.
ASCII()
--○ ASCII()
SELECT ASCII('A'), ASCII('Z'), ASCII('a'), ASCII('z')
FROM DUAL;
--==>> 65 90 97 122
Test14
--○ TBL_EMP 테이블에서 직종이 SALESMAN 과 CLERK 인 사원의
-- 사원명, 직종명, 급여 항목을 조회한다.
SELECT ENAME"사원명", JOB"직종명", SAL"급여"
FROM TBL_EMP
WHERE JOB='SALESMAN' OR JOB='CLERK';
SELECT ENAME"사원명", JOB"직종명", SAL"급여"
FROM TBL_EMP
WHERE JOB IN ('SALESMAN', 'CLERK');
SELECT ENAME"사원명", JOB"직종명", SAL"급여"
FROM TBL_EMP
WHERE JOB =ANY ('SALESMAN', 'CLERK');
--==>>
/*
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
*/
TBL_SAWON
--○ 테이블 생성 (TBL_SAWON)
CREATE TABLE TBL_SAWON
( SANO NUMBER(4) PRIMARY KEY
, SANAME VARCHAR2(40)
, JUBUN CHAR(13)
, HIREDATE DATE DEFAULT SYSDATE
, SAL NUMBER
);
--==>> Table TBL_SAWON이(가) 생성되었습니다.
--○ 데이터 입력
INSERT INTO TBL_SAWON VALUES(1001, '명소희', '9302272234567', TO_DATE('2017-03-02', 'YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1002, '임미영', '9310092234567', TO_DATE('2017-03-02', 'YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1003, '조태희', '9010271234567', TO_DATE('2017-03-02', 'YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1004, '김선규', '9202231234567', TO_DATE('2017-11-13', 'YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1005, '서운성', '9307151234567', TO_DATE('2017-11-13', 'YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1006, '박기범', '9002271234567', TO_DATE('2017-11-13', 'YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1007, '최진규', '9105071234567', TO_DATE('2017-11-13', 'YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1008, '김준협', '9212101234567', TO_DATE('2017-11-13', 'YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1009, '정승우', '9304211234567', TO_DATE('2017-11-13', 'YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1010, '오승우', '9112271234567', TO_DATE('2017-11-13', 'YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1011, '선동렬', '0212273234567', TO_DATE('2018-01-02', 'YYYY-MM-DD'), 2000000);
INSERT INTO TBL_SAWON VALUES(1012, '선우용녀', '0301024234567', TO_DATE('2018-01-02', 'YYYY-MM-DD'), 2000000);
INSERT INTO TBL_SAWON VALUES(1013, '선우선', '0405064234567', TO_DATE('2018-01-02', 'YYYY-MM-DD'), 2000000);
INSERT INTO TBL_SAWON VALUES(1014, '스윙스', '0505053234567', TO_DATE('2018-01-02', 'YYYY-MM-DD'), 2000000);
--==>> 14 행 이(가) 삽입되었습니다.
COMMIT;
--==>> 커밋 완료.
SELECT *
FROM TBL_SAWON;
--==>>
/*
1001 명소희 9302272234567 2017-03-02 4000000
1002 임미영 9310092234567 2017-03-02 4000000
1003 조태희 9010271234567 2017-03-02 4000000
1004 김선규 9202231234567 2017-11-13 4000000
1005 서운성 9307151234567 2017-11-13 4000000
1006 박기범 9002271234567 2017-11-13 4000000
1007 최진규 9105071234567 2017-11-13 4000000
1008 김준협 9212101234567 2017-11-13 4000000
1009 정승우 9304211234567 2017-11-13 4000000
1010 오승우 9112271234567 2017-11-13 4000000
1011 선동렬 0212273234567 2018-01-02 2000000
1012 선우용녀 0301024234567 2018-01-02 2000000
1013 선우선 0405064234567 2018-01-02 2000000
1014 스윙스 0505053234567 2018-01-02 2000000
*/
Test15
--○ TBL_SAWON 테이블에 존재하는 『최진규』 사원의 정보를 조회한다.
SELECT *
FROM TBL_SAWON
WHERE SANAME = '최진규';
--==>> 1007 최진규 9105071234567 2017-11-13 4000000
SELECT *
FROM TBL_SAWON
WHERE SANAME LIKE '최진규';
--==>> 1007 최진규 9105071234567 2017-11-13 4000000
--※ LIKE 동사 : 좋아하다
-- 부사 : ~처럼, ~와 같이
--※ WILD CHARACTER → 『%』
-- 『LIKE』 키워드와 함께 사용되는『%』 는 모든 글자를 의미하고
-- 『LIKE』 키워드와 함께 사용되는『_』 는 아무 글자 1개를 의미한다.
Test16
--○ TBL_SAWON 테이블에서 성이 『김』씨인 사원들의
-- 사원명, 주민번호, 급여를 조회한다.
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME = '김';
--==>> 조회결과 없음
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME = '김__';
--==>> 조회결과 없음
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME LIKE '김_';
--==>> 조회결과 없음
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME LIKE '김__';
--==>>
/*
김선규 9202231234567 4000000
김준협 9212101234567 4000000
*/
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME LIKE '김%';
--==>>
/*
김선규 9202231234567 4000000
김준협 9212101234567 4000000
*/
Test17
--○ TBL_SAWON 테이블에서 이름이 『승우』인 사원의
-- 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME LIKE '%승우';
--==>>
/*
정승우 9304211234567 4000000
오승우 9112271234567 4000000
*/
--> 성이 한 글자인 '승우'만 조회
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME LIKE '%승우';
--==>>
/*
정승우 9304211234567 4000000
오승우 9112271234567 4000000
*/
--> '승우'로 끝나는 이름을 모두 조회
Test18
--○ TBL_SAWON 테이블에서 두 번째 글자가 『선』인 사원의
-- 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME LIKE '_선_';
--==>>
/*
김선규 9202231234567 4000000
*/
--> 이름이 세 글자이면서 두 번째 글자가 '선'인 사람만 조회
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME LIKE '_선%';
--==>>
/*
김선규 9202231234567 4000000
*/
--> '선' 뒤에 여러 글자가 와도 상관 없음
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME LIKE '%선%';
--==>>
/*
김선규 9202231234567 4000000
선동렬 0212273234567 2000000
선우용녀 0301024234567 2000000
선우선 0405064234567 2000000
*/
--> 이름에 '선'이 들어가는 사람 모두 조회
Test19
--○ TBL_SAWON 테이블에서 성이 『선』씨인 사원들의
-- 사원명, 주민번호, 급여를 조회한다.
--> 결론부터 말하자면... 불가능하다.
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SANAME LIKE '선%';
--==>>
/*
선동렬 0212273234567 2000000
선우용녀 0301024234567 2000000
선우선 0405064234567 2000000
*/
--> '선우용녀'는 성이 '선'씨인가?
-- '선우선'은 성이 '선'씨인가? '선우'씨인가?
--※ 데이터베이스 설계 시 성과 이름을 분리해서 처리해야 할 업무 계획이 있다면
-- 테이블 구성(생성) 과정에서 성 컬럼과 이름 컬럼을 분리하여 구성해야 한다.~!!!
Test20
--○ TBL_SAWON 테이블에서 여직원들의 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE JUBUN LIKE '______2%'
OR JUBUN LIKE '______4%' ;
--==>>
/*
명소희 9302272234567 4000000
임미영 9310092234567 4000000
선우용녀 0301024234567 2000000
선우선 0405064234567 2000000
*/
TBL_WATCH
--○ 테이블 생성
CREATE TABLE TBL_WATCH
( WATCHNAME VARCHAR2(40)
, BIGO VARCHAR2(100)
);
--==>> Table TBL_WATCH이(가) 생성되었습니다.
--○ 데이터 입력
INSERT INTO TBL_WATCH VALUES('금시계', '순금 99.99% 함유된 최고급 시계');
INSERT INTO TBL_WATCH VALUES('은시계', '고객 만족도 99.99점 획득 시계');
--==>> 2 행 이(가) 삽입되었습니다.
--○ 커밋
COMMIT;
--==>> 커밋 완료.
Test21
--○ TBL_WATCH 테이블의 BIGO(비고) 컬럼에
-- 『99.99%』라는 글자가 들어있는 행의 정보를 조회한다.
SELECT *
FROM TBL_WATCH
WHERE BIGO LIKE '%99.99%%';
--==>>
/*
금시계 순금 99.99% 함유된 최고급 시계
은시계 고객 만족도 99.99점 획득 시계
*/
--> 쿼리문에 사용한 모든 % 를 WILE CHARACTER 로 인식하기 때문에
-- 원하는 결과를 얻을 수 없는 상황이다.
-- 즉, BIGO 컬럼의 데이터 중 문자열에 99.99 가 포함된 행의 정보 조회~!!!
SELECT *
FROM TBL_WATCH
WHERE BIGO LIKE '%99.99/%%' ESCAPE '/';
--==>> 금시계 순금 99.99% 함유된 최고급 시계
SELECT *
FROM TBL_WATCH
WHERE BIGO LIKE '%99.99$%%' ESCAPE '$';
--==>> 금시계 순금 99.99% 함유된 최고급 시계
SELECT *
FROM TBL_WATCH
WHERE BIGO LIKE '%99.99#%%' ESCAPE '#';
--==>> 금시계 순금 99.99% 함유된 최고급 시계
--※ ESCAPE 로 설정한 한 문자는
-- 와일드 캐릭터에서 탈출시켜라...라는 의미
-- 일반적으로 ESCAPE 로 설장하는 한 문자는...
-- 사용 빈도가 비교적 적은 특수문자로 설정한다.
정렬(ORDER BY)
--■■■ 정렬(ORDER BY) ■■■--
SELECT ENAME"사원명", DEPTNO"부서번호", JOB"직종", SAL"급여"
, (SAL*12)+NVL(COMM, 0)"연봉"
FROM EMP;
SELECT ENAME"사원명", DEPTNO"부서번호", JOB"직종", SAL"급여"
, (SAL*12)+NVL(COMM, 0)"연봉"
FROM EMP
ORDER BY DEPTNO ASC; -- 정렬 기준 컬럼 : DEPTNO(부서번호)
-- ASC : 오름차순을 의미한다. 생략 가능.
SELECT ENAME"사원명", DEPTNO"부서번호", JOB"직종", SAL"급여"
, (SAL*12)+NVL(COMM, 0)"연봉"
FROM EMP
ORDER BY DEPTNO;
SELECT ENAME"사원명", DEPTNO"부서번호", JOB"직종", SAL"급여"
, (SAL*12)+NVL(COMM, 0)"연봉"
FROM EMP
ORDER BY DEPTNO DESC; -- DESC : 내림차순을 의미. 생략 불가
DESC EMP;
SELECT ENAME"사원명", DEPTNO"부서번호", JOB"직종", SAL"급여"
, (SAL*12)+NVL(COMM, 0)"연봉"
FROM EMP
ORDER BY 부서번호 DESC;
SELECT ENAME"사원명", DEPTNO"부서번호", JOB"직종", SAL"급여"
, (SAL*12)+NVL(COMM, 0)"연봉"
FROM EMP
ORDER BY 2 DESC;
--> ORDER BY 절보다 SELECT 가 먼저 실행되기 때문에
-- 컬럼명 대신 ALIAS(별칭)을 사용하는 것도 가능하고,
-- SELECT 되는 컬럼의 순서를 사용하는 것도 가능하다.
SELECT ENAME"사원명", DEPTNO"부서번호", JOB"직종", SAL"급여"
, (SAL*12)+NVL(COMM, 0)"연봉"
FROM EMP
ORDER BY 2, 4;
SELECT ENAME"사원명", DEPTNO"부서번호", JOB"직종", SAL"급여"
, (SAL*12)+NVL(COMM, 0)"연봉"
FROM EMP
ORDER BY 2, 3, 4 DESC;
--> 4번째 컬럼만 내림차순 정렬
--> 부서번호별 오름차순 → 직종별 오름차순 → 급여별 내림차순
문자열 처리 함수
CONCAT()
--○ CONCAT()
--> 2개의 문자열을 연결하는 기능을 가진 함수(문자열 결합 함수)
-- 단, 오직 두 개만 연결할 수 있다.
SELECT ENAME || JOB, CONCAT(ENAME, JOB)
FROM EMP;
SELECT ENAME||JOB||SAL, CONCAT(ENAME, CONCAT(JOB, SAL))
FROM EMP;
--> 내부적인 형 변환이 일어나서 연결시켜주게 된다.
-- CONCAT() 은 문자열과 문자열을 연결하는 기능을 가진 함수이지만,
-- 내부적으로 숫자나 날짜를 문자로 바꾸는 과정이 포함되어 있는 것이다.
SUBSTR()
--○ SUBSTR()
--> 문자열을 추출하는 기능을 가진 함수
-- 첫 번째 파라미터 값을 대상 문자열 (추출의 대상)
-- 두 번째 파라미터 값은 추출을 시작하는 위치 (1번째 인덱스부터 시작)
-- 세 번째 파라미터 값은 추출할 문자열의 갯수
-- (생략이 가능하며, 생략 시... 시작 위치부터 해당 문자열의 길이만큼...(끝까지))
SELECT ENAME, SUBSTR(ENAME, 1, 2)
FROM EMP;
SELECT ENAME, SUBSTR(ENAME, 5, 1)
FROM EMP;
-- JAVA
-- "대상문자열".substring(0, 10)
SELECT ENAME, SUBSTR(ENAME, 5)
FROM EMP;
SELECT ENAME, SUBSTR(ENAME, 5, ENAME 의 끝까지...)
FROM EMP;
SUBSTRB()
--○ SUBSTRB()
--> 바이트 기반으로 처리한다. → 인코딩 방식에 주의할 것~!!!
SELECT SANAME, SUBSTR(SANAME, 1, 1), SUBSTR(SANAME, 1, 3)
FROM TBL_SAWON;
-- 오라클 환경변수(캐릿터 셋) 확인
SELECT PARAMETER, VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';
--==>> NLS_CHARACTERSET AL32UTF8
Test22
--○ TBL_SAWON 테이블에서 남자인 직원들의
-- 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SUBSTR(JUBUN, 7, 1) = '1'
OR SUBSTR(JUBUN, 7, 1) = '3';
SELECT SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SUBSTR(JUBUN, 7, 1) IN ('1', '3');
LENGTH(), LENGTHB()
--○ LENGTH(), LENGTHB()
--> LENGTH()는 글자 수를 반환하는 함수
-- LENGTHB()는 바이트 수를 반환하는 함수
SELECT ENAME, LENGTH(ENAME), LENGTHB(ENAME)
FROM EMP;
--==>>
/*
SMITH 5 5
ALLEN 5 5
WARD 4 4
JONES 5 5
MARTIN 6 6
BLAKE 5 5
CLARK 5 5
SCOTT 5 5
KING 4 4
TURNER 6 6
ADAMS 5 5
JAMES 5 5
FORD 4 4
MILLER 6 6
*/
SELECT SANAME, LENGTH(SANAME), LENGTHB(SANAME)
FROM TBL_SAWON;
--==>>
/*
명소희 3 9
임미영 3 9
조태희 3 9
김선규 3 9
서운성 3 9
박기범 3 9
최진규 3 9
김준협 3 9
정승우 3 9
오승우 3 9
선동렬 3 9
선우용녀 4 12
선우선 3 9
스윙스 3 9
*/
INSTR()
--○ INSTR()
--> 첫 번째 파라미터 값에 해당하는 문자열에서 (대상 문자열)
-- 두 번째 파라미터 값에서 정의한 문자열이 등장하는 위치를 검색
-- 세 번째 파라미터 값은 스캔을 시작하는 위치
-- 네 번째 파라미터 값은 몇 번째 등장하는지를 결정 (1은 생략 가능)
SELECT INSTR('oracle orahome biora', 'ora', 1, 1)
, INSTR('oracle orahome biora', 'ora', 1, 2)
, INSTR('oracle orahome biora', 'ora', 2, 1)
, INSTR('oracle orahome biora', 'ora', 2)
, INSTR('oracle orahome biora', 'ora', 2, 2)
FROM DUAL;
--==>> 1 8 8 8 18
SELECT INSTR('나의오라클 집으로오라 합니다', '오라', 1)
, INSTR('나의오라클 집으로오라 합니다', '오라', 2)
, INSTR('나의오라클 집으로오라 합니다', '오라', 10)
, INSTR('나의오라클 집으로오라 합니다', '오라', 11)
FROM DUAL;
--==>> 3 3 10 0
-- INSTR('대상 문자열', '찾을 문자열', 검색 시작 위치, 몇 번째 등장하는지)
REVERSE()
--○ REVERSE()
--> 문자열의 거꾸로 처리한 결과값을 반환한다.
-- 단, 한글은 사용할 수 없다.
SELECT 'ORACLE', REVERSE('ORACLE'), '오라클', REVERSE('오라클')
FROM DUAL;
--==>> ORACLE ELCARO 오라클 ���
Test23
/*
테이블명 : TBL_FILES
-----------------------------------------------
FILENO FILENAME
-----------------------------------------------
1 C:\AAA\BBB\CCC\SALES.XXLS
2 C:\AAA\STUDY.HWP
3 C:\RESEARCH\WORK\WORK.TXT
4 C:\TEST.PPTX
-----------------------------------------------
↓↓↓
파일번호 파일명
1 SALES.XXLS
2 STUDY.HWP
3 WORK.TXT
4 TEST.PPTX
*/
CREATE TABLE TBL_FILES
( FILENO NUMBER(4)
, FILENAME VARCHAR2(40)
);
INSERT INTO TBL_FILES VALUES(1, 'C:\AAA\BBB\CCC\SALES.XXLS');
INSERT INTO TBL_FILES VALUES(2, 'C:\AAA\STUDY.HWP');
INSERT INTO TBL_FILES VALUES(3, 'C:\RESEARCH\WORK\WORK.TXT');
INSERT INTO TBL_FILES VALUES(4, 'C:\TEST.PPTX');
SELECT *
FROM TBL_FILES;
COMMIT;
SELECT FILENO"파일번호"
-- FILENAME을 REVERSE 처리한 뒤 '\' 앞까지 자르고, 해당 문자를 다시 REVERSE 처리
, REVERSE(SUBSTR(REVERSE(FILENAME), 1, INSTR(REVERSE(FILENAME), '\', 1) - 1))"파일명"
FROM TBL_FILES;
--==>>
/*
1 SALES.XXLS
2 STUDY.HWP
3 WORK.TXT
4 TEST.PPTX
*/
LPAD(), RPAD()
--○ LPAD(), RPAD()
--> BYTE 수를 확보하여 왼쪽(오른쪽)부터 문자열로 채우는 기능을 가진 함수
SELECT 'ORACLE', LPAD('ORACLE', 10, '*')
FROM DUAL;
--==>> ORACLE ****ORACLE
--① 10Byte 공간을 확보한다. (두 번째 파라미터 값에 의해...)
--② 확보한 공간에 'ORACLE' 문자열을 담는다. (첫 번째 파라미터 값에 의해...)
--③ 남아있는 Byte 공간에 왼쪽부터(오른쪽부터) '*'로 채운다. (세 번째 파라미터 값에 의해...)
SELECT 'ORACLE', RPAD('ORACLE', 10, '*')
FROM DUAL;
--==>> ORACLE ORACLE****
LTRIM(), RTRIM()
--○ LTRIM(), RTRIM()
SELECT 'ORAORAORACLEORACLE'
, LTRIM('ORAORAORACLEORACLE', 'ORA')
, LTRIM('ORAoRAORACLEORACLE', 'ORA')
, LTRIM('ORAARAORACLEORACLE', 'ORA')
, LTRIM('ORA ORAORACLEORACLE', 'ORA')
FROM DUAL;
--==>> ORAORAORACLEORACLE CLEORACLE oRAORACLEORACLE CLEORACLE ORAORACLEORACLE
--> 첫 번째 파라미터 값에 해당하는 문자열을 대상으로
-- 왼쪽부터(오른쪽부터) 연속적으로 두 번째 인자값과
-- 같은 글자가 등장할 경우 제거한다.
-- 완성형으로 처리되지 않는다.
TRANSLATE()
--○ TRANSLATE()
--> 같은 위치에 해당하는 문자와 1 : 1 로 바꿔준다. (일치하는 문자가 없을 경우 바꾸지 않는다.)
SELECT TRANSLATE('MY ORACLE SERVER', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz')
FROM DUAL;
--==>> my oracle server
SELECT '010-2546-5187'
, TRANSLATE('010-2546-5187', '0123456789', '공일이삼사오육칠팔구')
FROM DUAL;
--==>> 010-2546-5187 공일공-이오사육-오일팔칠
REPLACE()
--○ REPLACE()
--> 대소문자 구분을 명확히 하며, 완성형으로 처리된다.
-- 1 : 1 로 사상되는 TRANSLATE() 함수와 비교해서 정리할 수 있도록 한다.
SELECT REPLACE('MY ORACLE SERVER', 'ORA', '오라')
FROM DUAL;
--==>> MY 오라CLE SERVER
숫자형 함수
ROUND()
--○ ROUND()
--> 반올림을 처리하는 함수
SELECT 48.678, ROUND(48.678, 2) -- 소수점 이하 두 번째 자리까지 표현
, ROUND(48.678, 0) -- 정수부까지 표현
, ROUND(48.678) -- 정수부까지만 표현할 경우 생략 가능
, ROUND(48.678, -1) -- 십의 자리까지 유효할 경우 표현
, ROUND(48.678, -2) -- 백의 자리까지 유효할 경우 표현
FROM DUAL;
--==>> 48.678 48.68 49 49 50 0
TRUNC()
--○ TRUNC()
--> 절삭
SELECT 48.678, TRUNC(48.678, 2) -- 소수점 이하 두 번째 자리까지 표현
, TRUNC(48.678, 0) -- 정수부까지 표현
, TRUNC(48.678) -- 정수부까지만 표현할 경우 생략 가능
, TRUNC(48.678, -1) -- 십의 자리까지 유효할 경우 표현
, TRUNC(48.678, -2) -- 백의 자리까지 유효할 경우 표현
FROM DUAL;
--==>> 48.678 48.67 48 48 40 0
MOD()
--○ MOD() → %
SELECT MOD(5, 2) -- 5를 2로 나눈 나머지
FROM DUAL;
--==>> 1
POWER()
--○ POWER() → 제곱
SELECT POWER(5, 3)
FROM DUAL;
--==>> 125
SQRT()
--○ SQRT() → 루트
SELECT SQRT(2)
FROM DUAL;
--==>> 1.41421356237309504880168872420969807857
LOG()
--○ LOG() → 로그 함수 -- 상용로그만 지원한다.
-- (MSSQL 에서는 자연로그도 지원한다.)
SELECT LOG(10, 100)
FROM DUAL;
삼각함수
--○ 삼각함수
-- SIN(), COS(), TAN()
SELECT SIN(1), COS(1), TAN(1)
FROM DUAL;
--==>> 0.8414709848078965066525023216302989996233 0.5403023058681397174009366074429766037354 1.55740772465490223050697480745836017308
삼각함수의 역함수
--○ 삼각함수의 역함수(범위 : -1 ~ 1)
-- ASIN(), ACOS(), ATAN()
SELECT ASIN(0.5), ACOS(0.5), ATAN(0.5)
FROM DUAL;
--==>> 0.52359877559829887307710723054658381405 1.04719755119659774615421446109316762805 0.4636476090008061162142562314612144020295
SIGN()
--○ SIGN() 부호, 서명, 특징
--> 연산 처리에 대한 결과값이 양수이면 1, 0이면 0, 음수이면 -1을 반환한다.
SELECT SIGN(5-2), SIGN(5-5), SIGN(2-5)
FROM DUAL;
--==>> 1 0 -1
--> 매출이나 수지와 관련하여 적자 및 흑자 여부를 나타낼 때 주로 사용된다.
ASCII(), CHR()
--○ ASCII(), CHR()
SELECT ASCII('A'), CHR(65)
FROM DUAL;
--==>> 65 A
날짜 연산
날짜 연산
--※ 날짜 연산의 기본 단위는 DAY(일수)이다.
-- (2018-01-09 16:48:27) + 2
SELECT SYSDATE, SYSDATE+1, SYSDATE-2
FROM DUAL;
--==>> 2018-01-09 16:49:45 2018-01-10 16:49:45 2018-01-07 16:49:45
-- 시간 단위 연산
SELECT SYSDATE, SYSDATE+1/24, SYSDATE-2/24
FROM DUAL;
--==>> 2018-01-09 16:50:38 2018-01-09 17:50:38 2018-01-09 14:50:38
Test24
--○ 문제
-- 현재 시간과, 1일 2시간 3분 4초 후를 조회(출력)한다.
----------------------------------------------------------
-- 현재 시간 | 연산 후 시간
----------------------------------------------------------
-- 2018-01-09 16:50:36 | 2018-01-10 18:53:40
----------------------------------------------------------
SELECT SYSDATE"현재 시간"
, SYSDATE + 1 + 2/24 + 3/24/60 + 4/24/60/60"연산 후 시간"
FROM DUAL;
반응형
'DB > Oracle' 카테고리의 다른 글
| [Oracle] 교육 5일차 (0) | 2018.01.11 |
|---|---|
| [Oracle] 교육 4일차 (0) | 2018.01.10 |
| [Oracle] 교육 2일차 (0) | 2018.01.05 |
| [Oracle] 교육 1일차 (0) | 2018.01.04 |
댓글
반응형
공지사항
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
링크
TAG
- 빅데이터 강의
- 기초 of 기초 데이터 개념
- Hyperledger Fabric v1.2
- codility
- 빅데이터
- ubuntu
- 블록 체인
- 하이퍼레저 인디
- Hyperledger Indy
- Private Data
- 코딜리티
- 빅데이터 교육
- 문제풀이
- 블록체인
- 직딩잇템
- 암브로셔스
- Blockchain
- Hyperledger Fabric v1.1
- docker
- ambrosus
- DOCs
- javascript
- 알고리즘
- 하이퍼레저 페브릭
- 코딩테스트
- 어서와 데이터는 처음이지
- 하이퍼레저 패브릭
- Hyperledger Fabric
- 빅데이터 기초
- 코테
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 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 |
글 보관함