2018년 5월 16일

--sequence 생성하기

create sequence c_emp_id2

increment by 1 start with 26

maxvalue 9999999

nocache

nocycle

;

--시퀀스 넘버 올리기 

select c_emp_id2.nextval from dual;

--시퀀스 정보확인

select *

from user_sequences

;

--s_emp테이블에서 이름은 홍길동, 급여는 2000, 나머지는 null을 입력, 사번은 sequence값을 이용

insert into s_emp

(id,name,salary) values(c_emp_id2.nextval,'홍',2000)

;

--추가된 테이블확인

select *

from s_emp;

select rowid, id, name from s_emp;

--index의 생성과 삭제

create index s_emp_idx1 on s_emp(name);

drop index s_emp_idx1;

--view의 생성

create view vw_emp as select * 

from s_emp

where dept_id = 113;

--가지고있는 view확인

select * from user_views;

--DECODE FUNCTION 예제

--s_emp테이블에서 각 사원의 이름과 급여, 급여등급을 나타내시오.

-- 급여가 4000만원 이상이면 A, 3000만원 이상이면 B,

-- 2000만원 이상이면 C, 1000만원 이상이면 D,1000만원 이하이면 E등급

--DECODE를 이용.

select name, salary, trunc(salary/1000),

decode(trunc(salary/1000),0,'E',1,'D',2,'C',3,'B','A') 급여등급 -- Switch

from s_emp

order by 4 asc, 2 desc

;

--CASE를 이용

select name, salary,

case trunc(salary/1000)

 when 0 then 'E'

 when 1 then 'D'

 when 2 then 'C'

 when 3 then 'B'

 else 'A'

 end

from s_emp

;

--PIVOT함수 : 세로로된 데이터를 가로로 돌림

select *

from (select dept_id, title from s_emp)

pivot(

    count(*)

    for title in ('사원','과장','부장','이사','사장')

    )

order by dept_id

;

--PIVOT함수를 사용할 수 없는 경우에는 DECODE함수 사용

--부서에서 직책만큼 카운트 (decode 함수사용)

select dept_id,

count(decode(title, '사원',0)) "사원", --사원이면 0부터 카운트

count(decode(title, '과장',0)) "과장",

count(decode(title, '부장',0)) "부장",

count(decode(title, '이사',0)) "이사",

count(decode(title, '사장',0)) "사장"

from s_emp

group by dept_id

order by dept_id

;

--CORRELATED SubQuery (상관쿼리) 예제

--★ 자신평균급여가 자신의 부서평균급여보다 적은 직원의 이름,급여,부서번호 출력

--1. outer 쿼리가 먼저 생성

--2. sub쿼리를 가져다씀

select name, salary,dept_id

from s_emp outer

where salary < (select avg(salary) from s_emp where dept_id = outer.dept_id);

--부서평균연봉

select dept_id, avg(salary)

from s_emp

group by dept_id

;

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

--과제 그룹별 통계

--1.

select dept_id,title,count(*)

from s_emp

where dept_id in (106,112,113)

group by dept_id,title 

order by 1 asc

;

select dept_id,title,count(*)

from s_emp

where dept_id in (106,112,113)

group by dept_id,title

order by 1 asc

;

select dept_id,title,count(*)

from s_emp

where dept_id in 106

group by dept_id,title

;

--2. ROLLUP함수로 만든 통계

select dept_id, title, count(*)

from s_emp

where dept_id in (106,112,113)

group by rollup(dept_id, title)

order by dept_id

;

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

--Multi Row Comparison Operator -Exists 예제

--본인이 다른 사람의 관리자(manager_id)로 되어 있는 직원의 사번, 이름, 직책, 부서번호를 나타내시오.

select id,name,title,dept_id

from s_emp e

where exists (select id from s_emp where manager_id = e.id)

;

--위 문제를 Exists를 사용하지않고 쿼리작성.

select id, name, title, dept_id

from s_emp e

where id in (select distinct manager_id from s_emp)

;

--distinct를 사용해서 매니저만 가져와서 비교하면된다 ,그러나 Exists 사용한것이 성능이더좋음

select dept_id,title

from s_emp

order by 1 asc

;