혼코딩

1. 다운로드를 받는다.

https://www.oracle.com/index.html

2. 컴퓨터 관리 상태 바꾸기

시작됨 / 자동으로 바뀌었는지 확인하고 바꾸어준다.

3. 방화벽 인바운드 규칙을 만들어준다.

방화벽 > 고급설정 클릭

인바운드 규칙 > 새 규칙

포트번호 설정

연결허용 선택

규칙 이름 설정

4. 오라클 설치 후 작업

DOS> sqlplus /nolog (sqlplus 명령 실행)
sql> conn 관리자/비밀번호 as sysdba (접속)
Connected.

sql> show user (현재 접속 사용자명 검색)
USER is "SYS"

sql> @C:\ sql파일경로 (파일에 저장된 SQL 명령 실행)
☞ 사용자 생성

sql> show user
USER is "SCOTT"

sql> select table_name from tabs; (현재 접속 사용자의 테이블 목록 확인)

sql 명령 실행 및 사용자 생성

오라클 첫 실행 - 경로설정

오라클 실행

오라클 접속

JOIN

• 두 개이상의 테이블에서 원하는 컬럼값을 검색하기 위한 기능

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 부서번호 검색

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP;

DEPT 테이블에서 모든 부서의 부서번호, 부서이름, 부서위치 검색

SELECT DEPTNO, DNAME, LOC FROM DEPT;

EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 부서이름, 부서위치 검색 두 개이상의 테이블에서 컬럼값을 검색하기 위해서는 반드시 조인조건을 명시하여 사용

카타시안 프로덕트(CATASIAN PRODUCT)

• 조인조건을 명시하지 않은 조인 명령
• 조인 테이블들에 대한 모든 행의 컬럼값을 교차조인하여 검색

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP, DEPT;

동등조인(EQUI JOIN)

• 조인조건에서 조인 테이블의 컬럼값이 같을 경우 조인하여 검색 조인조건은 WHERE 구문의 조건식으로 표현하여 사용

EMP 테이블과 DEPT 테이블의 조인조건은 부서번호가 같은 경우 조인하여 검색

→ 테이블에 동일한 컬럼명이 존재할 경우 테이블명을 이용하여 컬럼명을 명확하게 표현

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 부서번호, 부서이름, 부서위치 검색

SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, ENAME, SAL, DEPT.DEPTNO, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

조인 테이블의 동일 컬럼명을 동시에 검색할 경우 두번째 선언된 컬럼명은 자동으로 변경되어 검색

SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DEPT.DEPTNO, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

조인 테이블의 동일 컬럼명을 동시에 검색할 경우 컬럼 별칭을 사용하여 검색하는 것을 권장

SELECT EMPNO, ENAME, SAL, EMP.DEPTNO EMP_DEPTNO, DEPT.DEPTNO DEPT_DEPTNO, DNAME, LOC 
    FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

테이블 별칭(TABLE ALIAS)

• 조인 테이블에 새로운 이름을 일시적으로 부여하는 기능
• 테이블 이름을 간단하게 표현하거나 셀프 조인하기 위해 사용
• 형식) 테이블명 별칭
• 테이블에 별칭을 부여한 경우 해당 명령에서 실제 테이블명을 사용하면 에러 발생

SELECT EMPNO, ENAME, SAL, E.DEPTNO EMP_DEPTNO, D.DEPTNO DEPT_DEPTNO, DNAME, LOC 
    FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;

비동등 조인(NON-EQUI JOIN)

• = 연산자를 제외한 조인조건을 사용하여 컬럼값을 비교하여 조인

EMP 테이블에 모든 사원의 사원번호,사원이름,급여 검색

SELECT EMPNO,ENAME,SAL FROM EMP;

SALGRADE 테이블에서 모든 등록의 급여등급, 최소급여, 최대급여 검색

SELECT GRADE,LOSAL,HISAL FROM SALGRADE;

EMP 테이블과 SALGRADE 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 급여등급 검색

조인조건 : 급여가 최소급여과 최대급여 범위에 포함될 경우 급여등급 검색

SELECT EMPNO, ENAME, SAL, GRADE FROM EMP, SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL;

외부조건(OUTER JOIN)

• 조인조건이 맞는 행만 검색하는 것이 아니라 조인조건이 맞지 않는 행도 검색하는 조인
• 조인조건이 맞지 않는 행이 저장된 테이블에 (+)를 사용하여 컬럼값이 NULL로 검색되도록 설정

EMP 테이블에서 모든 사원의 부서번호를 중복되지 않고 하나만 검색 - 결과 : 10, 20, 30 검색(부서번호)

SELECT DISTINCT DEPTNO FROM EMP;

DEPT 테이블에서 모든 부서의 부서번호, 부서이름, 부서위치 검색 - 10, 20, 30, 40 검색(존재하는 모든 부서번호)

SELECT DEPTNO, DNAME, LOC FROM DEPT;

EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 부서이름, 부서위치 검색

40번 부서에 근무하는 사원이 존재하지 않으므로 40번 부서에 대한 부서이름과 부서위치 미검색

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 부서이름, 부서위치 검색

사원이 존재하지 않는 부서가 검색될 경우 사원번호, 사원이름, 급여는 NULL 검색

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO(+)=DEPT.DEPTNO;

셀프조인(SELF JOIN) : 하나의 테이블에 다른 이름(TABLE ALIAS)을 여러 개 부여하여 조인

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 매니저번호(매니저의 사원번호) 검색

SELECT EMPNO,ENAME,MGR FROM EMP;

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 매니저번호(매니저의 사원번호), 매니저이름(매니저의 사원이름) 검색 매니저번호에 NULL이 저장된 사원 미검색 - 검색오류

SELECT WORKER.EMPNO, WORKER.ENAME WORKER_ENAME, WORKER.MGR, MANAGER.ENAME MANAGER_ENAME
    FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR=MANAGER.EMPNO;

매니저번호에 NULL이 저장된 사원도 검색하기 위해 외부조인 사용

