Wild Flower

2010/05/20

--분석함수로 쓰는 AVG (AVG,LANK..많이쓰임)

SELECT employee_id
      , AVG(salary) OVER()
FROM employees

-- 분석함수 -> 집계함수

SELECT DISTINCT department_id
      , AVG(salary) OVER(PARTITION BY department_id)
FROM employees

--각 사원의 부서내 월급 순위를 알아보자
-- 출력결과> 사원번호, 월급, 순위
SELECT employee_id
      , salary
      , department_id
      , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC)
        AS rnk
FROM EMPLOYEES

--MAX MIN AVG SUM COUNT
--RANK DENSE_RANK PERCENT_RANK ROW_NUMBER NTILE LAG LEAD

--분석함수
--사원 테이블에서 월급이 많은 순서대로 등위를 산출하자
-- Inline View

SELECT *
FROM
(SELECT employee_id
        ,salary
        ,RANK() OVER(ORDER BY salary DESC) AS rnk
FROM employees)
WHERE rnk <= 10;

--PARTITION BY

SELECT *
FROM
(SELECT employee_id
      , salary
      , department_id
      , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC)
        AS rnk
FROM employees)
WHERE rnk = 1;

-- 부서별 평균월급보다 많이 받는 사원을 출력해보자
-- 출력결과> 사번, 월급, 평균월급

SELECT *
FROM
(SELECT employee_id, salary,
          CEIL(AVG(salary) OVER(PARTITION BY department_id)) as avgsal
 FROM employees)
WHERE salary > avgsal;

-- DENSE_RANK, PERCENT_RANK

SELECT employee_id
      , hire_date
      , DENSE_RANK() OVER(ORDER BY hire_date ASC)
FROM employees

SELECT employee_id
      , hire_date
      , ROUND(PERCENT_RANK() OVER(ORDER BY hire_date ASC)*100,2) AS prnk
FROM employees

-- 직무별로 월급 상위 10%이상인 사람의 정보를 나타내자
-- 출력결과> 사번, 직무, 월급, prnk

SELECT *
FROM
(SELECT employee_id
      , job_id
      , salary
      , ROUND(PERCENT_RANK() OVER(PARTITION BY job_id ORDER BY salary DESC)
              *100,2) AS prnk
FROM employees)
WHERE prnk  <= 10;

-- ROW_NUMBER
SELECT employee_id
      ,
FROM employees

-- NTILE
SELECT employee_id
      , salary
      , NTILE(4) OVER(ORDER BY salary DESC)
FROM employees

-- 전후의 컬럼 비교
-- LAG, LEAD
SELECT employee_id
      , LAG(employee_id, 1) OVER (ORDER BY employee_id) AS LAG
      , LEAD(employee_id, 1) OVER (ORDER BY employee_id) AS LEAD
FROM employees
WHERE department_id = 30;

-- 출력값 > 부서번호 중복되는 번호는 생략
SELECT department_id
      , job_id
      , COUNT(*)
FROM employees
WHERE department_id IN(50,80)
GROUP BY department_id, job_id
ORDER BY department_id;

--다음과 같은 형태로
--  dep_id      job_id      인원
--   50         SH_CLERK      20

SELECT NULLIF(department_id, LAG(department_id,1) OVER(ORDER BY department_id))
       , job_id
       , count(*)
FROM employees
WHERE department_id IN (50, 80)
GROUP BY department_id, job_id
ORDER BY department_id;

CREATE TABLE stock(
item VARCHAR2(15),
profit NUMBER
);

INSERT INTO stock
VALUES('2005.12',25.8);

INSERT INTO stock
VALUES('2006.12',0.6);

INSERT INTO stock
VALUES('2007.12',-37.9);

INSERT INTO stock
VALUES('2008.12',57.8);

INSERT INTO stock
VALUES('2009.12',26.9);

SELECT *
FROM STOCK

--토비스의 당기 순이익 변화율을 알아보자
--출력결과> ITEM, 당기 순이익 변화율

