<SQL 활용>
📌 7. 조건부 표현식(if-then-else)
📁 CASE 식
[예제1]
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 표현식
[예제1] (= CASE식[예제1])
select last_name, job_id, salary,
case when job_id='IT_PROG' then 1.1*salary
when job_id='ST_CLERK' then 1.15*salary
when job_id='SA_REP' then 1.2*salary
else salary end "REVISED_SALARY"
from employees;
[예제2]
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 함수
[예제1]
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;
[예제2]
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;
✏️ <연습문제>
- employees 테이블로부터 JOB_ID 값을 기반으로 모든 사원의 등급을 표시하는 query를 작성하시오.
DECODE 함수와 Case 식 두 가지 방법으로 작성하시오.
[Case식]
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;[DECODE함수]
select job_id,
decode(job_id,'AD_PRES', 'A',
'ST_MAN' , 'B',
'IT_PROG', 'C',
'SA_REP', 'D',
'ST_CLERK', 'E',
'0')
GRADE
FROM employees;
- employees 테이블로부터 사원의 총 수와 2002년, 2003년, 2004년 및 2005년에 채용된 사원의 수를 표시하는 query를 작성합니다.
컬럼 제목을 년도로 지정하십시오.
select count(*) TOTAL,
sum(decode(to_char(hire_date,'YYYY'),2002,1,0)) "2002",
sum(decode(to_char(hire_date,'YYYY'),2003,1,0)) "2003",
sum(decode(to_char(hire_date,'YYYY'),2004,1,0)) "2004",
sum(decode(to_char(hire_date,'YYYY'),2005,1,0)) "2005"
from employees;
(==)
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;
(==)
select count(*) TOTAL,
count(decode(to_char(hire_date,'YYYY'),2002,1)) "2002",
count(decode(to_char(hire_date,'YYYY'),2003,1)) "2003",
count(decode(to_char(hire_date,'YYYY'),2004,1)) "2004",
count(decode(to_char(hire_date,'YYYY'),2005,1)) "2005"
from employees; 
- employees 테이블로부터 부서 20, 50, 80 및 90에 대해 job_id, department_id별 해당 직무에 대한 급여 및 해당 직무에 대한 총 급여를 표시하는 구문을 작성하시오.
select job_id Job, sum(decode(department_id, 20, salary)) "Dept20",
sum(decode(department_id, 50, salary)) "Dept50",
sum(decode(department_id, 80, salary)) "Dept80",
sum(decode(department_id, 90, salary)) "Dept90",
sum(salary) "Total"
from employees
group by job_id;
<SQl구문유형>
- DQL(데이터질의어) : select
- DML(데이터조작어) : insert, update, delete
=> commit, rollback 결정해야함. - DDL(데이터정의어) : create, alter, drop, truncate
=> autocommit 내포함 - DCL(데이터제어어) : grant, revoke
=> autocommit 내포함
📌 8. 데이터 제어어(DCL)
📁 1. User 생성
- create user 권한 소유자가 DB에 User를 생성할 수 있음
SQL> conn sys/oracle as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> create user test
2 identified by test01;
User created.
📁 2. 권한 유형
📁 3. System 권한
- DB를 조작할 수 있는 권한, System 권한의 종류가 100개 정도 됨.
- 모든 System 권한은 DBA가 소유함.
SQL> conn sys/oracle as sysdba
Connected.
SQL> show user
USER is "SYS"
1) System 권한 부여
create table -> table과 index를 생성/운영할 수 있는 권한
create view
create sequence
...
SQL> GRANT create session, create table
2 TO test;
Grant succeeded.SQL> conn test/test01
Connected. => 접속 가능 여부 확인!!!
SQL> show user
USER is "TEST"
- 비밀번호 변경
SQL> alter user test
2 identified by busan_123;
User altered.
2) System 권한 회수
SQL> conn sys/oracle as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> revoke create table
2 from test;
Revoke succeeded.
📁 4. Object 권한
- Object를 조작할 수 있는 권한, Object 권한의 종류 다양함.
- 모든 Object 권한은 Object의 주인이 소유함.
SQL> show user
USER is "TEST"
SQL> col last_name format a20
SQL> select employee_id, last_name, salary
2 from hr.employees;
from hr.employees
*
ERROR at line 2:
ORA-00942: table or view does not exist => error! (권한부족)
1) Object 권한 부여
SQL> conn hr/hr
Connected.
SQL> show user
USER is "HR"
SQL> grant select, update
2 on employees
3 to test;
Grant succeeded.
=> 권한부여완료!!
SQL> conn test/busan_123
Connected.
SQL> select employee_id, last_name, salary
2 from hr.employees;
2) Object 권한 회수
SQL> conn hr/hr
Connected.
SQL> revoke update
2 on employees
3 from test;
Revoke succeeded.=> test에게 줬던 update 권한 회수!!
Hello