SELECT WORKER.EMPNO, WORKER.ENAME WORKER_ENAME, WORKER.MGR, MANAGER.ENAME MANAGER_ENAME
    FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR=MANAGER.EMPNO(+);

EMP 테이블과 DEPT 테이블에서 SALES 부서에 근무하는 사원의 사원번호, 사원이름, 급여, 부서이름, 부서위치 검색

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME='SALES';

1999 JOIN

• 1999년에 채택된 표준 SQL(SQL3)에 추가된 JOIN 구문

CROSS JOIN

• 조인 테이블의 모든 행들을 교차 조인
• 조인조건을 명시하지 않는다.
• 형식) SELECT 검색대상,... FROM 테이블명1 CROSS JOIN 테이블명2

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP CROSS JOIN DEPT;

NATURAL JOIN

• 조인 테이블에 하나의 동일한 컬럼명의 컬럼값이 같은 경우 조인
• 형식) SELECT 검색대상,... FROM 테이블명1 NATURAL JOIN 테이블명2

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP NATURAL JOIN DEPT;

• 동일한 컬럼명에 대한 테이블명 명시 불필요

SELECT EMPNO, ENAME, SAL, DEPTNO, DNAME, LOC FROM EMP NATURAL JOIN DEPT;

조인 테이블에 동일한 이름에 컬럼이 여러개 존재할 경우 USING 구분을 이용하여 조인 가능

형식) SELECT 검색대상,... FROM 테이블명1 JOIN 테이블명2 USING(컬럼명)

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP JOIN DEPT USING(DEPTNO);
SELECT EMPNO, ENAME, SAL, DEPTNO, DNAME, LOC FROM EMP JOIN DEPT USING(DEPTNO);

INNER JOIN

• 조건조건을 명시하여 조인조건이 맞을 경우 조인
• 형식) SELECT 검색대상,... FROM 테이블명1 [INNER] JOIN 테이블명2 ON 조인조건
• 조인 테이블에 같은 이름에 컬럼이 존재할 경우 테이블명을 명시하여 컬럼 표현

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME, LOC FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, ENAME, SAL, DEPT.DEPTNO, DNAME, LOC FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

EMP 테이블과 SALGRADE 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 급여등급 검색

SELECT EMPNO, ENAME, SAL, GRADE FROM EMP JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL;

EMP 테이블과 DEPT 테이블에서 SALES 부서에 근무하는 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP 
	JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE DNAME='SALES';

OUTER JOIN

• 조인조건을 명시하여 조인조건이 맞을 경우뿐만 아니라 맞지 않을 경우에도 조인
• 형식) SELECT 검색대상,... FROM 테이블명1 {LEFT|RIGTH|FULL} [OUTER] JOIN 테이블명2 ON 조인조건
• LEFT OUTER JOIN : 왼쪽 방향에 선언된 조인 테이블의 모든 행을 검색하되 조인조건이 맞지 않는 오른쪽방향의 테이블 행에 컬럼값은 NULL 검색
• RIGTH OUTER JOIN : 오른쪽 방향에 선언된 조인 테이블의 모든 행을 검색하되 조인조건이 맞지 않는 왼쪽방향의 테이블 행에 컬럼값은 NULL 검색
• FULL OUTER JOIN : 양쪽 방향에 선언된 조인 테이블의 모든 행을 검색하되 조인조건이 맞지 않는 왼쪽 또는 오른쪽 방향의 테이블 행에 컬럼값은 NULL 검색

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP FULL OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 매니저번호, 매니저이름 검색 - SELF JOIN

SELECT WORKER.EMPNO, WORKER.ENAME WORKER_ENAME, WORKER.MGR, MANAGER.ENAME MANAGER_ENAME
    FROM EMP WORKER JOIN EMP MANAGER ON WORKER.MGR=MANAGER.EMPNO;

SELECT WORKER.EMPNO, WORKER.ENAME WORKER_ENAME, WORKER.MGR, MANAGER.ENAME MANAGER_ENAME
    FROM EMP WORKER LEFT JOIN EMP MANAGER ON WORKER.MGR=MANAGER.EMPNO;

SUBQUERY

• SQL 명령에 포함되어 실행되는 SELECT 명령
• 여러번의 SQL 명령으로 얻을 수 있는 결과를 하나의 SQL 명령으로 얻기 위해 사용하는 기능
• SELECT 명령(MAINQUERY)에 포함되어 실행되는 SELECT 명령(SUBQUERY)
• SUBQUERY 명령 실행 후 MAINQUERY 명령 실행
• SELECT 명령에서 SUBQUERY 명령은 FROM, WHERE, HAVING 구문에서 ( ) 안에 작성

EMP 테이블에서 사원이름이 SCOTT인 사원보다 많은 급여를 받는 사원의 사원번호, 사원이름, 급여 검색

• WHERE 구문에서 조건식의 비교값 대신 SUBQUERY 명령의 결과값으로 사용
• 조건식의 비교대상과 같은 자료형의 값이 하나만 검색되도록 SUBQUERY 명령 작성

SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SCOTT');

EMP 테이블에서 사원번호 7844인 사원과 같은 업무를 하는 사원의 사원번호, 사원이름, 업무, 급여 검색

SELECT EMPNO, ENAME, JOB, SAL FROM EMP 
    WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7844) AND EMPNO<>7844;

EMP 테이블에서 사원번호가 7521인 사원과 같은 업무를 하는 사원 중 사원번호 7900인 사원보다 많은 급여를 받는 사원의 사원번호, 사원이름, 업무, 급여 검색

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7521)
    AND EMPNO<>7521 AND SAL>(SELECT SAL FROM EMP WHERE EMPNO=7900);

EMP 테이블에서 SALES 부서에 근무하는 사원의 사원번호, 사원이름, 업무, 급여 검색

• 부서이름은 DEPT 테이블에 저장되어 있으므로 테이블 조인을 이용하여 검색

SELECT EMPNO, ENAME, JOB, SAL FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE DNAME='SALES'; 