SELECT ABS(-10) FROM dual; --절대값

--순이익 변화율:(올해 순이익 - 전년 순이익) / ABS(작년 순이익)

SELECT ITEM
      , ROUND(profit - LAG(profit, 1) OVER(ORDER BY item)
      / ABS(LAG(profit, 1) OVER(ORDER BY item))*100,2) AS ch_ratio
FROM stock

--join

--9i 이전
SELECT employee_id
      , last_name
      , e.department_id
      , department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.department_id = 80;

--위와 다른형태 조인

SELECT employee_id
      , last_name
      , e.department_id
      , department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

-- 모든 부서의 위치 정보를 얻어보자
-- 출력결과> 부서번호, 부서이름, 도시이름

SELECT department_id
      , department_name
      , city
FROM departments d, locations l
WHERE d.location_id = l.location_id;

SELECT department_id
      , department_name
      , city
FROM departments d JOIN  locations l
ON d.location_id = l.location_id;

-- 2000년 1/2분기에 입사안 사원 정보를 가져오자.
-- 출력결과> 사원번호, 이름(employees table), 부서이름(departments table), 직무이름(jobs table)

SELECT employee_id
      , last_name
      , department_name
      , job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND TO_CHAR(hire_date,'YYYY-MM') BETWEEN '2000-01' AND '2000-06';

SELECT employee_id
      , last_name
      , department_name
      , job_title
FROM employees e JOIN  departments d
ON e.department_id = d.department_id
JOIN jobs j
ON e.job_id = j.job_id
WHERE TO_CHAR(hire_date,'YYYY-MM') BETWEEN '2000-01' AND '2000-06';

-- 이름(성) 이 'Smith', 'King'인 사원의 정보를 얻자
-- 출력결과> 사원번호, 이름(성), 월급, (해당직무)최소 월급, (해당직무)최대 월급
--           부서이름

SELECT employee_id
      , last_name
      , salary
      , min_salary
      , max_salary
      , department_name
FROM employees e JOIN jobs j
ON e.job_id = j.job_id
JOIN departments d
ON e.department_id = d.department_id
WHERE last_name IN ('Smith', 'King');

-- OUTER JOIN
-- 명시적 JOIN : null을 가진 칼럼 방향을 가리켜라
-- 비명시적 JOIN : null을 가지지 못한 컬럼을 (+) 도와라

SELECT employee_id
      , last_name
      , hire_date
      , e.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE TO_CHAR(hire_date,'YYYY-MM') BETWEEN '1999-01' AND '1999-06'

-- 부서와 부서 관리자 정보를 얻어보자
-- 단 관리자가 없는 부서는 'No Manager'라고 표시하자.
-- 출력결과> 부서이름, 관리자 이름(성)

SELECT department_name
      , NVL(last_name, 'No Manager')
     
FROM departments d , employees e
WHERE d.manager_id = e.employee_id(+)

--SELF JOIN
-- 각 사원의 매니저 이름(성)을 알아보자
-- 단 매니저가 없는 사원은 'Boss' 라고 표시하자
-- 출력결과> 사원번호, 이름, 매니저이름

SELECT e1.employee_id
      , e1.last_name
      , NVL(e2.last_name, 'Boss')
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id(+)

--1. 사원 테이블에서 사원번호를 ‘7-369’ 형태로 출력하세요

SELECT SUBSTR(empno, 1, 1) || '-' || SUBSTR(empno, 2) AS 사원번호
      , ename
FROM emp;
 
 
--2. 사원 테이블에서 모든 사원의 정보를 출력하세요.
--    단, 부서정보가 없을 경우 0으로 표시하세요.

SELECT empno
      , ename
      , sal
      , NVL(deptno,0) AS DEPNO
FROM emp;
 
 
--3. 사원 테이블에서 모든 사원의 정보를 출력하세요.
--    단 부서번호가 10이면 ‘Accounting’, 20이면 ‘Research’,
--                 30이면 ‘Sales’, 40이면 ‘Operations’
--                 기타는 ‘Others’로 나타내세요

