SQL 문제풀기 3

select c_emp_id.currval from dual;
select c_emp_id.nextval from dual;

select c_emp_id.nextval, c_emp_id.currval from dual;

select * from s_emp order by id desc;

insert into s_emp(id, name, salary)
values(c_emp_id.nextval, '홍길동', 2000)
;

create index s_emp_idx
on s_emp(name);

-- Q. s_dept와 region을 join하는 쿼리를 만들고 이 쿼리로 view를 생성하시오.

create view v_dept_region as
select d.id, d.name, r.name region_name from s_dept d, s_region r
where d.region_id = r.id;

select * from v_dept_region; --join 되어있는 상태로 view 생성. 보기편함


-- decode

select name,salary,
decode(trunc(salary/1000), 0, 'E', 1, 'D', 2, 'C', 3, 'B', 'A') 급여등급
from s_emp;

-- case
select id, name,
    case salary
        when 1000 then 'Low'
        when 5000 then 'High'
        else 'Medium'
    end
from s_emp;

select id, name,
    case
        when salary < 1000 then 'Very Low'
        when salary >= 1000 and salary <= 5000 then 'Low'
        when salary > 5000 then 'High'
        else 'Medium'
    end
from s_emp;

-- pivot-> decode변환
select dept_id,
    count(decode(title, '사원', 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;

-- 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;

-- rank
select id, name, salary,
    rank() over(order by salary desc) as rank
from s_emp;

-- correlated subquery
select name, salary, dept_id
from s_emp

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

-- multi row comparison operator
select id, name, title, dept_id
from s_emp e
where exists (select id from s_emp where manager_id = e.id);

select id, name, title, dept_id
from s_emp
where id in (select distinct manager_id from s_emp);

-- pseudo column
select rowid, id, name, salary from s_emp;

select rownum, id, name, salary from s_emp
where rownum <= 5
order by salary desc;
;

select rownum, id, name, salary from (select id, name, salary from s_emp
                                    where rownum <= 5
                                    order by name)
;

select rownum, id, name, salary from s_emp
where rownum <= 5
order by name
;

-- 사원이 들어있지 않는 테이블 id, name, title뽑기
select id, name, title
from s_emp e
where NOT EXISTS (select 'X' from s_emp
where e.title = '사원')
;

-- decode
select name, salary, decode(TRUNC(salary/1000), 0, 'E', 1, 'D', 2, 'C', 3, 'B', 'A') 급여등급
from s_emp;