• 테이블 조인 대신 서브쿼리를 사용하여 검색

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');

EMP 테이블에서 급여를 가장 적게 받는 사원의 사원번호, 사원이름, 업무, 급여 검색

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM EMP);

EMP 테이블에서 SALES 부서에 근무하는 사원 중 가장 많은 급여를 받는 사원의 사원번호, 사원이름, 업무, 급여 검색

오라클은 서브쿼리 내부에 다른 서브쿼리 사용 가능

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL=
    (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES'));

EMP 테이블에서 부서별 평균 급여 중 가장 많은 평균 급여를 받는 부서의 부서번호, 평균급여 검색

SELECT DEPTNO, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY DEPTNO 
    HAVING AVG(SAL)=(SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO);

함수(FUNCTION)

• 값을 전달받아 처리하여 결과값을 반환하는 기능을 제공

단일함수

• 하나의 값을 전달받아 처리하여 결과값을 반환하는 함수
• 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수

그룹함수 : 여러 개의 값을 전달받아 처리하여 결과값을 반환하는 함수

문자함수 : 문자형 상수를 전달받아 처리하여 결과를 반환하는 함수

UPPER (문자상수) : 전달받은 문자형 상수를 모두 대문자로 변환하여 반환하는 함수

LOWER (문자상수) : 전달받은 문자형 상수를 모두 소문자로 변환하여 반환하는 함수

SELECT ENAME, UPPER(ENAME), LOWER(ENAME) FROM EMP WHERE ENAME = 'SCOTT';

문자형 상수는 대소문자를 구분함

UPPER 함수 또는 LOWER 함수를 이용하여 대소문자 구분 없이 비교하여 검색 가능

SELECT EMPNO,ENAME,SAL FROM EMP WHERE UPPER(ENAME) = UPPER('smith');

INITCAP (문자상수) : 문자형 상수를 전달받아 첫문자만 대문자로 변환하고 나머지는 소문자로 변환하여 반환하는 함수

SELECT ENAME, INITCAP(ENAME) FROM EMP;

CONCAT (문자상수,문자상수)

• 두개의 문자형 상수를 결합하여 반환하는 함수
• || 기호와 동일한 기능 제공

SELECT ENAME, JOB, CONCAT(ENAME, JOB), ENAME || JOB FROM EMP;

SUBSTR(문자상수, 시작위치, 갯수) : 문자형 상수에서 시작위치(INDEX)부터 갯수만큼의 문자를 분리하여 반환하는 함수

SELECT EMPNO, ENAME, JOB, SUBSTR(JOB, 6, 3) FROM EMP WHERE EMPNO=7499;

LENGTH(문자상수) : 문자형 상수의 문자갯수를 반환하는 함수

SELECT EMPNO, ENAME, LENGTH(ENAME) FROM EMP WHERE EMPNO=7499;

INSTR(문자상수, 검색문자, 시작위치, 검색갯수)

• 문자형 상수에서 검색문자를 시작위치부터 검색하여 검색갯수의 시작위치(INDEX)를 반환하는 반환하는 함수
• 검색문자가 없는 경우 0 반환

SELECT EMPNO, ENAME, JOB, INSTR(JOB, 'A', 1, 2) FROM EMP WHERE EMPNO=7499;

LPAD(문자상수, 자릿수, 채울문자) : 문자형 상수를 자릿수만큼의 길이로 오른쪽 정렬하여 검색하되 왼쪽 남은 자릿수는 채울문자로 채워 반환하는 함수

RPAD(문자상수, 자릿수, 채울문자) : 문자형 상수를 자릿수만큼의 길이로 왼쪽 정렬하여 검색하되 오른쪽 남은 자릿수는 채울문자로 채워 반환하는 함수

SELECT EMPNO, ENAME, SAL, LPAD(SAL, 8, '*'), RPAD(SAL, 8, '*') FROM EMP WHERE EMPNO=7844;

TRIM( {LEADING | TRAILING} 제거문자 FROM 문자상수) : 문자상수의 앞(LEADING) 또는 뒤(TRAILING)에 존재하는 제거문자를 모두 없애고 반환하는 함수

SELECT EMPNO, ENAME, JOB, TRIM(LEADING 'S' FROM JOB) , TRIM(TRAILING 'N' FROM JOB) FROM EMP WHERE EMPNO=7844;

REPLACE(문자상수, 검색문자, 변환문자) : 문자형 상수에 검색문자를 찾아 변환문자로 변환하여 반환하는 함수

SELECT EMPNO, ENAME, JOB, REPLACE(JOB, 'MAN', 'PERSON') FROM EMP WHERE EMPNO=7844;

숫자함수 : 숫자형 상수를 전달받아 처리하여 결과값을 반환하는 함수

ROUND(숫자상수, 소수점자릿수)

• 숫자형 상수를 소숫점 자릿수만큼 검색되도록 반올림하여 반환하는 함수

DUAL

• 가상테이블
• 테이블 없이 SELECT 명령을 작성할 경우 사용하는 테이블

SELECT 45.582, ROUND(45.582, 2), ROUND(45.582, 0), ROUND(45.582, -1) FROM DUAL;

TRUNC(숫자상수, 소수점자릿수) : 숫자형 상수를 소숫점 자릿수만큼 검색되도록 절삭하여 반환하는 함수

SELECT 45.582, TRUNC(45.582, 2), TRUNC(45.582, 0), TRUNC(45.582, -1) FROM DUAL;

CEIL(숫자상수) : 숫자형 상수에서 소숫점 이하 값이 존재할 경우 증가하여 정수값으로 반환하는 함수

SELECT 15.3, CEIL(15.3), CEIL(-15.3) FROM DUAL;

FLOOR(숫자상수) : 숫자형 상수에서 소숫점 이하 값이 존재할 경우 감소하여 정수값으로 반환하는 함수

SELECT 15.3, FLOOR(15.3), FLOOR(-15.3) FROM DUAL;

MOD(숫자상수, 숫자상수) : 숫자형 상수를 나누어 나머지를 반환하는 함수

SELECT 20/8, MOD(20, 8) FROM DUAL;
--정수값을 나눈 결과로 실수값 발생 가능

POWER(숫자상수, 숫자상수) : 숫자형 상수의 제곱근을 계산하여 반환하는 함수

SELECT 3*3*3*3*3, POWER(3, 5) FROM DUAL;

날짜함수

• 날짜형 상수를 전달받아 처리하여 반환하는 함수

SYSDATE

• 시스템의 현재 날짜와 시간정보를 제공하기 위한 키워드
• 자료형이 날짜형인 경우 표면적으로 'RR/MM/DD'로 표현되지만 내부적으로 날짜와 시간정보 저장

SELECT SYSDATE FROM DUAL;

ADD_MONTHS(날짜상수, 숫자상수) : 날짜형 상수에 숫자형 상수만큼의 개월수를 더한 결과를 반환하는 함수

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 5) FROM DUAL;

