Oracle

SQL 이 정확히 뭘까?

[정형 데이터 처리 ]

Structured Query Language - 구조적 ->Table ->행(Raw)과 열(Column)의 집합(Set)

                                                질의어->질문하는 언어

 

vs

[비(반)정형 데이터 처리]

NoSQL(Not only SQL) 

============================================================================================

SQL Statment

 

(DML)

insert   -> 새로운 행을 입력 -> 새로운 행을 삭제하는 undo가 저장 

update -> 100(old)을 200(new)으로 변경 -> commit? rollback(undo ->실행취소)? 

delete  ->  특정 행(old = undo)을 삭제 ->

 

(DDL)  - auto commit

 

select * from cp_emp;

drop table cp_emp;

rollback; -- DDL은 auto commit이라 해도 안됨



select * from user_recyclebin;



select * from ~~~~;

flashback table cp_emp to before drop; -- 이렇게 rollback 이 가능함

 

 

 

oracle에서 데이터 ERD 테이블 모델링 하는법

들어가서 다음 다음 전체선택하면 다음과 같은 그림이 나옴

 

============================================================================================

계층화 쿼리(Hiearchial Query)

select LPAD(last_name,LENGTH(last_name) + (LEVEL*2)-2,'_') AS org_chart
from employees
start with last_name='King'
connect by prior employee_id = manager_id;

start with 은 꼭짓점, connect by 은 연결 과정이고  prior 가 앞에 있으면 top -down 방식. 뒤에 있으면 bottom-up 방식.

select * from tab;

select * from employees;

select LPAD(last_name,LENGTH(last_name) + (LEVEL*2)-2,'_') AS org_chart
from employees
start with last_name='Kochhar'
connect by employee_id = prior manager_id;

select employee_id
    ,LEVEL lv
    ,RPAD(' ',LEVEL*3-2) || last_name ename
    ,SUBSTR(sys_connect_by_path(last_name,'-'),2) enames
FROM employees
start with manager_id IS NULL --king
connect by PRIOR employee_id = manager_id
order by employee_id;

  

============================================================================================

SQL 기본구문

 

-- 사원정보 테이블로부터 업무별 전체 급여 합계 중 $13000보다 큰 것만 출력하시오
-- 단, 업무에  REP 포함된 것은 제외하고, 전체 급여에 대한 내림차순 정렬을 수행
select job_id,sum(salary)
from employees
where job_id not like '%REPS'
group by job_id
having sum(salary) > 13000
order by sum(salary) DESC;

실행 순서에 맞춰서 코드쓰는 연습을 많이 해보기!!

 

Exam1) HR.employees table의 구조를 확인하고, HR 부서에서사원ID가먼저나타나고이어서 각사원에대한성, 직무ID, 채용날짜및사원ID를표시하는query를요구합니다. HIRE_DATE 열 에대한alias로STARTDATE를입력하십시오.

 

desc HR.employees;

select last_name,job_id, employee_id, hire_date as "startdate"
from HR.employees;

 

Exam2) HR 부서에서EMPLOYEES 테이블의모든고유직무ID를표시하는query를요구합니다.

select DISTINCT department_id
from employees;

 

Exam3) HR 부서에서 보고에 적합하도록 열 머리글의 사원 정보를 쉽게 표시해 달라고 합니다. 열 머리글의 이름을 각각 Emp #, Employee, Job, Hire Date로 지정합니다.

select employee_id as "EMP #", last_name as "Employee",job_id as "job",hire_date as "Hire Date" 
from employees;

 

Exam4) HR 부서에서모든사원과그들의직무ID에대한보고서를요청했습니다. 성과직무ID 를이어서표시하고(쉼표와공백으로구분) 열이름을Employee and Title로지정합니다.

select last_name || '는' || job_id || '업무를 수행합니다' 
from employees;

select '사번' || employee_id || '의 업무는' || job_id || '하고 입사일은' || hire_date || '입니다. 연봉은' || 
to_char(salary*12,'$9999,999.9') || '입니다'
from employees;

 

