KITRI 공공데이터 기반 JAVA Programming과정 3일차

--각 년도별 입사자수를 출력하시오. 년도는 오름차순 정렬

SELECT SUBSTR(hire_date, 1, 2) 년도, COUNT(hire_date)||'명' 입사자수

FROM employees 

GROUP BY SUBSTR(hire_date, 1, 2)

ORDER BY SUBSTR(hire_date, 1, 2) ASC

--월별 입사자수를 출력하시오. 입사자가 많은 순서대로 정렬하시오.

SELECT SUBSTR(hire_date, 4, 2) 월, COUNT(hire_date)||'명' 입사자수

FROM employees 

GROUP BY SUBSTR(hire_date, 4, 2)

ORDER BY COUNT(hire_date) DESC

--------------------------------------------------------------------

--서브쿼리

--최대급여를 출력하시오

SELECT MAX(salary)

FROM employees

--최대급여를 받는 사원의 사번, 이름, 급여를 출력하시오.

1)최대급여계산

2)1)와 같은 급여를 받는 사원을 출력

SELECT employee_id, first_name, salary

FROM employees

WHERE salary = (SELECT MAX(salary)FROM employees)

--'IT'부서(DEPARTMENT_NAME) 소속사원의 이름과 입사일을 출력하시오.

1)부서명이 IT인 부서번호 얻기

2)1)와 같은 부서번호를 갖는 사원을 출력

SELECT first_name "이름", TO_CHAR(hire_date,'YY/MM/DD') "입사일", department_id "부서번호"

FROM employees

WHERE department_id = (SELECT department_id 

                       FROM departments 

                       WHERE department_name = 'IT')

--업무명(job_title)이 President인 사원의 사원명과 업무(job_id)을 출력하시오.

1)업무명(job_title)이 President인 업무번호얻기

2)1)과같은 업무번호를 갖는 사원을 출력

SELECT first_name "사원명", job_id "업무"

FROM employees

WHERE job_id = (SELECT job_id 

                FROM jobs 

                WHERE job_title = 'President')

--업무명(job_title)이 President인 사원을 제외한 사원명과 직종(job_id)을 출력하시오.

1)업무명(job_title)이 President인 사원을 제외한 사원명 얻기

2)1)과같은 사원명을 갖는 사원의 직종을 출력

SELECT first_name, job_id

FROM employees

WHERE job_id <> (SELECT job_id 

                 FROM jobs 

                 WHERE job_title = 'President')

--Seattle city에 있는 부서번호와 부서명을 출력하시오.

1)Seattle city에 있는 부서번호 얻기

2)1)과같은 부서명을 출력

SELECT department_id "부서번호", department_name "부서명"

FROM departments

WHERE location_id = (SELECT location_id 

                     FROM locations 

                     WHERE city = 'Seattle')

--매니저가[이름 last_name 'King'이고 first_name이 'Steven']인 사원의 이름과 급여를 출력하시오.

1)매니저가[이름 last_name 'King'이고 first_name이 'Steven']인 사번얻기

2)1)과같은 사원의 급여를 출력

SELECT first_name||' '||last_name "이름", salary "급여"

FROM employees

WHERE manager_id = (SELECT employee_id 

                    FROM employees 

                    WHERE first_name = 'Steven' AND last_name = 'King')

--평균급여보다 많은 급여를 받는 사원들의 사번, 급여를 출력하시오.

1)평균급여얻기

2)1)보다 큰 급여를 받는 사원 출력

SELECT employee_id "사번", salary "급여"

FROM employees

WHERE salary > (SELECT AVG(salary)

                FROM employees)

--'Sales'부서의 평균급여보다 많은 급여를 받는 사원들을 출력하시오.

SELECT employee_id "사원번호", first_name "이름"

FROM employees

WHERE salary > (SELECT AVG(salary)

                FROM employees

                WHERE department_id = (SELECT department_id

                                       FROM departments

                                       WHERE department_name = 'Sales'))

--'Sales'부서의 평균급여보다 많은 급여를 받는 'Sales' 사원들을 출력하시오. <- JOIN 활용

SELECT employee_id "사원번호", first_name "이름"

FROM employees e JOIN departments d ON e.department_id = d.department_id

WHERE d.department_name = 'Sales' AND salary > (SELECT AVG(salary)

                                                FROM employees

                                                WHERE department_id = (SELECT department_id

                                                                       FROM departments

                                                                       WHERE department_name = 'Sales'))

--상호연관 Subquery로 변경가능

  일반 Subquery : Subquery수행후 Mainquery수행

  상호연관Subquery : mainquery수행결과를 Subquery에 반영수행 결과를 Mainquery에 반영  <- 사용안하는게 좋음 퍼포먼스가 떨어짐 어쩔수 없이 써야할때 쓰도록