NEXT_DAY(날짜상수, 요일) : 날짜형 상수에서 다가오는 특정 요일의 날짜를 반환하는 함수

SELECT SYSDATE, NEXT_DAY(SYSDATE, '토') FROM DUAL;

오라클에 접속된 사용자 환경(세션 : SESSION)에 따라 날짜와 시간정보를 다르게 표현 가능

세션의 날짜에 대한 표현 언어 변경

ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; 
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'SAT') FROM DUAL;

ALTER SESSION SET NLS_LANGUAGE='KOREAN'; 
SELECT SYSDATE, NEXT_DAY(SYSDATE, '토') FROM DUAL;

TRUNC(날짜상수, 표현단위) : 날짜형 상수를 원하는 단위까지만 표현하며 나머지는 초기값으로 반환하는 함수

SELECT SYSDATE, TRUNC(SYSDATE, 'MONTH'), TRUNC(SYSDATE, 'YEAR') FROM DUAL;

날짜형 상수는 연산 가능

날짜상수 + 숫자상수 = 날짜상수 → 일 증가

SELECT SYSDATE, SYSDATE + 7 FROM DUAL;

날짜상수 + 숫자상수/24 = 날짜상수 → 시간 증가

SELECT SYSDATE, SYSDATE + 100/24 FROM DUAL;

날짜상수 - 숫자상수 = 날짜상수 → 일 감소

SELECT SYSDATE, SYSDATE - 7 FROM DUAL;

날짜상수 - 숫자상수/24 = 날짜상수 → 시간 감소

SELECT SYSDATE, SYSDATE - 100/24 FROM DUAL;

날짜상수 - 날짜상수 = 숫자상수 → 일(실수)

SELECT EMPNO, ENAME, HIREDATE, SYSDATE-HIREDATE FROM EMP WHERE EMPNO=7499; 
SELECT EMPNO, ENAME, HIREDATE, CEIL(SYSDATE-HIREDATE) || '일' "근속일수" FROM EMP WHERE EMPNO=7499;

변환함수

• 전달받은 상수를 원하는 자료형의 상수로 변환하여 반환하는 함수

TO_NUMBER(문자상수)

• 문자형 상수를 전달받아 숫자형 상수로 변환하여 반환하는 함수
• 전달받은 문자형 상수가 숫자가 아닌 경우 에러 발생

SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO=7839;

비교 컬럼의 자료형이 숫자형인 경우 비교값이 문자형 상수인 경우 숫자형 상수로 변환하여 비교 - 강제 형변환

SELECT EMPNO,ENAME,SAL FROM EMP WHERE EMPNO=TO_NUMBER('7839');

비교 컬럼의 자료형이 숫자형인 경우 비교값이 문자형 상수라면 자동으로 숫자형 상수로 변환하여 비교 - 자동 형변환

SELECT EMPNO,ENAME,SAL FROM EMP WHERE EMPNO='7839';

EMP 테이블에서 사원번호가 7839인 사원의 사원번호, 사원이름, 급여, 급여*0.9 검색

SELECT EMPNO, ENAME, SAL, SAL*0.9 FROM EMP WHERE EMPNO=7839; 
SELECT EMPNO, ENAME, SAL, SAL*TO_NUMBER('0.9') FROM EMP WHERE EMPNO=7839; 
SELECT EMPNO, ENAME, SAL, SAL*'0.9' FROM EMP WHERE EMPNO=7839;

문자형 상수를 계산할 경우 자동으로 숫자형 상수로 변환 

SELECT '10'+'20' FROM DUAL;

TO_DATE (문자상수 [,패턴기호])

• 문자형 상수를 전달받아 날짜형 상수로 변환하여 반환하는 함수
• 문자형 상수가 날짜 형식가 아닌 경우 에러 발생 - 기본적으로 'RR/MM/DD' 형식으로 표현

비교 컴럼의 자료형이 날짜형인 경우 비교값이 문자형 상수인 경우 날짜형 상수로 변환하여 비교 - 강제 형변환

SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE=TO_DATE('82/01/23');

비교 컴럼의 자료형이 날짜형인 경우 비교값이 문자형 상수라면 자동으로 날짜형 상수로 변환하여 비교 - 자동 형변환 

SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE='82/01/23';

'RR/MM/DD' 형식 대신 'YYYY-MM-DD' 형식으로 표현 가능 

SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE='1982-01-23';

TO_DATE 함수의 패턴기호를 이용하여 원하는 형식의 날짜형 상수로 표현 가능

SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE=TO_DATE('01-23-1982', 'MM-DD-YYYY');

EMP 테이블에서 사원번호가 7844인 사원의 사원번호, 사원이름, 입사일, 입사일로부터 2000년 1월 1일까지 근무한 일자를 계산하여 검색

SELECT EMPNO, ENAME, HIREDATE, TO_DATE('2000-01-01') - HIREDATE FROM EMP WHERE EMPNO=7844;

TO_CHAR( {숫자상수|날짜상수}, 패턴기호)