SELECT empno
      , ename
      , sal
      , deptno
      , CASE deptno WHEN 10 THEN 'Accounting'
                     WHEN 20 THEN 'Research'
                     WHEN 30 THEN 'Sales'
                     WHEN 40 THEN 'Operations'
                     ELSE 'Others'
        END AS DEPT_NAME
FROM emp;

--4. 사원 테이블에서 연도별 입사 인원을 구하세요.
--    단, 입사인원이 2명 이상인 해만 출력하세요.

SELECT EXTRACT(YEAR FROM hiredate)
      , COUNT(*)
FROM emp
GROUP BY EXTRACT(YEAR FROM hiredate)
HAVING COUNT(*) >= 2;

--5. 사원 테이블에서 부서번호가 30인 사원들의 근무기간을
--    1995년 12월 31일을 기준으로 구하세요.

SELECT empno
      , ename
      , sal
      , deptno
      , CEIL(MONTHS_BETWEEN('1995-12-31',hiredate)/12) || '년' ||
        CEIL(MOD(months_between('1995-12-31',hiredate), 12)) || '개월'
FROM emp
WHERE deptno = 30;

2010/05/19

--각 사원의 근무 기간을 출력해보자.
--출력결과> 사원번호, 입사일자, 근속기간

SELECT employee_id
      , hire_date                                        
      , FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date)/12) AS 년
      , FLOOR(MOD(months_between(SYSDATE, hire_date), 12)) AS 월
FROM employees

--각 사원의 입사 분기를 알아보자

SELECT TO_CHAR(hire_date, 'Q')
      , COUNT(*)
FROM employees
GROUP BY TO_CHAR(hire_date, 'Q')

-- 오늘과 2007년 1월 1일간의 개월 수는?

SELECT months_between(SYSDATE, to_date('2007-01-01', 'YYYY-MM-DD'))
FROM dual;

-- 오늘 날짜에 1년을 더해보자.

SELECT ADD_MONTHS(SYSDATE, 12) FROM DUAL;
SELECT SYSDATE +TO_YMINTERVAL('01-00') FROM dual;

-- NULL 과의 연산
-- NULL 과 연산 X -->  NVL 함수사용하여 대체

SELECT NULL + 1
FROM dual;

-- 직원들의 예상 월급을 구해보자
-- 출력결과> 사원번호, 이름, 월급, 커미션퍼센트, 예상월급(월급+월급X커미션퍼센트)

SELECT employee_id
      , last_name
      , salary
      , commission_pct
      , salary + salary * NVL(commission_pct, 0) AS 예상월급
FROM employees

--DECODE 보다는 CASE 사용
--CASE 두가지로 표현
-- 모든 사원에 대항 부서번호가 60이면 'IT', 80이면 'Sales', 나머지는 'Other'로
-- 나타내 보세요
-- 출력결과> 사원번호, 이름, 부서번호, 부서이름
SELECT employee_id
      , last_name
      , department_id
      , DECODE(department_id, 60, 'IT' , 80, 'Sales' , 'Other')
FROM employees

SELECT employee_id
      , last_name
      , department_id
      , CASE department_id
              WHEN 60 THEN 'IT'
              WHEN 80 THEN 'Sales'
              ELSE 'Other'
        END
FROM employees

--CASE 만 가능한 비교
--사원들의 월급에 등급을 매겨보자
--평균월급(6462) 보다 적으면 L, 많으면 H
--출력결과> 사원번호, 월급, 등급
SELECT employee_id
      , salary
      , CASE WHEN salary < 6462 THEN 'L'
             WHEN salary > 6462 THEN 'H'
             ELSE 'etc'
        END AS rnk
FROM employees

