조건부 표현식(if-then-else)

case 식

select last_name, job_id, salary,
case job_id 
when 'IT_PROG' then 1.10*salary
when 'ST_CLERK' then 1.15*salary
when 'SA_REP' then 1.20*salary
else salary
end "REVISED_SALARY"
from employees;

case 표현식

select last_name, salary,
case 
when salary<5000 then 'Low'
when salary<10000 then 'Medium'
when salary<20000 then 'Good'
else 'Excellent'
end qualified_salary
from employees;

decode 함수

select last_name, job_id, salary,
DECODE (job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary) REVISED_SALARY
from employees;

select last_name, salary,
decode (trunc(salary/2000,0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45)  TAX_RATE
from employees
where department_id = 80;

연습문제

1번

select job_id,
case job_id when 'AD_PRES' then 'A'
when 'ST_MAN' then 'B'
when 'IT_PROG' then 'C'
when 'SA_REP' then 'D'
when 'ST_CLERK' then 'E'
else '0' 
end "GRADE"
from employees;

2번

select count(*) total,
count(decode(to_char(hire_date,'YYYY'), 2002, 1, null)) "2002",
count(decode(to_char(hire_date,'YYYY'), 2003, 1, null)) "2003",
count(decode(to_char(hire_date,'YYYY'), 2004, 1, null)) "2004",
count(decode(to_char(hire_date,'YYYY'), 2005, 1, null)) "2005"
from employees;

3번

select job_id "Job",
sum(decode(department_id, 20, salary, null)) "Dept 20",
sum(decode(department_id, 50, salary, null)) "Dept 50",
sum(decode(department_id, 80, salary, null)) "Dept 80",
sum(decode(department_id, 90, salary, null)) "Dept 90",
sum(salary) "Total"
from employees
group by job_id;

<SQL 구문 유형>

1. DQL(데이터질의어) : select
2. DML(데이터조작어) : insert, update, delete
   -> commit, rollback 결정해야함
3. DDL(데이터정의어) : create, alter, drop, truncate
   -> autocommit 내포함
4. DCL(데이터제어어) : grant, revoke
   -> autocommit 내포함

데이터 제어어(DCL)

User 생성

sql plus에서 실습

• sys로 접속하기
  

• test 유저 생성
  

• test 유저 접속
  
  DB 접근 권한 없어서 오류 메시지 뜸

권한 유형

권한 유형	개념	소유자
System 권한	DB를 조작할 수 있는 권한	DBA
Object 권한	Object를 조작할 수 있는 권한	Object 소유자

System 권한

create session
create user
create table
create view
create sequence... 등등

• DB를 조작할 수 있는 권한, System 권한의 종류가 100개 정도 됨.
• 모든 System 권한은 DBA가 소유함.

System 권한 부여

접속 실패해서 현재 접속한 user 없음

• sys로 다시 접속
  

• test user에 DB 접속, 테이블 생성 권한 부여
  

- grant 권한 to 유저

• test user 접속 성공
  

• table 생성 가능
  

- view 생성은 불가능

• index 생성은 가능
  

- create table 권한은 index도 만들수 있는 권한 포함

• 비밀번호 변경
  

- test 접속상태에서 시행

System 권한 회수

- sys로 접속해서 시행
- revoke 권한 from 유저

• test 유저 create table 권한 없어서 생성 불가능
  

Object 권한

hr.employees -> select, insert, update, delete ...
scott.members -> select, insert, update, delete ...

• Object를 조작할 수 있는 권한, Object 권한의 종류 다양함.
• 모든 Object 권한은 Object의 주인이 소유함.

- test 유저로 hr 유저 소유의 employees 테이블 접근 불가능

Object 권한 부여

- hr로 접속하여 권한 부여하기
- grant 권한 on 테이블명 to 유저

• test 유저로 hr 소유 테이블 select 해보기
  
• update 해보기
  

commit;

Object 권한 회수

• hr로 접속해서 update 권한 회수하기
  
  - revoke 권한 on 테이블명 from 유저

연습문제

-- 1번
select last_name, salary
from employees
where salary not between 5000 and 12000;

-- 2번
select last_name, job_id, hire_date
from employees
where last_name in ('Matos', 'Taylor')
order by hire_date;

-- 3번
select last_name, department_id
from employees
where department_id in ( 20, 50)
order by last_name;

-- 4번
select last_name "Employee", salary "Monthly Salary"
from employees
where department_id in (20,50) and salary between 5000 and 12000;