• 전달받은 숫자형 상수 또는 날짜형 상수를 패턴기호 형식의 문자형 상수로 변환하여 반환하는 함수

날짜패턴기호 : YYYY(년), RR(년), MM(월), DD(일), HH24(시), HH12(시), AM(오전), PM(오후), MI(분), SS(초)

SELECT SYSDATE FROM DUAL; 
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL; 
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

EMP 테이블에서 1981년도에 입사한 사원의 정보 검색

세션의 날짜형 표현방식(NLS_DATE_FORMAT)이 'RR/MM/DD' 형식일 경우에만 가능

SELECT * FROM EMP WHERE HIREDATE LIKE '81%';

날짜패턴기호를 이용하여 원하는 날짜값만 반환받아 문자형 상수로 비교

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY')='1981';

숫자패턴기호

• 9(숫자 또는 공백), 0(숫자), L(화폐단위), $(달러)
• 자릿수가 모자란 경우 #으로 표현

SELECT 100000000, TO_CHAR(100000000, '999,999,990' ) FROM DUAL;

EMP 테이블에서 사원번호가 7844인 사원의 사원번호,사원이름,급여 검색

SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO=7844; 
SELECT EMPNO, ENAME, TO_CHAR(SAL,'999,990') SAL FROM EMP WHERE EMPNO=7844; 
SELECT EMPNO, ENAME, TO_CHAR(SAL,'L99,990') SAL FROM EMP WHERE EMPNO=7844; 
SELECT EMPNO, ENAME, TO_CHAR(SAL,'$99,990.00') SAL FROM EMP WHERE EMPNO=7844;

일반함수 : 전달값이 특정 조건일 경우 처리하여 결과를 반환하는 함수

NVL(전달값, 변경값) : 전달값이 NULL인 경우 변경값으로 변환하여 반환하는 함수

변경값은 전달값의 자료형과 동일한 자료형의 값으로 변경하지 않으면 에러 발생

EMP 테이블에서 모든 사원의 사원번호,사원이름,연봉((급여+성과급)*12) 검색

NVL 함수를 이용하여 성과급이 NULL인 경우 0으로 변환하여 연산

SELECT EMPNO, ENAME, (SAL+NVL(COMM, 0)) * 12 ANNUAL FROM EMP;

NVL2(전달값, 변경값1, 변경값2) : 전달값이 NULL이 아닌 경우 변경값1로 변환하고 NULL인 경우 변경값2로 변환하여 반환하는 함수

SELECT EMPNO, ENAME, (SAL+NVL2(COMM, COMM, 0))*12 ANNUAL FROM EMP; 
SELECT EMPNO, ENAME, NVL2(COMM, (SAL+COMM)*12, SAL*12) ANNUAL FROM EMP;

DECODE(전달값, 비교값1, 변경값1, 비교값2, 변경값2, ...[,기본변경값])

• 전달값을 비교값과 차례대로 비교하여 같은 경우 변경값으로 변환하여 반환하는 함수
• 같은 비교값이 없는 경우 기본 변경값으로 변환하여 반환 - 기본 변경값이 생략된 경우 NULL 반환

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 업무, 급여, 업무별 실급여 검색

업무별 실급여 : 업무별로 급여를 다시 계산하여 실제로 지불할 급여

ANALYST : 급여*1.1, CLERK : 급여*1.2, MANAGER : 급여*1.3, PRESIDENT : 급여*1.4, SALESMAN : 급여*1.5

SELECT EMPNO, ENAME, JOB, SAL, DECODE(JOB, 'ANALYST', SAL*1.1, 'CLERK', SAL*1.2 , 'MANAGER', SAL*1.3, 'PRESIDENT', SAL*1.4, 'SALESMAN', SAL*1.5, SAL) "업무별 실급여" FROM EMP;

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 업무별 급여 검색

해당 업무가 아닌 경우 NULL 검색

SELECT EMPNO, ENAME, DECODE(JOB, 'ANALYST', SAL) ANALYST , DECODE(JOB, 'CLERK', SAL) CLERK, DECODE(JOB, 'MANAGER', SAL) MANAGER , DECODE(JOB, 'PRESIDENT', SAL) PRESIDENT, DECODE(JOB, 'SALESMAN', SAL) SALESMAN FROM EMP;

그룹함수

• 값을 여러개 전달받아 처리하여 결과를 반환하는 함수

COUNT(전달값)

• 전달값의 갯수를 반환하는 함수 - 검색행의 갯수 반환
• 다른 검색대상과 그룹함수를 동시에 사용 불가능 : 검색행의 갯수가 다르므로 에러 발생
• 그룹함수는 NULL이 전달될 경우 값으로 인식되지 않아 미처리

SELECT COUNT(EMPNO) FROM EMP;

전달값으로 * 기호를 사용하여 모든 컬럼값으로 행의 갯수를 계산하여 반환

SELECT COUNT(*) FROM EMP;

MAX(전달값) : 전달값 중 최대값을 반환하는 함수

SELECT MAX(SAL) FROM EMP; 
SELECT MAX(ENAME) FROM EMP; 
SELECT MAX(HIREDATE) FROM EMP;

MIN(전달값) : 전달값 중 최소값을 반환하는 함수

SELECT MIN(SAL) FROM EMP; 
SELECT MIN(ENAME) FROM EMP; 
SELECT MIN(HIREDATE) FROM EMP;

SUM(전달값) : 숫자형 상수를 전달받아 전달값의 합계를 계산하여 반환하는 함수

SELECT SUM(SAL) FROM EMP;

AVG(전달값) : 숫자형 상수를 전달받아 전달값의 평균을 계산하여 반환하는 함수

SELECT AVG(SAL) FROM EMP; 
SELECT ROUND(AVG(SAL), 2) FROM EMP;

EMP 테이블에서 모든 사원의 평균 성과급 검색

성과급이 NULL이 아닌 사원의 평균 성과급 검색 - 검색오류

SELECT AVG(COMM) FROM EMP;