-- 등급: 월급이 20000 이상이면 A, 15000이상 20000미만 이면 B,
--      10000이상 15000미만이면 C, 5000이상 10000미만이면 D
--      이외는 E
SELECT employee_id
      , salary
      , CASE WHEN salary >= 20000 THEN 'A'
             WHEN salary >= 15000 THEN 'B'
             WHEN salary >= 10000 THEN 'C'
             WHEN salary >= 5000 THEN 'D'
             ELSE 'E'
        END AS 월급등급
FROM employees

2010/05/19

예제 위주...


SELECT CEIL(15.7) "Ceiling" FROM DUAL;

SELECT FLOOR(15.7) "Floor" FROM DUAL;

SELECT MOD(11,4) "Modulus" FROM DUAL;

SELECT ROUND(15.193,1) "Round" FROM DUAL;

SELECT WIDTH_BUCKET(92,0,100,10) "Score" FROM dual;

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 30), 'YYYY-MM-DD') FROM dual;

SELECT LAST_DAY(SYSDATE) - SYSDATE "REMAIN DAYS" FROM dual;

SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

SELECT MONTHS_BETWEEN(LAST_DAY(SYSDATE),SYSDATE) "REMAIN MONTHS" FROM DUAL;

SELECT SYSDATE FROM DUAL;

SELECT SYSTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = '-5:00';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;

-- 주민등록번호 123456-7890123
-- 123456-7******

SELECT RPAD(SUBSTR('123456-1234567',0,8),14,'*') "NUMBER"
FROM dual;

-- 월별 입사인원을 구해보자
-- 출력결과 > 월, 인원
SELECT EXTRACT(MONTH FROM hire_date), count(*)
FROM employees
GROUP BY EXTRACT(MONTH FROM hire_date)
ORDER BY EXTRACT(MONTH FROM hire_date);

-- 분기별 입사인원을 구해보자
-- 출력결과 > 분기, 인원
SELECT hire_date, WIDTH_BUCKET(EXTRACT(MONTH FROM hire_date),1,12,4)
FROM employees;

SELECT CEIL(EXTRACT(MONTH FROM hire_date)/3), COUNT(*)
FROM employees
GROUP BY CEIL(EXTRACT(MONTH FROM hire_date)/3)
ORDER BY CEIL(EXTRACT(MONTH FROM hire_date)/3);

-- 월(1년) 더하기
SELECT ADD_MONTHS(TO_DATE('2008/01/01','YYYY/MM/DD'),12)
FROM dual;

2010/05/19

-----------------우선 과제를 위한 생성-----------------------

CREATE TABLE emp
       (empno NUMBER(4) NOT NULL,
        ename VARCHAR2(10),
        job VARCHAR2(9),
        mgr NUMBER(4),
        hiredate date,
        sal NUMBER,
        comm NUMBER,
        deptno NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
         '1980-12-17',  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
         '1981-2-20', 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
         '1981-2-22', 1250,  500, NULL);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
         '1981-4-2',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
         '1981-9-28', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
         '1981-5-1',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
         '1981-6-9',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
         '1982-12-9', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
         '1981-11-17', 5000, NULL, NULL);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
         '1981-12-8',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
         '1983-1-12', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
         '1981-12-3',  950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
         '1981-12-3',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
         '1982-1-23', 1300, NULL, 10);