Exam1) Matos 및 Taylor라는 성을가진사원에대해성, 직무ID, 채용날짜를표시하는보고서를작 성하고, 채용날짜를기준으로오름차순정렬하시오

select last_name,job_id, hire_date
from employees
where last_name IN ('Matos','Taylor')
order by hire_date asc;

 

Exam2) HR부서에서급여가$5,000 ~ $12,000의급여를받고부서20 또는50에속하는사원의성 과급여를출력하시오. Column heading은아래와같이.

select last_name as "Employee", salary as "Monthly Salary"
from HR.employees
where salary between 5000 and 12000
and department_id IN(20,50);

 

기본 함수 사용

 

Trim : 문자 제거

ltrim : 왼쪽의 공백 제거

rtrim : 오른쪽의 공백 제거

 

Exam3) 사원의성과커미션금액을출력. 사원이커미션을받지않으면"No Commission"을표시. 열레이블을COMM으로지정.

select last_name,
NVL(TO_CHAR(commission_pct),'No Commission')COMM
from employees;

 

Exam4) 회사에서 커미션을 받지 않는 사원에게$2,000의 급여 인상을제공하려고한다. 또한, 커미션을받는사원의경우에는기존급여에커미션금액을추가한새급여를출력하시오

select last_name,salary,commission_pct,
COALESCE((salary + (commission_pct * salary)),salary+2000,salary) as "New Salary"
from employees;

 

Exam5) 다음데이터를사용하여DECODE/CASE 함수를통해JOB_ID 열의값을기반으로모든사원 의등급을출력하시오

select job_id,decode(job_id,
'ST_CLERK','E',
'SA_REP','D',
'IT_PROG','C',
'SA_REP','B',
'AD_PRES','A',
'0')GRADE
from employees;

 

Exam4) 관리자번호및해당관리자에속한사원의최저급여를출력. 관리자를알수없는사원 및최저급여가$6,000 미만인그룹은제외시키고, 최저급여에대한내림차순정렬하시오.

select manager_id, MIN(salary)
from employees
where manager_id is not null
group by manager_id
having MIN(salary) >= 6000
order by MIN(salary) DESC;

 

Exam5) 부서별사원수를구하고, 전체사원수가3이하인부서정보만출력하시오. 단, 사원중부 서에소속되어있지않은사원은제외하고, 부서별로오름차순정렬하시오.

select department_id,count(*)
from employees
where department_id is not null
group by department_id
having count(*) <=3
order by department_id;

 

Exam6) 업무별급여합계및평균급여를$표시와함께출력하시오. 단, 80번부서에대한정보만 출력하고, 업무별로오름차순정렬하시오.

select job_id,
TO_CHAR(sum(salary),'$999,999.999')tot_sal,
TO_CHAR(avg(salary),'$999,999.999')avg_sal
from employees
where department_id = 80
group by job_id
order by job_id;

 

============================================================================================

JOIN

 

N개의 테이블 조인시 반드시 N-1 개의 조인 조거을 설정해야 합니다.

=> 조인 조건?

1) Oracle 

ㄴ where a.id = b.id

 

2)표준 SQL

ㄴ on (a.id = b.id)

 

 select a.employee_id , a.last_name, d.job_title,
 b.start_date , b.end_date, c.department_name
 from employees a,
    job_history b,
    departments c,
    jobs d
    
where a.employee_id = b.employee_id
and b.department_id = c.department_id
and b.job_id = d.job_id
and a.employee_id = &empno;

Exam5) 도시이름이'T' 로시작하는지역에사는사원들의사번, LAST_NAME 및부서번호를조회하 시오. 단, join을 피하고subquery로작성하시오

select employee_id,last_name,department_id
from employees
where department_id in( select department_id
                        from departments
                        where location_id in(
                                select location_id
                                from locations
                                where city like 'T%'));

 

 

Exam6) 요일(월,화,수요일...) 별고용된인원이가장많은요일과동일한요일에고용된모든사람 들의LAST_NAME 및요일을조회하시오.