NVL 함수를 이용하여 성과급이 NULL인 경우 0으로 변환하여 평균 성과급 검색

SELECT AVG(NVL(COMM, 0)) FROM EMP; 
SELECT CEIL(AVG(NVL(COMM, 0))) "평균 성과급" FROM EMP;

GROUP BY

• 그룹함수를 사용할 경우 컬럼값으로 그룹을 세분화하여 검색하기 위해 사용
• 컬럼값이 같은 경우 같은 그룹으로 처리되어 검색하는 구문
• 형식) SELECT 그룹함수[,검색대상],... FROM 테이블명,... [WHERE 조건식] GROUP BY {컬럼명|연산식|함수},... [ORDER BY {컬럼명|연산식,함수,위치값,별칭} {ASC|DESC},... ]

GROUP BY 구문에서 사용된 그룹 지정 표현식은 검색대상으로 사용 가능 - 권장

SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO;

컬럼의 별칭을 GROUP BY 구분에서 사용할 경우 에러 발생

SELECT DEPTNO DNO, COUNT(*) FROM EMP GROUP BY DNO;

EMP 테이블에서 모든 사원의 업무별 평균 급여 검색

SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY JOB;

EMP 테이블에서 업무가 PRESIDENT인 사원의 제외한 사원들의 업무별 평균 급여 검색

SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP WHERE JOB<>'PRESIDENT' GROUP BY JOB;

EMP 테이블에서 업무가 PRESIDENT인 사원의 제외한 사원들의 업무별 평균 급여를 평균 급여로 내림차순 정렬하여 검색

SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP WHERE JOB<>'PRESIDENT' GROUP BY JOB ORDER BY AVG_SAL DESC;

SUM : EMP 테이블에서 모든 사원의 부서별 급여 합계 검색

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO;

HAVING

• GROUP BY 구문으로 세분화되어 검색된 결과의 그룹 조건을 부여하여 검색
• 형식) SELECT 그룹함수[,검색대상],... FROM 테이블명,... [WHERE 조건식] GROUP BY {컬럼명|연산식|함수},... HAVING 그룹조건식 [ORDER BY {컬럼명|연산식,함수,위치값,별칭} {ASC|DESC},... ]

EMP 테이블에서 모든 사원의 부서별 급여 합계 중 부서별 급여 합계가 9000 이상인 경우에만 검색

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>=9000;

EMP 테이블에서 업무가 PRESIDENT인 사원의 제외한 사원들의 업무별 평균 급여 검색

SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP WHERE JOB<>'PRESIDENT' GROUP BY JOB; 
SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY JOB HAVING JOB<>'PRESIDENT';

분석함수

• 윈도우함수(그룹함수, 순서함수, 순위함수 등)에 의해 발생된 값을 제공받아 처리하여 결과를 반환하는 함수
• 형식) SELECT 윈도우함수 OVER ([PARTITION BY 컬럼명] [ORDER BY 컬럼명] [WINDOWING]), 검색대상, ... FROM 테이블명

MAX( )

EMP 테이블에서 모든 사원의 급여 중 가장 많은 급여 검색

SELECT MAX(SAL) FROM EMP;

EMP 테이블에서 모든 사원 중 가장 많은 급여를 받는 사원의 사원번호, 사원이름, 급여 검색

그룹함수를 사용할 경우 다른 검색대상을 작성할 경우 에러 발생

SELECT EMPNO, ENAME, MAX(SAL) FROM EMP;

SELECT 명령을 여러 번 사용하여 가장 많은 급여를 받는 사원의 사원번호, 사원이름, 급여 검색 가능

SELECT MAX(SAL) FROM EMP; --검색결과 : 5000
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL=5000;

OVER( )

그룹함수를 분석함수와 같이 사용할 경우 다른 검색대상 사용 가능

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 최대급여 검색

SELECT EMPNO, ENAME, SAL, MAX(SAL) OVER() FROM EMP;

AVG( )

EMP 테이블에서 모든 사원의 부서별 평균 급여 검색

SELECT DEPTNO, CEIL(AVG(SAL)) "부서별 평균 급여" FROM EMP GROUP BY DEPTNO;

# CEIL() 함수는 이전 포스팅 글에 나와있음

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 부서번호, 부서별 평균 급여 검색

분석함수를 사용할 경우 GROUP BY 구문 사용 불가능

SELECT EMPNO, ENAME, SAL, DEPTNO, CEIL(AVG(SAL) OVER()) "부서별 평균 급여" FROM EMP GROUP BY DEPTNO;

분석함수에서는 GROUP BY 구문 대신 PARTITION BY 기능을 제공

SELECT EMPNO, ENAME, SAL, DEPTNO, CEIL(AVG(SAL) OVER(PARTITION BY DEPTNO)) "부서별 평균 급여" FROM EMP;

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 급여합계를 급여로 내림차순 정렬하여 검색

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER() "급여합계" FROM EMP ORDER BY SAL DESC;

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 순차적 누적 급여합계를 급여로 내림차순 정렬하여 검색

• OVER 함수에서 ORDER BY 기능을 이용하면 정렬하면서 윈도우 함수의 결과를 차례대로 반환
• 정렬값이 동일한 경우 윈도우 함수의 동시 처리 결과값 반환

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL DESC) "순차적 누적 급여합계" FROM EMP;

정렬값이 동일한 경우 다른 정렬값을 제공하면 동시 처리 결과값 미반환

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL DESC, ENAME) "순차적 누적 급여합계" FROM EMP;

OVER 함수에 WINDOWING 기능을 이용하면 정렬값이 동일한 경우에도 동시 처리 결과값 반환 방지