CREATE TABLE DEPT
       (deptno NUMBER(2),
        dname VARCHAR2(14),
        loc VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
        (ename VARCHAR2(10),
         job   VARCHAR2(9),
         sal   NUMBER,
         comm  NUMBER);

CREATE TABLE SALGRADE
        (grade NUMBER(1),
         losal NUMBER,
         hisal NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;


-------------------------- 문제-------------------------------


-- 1번
--사원 테이블에서 사원번호가 7369, 7698 번인 사원번호와 이름을
--출력하세요.

SELECT empno
      , ename
FROM emp
WHERE empno IN('7369','7698');

-- 2번
-- 사원 테이블에서 사원번호가 7369, 7698 번인 아닌 사원번호와 이름을 출력하세요

SELECT empno
      , ename
FROM emp
WHERE empno NOT IN('7369','7698');

-- 3번
-- 사원 테이블에서 급여(SAL)가 3000에서 5000사이인 사원 정보를 다 출력하세요

SELECT *
FROM emp
WHERE sal BETWEEN 3000 AND 5000;

-- 4번
-- 사원 테이블에서 성과급(COMM)을 받는 사원의 정보를 모두 출력하세요.

SELECT *
FROM emp
WHERE comm IS NOT NULL AND comm > 0;

-- 5번
-- 사원 테이블에서 고용일자(HIREDATE)가 1981년인 사원의 정보를 모두 출력하세요

SELECT *
FROM emp
WHERE TO_CHAR(hiredate,'YYYY') = 1981;

-- 6번
-- 사원 테이블에서 직업(JOB)이 SALESMAN 중에서 사원번호의 최대값을 출력하세요.

SELECT MAX(empno)
FROM emp
WHERE job IN('SALESMAN')

-- 7번
-- 사원 테이블에서 부서번호(DEPTNO)별 최대월급을 구하세요.
SELECT deptno
      , MAX(sal)
FROM emp
GROUP BY deptno;

-- 8번
-- 사원 테이블에서 직무(JOB)별 평균월급을 구하세요.
-- 단 부서 인원이 1명인 부서는 제외하고 직무에 대한 오름차순으로 나타내세요

SELECT job
      , AVG(sal)
FROM emp
GROUP BY job
HAVING COUNT(*) > 1
ORDER BY job;

정렬

-- 오름차순이 디폴트

예)
-- 전 사원의 정보를 얻자 (단, 월급에 대한 내림차순)
-- 출력결과> 사원번호, 이름(성), 급여

SELECT employee_id
      , last_name
      , salary
FROM employees

SELECT employee_id
      , last_name
      , salary
FROM employees
ORDER BY job_id;

--입사일자 순
SELECT employee_id
      , last_name
      , hire_date
FROM employees
ORDER BY hire_date;

WHERE

1. AND
2. OR
3. BETWEEN AND
4. IN()
5. IS NULL/IS NOT NULL
6. LIKE

WHERE

1. AND OR

예)
SELECT employee_id
      , last_name
      , department_id
      , job_id
FROM employees
WHERE last_name = 'Gee' OR last_name = 'Smith';

2. BETWEEN AND

기본적 AND 있지만 쉽게 쓰기위해서

예)
SELECT employee_id
      , last_name
      , department_id
      , job_id
FROM employees
WHERE salary BETWEEN 8000 AND 10000;

3. IN()
이녀석도 OR 쉽게 쓰기위해

예)
SELECT employee_id
      , last_name
      , department_id
      , job_id
FROM employees
WHERE last_name IN ('Gee','Smith');

4. IS NULL

예)

--부서를 발령바디 않은 사원의 정보를얻자
--사원번호 이름(성) 입사일자 부서ID

SELECT employee_id
      , last_name
      , hire_date
      , department_id
FROM employees
WHERE department_id IS NULL;

5. LIKE

예)
--1999년 입자사 중에서 영업사원 이거나 급여를 3000 이하를 받는 사원의 정보를 얻자

SELECT employee_id
      , last_name
      , hire_date
      , job_id
      , salary
FROM employees
WHERE hire_date LIKE '99%'
    --AND job_id IN ('SA_MAN', 'SA_REP);
AND (job_id LIKE 'SA%'OR salary <= 3000);

2010/05/18 (화)

SQL Developer 실행

도구 - 환경설정
- 글꼴 및 크기 조절, 행번호 띄우기 등 자신의 맞게 설정

쿼리 실행 명령어
- 드래그 ctrl+enter

예약어 : 대문자

같은성능이라면 짫은쿼리를 활용하여 작성

SELECT/ FROM

HR database 에서 실습

예1)
SELECT employee_id
            ,last_name
            ,hire_date
            ,salary
FROM employees;

예2)
SELECT department_id, department_name, manager_id
FROM departments;