SELECT USER
FROM DUAL;
--==>>SCOTT
문제 1
TBL_EMP 테이블에서 모든 사원들의 사원번호, 사원명, 급여, 커미션, 연봉 정보 조회
( 단, 급여(SAL)는 매월 지급한다. 수당(COMM)은 1회/년 지급한다. 연봉 = (급여 * 12) + 커미션 )
SELECT EMPNO "사원번호", ENAME "사원명", SAL"급여", COMM"커미션", (SAL*12)+COMM "연봉정보"
FROM TBL_EMP;
--> 이와 같이 연봉을 연산하여 조회할 경우 수당(COMM)이 NULL인 직원들은 연봉까지도 NULL로 연산되는 것을 확인할 수 있다.
SELECT EMPNO "사원번호", ENAME "사원명", SAL"급여", COMM"커미션", (SAL*12) "연봉"
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)"커미션"
, (SAL*12) + NVL(COMM, 0)"연봉"
FROM TBL_EMP;
--==>>
/*
7369 SMITH 800 9600
7499 ALLEN 1600 300 19500
7521 WARD 1250 500 15500
7566 JONES 2975 35700
7654 MARTIN 1250 1400 16400
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 0 18000
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
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인 경우, 세 번째 파라미터 값을 반환한다.
문제 2
--○ TBL_EMP 테이블에서 COMM 컬럼의 값이 NULL이 아니면 3000, NULL이면 4000으로 특별 수당을 지급하는 상황이다.
이에 대한 연산 결과를 조회할 수 있도록 한다. 사원명, 급여, 수당, 특별수당 항목을 조회한다.
SELECT ENAME"사원명", SAL"급여", NVL(COMM,0)"수당", NVL2(COMM, 3000, 4000)"특별수당"
FROM TBL_EMP;
문제 3
--○ TBL_EMP 테이블에서 모든 사원들의 사원번호, 사원명, 급여, 커미션, 연봉 정보를 조회한다. 단, 급여(SAL)는 매월 지급한다. 수당(COMM)은 1회/년 지급한다. 또한, NVL2() 함수를 활용하여 처리한다.
SELECT EMPNO "사원번호", ENAME "사원명", SAL"급여", NVL(COMM, 0)"커미션"
--, (커미션이 NULL이 아닌 사원들은 SAL*12+COMM 으로 처리하고... 커미션이 NULL인 사원들은 SAL*12로 처리한다.) "연봉"
, NVL2(COMM, SAL*12+COMM, SAL*12)"연봉"
FROM TBL_EMP;
COALESCE()
--○ COALESCE()
--> 맨 앞부터 차례로 값이 NULL이 아니면 적용(반환)하고 NULL 인 경우에는 다음 파라미터를 확인하는 연산으로 넘어간다.
SELECT COALESCE(NULL, NULL, NULL, NULL, 10)"첫 번째 칼럼"
, COALESCE(10, 20, 30)"두 번째 칼럼" --NULL이 아니네? -> 10
FROM DUAL;
--※ 모든 경우의 수를 확인할 수 있다.
-- TBL_EMP 테이블 데이터 수정
UPDATE TBL_EMP
SET SAL = NULL
WHERE EMPNO=7521;
--==>>1 행 이(가) 업데이트되었습니다.
SELECT *
FROM TBL_EMP;
COMMIT;
--==>>커밋 완료.
문제 4
--○ TBL_EMP 테이블에서 연봉에 대한 연산 결과가 NULL이 나오는 경우는 급여(SAL)가 NULL, 혹은 수당(COMM)이 NULL, 또는 둘 다 NULL 모든 경우를 고려하여 연봉을 조회할 수 있도록 쿼리문을 구성한다. COALESCE() 함수를 사용한다. 사원번호, 사원명, 급여, 수당, 연봉을 조회한다. 단, 연봉은 급여*12+수당 으로 연산처리한다.
SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", COMM"수당", COALESCE(NVL2(SAL, 0), SAL*12, SAL*12+COMM)"연봉"
FROM TBL_EMP;
SELECT EMPNO"사원번호", ENAME"사원명", COALESCE(SAL,0)"급여"
, COALESCE(COMM,0)"수당"
, COALESCE(SAL*12+COMM, SAL*12, COMM, 0)"연봉"
FROM TBL_EMP;
--※ 날짜에 대한 세션 설정 변경
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24-MI:SS';
현재 시간(날짜)을 조회하는 함수
--○ 현재 시간(날짜)을 조회하는 함수
SELECT SYSDATE, CURRENT_DATE, LOCALTIMESTAMP
FROM DUAL;
컬럼과 컬럼의 연결(『||』)
--○ 컬럼과 컬럼의 연결(『||』)
SELECT '김선규', '최진규'
FROM DUAL;
SELECT '김선규'||'최진규'
FROM DUAL;
--==>>김선규최진규
SELECT 10 || 20
FROM DUAL;
--==>>1020
SELECT '김선규' || 10 || SYSDATE
FROM DUAL;
--==>>김선규102018-01-09 10-11:34
--> 문자열 || 숫자 || 날짜
-- 결과적으로 문자열 타입으로 표현~!!!
--※ 오라클에서는 문자열로 형 변환의 과정 없이
-- 『||』만 넣어주면 간단히 컬럼과 컬럼을 연결하는 것이 가능하다.
-- MSSQL 에서는 모든 문자열을 CONVERT 해야한다.
SELECT *
FROM TBL_EMP;
문제 5
--○ 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;
DESC 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이다.
*/
/*
SELECT ENAME || ''s 입사일은 ' || HIREDATE || '이다. 그리고 급여는 ' || SAL || '이다.'
FROM TBL_EMP;
--==>> 에러 발생
*/
SELECT ENAME || '''s 입사일은 ' || HIREDATE || '이다. 그리고 급여는 ' || SAL || '이다.'
FROM TBL_EMP;
--==>>
/*
SMITH's 입사일은 1980-12-17이다. 그리고 급여는 800이다.
ALLEN's 입사일은 1981-02-20이다. 그리고 급여는 1600이다.
WARD's 입사일은 1981-02-22이다. 그리고 급여는 이다.
JONES's 입사일은 1981-04-02이다. 그리고 급여는 2975이다.
.
.
MILLER's 입사일은 1982-01-23이다. 그리고 급여는 1300이다.
*/
--※ 오라클은 문자열 영역 안에서
--『'』(홑따옴표) 두 개가 하나를 표현하는 구조로 가능하다.
오라클에서 데이터의 값 만큼은 반드시 대소문자 구분을 엄격하게 처리
--○ 오라클에서 데이터의 값 만큼은 반드시 대소문자 구분을 엄격하게 처리한다.
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');
문제 6
--○ TBL_EMP 테이블에서 입사일이 1981년 9월 28일 입사한 직원의 사원명, 직종명, 입사일 항목을 조회한다.
SELECT ENAME"사원명", JOB"직종명", HIREDATE"입사일"
FROM TBL_EMP
WHERE HIREDATE='1981-09-28';
-- HIREDATE는 자료형이 DATE타입, 오라클의 자동형변환을 믿으면 안된다. 상황에 따라서 다름
--==>> MARTIN SALESMAN 1981-09-28
DESC TBL_EMP;
SELECT ENAME"사원명", JOB"직종명", HIREDATE"입사일"
FROM TBL_EMP
WHERE HIREDATE='1981-09-28';
-- '1981-09-28' → '1981-09-28'의 문자열
-- TO_DATE('1981-09-28', 'YYYY-MM-DD') → 1981년 9월 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초 날짜형
SELECT ENAME"사원명", JOB"직종명", HIREDATE"입사일"
FROM TBL_EMP
WHERE HIREDATE=TO_DATE('1981-09-28', 'YYYY-MM-DD');
--==>> MARTIN SALESMAN 1981-09-28
문제 7
--○ TBL_EMP 테이블에서 입사일이 1981년 9월 28일 이후로 입사한 직원의 사원번호, 사원명, 입사일 항목을 조회한다.
/*
SELECT EMPNO"사원번호", ENAME"사원명", HIREDATE"입사일"
FROM TBL_EMP
WHERE 입사일이 1981년 9월 28일 이후;
*/
SELECT EMPNO"사원번호", ENAME"사원명", HIREDATE"입사일"
FROM TBL_EMP
WHERE HIREDATE >= TO_DATE('1981-09-28', 'YYYY-MM-DD');
/*
7369 SMITH 1980-12-17
7499 ALLEN 1981-02-20
7521 WARD 1981-02-22
7566 JONES 1981-04-02
7698 BLAKE 1981-05-01
7782 CLARK 1981-06-09
7844 TURNER 1981-09-08
*/
문제 8
--○ TBL_EMP 테이블에서 입사일이 1981년 4월 2일부터 1981년 9월 28일 사이에 입사한 직원의 사원번호, 사원명, 입사일 항목을 조회한다.
SELECT EMPNO"사원번호", ENAME"사원명", HIREDATE"입사일"
FROM TBL_EMP
WHERE TO_DATE('1981-04-02', 'YYYY-MM-DD') <= HIREDATE <= TO_DATE('1981-09-28', 'YYYY-MM-DD');
-- WHERE TO_DATE('1981-09-28', 'YYYY-MM-DD')>HIREDATE, HIREDATE>=TO_DATE('1981-04-02', 'YYYY-MM-DD');
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';
--==>> 결과가 없음
-- ASCII 값이 소문자가 대문자 뒤에 있기 때문에
SELECT *
FROM TBL_EMP
WHERE ENAME BETWEEN 'C' AND 's';
--※ BETWEEN ⓐ AND ⓑ 는 날짜, 숫자, 문자 모두에 적용된다.
-- 하지만 문자일 경우 아스키코드 순서를 따르기 때문에
-- 대문자가 앞에 있고 소문자가 뒤에 있다는 배열과정의 순서를 주의해야 한다.
-- 또한, BETWEEN ⓐ AND ⓑ 는 수행되는 시점에서
-- 오라클 내부적으로는 부등호 연산자의ㅏ 형태로 바뀌어 연산된다.
-- 마지막으로 문자일 경우... 사전식 배열과정의 순서를 주의해야 한다.
--==>>
/*
7369 SMITH CLERK 7902 1980-12-17 800 20
7521 WARD SALESMAN 7698 1981-02-22 500 30
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
7788 SCOTT ANALYST 7566 1987-07-13 3000 20
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
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
*/
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
-- 'S'로 시작하는 사원이름이 빠진다.
-- ●-----------○
-- 'C' 'S'
-- 사전식이기 때문에 사원명이 S인 사람은 나오지만 SA어쩌구는 나오지 않는다.
-- 사전식 배열
*/
ASCII()
--○ ASCII()
SELECT ASCII('A'), ASCII('Z'), ASCII('a'), ASCII('z')
FROM DUAL;
--==>>65 90 97 122
문제 9
--○ 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');
-- 내부적으로는 OR 연산으로 바뀜
SELECT ENAME, JOB, SAL
FROM TBL_EMP
WHERE JOB =ANY ('SALESMAN','CLERK');
-- 내부적으로는 OR 연산으로 바뀜
--==>>
/*
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
*/
문제 10
--○ 테이블 생성(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이(가) 생성되었습니다.
CREATE TABLE EXAM1
( NO NUMBER -- 크기를 명시하지 않으면 NUMBER의 최대값을 사용하겠다.
, NAME CHAR -- 크기를 명시하지 않으면 CHAR의 최소값을 사용하겠다.
);
--○ 데이터 입력
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, '최진규', '9205071234567', 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, '오승우', '9212271234567', 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, '선우선', '0412274234567', 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 최진규 9205071234567 2017-11-13 4000000
1008 김준협 9212101234567 2017-11-13 4000000
1009 정승우 9304211234567 2017-11-13 4000000
1010 오승우 9212271234567 2017-11-13 4000000
1011 선동렬 0212273234567 2018-01-02 2000000
1012 선우용녀 0301024234567 2018-01-02 2000000
1013 선우선 0412274234567 2018-01-02 2000000
1014 스윙스 0505053234567 2018-01-02 2000000
*/
문제 11
--○ TBL_SAWON 테이블에 존재하는 『최진규』사원의 정보를 조회한다.
--○ TBL_SAWON 테이블에 존재하는 『최진규』사원의 정보를 조회한다.
SELECT *
FROM TBL_SAWON
WHERE SANAME='최진규';
--==>>1007 최진규 9205071234567 2017-11-13 4000000
SELECT *
FROM TBL_SAWON
WHERE SANAME LIKE '최진규';
--==>>1007 최진규 9205071234567 2017-11-13 4000000
--※ LIKE 동사: 좋아하다
-- 부사: ~처럼 ~와 같이
--※ WILD CHARACTER → 『%』
-- 『LIKE』 키워드와 함께 사용되는 『%』는 모든 글자를 의미하고
-- 『LIKE』 키워드와 함께 사용되는 『_』는 아무 글자 1개를 의미한다.
문제 12
--○ TBL_SAWON 테이블에서 성이 『김』씨인 사원들의 사원명, 주민번호, 급여를 조회한다.
--○ 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 '김__';
--==>>
/*
김선규 9202231234567 4000000
김준협 9212101234567 4000000
*/
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '김%';
--==>>
/*
김선규 9202231234567 4000000
김준협 9212101234567 4000000
*/
-- 아무글자가 됬든 몇 글자가 됬든 모든 김씨 출력
문제 13
--○ TBL_SAWON 테이블에서 이름이 『승우』인 사원의 사원명, 주민번호, 급여 항목을 조회한다.
--○ TBL_SAWON 테이블에서 이름이 『승우』인 사원의 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '_승우';
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '%승우';
--==>>
/*
정승우 9304211234567 4000000
오승우 9212271234567 4000000
*/
문제 14
--○ TBL_SAWON 테이블에서 이름이 『선』인 사원의 사원명, 주민번호, 급여 항목을 조회한다.
--○ TBL_SAWON 테이블에서 이름이 『선』인 사원의
-- 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '_선_';
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
선우선 0412274234567 2000000
*/
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '%선선%';
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '%선%선%';
--%에 글씨가 있어도 되고 없어도 됨
문제 15
--○ TBL_SAWON 테이블에서 성이 『선』씨인 사원들의 사원명, 주민번호, 급여를 조회한다.
--○ TBL_SAWON 테이블에서 성이 『선』씨인 사원들의
-- 사원명, 주민번호, 급여를 조회한다.
--> 결론부터 말하자면... 불가능하다
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '선%';
--※ 데이터베이스 설계 시 성과 이름을 분리해서 처리해야 할 업무 계획이 있다면
-- 테이블 구성(생성) 과정에서 성 칼럼과 이름 칼럼을 분리하여 구성해야 한다~!!
문제 16
--○ TBL_SAWON 테이블에서 여직원들의 사원명, 주민번호, 급여 항목을 조회한다.
--○ TBL_SAWON 테이블에서 여직원들의 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE JUBUN LIKE '______2______'
OR JUBUN LIKE '______4______';
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE JUBUN LIKE '______2%'
OR JUBUN LIKE '______4%';
--○ 테이블 생성
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;
--==>>커밋 완료.
문제 17
--○ TBL_WATCH 테이블의 BIGO(비고) 칼럼에 『99.99%』라는 글자가 들어있는 행의 정보를 조회한다.
--○ TBL_WATCH 테이블의 BIGO(비고) 칼럼에
-- 『99.99%』라는 글자가 들어있는 행의 정보를 조회한다.
SELECT *
FROM TBL_WATCH
WHERE BIGO LIKE '%99.99%%';
--> 쿼리문에 사용한 모든 %를 WILD 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로 설정한 한 문자는
-- 와일드 캐릭터에서 탈출시켜라... 라는 의미
-- 일반적으로 EXCAPE로 설정하는 한 문자는...
-- 사용 빈도가 비교적 적은 특수문자로 설정한다.
정렬(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; -- DESC(DESCRIBE)
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; -- SELECT 하고 있는 2번째 컬럼
--> 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
FROM EMP
ORDER BY 2, 3, 4 DESC;
--> 4번째 컬럼만 내림차순 정렬
--> 부서번호별 오름차순 → 직종별 오름차순 → 급여별 내림차순
문자열 처리 함수
--■■■ 문자열 처리 함수 ■■■--
CONCAT()
--> 2개의 문자열을 연결하는 기능을 가진 함수(문자열 결합 함수) 단, 오직 두 개만 연결할 수 있다.
SELECT ENAME || JOB, CONCAT(ENAME, JOB)
FROM EMP;
SELECT ENAME||JOB||SAL, CONCAT(ENAME, CONCAT(JOB,SAL))
FROM EMP;
--> 내부적인 형 변환이 일어나서 연결시켜주게 된다.
-- CONCAT() 은 문자열과 문자열을 연결하는 기능을 가진 함수이지만, 내부적으로 숫자나 날짜를 문자로 바꾸는 과정이 포함되어 있는 것이다.
SUBSTR()
--> 문자열을 추출하는 기능을 가진 함수
- 첫 번째 파라미터 값은 대상 문자열 (추출의 대상)
- 두 번째 파라미터 값은 추출을 시작하는 위치 (1번째 인덱스부터 시작)
- 세 번째 파라미터 값은 추출할 문자열의 갯수 (생략이 가능하며, 생략 시...시작 위치부터 해당 문자열의 길이만큼...(끝까지))
SELECT ENAME, SUBSTR(ENAME, 1, 2)
FROM EMP;
--==>>
/*
SMITH SM
ALLEN AL
WARD WA
JONES JO
MARTIN MA
BLAKE BL
CLARK CL
SCOTT SC
KING KI
TURNER TU
ADAMS AD
JAMES JA
FORD FO
MILLER MI
*/
SELECT ENAME, SUBSTR(ENAME, 5, 1)
FROM EMP;
--==>>
/*
SMITH H
ALLEN N
WARD (null)
JONES S
MARTIN I
BLAKE E
CLARK K
SCOTT T
KING (null)
TURNER E
ADAMS S
JAMES S
FORD (null)
MILLER E
*/
-- JAVA
-- "대상문자열".substring(5, 10)
*/
SELECT ENAME, SUBSTR(ENAME, 5)
FROM EMP;
/*
SELECT ENAME, SUBSTR(ENAME, 5, ENAME의 끝까지...)
FROM EMP;
*/
SUBSTRB()
--> 바이트 기반으로 처리한다. → 인코딩 방식에 주의할 것~!!
SELECT SANAME, SUBSTR(SANAME, 1, 1), SUBSTRB(SANAME, 2, 3)
FROM TBL_SAWON;
-- 오라클 환경변수(캐릭터 셋) 확인
SELECT PARAMETER, VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';
--==>>NLS_CHARACTERSET AL32UTF8
문제 18
--○ 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()는 바이트 수를 반환하는 함수
SELECT ENAME, LENGTH(ENAME), LENGTHB(ENAME)
FROM EMP;
--==>>
/*
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
*/
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()
SELECT 'ORACLE', REVERSE('ORACLE'), '오라클', REVERSE('오라클')
FROM DUAL;
--==> ORACLE ELCARO 오라클 ���
/*
------------------------------------------------------------------
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.HTML
3 WORK.TXT
4 TEST.PPTX
*/
CREATE TABLE TBL_FILE
( FILENO VARCHAR2(100)
, FILENAME VARCHAR2(100)
);
--○ 데이터 입력
INSERT INTO TBL_FILE VALUES(1, 'C:\AAA\BBB\CCC\SALES.XXLS');
INSERT INTO TBL_FILE VALUES(2, 'C:\AAA\STUDY.HWP');
INSERT INTO TBL_FILE VALUES(3, 'C:\RESEARCH\WORK\WORK.TXT');
INSERT INTO TBL_FILE VALUES(4, 'C:\TEST.PPTX');
--==>> 4 행이(가) 삽입되었습니다.
COMMIT;
SELECT *
FROM TBL_FILE;
SELECT FILENO"파일번호", SUBSTR(FILENAME,(LENGTH(FILENAME)+2)-INSTR(REVERSE(FILENAME), '\'))"파일명"
FROM TBL_FILE;
SELECT FILENO"파일번호", REVERSE(SUBSTR(REVERSE(FILENAME), 1, INSTR(REVERSE(FILENAME), '\')-1))"파일명"
FROM TBL_FILE;
LPAD(), RPAD()
--> BYTE 수를 확보하여 왼쪽(오른쪽)부터 문자열로 채우는 기능을 가진 함수
SELECT 'ORACLE', LPAD('ORACLE', 10, '*')
FROM DUAL;
--① 10Byte 공간을 확보한다. (두 번째 파라미터 값에 의해...)
--② 확보한 공간에 'ORACLE' 문자열을 담는다. (첫 번째 파라미터 값에 의해...)
--③ 남아있는 Byte 공간에 왼쪽부터(오른쪽부터) '*'로 채운다. (세 번째 파라미터 값에 의해...)
--==>>ORACLE ****ORACLE
SELECT 'ORACLE', RPAD('ORACLE', 10, '*')
FROM DUAL;
--==>>ORACLE ORACLE****
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()
-- 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()
SELECT REPLACE('MY ORACLE SERVER', 'ORA', '오라')
FROM DUAL;
--==>>MY 오라CLE SERVER
-- 대소문자 구분을 명확히 하며, 완성형으로 처리된다.
-- 1대 1로 사상되는 TRANSLATE() 함수와 비교해서 정리할 수 있도록 한다.
숫자형 함수
--■■■ 숫자형 함수 ■■■--
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()
--> 절삭, 반올림을 처리하는 함수
SELECT 48.678, TRUNC(48.678, 2) -- 소수점 이하 두 번재 자리까지 표현
, TRUNC(48.678, 0) -- 정수부
, TRUNC(48.678)
, TRUNC(48.678, -1) -- 십의 자리까지 표현
, TRUNC(48.678, -2)
FROM DUAL;
MOD()
→ %
SELECT MOD(5,2) -- 5를 2로 나눈 나머지
FROM DUAL;
--==>> 1
POWER()
→ 제곱
SELECT POWER(5, 3) -- 5의 3승
FROM DUAL;
SQRT()
→ 루트
SELECT SQRT(2) -- 루트2
FROM DUAL;
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)
SELECT ASIN(0.5), ACOS(0.5), ATAN(0.5)
FROM DUAL;
--==>>
/*
0.52359877559829887307710723054658381405
1.04719755119659774615421446109316762805
0.4636476090008061162142562314612144020295
*/
SIGN() 부호, 서명, 특징
--> 연산 처리에 대한 결과값이 양수이면 1, 0이면 0, 음수이면 -1을 반환한다.
SELECT SIGN(5-2), SIGN(5-5), SIGN(2-5)
FROM DUAL;
--==>> 1 0 -1
--> 매출이나 수지와 관련하여 적자 및 흑자 여부를 나타낼 때 주로 사용된다.
ASCII(), CHR()
SELECT ASCII('A'), CHR(65)
FROM DUAL;
--==>> 65 A
--> 우측정렬은 숫자, 좌측정렬은 문자
SELECT 65, '65'
FROM DUAL;
날짜 연산
--■■■ 날짜 연산 ■■■--
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.
--※ 날짜 연산의 기본 단위는 DAY(일수)이다.
-- (2018-01-09 16:48:27) + 2
SELECT SYSDATE, SYSDATE+1, SYSDATE-2
FROM DUAL;
--==>> 2018-01-09 16:49:40 2018-01-10 16:49:40 2018-01-07 16:49:40
-- 시간 단위 연산
SELECT SYSDATE, SYSDATE+1/24, SYSDATE-2/24
FROM DUAL;
--==>> 2018-01-09 16:50:52 2018-01-09 17:50:52 2018-01-09 14:50:52
과제
-- 현재 시간과, 1일 2시간 3분 4초 후를 조회(출력)한다.
-----------------------------------------------------
-- 현재 시간 | 연산 후 시간
-----------------------------------------------------
-- 2018-01-09 16:50:52 | 2018-01-10 18:53:56
SELECT SYSDATE, SYSDATE+1+(2/24)+(3/(60*24))+(4/(60*60*24))
FROM DUAL;
'교육 및 세미나 > 신입사원 교육' 카테고리의 다른 글
| 7일차(2018.01.11) (0) | 2018.01.12 |
|---|---|
| 6일차(2018.01.10) (0) | 2018.01.12 |
| 4일차(2018.01.05) (0) | 2018.01.12 |
| 3일차(2018.01.04) (0) | 2018.01.09 |
| 2일차(2018.01.03) (0) | 2018.01.09 |