• WINDOWING : 순차적인 검색행을 기준으로 이전 또는 이후에 존재하는 행(범위)을 표현하는 기능
• ROWS UNBOUNDED PRECEDING : 검색행을 기준으로 이전에 존재하는 모든 행만을 검색대상으로 설정

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) "순차적 누적 급여합계" FROM EMP;

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 검색사원의 급여와 검색사원의 직전 사원 급여와 검색사원의 직후 사원 급여의 합계를 급여로 오름차순 정렬하여 검색

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER
    (ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) "부분합계" FROM EMP;

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 검색사원의 급여보다 100 작거나 200 큰 사원의 인원수를 급여로 오름차순 정렬하여 검색

SELECT EMPNO, ENAME, SAL, COUNT(*) OVER
    (ORDER BY SAL RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING)-1 "인원수" FROM EMP;

순위함수

• RANK, DENSE_RANK, ROW_NUMBER
• 분석함수와 같이 사용해야 되는 함수

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 급여순위를 급여로 내림차순 정렬하여 검색

RANK 함수 : 정렬값이 동일한 경우 같은 순위로 지정하며 다음 순위를 건너뛰고 검색하는 함수

SELECT EMPNO, ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) 급여순위 FROM EMP;

DENSE_RANK 함수 : 정렬값이 동일한 경우 같은 순위로 지정하며 다음 순위로 검색하는 함수

SELECT EMPNO, ENAME, SAL, DENSE_RANK() OVER(ORDER BY SAL DESC) 급여순위 FROM EMP;    

ROW_NUMBER 함수 : 정렬값이 동일한 경우 다른 순위로 지정하여 검색하는 함수 - 행번호 지정

SELECT EMPNO, ENAME, SAL, ROW_NUMBER() OVER(ORDER BY SAL DESC) 급여순위 FROM EMP;   

순서함수

• FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 분석함수와 같이 사용해야 되는 함수

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 급여를 가장 많이 받는 사원의 사원이름, 급여를 가장 적게 받는 사원이름을 급여로 내림차순 정렬하여 검색

• FIRST_VALUE 함수 : 첫번째 검색행의 컬럼값을 반환하는 함수
• LAST_VALUE 함수 : 마지막 검색행의 컬럼값을 반환하는 함수

SELECT EMPNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER(ORDER BY SAL DESC) MAX_ENAME
    ,LAST_VALUE(ENAME) OVER(ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW 
    AND UNBOUNDED FOLLOWING) MIN_ENAME FROM EMP;

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 검색사원 직전 사원의 급여, 검색사원 직후 사원의 급여를 급여로 내림차순 정렬하여 검색

• LAG 함수 : 검색행 이전 행의 컬럼값을 반환하는 함수
  • 형식) LAG(컬럼명, 이전행위치, 변경값) : 이전 위치의 행에 컬럼값을 반환하되 이전 행이 없으면 변경값 반환
• LEAD 함수 : 검색행 이후 행의 컬럼값을 반환하는 함수
  • 형식) LEAD(컬럼명, 이전행위치, 변경값) : 이후 위치의 행에 컬럼값을 반환하되 이후 행이 없으면 변경값 반환

SELECT EMPNO, ENAME, SAL, LAG(SAL,1,0) OVER(ORDER BY SAL DESC) BEFORE_SAL
    ,LEAD(SAL,1,0) OVER(ORDER BY SAL DESC) AFTER_SAL FROM EMP;

설명문 처리

워크시트의 명령을 전달하여 실행 : [CTRL]+[ENTER] 또는 [F5]

[CTRL]+[ENTER] : 커서 위치의 SQL 명령을 전달하여 실행

[F5] : 워크시트의 모든 SQL 명령을 전달하여 실행

범위를 지정하여 [CTRL]+[ENTER] 또는 [F5]를 입력할 경우 범위의 명령만 전달하여 실행 가능

SQL 명령은 대소문자를 구분하지 않으며 하나의 SQL 명령을 구분하기 위해 ;(세미콜론) 사용

테이블(TABLE)

• 데이타베이스에서 정보를 저장하기 위한 기본 객체
• 현재 접속 사용자의 스키마에 존재하는 테이블 목록 확인

SELECT TABLE_NAME FROM TABS;
SELECT * FROM TAB;

테이블의 구조 확인

• 테이블 속성 정보(컬럼명과 자료형)
• 형식) DESC 테이블명

EMP 테이블 : 사원정보를 저장하기 위한 테이블

DEPT 테이블 : 부서정보를 저장하기 위한 테이블

DESC EMP;
DESC DEPT;

DQL(DATA QUERY LANGUAGE)

• 데이타 질의어
• SELECT : 하나 이상의 테이블에서 행을 검색하기 위한 명령
• 형식) SELECT 검색대상,검색대상,... FROM 테이블명, 테이블명,...
• 검색대상 : * (모든 컬럼) - 다른 검색대상과 동시 사용 불가, 컬럼명, 연산식, 함수 등

SELECT * FROM EMP;
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP;

COLUMN ALIAS : 검색대상에 별칭(임시 컬럼명)을 부여하는 기능

• 형식) SELECT 검색대상 [AS] 별칭, 검색대상 [AS] 별칭 FROM 테이블명,...
• 검색대상을 명확하게 표현하기 위해 별칭 사용
• AS 생략 가능

SELECT EMPNO AS NO, ENAME AS NAME, DEPTNO AS DNO FROM EMP;
SELECT EMPNO 사원번호, ENAME 사원이름, DEPTNO 부서코드 FROM EMP;

컬럼 별칭으로 공백 또는 특수문자를 사용하고자 할 경우 " " 기호를 사용하여 표현

SELECT EMPNO "사원 번호", ENAME 사원이름, SAL*12 연봉 FROM EMP;

식별자(테이블명, 컬럼명, 컬럼 별칭 등)는 단어와 단어를 _로 구분하여 표현

SELECT EMPNO, ENAME, SAL*12 ANNUAL_SALARY FROM EMP;

|| : 결합 연산자

• 검색대상의 결과값 결합
• 문자형 상수는 ' ' 기호를 이용하여 표현

SELECT ENAME || JOB FROM EMP;
SELECT ENAME || '님의 업무는' ||JOB|| '입니다.' FROM EMP;

