개발새발

12/05/09

기본적으로 제공되는 scott/tiger 계정으로 연습한 쿼리 예제문

http://www.oracleclub.com => 오라클강좌 참고

-- 테이블 생성 및 제약조건
 CREATE TABLE EMP2(
    EMPNO  NUMBER    CONSTRAINT emp_pk_empno PRIMARY KEY,    
    ENAME  VARCHAR2(20) CONSTRAINT emp_nn_ename NOT NULL,
    JOB    VARCHAR2(40),
    MGR    NUMBER,
    HIREDATE  DATE,
    SAL    NUMBER,
    COMM   NUMBER,
    DEPTNO NUMBER
);

-- SELECT 를 이용한 테이블 생성
CREATE TABLE emp2
  AS SELECT * FROM emp;

-- 테이블 삭제
DROP TABLE EMP2;

-- 테이블 복사
CREATE TABLE EMP3
  AS SELECT * FROM EMP;
 
-- 컬럼 추가
ALTER TABLE emp
  ADD (addr VARCHAR2(50));

-- 컬럼 수정
ALTER TABLE emp
  MODIFY (ename VARCHAR2(50));

-- 컬럼 삭제
ALTER TABLE emp
  DROP COLUMN addr;

-- 데이터 삽입
INSERT INTO dept (deptno, dname)
  VALUES(10, 'ACCOUNTING' );

-- 데이터 수정
UPDATE emp
  SET deptno = 30
  WHERE empno = 7902;
 
-- 데이터 삭제
DELETE FROM emp
  WHERE empno = 7902 ;
 
-- SELECT문( IN )
select ename, empno
  from emp
  where empno IN(7900, 7934);
 
-- SELECT문(BETWEEN)
select empno, ename
  from emp
  where sal between 3000 and 5000;
 
-- SELECT문(LIKE)
select empno, ename
  from emp
  where UPPER(name) LIKE '%K%';

-- SELECT문(ORDER BY) asc=오름차순, desc=내림차순
select empno, ename
  from emp
  where deptno = 30
  ORDER BY ename ASC;
 
-- JOIN(EQUI JOIN)
SELECT  e.ename, d.dname
  FROM   emp e , dept d
  WHERE e.deptno = d.deptno;

-- JOIN(OUTER JOIN)
SELECT DISTINCT(a.deptno), b.deptno
  FROM emp a, dept b
  WHERE  a.deptno(+) = b.deptno;

-- 내장함수(숫자함수)
--    반올림
SELECT ROUND(192.123, 1) test FROM DUAL ;

-- 내장함수(숫자함수)
--    올림
SELECT CEIL(10.1) test FROM DUAL;

-- 내장함수(숫자함수)
--    버림
SELECT FLOOR(10.1) test FROM DUAL;

-- 내장함수(문자열 함수)
--    문자 결합
SELECT CONCAT('Oracle', 'Club') FROM DUAL;

-- 내장함수(문자열 함수)
--    LOWER <= 소문자  , UPPER <= 대문자
SELECT LOWER('KIM JUNG SICK') FROM DUAL;
SELECT UPPER('KIM JUNG SICK') FROM DUAL;

-- 내장함수(문자열 함수)
--    SUBSTR('A', B, C) <= A의 B번째 문자부터 C의 개수만큼 반환
SELECT SUBSTR('JUNG-SICK', 3, 4) FROM DUAL;

-- 내장함수(문자열 함수)
--    LENGTH <= 문자 개수 반환
SELECT LENGTH('JUNG-SICK') TEST FROM DUAL;

-- 내장함수(문자열 함수)
--    REPLACE('A', 'B', 'C') <= 'A'문자 중에 'B'에 해당하는 문자를
--                              'C'로 반환한다
SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;

-- 내장함수(문자열 함수)
--   왼쪽에서 3번째부터 시작을 해서 비교를 하는데 
--   OR이 두 번째 검색되는 지점의 위치를 반환 한다.
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL;

-- 내장함수(문자열 함수)
--    TRIM('A' from 'ABCDE' <= 'A'를 제거한다
SELECT TRIM('A' from 'ABCDE') "TRIM EXAMPLE"
  FROM DUAL;

-- 내장함수(날짜 처리 함수)
--   오늘날짜 출력
SELECT to_char(sysdate, 'YYYY-MM-DD') today
  FROM dual;
 
-- 내장함수(날짜 처리 함수)
--  날짜 출력
SELECT TO_DATE('2011/05/08','YY/MM/DD') FROM DUAL;

-- 내장함수 (General Functions)
--    DECODE
SELECT deptno,
      DECODE(deptno, 10 , 'ACCOUNTING' ,
                     20 , 'RESEARCH' ,
                     30 , 'SALES' ,
                     40 , 'OPERATIONS')
     FROM emp;

-- 내장함수 (CASE)
--    DECODE와 같은 기능
SELECT deptno,
        CASE deptno
          WHEN 10 THEN 'ACCOUNTING'
            WHEN 20 THEN 'RESEARCH'
            WHEN 30 THEN 'SALES'
            ELSE 'OPERATIONS'
          END as "Dept Name"
     FROM emp;

-- GROUP BY
-- 부서별로 그룹한 결과와 사원수 조회
SELECT b.deptno, COUNT(a.empno)
  FROM emp a, dept b
  WHERE a.deptno = b.deptno
  GROUP BY b.deptno;

-- 부서별로 그룹하여  부서번호, 인원수, 급여의 평균,
-- 급여의 합을 조회하는 예제
SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균",
            ROUND(SUM(sal)) "급여합계"
     FROM emp
     GROUP BY deptno;
 
-- 업무별로 그룹하여  업무, 인원수, 평균 급여액,
-- 최고 급여액, 최저 급여액 및 합계를 조회하는 예제
SELECT job, count(empno),AVG(sal)"평균",
       MAX(sal), MIN(sal), SUM(sal)
  FROM emp
  GROUP BY job;

--HAVING
--사원수가 5명이 넘는 부서의 부서명과 사원수 조회
SELECT d.dname, count(e.empno)
  from emp e, dept d
  where e.deptno = d.deptno
  group by d.dname
  having count(e.empno) >= 5;
 
--서브쿼리
SELECT ename,job
  FROM emp
  WHERE job = (SELECT job
              FROM emp
              WHERE empno = 7369);