SELECT employee_id "사원번호", first_name "이름"

FROM employees e JOIN departments d ON e.department_id = d.department_id

WHERE d.department_name = 'Sales' AND salary > (SELECT AVG(salary)

                                                FROM employees

                                                WHERE department_id = d.department_id)

--Subquery 반환행의 종류

  -단일행 : 비교연산자 사용가능

  -다중행 : IN, >ALL, >ANY

--Subquery 반환컬럼의 종류

  -단일컬럼

  -다중컬럼 : main쿼리의 where절 컬럼수와 subquery의 컬럼수를 일치(페어와이징한다고 표현)

--Subquery의 위치별 종류

  -WHERE절   : 일반적 Subquery

  -FROM절    : inline view

  -SELECT절  : scalar subquery

>>다중행 Subquery

--last_name이 S로 시작하는 사원의 부서명을 출력하시오.

SELECT department_id, department_name

FROM departments

WHERE department_id IN (SELECT department_id

                       FROM employees

                       WHERE last_name LIKE 'S%')

>>다중컬럼 서브쿼리<<

부서별 최대급여를 받는 사원의 부서번호, 사번, 급여를 출력하시오

SELECT department_id, employee_id, salary

FROM employees

WHERE (department_id, salary) IN (SELECT department_id, MAX(salary)

                                  FROM employees

                                  GROUP BY department_id)

ORDER BY department_id, salary

>>FROM절에서 사용되는 subquery : inline view <<

ROWNUM컬럼 : 의사컬럼, 행번호값을 갖음. 1부터 시작

SELECT rownum, employee_id, salary

FROM employees

SELECT rownum, employee_id, salary

FROM employees

WHERE salary BETWEEN 5000 AND 10000

SELECT rownum, employee_id, salary

FROM employees

ORDER BY salary DESC

1)

SELECT rownum, a.*

FROM (SELECT employee_id, salary

      FROM employees

      ORDER BY salary DESC) a

WHERE rownum BETWEEN 11 AND 20

2)

SELECT *

FROM (SELECT rownum rn, a.*

      FROM (SELECT employee_id, salary

            FROM employees

            ORDER BY salary DESC) a

      ) b

WHERE rn BETWEEN 21 AND 30

--Smith사원의 입사일, 부서명을 출력하시오.

SELECT e.hire_date 입사일,

       (SELECT department_name

        FROM departments d

        WHERE d.department_id = e.department_id) 부서명

FROM employees e

WHERE last_name = 'Smith'

-------------------------------------테이블 구조 생성, 변경 및 삭제 하는 DDL(Data Definition Language)

>>D D L<<

객체 생성 - CREATE

객체 구조변경 - ALTER

객체 제거 - DROP

---------------------

--테이블 생성

CREATE TABLE a (

  c1 number default 0,

  c2 varchar2(10),

  c3 date)

DESC a;

---------------------

--테이블 제거

DROP TABLE a

---------------------

--테이블 구조변경

--컬럼추가

ALTER TABLE a

ADD c4 char(1)

---------------------

--컬럼 이름변경

ALTER TABLE a

RENAME COLUMN c4 TO c41

DESC a;

--컬럼 제거

ALTER TABLE a

DROP COLUMN c41

--컬럼 자료형변환 또는 자리수 변환

ALTER TABLE a

MODIFY c2 VARCHAR2(20)

-----------------------------

>>Dictionary View <<

DESC USER_TABLES

SELECT * FROM user_tables

SELECT * FROM user_sequences

SELECT * FROM user_indexes

--고객테이블--  Customer

아이디           id           varchar2(20)

비밀번호         password     varchar2(20)

이름             name         varchar2(20)

전화번호         phone_no     varchar2(20)

우편번호         zip_code     char(6)

주소             address      varchar2(50)

성별             gendar       char(1) default 'M'

생년월일         birth_date   varchar2(8)

가입일자         reg_date     date default SYSDATE

CREATE TABLE Customer(

  id           varchar2(20),

  password     varchar2(20),

  name         varchar2(20),

  phone_no     varchar2(20),

  zip_code     char(6),

  address      varchar2(50),

  gendar       char(1) default 'M',

  birth_date   varchar2(8),

  reg_date     date default SYSDATE

)

DESC Customer

>>D M L <<

자료추가 : INSERT

자료수정 : UPDATE

자료삭제 : DELETE

INSERT INTO Customer(id, password, name) VALUES( 'id1', 'p1', 'n1')

INSERT INTO Customer(id, password, name, phone_no) VALUES( 'id2', 'p2', 'n2', '')

INSERT INTO Customer(id, password, name, phone_no) VALUES( 'id3', 'p3', 'n3', NULL)

INSERT INTO Customer VALUES( 'id4', 'p4', 'n4', '', '', '', '', '', '')

SELECT *

FROM Customer