EMP 테이블에 저장된 모든 사원의 업무 검색 (중복된 컬럼값 검색)

SELECT JOB FROM EMP;

DISTINCT : 검색대상의 중복값을 제외하고 유일한 하나의 결과값만 검색하는 기능 제공

형식) SELECT [DISTINCT] 검색대상[, 검색대상, ...] FROM 테이블명,...

SELECT DISTINCT JOB FROM EMP;

DISTINCT 키워드에 검색대상을 여러 개 나열 가능

SELECT DISTINCT JOB, DEPTNO FROM EMP;

WHERE : 조건식을 사용하여 조건이 참인 행을 검색하는 기능 제공

형식) SELECT 검색대상,... FROM 테이블명,... WHERE 조건식

EMP 테이블에서 사원번호가 7698인 사원의 사원번호, 사원이름, 업무, 급여 검색

단일행을 검색하고자 할 경우 조건식에 PK 제약조건이 부여된 컬럼을 이용하여 검색

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7698;

EMP 테이블에서 사원이름이 KING인 사원의 사원번호, 사원이름, 업무, 급여 검색

문자형 상수는 ' ' 기호를 이용하여 표현

문자형 상수는 대소문자를 구분하여 비교

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE ENAME='KING';

EMP 테이블에서 입사일이 1981년 6월 9일인 사원의 사원번호, 사원이름, 업무, 급여, 입사일 검색

날짜형 상수는 ' ' 기호에 RR/MM/DD 형식의 패턴으로 표현 - 검색결과는 RR/MM/DD 표현

SELECT EMPNO, ENAME, JOB, SAL, HIREDATE FROM EMP WHERE HIREDATE='81/06/09';

EMP 테이블에서 업무가 SALESMAN이 아닌 사원의 사원번호, 사원이름, 업무, 급여 검색

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE JOB != 'SALESMAN'; 
SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE JOB <> 'SALESMAN';

EMP 테이블에서 사원이름이 A, B, C로 시작되는 사원의 사원번호, 사원이름, 업무, 급여 검색

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE ENAME < 'D';

이중 검색 - WHERE 조건 AND 조건

EMP 테이블에서 업무가 SALESMAN인 사원 중 급여가 1500 이상인 사원의 모든 정보 검색

SELECT * FROM EMP WHERE JOB = 'SALESMAN' AND SAL >= 1500;

WHERE 조건 OR 조건

EMP 테이블에서 부서번호가 10이거나 업무가 MANAGER인 사원의 사원번호, 사원이름, 업무, 급여, 부서번호 검색 

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE DEPTNO=10 OR JOB='MANAGER';

범위연산자를 이용하여 컬럼값이 작은값부터 큰값 범위에 포함될 경우의 조건식 구현

형식) 컬럼명 BETWEEN 작은값 AND 큰값

SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 3000;

선택연산자를 이용하여 컬럼값이 여러 개중 하나인 경우의 조건식 구현

형식) 컬럼명 IN(값,값,...)

SELECT * FROM EMP WHERE JOB IN('ANALYST' , 'SALESMAN');

EMP 테이블에서 사원이름이 A로 시작되는 사원의 모든 정보 검색

SELECT * FROM EMP WHERE ENAME>='A' AND ENAME<'B'; 
SELECT * FROM EMP WHERE (ENAME BETWEEN 'A' AND 'B') AND ENAME<>'B';

검색패턴기호 : 부정확한 값을 검색하기 위해 제공하는 기호

• %(전체) 또는 _(문자)
• 값이 비교하기 위해 = 연산자를 사용할 경우 % 또는 _를 문자로 인식하여 비교

SELECT * FROM EMP WHERE ENAME = 'A%';

패턴연산자를 이용하여 검색패턴기호로 조건식 구현

형식) 컬럼명 LIKE '값 또는 검색패턴기호'

SELECT * FROM EMP WHERE ENAME LIKE 'A%';

EMP 테이블에서 이름에 A가 포함된 사원의 모든 정보 검색

SELECT * FROM EMP WHERE ENAME LIKE '%A%';

EMP 테이블에서 이름 두번째 문자가 L인 사원의 모든 정보 검색

SELECT * FROM EMP WHERE ENAME LIKE '_L%';

※ 주의 - LIKE 연산자는 % 또는 _를 검색패턴기호로 인식하여 검색

ESCAPE 키워드를 이용하여 ESCAPE 문자를 표현하기 위한 기호 설정

% 또는 _를 검색패턴기호가 아닌 ESCAPE 문자로 인식되도록 설정

SELECT * FROM EMP WHERE ENAME LIKE '%\_%' ESCAPE '\';

컬럼값 대신 NULL로 표현된 경우 존재하는 값이 아니므로 연산식 미실행

IS 연산자를 이용하여 NULL 비교

형식) 컬럼명 IS NULL 또는 컬럼명 IS NOT NULL

SELECT * FROM EMP WHERE COMM IS NULL;

ORDER BY

• 컬럼값을 비교하여 행이 정렬되도록 검색하는 기능 제공
• 형식) SELECT 검색대상, ... FROM 테이블명, ... [WHERE 조건식] ORDER BY {컬럼명 | 연산식 | 별칭 | INDEX} {ASC | DESC}, {컬럼명 | 연산식 | 별칭 | INDEX} {ASC | DESC}, ...
• ASC : 오름차순 정렬, DESC : 내림차순 정렬
• 첫번째 정렬값이 동일한 경우 두번째 값으로 정렬
• ASC는 생략 가능 (기본값)

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY SAL DESC;

검색대상에는 자동으로 첨자(COLUMN INDEX)가 부여

오라클에서는 첨자가 1부터 1씩 증가되는 값으로 표현

SELECT EMPNO, ENAME, SAL*12 ANNUAL FROM EMP ORDER BY 3 DESC;

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 업무, 급여, 부서번호를 부서번호로 오름차순 정렬하고 같은 부서번호인 경우 급여로 내림차순 정렬하여 검색

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY DEPTNO, SAL DESC;