포스팅 목차
▒ DATABASE
DBMS - 계층형, 네트워크형, 관계형(RDBMS)
RDBMS(관계형 DBMS) - oracle, mysql, mssql, DB2 (가장 널리 상용화된 모델)
① 데이터 중복 최소화
② 데이터 일관성
③ 보안
④ 공유
- 논리적인 구조, 물리적인 구조 설계가 어렵다.
- 관리가 어려움 (복구, 백업)
- 한쪽 부분에 문제가 생기면 전체에 문제가 발생된다.
==> 이런 부분은 DBA의 업무
▒ DATABASE 연결
[user 들] -----------------------> [Web] ----------------------> [DBserver]
WAS서버(web application server)
[user 들] ----------------------->[application]------------------> [DBserver]
Middle ware 서버 (통신회사, 은행등에서 많이 씀)
cf) 일반유저가 DBserver가 알아들을수 있는 명령어를 사용하기 위해 필요한 프로그램이 필요한데, 이것을 클라이언트 프로그램이라고 함. (sqlplus, isqlplus)
▒ SQL 함수
▶ DDL : DB 논리적 구조, 물리적 구조 명시
▶ DML : DB 검색, 수정, 삭제
▶ DCL : DB 제어
▒ ORACLE 접속및 끊기
# su - oracle
$ sqlplus /nolog (로그인하지 않고 sqlplus를 실행시킴)
sql> conn /as sysdba
sql> startup (오라클 가동 확인) cf) shutdown immediate (오라클 내리기)
sql> create user username
identified by passwd; (숫자+문자)
sql> grant dba to username; (사용자에게 권한 부여)
sql> host
$ cd /oracle/product/10g/bin
$ ./lsnrctl start
$ ./isqlplusctl start
-----------------------------------------------------------------------------
★ 사용자로 접속
SQL> conn
Enter user-name : spy
Enter password : *****
Connected.
SQL> show user
USER is "spy"
SQL> select * from employees
r : 다시 방금 전 sql문 실행
ed : 실행 했던 sql문 보기
-----------------------------------------------------------------------------
★ sqlplus에서 테이블 사이즈 조절
set linesize 200
pagesize 200
테이블의 구조를 보고 맞춰 줌.
SQL> col 컬럼명 format 문자일 경우 (a3)
숫자 일경우 (999 이용)
------------------------------------------------------------------------------
♣ select 문 (DB검색)
- 원본에는 영향을 주지 않는다.
- 컬럼구분은 ,로 구분함.
- 형식 : select 컬럼명 from 테이블명
<Example>
- select * from employees
(*는 모든것을 검색)
- select LAST_NAME,SALARY from employees
(LAST_NAME,SALARY 컬럼 검색)
- select LAST_NAME as a,SALARY as b from employees
(LAST_NAME,SALARY 컬럼이름을 a,b로 바꿔서 검색, as는 생략가능)
- select LAST_NAME ,SALARY+300 from employees
(산술연산을 이용한 검색, SALARY에 300씩 더해서 보여줌)
- select last_name, department_id from employees where department_id=80
(where를 통해 조건을 줄수 있음.)
- select last_name,salary,job_id from employees where job_id='SA_REP' order by salary desc
- select distinct last_name,salary,job_id,department_id from employees where department_id=80
(중복 행은 제거)
- select last_name,salary,job_id,department_id from employees where salary between 5000 and 10000
(salary가 5000~10000 검색)
- select last_name,salary,job_id,department_id from employees where salary in (5000,10000)
(salary가 5000,10000 검색)
- select last_name,salary,job_id,department_id from employees where last_name like '%a%'
(last_name에 a가 들어간 사람 검색)
- select distinct last_name,hire_date from employees where hire_date between '96/12/31' and '98/01/01'
(97년에 입사한 사람 검색)
- select last_name,salary from employees where department_id in(50,80,90) and salary not between 5000 and 10000
(부서ID가 50,80,80이고 salary가 5000~10000이 아닌 것 검색)
- select concat(last_name,first_name),lpad(salary,6,'0') from employees where substr(job_id,4)='REP'
(job_id에 REP가 들어가는 것중에, 이름이 다나오게 하고, 연봉을 6자리로 표현하고, 빈부분은 '0'으로 채움)
cf) desc 테이블명 : 테이블의 구조를 볼수 있음
cf) 단일 비교 연산자 (= > < >= <=)
다중 비교 연산자 ( between A and B , in (A,B), Like 특정문자열)
is null(null값) , not (제외하고 검색)
cf) order by (정렬)
cf) Null 값 : 사용할수 없는 값
Salary * null => null
특정 COLUMN 에 값이 입력되어 있지 않을 때, 그 값을 NULL 이라 부른다.
NULL 값은 0 이나 공백과 같지 않다.
NULL 값이 산술 연산식에 포함되면 그 결과도 NULL 이다.
그러므로 NVL FUNCTION 을 사용하여 NULL 값을 다른 값으로 대체하여야 한다.
NULL 값을 다른 값으로 대체한다.
NVL (number_column, 9)
NVL (date_column, '01-JAN-95')
NVL (character_column, 'ABCDE')
[ 예제 ]
1. S_EMP TABLE 에서 LAST_NAME, COMMISSION 값을 검색하시오.
2. COMMISSION 은 SALARY * COMMISSION_PCT /100 으로 계산하시오.
SELECT LAST_NAME, SALARY * NVL(COMMISSION_PCT,0) /100 COMMISSION
FROM S_EMP ;
cf) select last_name || first_name from employees
select first_name || last_name || ' is a ' || salary from employees
(하나의 컬럼안에 두가지를 같이 나오게 할때, 연결연산자)
-------------------------------------------------------------------------------------------------
▒ 단일행 함수 : 데이터 조작 함수
- 형식 : function_name (arg1, arg2)
arg : 상수,문자열,colum
- lower (소문자)
ex) select lower ('I Love J') from dual
dual(더미테이블:가상의 테이블,테스트용)
- upper (대문자)
- initcap (앞자리만 대문자)
- concat (결합함수)
ex) concat ('World','Hello') => WorldHello
- substr
ex) substr ('WorldHello',1,5) => World (1부터 5까지 추출)
select substr ('dfaadsfa',1,5) from dual => dfaad
- instr
ex) substr ('WorldHello','w') => w가 몇번째 있는지
- length
ex) length ('WorldHello') => 10 (문자의 길이)
- Lpad | rpad
ex) lpad ('salary',10,'*') => salary 데이터를 10자리까지 나타내고 남을 경우 왼쪽에 '*'로 채움
select lpad(salary,10,'*') from employees => ******2500
ex) rpad ('salary',10,'*') => salary 데이터를 10자리까지 나타내고 남을 경우 오른쪽에 '*'로 채움
select lpad(salary,10,'*') from employees => 2500 ******
- trim
ex) trim ('H' from 'Hello')
select trim ('H' from 'Hello') from dual (H를 잘라네네요)
▶ 숫자함수
- Round (반올림)
ex) round (78.923,2) => 78.92 (소수셋째자리에서 반올림)
- trunc (버림)
ex) trunc (78.923,2) => 78.92 (소수셋째자리부터 버림)
- Mod (나머지)
- Width_bucket (구간)
ex) width_bucket (salary,0,100000,10) => 0부터 100000까지를 10등분을 한 salary의 등급표시.
▶ 날짜함수
- sysdate
ex) select sysdate from dual
- add_months (날짜)
select add_months('97/01/01',6) from dual = > 97/07/01
select add_months(sysdate,6) from dual => 07/12/26
- last_day (현재날짜에서 마지막 날짜까지 남은 날)
select last_day('99/02/01') from dual => 99/02/28
select last_day(sysdate)-sysdate from dual => 현재날에 마지막날까지 남은 날수계산
▶ null함수
- nvl (expr,A) : expr가 null이 나오면 A로 바꾸라
- nvl2 (expr,A,B) : expr이 null이 아니면 A로 , null이 아니면 B로
- nullif (expr1,expr2) : expr1과 expr2를 비교한것이 같으면 null을 반환, 다르면 expr1을 반환하라
- coalesce (expr1, expr2, expr3......) : expr1이 null이면 다음 expr2로 또 null이면 expr3으로 null이 아니면 반환
-select last_name,salary 연봉,salary+nvl(salary*COMMISSION_PCT,0) 총연봉 from employees
-select last_name,salary 연봉,nvl2(COMMISSION_PCT,salary+salary*COMMISSION_PCT,salary) 총연봉 from employees
-select first_name,length(first_name),last_name,length(last_name), nullif(length(first_name),length(last_name)) from
employees
▶ 기타함수
- case문
select last_name,department_id,
CASE department_id
when 90 then '임원'
when 60 then '관리부'
when 50 then '자재부'
when 80 then '영업부'
else '신입사원'
end CASE
from employees
- decode 문
select last_name,department_id,
decode(department_id,
when 90 then '임원'
when 60 then '관리부'
when 50 then '자재부'
when 80 then '영업부'
else '신입사원')
from employees
▶ 데이터타입
- number : 숫자
- varchar : 가변형 문자
- char : 고정형 문자
- date : 날짜
① 암시적변환 : 오라클 자동변환
② 명시적 변환 : 수동 변환
- To_char : 다른 데이터타입을 문자형으로 변환
ex) select To_char(sysdate,'YYYY-MON-DAY')
- To_number :다른 데이터타입을 숫자형으로 변환
- To_date : 다른 데이터타입을 날짜형으로 변환
select To_char(sysdate,'YYYY-MON-ddspth-HH:MM:SS') from dual
---------------------------------------------------------------------------------------------
▒ sql쿼리 처리 과정
1. 오라클은 제일 먼저 sql 쿼리문이 맞는지 검사, 틀리면 에러
2. 테이블이 있는지 검사
3. 테이블안에 컬럼이 있는지 확인
4. where문 분석해서 실행(서로 조건과 맞는지 매칭)
---------------------------------------------------------------------------------------------
▒ JOIN
형식 : select colum from tables
employees,departments (테이블들을 하나로 인식)
① 카타시안곱 조인 방식
employees의 각 행이 departments의 각 행과 1:1로 대응해서 조인.
ex) select last_name, department_name from employees, departments
② 등가 조인 방식
각테이블에 기준을 가지고 그 기준에 맞추어 대응시키는 방식.
ex) -select last_name, department_name from employees, departments
where employees.department_id = departments.department_id
- select last_name, department_name,department_id from employees, departments
where employees.department_id = departments.department_id => error
(department_id가 employees에 있는 것인지 departments에 있는 것인지 불명확하기 때문에)
- select last_name, department_name,employees.department_id from employees, departments
where employees.department_id = departments.department_id =>good!
- select last_name, department_name,employees.department_id from employees a, departments b
where a.department_id = b.department_id =>별칭 사용 good!
- select last_name, department_name from employee e,departments d,location l
where e.department_id=l.department_id and d.location_id=l.location_id
(employee ,departments ,location 3개의 테이블을 등가 조인)
③ 비등가 조인 방식
동등하지 않은 연산자를 사용하여 조인.
ex) select e.last_name,e.salary,j.grade_level from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal
(각 연봉별 등급을 표시)
④ 포괄 조인
ex) select last_name,salary from employees e, departments d
where e.department_id=d.department_id(+)
⑤ 자체 조인
ex) - select w.employee_id, w.last_name, w.manager_id, s.last_name from employees w, employees s
where w.manager_id=s.employee_id
- select w.last_name, w.hire_date, s.last_name, s.hire_date from employees w, employees s
where w.manager_id=s.employee_id and w.hire_date < s.hire_date
(관리자보다 먼저 입사한 사원의 이름, 입사일과, 관리자 이름, 입사일을 표시)
⑥ 교차 조인
:카타시안곱 조인 방식과 동일.
ex) - select * from employees cross join departments
⑦ 자연 조인
: 오라클이 알아서 조인함->오라클이 자신이 기준을 찾아서 조인함.
ex) - select * from employees natural join departments
공통된 컬럼,여기선 2개, department_id, manage_id 두개를 기준으로 조인하기 때문에,
department_id와 manage_id 두개가 같은것만 조인하게됨.
⑧ using을 사용한 조인
ex) select * from departments join locations using(location_id)
⑨ on절을 사용한 조인
ex) select * from departments d join locations l on(d.location_id=l.location_id)
ex) select * from departments d
join locations l on d.location_id=l.location_id
join employees e on e.department_id=d.department_id
(테이블 3개를 조인)
⑩ outer join :포괄 조인과 동일
ex) select * from departments d
left outer join locations l
on d.location_id=l.location_id
left (왼쪽) ,right(오른쪽) ,full(전체)
-------------------------------------------------------------------------------------------------
▒ 그룹함수
AVG,SUM,MAX,MIN
ex) select avg(salary), sum(salary), Max(salary),min(salary) from employees
select avg(salary), sum(salary), Max(salary),min(salary) from employees where department_id=80
(조건문이 있을 경우, 조건을 먼저 해석한뒤, 함수를 처리함.)
select department_id, sum(salary) from employees group by department_id 부서별 합계를 나타냄.
- 그룹함수는 null값을 무시함!!!
select avg(nvl(commission_pct,0)) from employees
▒ count 함수
형식 : count (expr)
expr : *, 컬럼
ex ) select count(*) from employees
select count(commission_pct) from employees (null을 무시)
select count(nvl(commission_pct,0)) from employees (null 까지 포함)
---------------------------------------------------------------------------------------------
select department_id, sum(salary)
from employees
group by department_id
having sum(salary) > 10000 (그룹에 대한 조건을 써줄수 있음)
ex) 부서 id가 50,60,80인 사원의 업무별 평균 연봉이 5000보다 큰 것을 출력
select job_id,avg(salary) from employees
where department_id in(50,60,80)
group by job_id
having avg(salary) > 5000
-----------------------------------------------------------------------------------------------
▒ 분석함수
avg
sum (컬럼) over order by 컬럼 : 정렬을 하고 분석
count partition by 컬럼 : 그룹화한 다음에 분석함수 적용
rank
ex) - select employee_id, department_id, salary, first_value(salary) over (order by department_id) "aa" from employees
cf) first_value : 정렬된후 첫번째 컬럼으로 할 것.
- select employee_id, department_id, salary, first_value(salary) over (partiton by department_id) "aa" from employees
그룹화하여 그 부서의 가장 첫번째 값을 반환함.
- select employee_id, department_id, salary, sum(salary) over (group by department_id) "aa" from employees
누적하여 합계가 나옴.
- select employee_id, department_id, salary, sum(salary) over (partition by department_id order by employee_id) "aa"
from employees
- select employee_id, department_id, salary, count(*) over (order by salary) from employees
- select employee_id, department_id, salary, count(*) over (order by salary desc) from employees
- select employee_id, department_id, salary, avg(salary) over (partition by department_id) from employees
부서별로 그룹화하여 그부서별 평균을 냄.
- select employee_id, department_id, salary, rank() over (order by salary desc) from employees (연봉순위)
- select employee_id, department_id, salary, rank() over (partition by department_id order by salary desc)
from employees (부서별 연봉 순위를 보여줌, 동일한 값은 같은 순위로 보여줌)
- select employee_id, department_id, salary, row_number() over (partition by department_id order by salary desc)
from employees (부서별 연봉 순위를 보여줌, 동일한 값도 순위로 보여줌)
cf) dense_rank() : row_number 와 비슷함
------------------------------------------------------------------------------------------------
▒ 서브쿼리
ex) Abel이라는 사원보다 급여를 많이 받는 사원을 찾아보자.
1. Abel의 급여 확인 (서브쿼리-메인쿼리를 이용하기 위해 보조적으로 사용, 메인쿼리보다 서브쿼리가 먼저 실행)
2. salary에서 Abel보다 큰 급여를 받는 사람 검색 (메인쿼리)
select last_name,salary from emplyees where salary > Abel 급여 (select salary from employees where last_name='Abel')
ex) 사원 ID 144와 같은 업무를 하는 사원 연봉및 부서를 표시
select last_name,job_id,department_id,salary
from employees
where job_id=(select job_id from employees where employee_id=144)
ex) Rajs와 같은 업무를 하며 사원 ID 142인 사원보다 연봉이 큰 사원의 연봉, 업무, 부서를 표시
select last_name,salary, job_id,department_id
from employees
where salary > (select salary from employees where employee_id=142) and job_id= (select job_id from employees where last_name='Rajs')
ex) Rajs와 같은 업무를 하며 사원 ID 142인 사원보다 연봉이 큰 사원의 연봉, 업무, 부서이름, 근무지를 표시
select e.last_name,e.salary,e.job_id, d.department_name,l.city
from employees e, departments d, locations l
where e.department_id=d.department_id and d.location_id=l.location_id
and salary > (select salary from employees where employee_id=142) and job_id= (select job_id from employees where last_name='Rajs')
ex) 부서 ID가 80인 사원중 연봉이 가장 작은 사원보다 연봉을 많이 받는 사원의 연봉및 업무를 표시
select last_name,salary,job_id from employees
where salary > (select min(salary) from employees where department_id=80)
ex) 부서 ID가 80인 사원중 연봉이 가장 작은 사원보다 부서ID가 50,60인 사원들 중 연봉을 많이 받는 사원의 연봉및 업무를 표시
select last_name,salary,job_id from employees
where salary > (select min(salary) from employees where department_id=80) and department_id in (50,60)
ex) 회사 내 전체 평균연봉보다 적게 받는 사원
select last_name,salary,job_id, from employees
where salary < (select avg(salary) from employees)
ex) 부서별 평균연봉보다 적게 받는 부서별 사원
select last_name,salary,job_id, from employees
where salary < (select avg(salary) from employees group by department_id)
< : 단일조건 연사자라서 뒤에 서브쿼리가 여러개의 값을 갖기 때문에 error.
-첫번째 방법 :
select * from employees a where a.salary <
(select avg(b.salary) from employees b
where b.department_id = a.department_id)
-두번째 방법:
select * from (select last_name, department_id,
employee_id, salary, avg(salary) over
(partition by department_id )
as salary2 from employees)
where salary < salary2;
<any : 최대값보다 작음
>any : 최소값보다 큼
>all : 최대값보다 큼
<all : 최소값보다 작음
----------------------------------------------------------------------------------------------------------
▒ set 연산자
: select문의 결과를 결합하는 연산자
① union 연산자
A의 결과값↘
중복 -> 1번 출력 (합집합)
B의 결과값↗
ex)
- select employee_id, department_id from employees
union select employee_id , department_id from job_history
업무부서를 바꾼 사람들을 표사, 중복되어 두번 표시됨
- select employee_id, job_id from employees
union select employee_id , job_id from job_history
업무를 바꾼 사람들을 표시, 중복되어 두번 표시됨.
cf) union all : 중복된것도 또 한번 출력함. A결과, B결과 모두 뽑아져 나옴.
- select employee_id, department_id from employees
union all select employee_id , department_id from job_history
② intersect 연산자
A의 결과값↘
중복된 것만 출력 (교집합)
B의 결과값↗
ex) - select employee_id, department_id from employees
intersect select employee_id , department_id from job_history
③ minus 연산자
A의 결과값 - B의 결과값 (차집합)
ex) - select employee_id, department_id from employees
minus select employee_id , department_id from job_history
------------------------------------------------------------------------------------------------------
▒ group by 확장 함수
① rollup
group by rollup (a,b) => (a,b) (a) ( ) 이 3개의 쿼리를 union으로 결합한것과 같은 효과.
group by rollup (a,b,c) => (a,b,c) (a,b) (a) ( )
a,b : colum
ex) - select department_id,avg(salary) from employees group by rollup(department_id)
(department_id로 하나로 묶어서 평균연봉값)
DEPARTMENT_ID AVG(SALARY)
------------- ---------------
10 4400
20 9500
50 3500
60 6400
80 10033.3333
90 19333.3333 -> 부서별 평균
110 10150
7000 -> department_id가 없는 사람의 평균
8775 -> 부서를 다 합한 평균
ex) - select department_id,job_id,avg(salary) from employees group by rollup(department_id,job_id)
부서별, 업무별평균, 부서별평균, 전체평균 이렇게 3개가 나와야 함.
② cube
group by cube (a,b,c) => (a,b,c) (a,b) (a,c) (b,c) (a) (b) (c) ( )
ex) - select department_id, job_id, avg(salary) from employees group by cube (department_id,job_id)
ex) - select job_title, department_name, avg(salary) Avg_salary
from employees e Join departments d on d.department_id=e.department_id Join jobs j on j.job_id=e.job_id
group by cube (department_name,job_title)
| JOB_TITLE | DEPARTMENT_NAME | AVG_SALARY |
|---|---|---|
| 8717.64706 | ||
| Preesident | 24000 | |
| Programmer | 6400 | |
| Stock Clerk | 2925 | |
| Sales Manager | 10500 | |
| Stock Manager | 5800 | |
| Marketing Manager | 13000 | |
| Sales Representative | 9800 | |
| Administration Assistant | 4400 | |
| Marketing Representative | 6000 | |
| Administration Vice President | 17000 | |
| IT | 6400 | |
| Programmer | IT | 6400 |
| Sales | 10033.3333 | |
| Sales Manager | Sales | 10500 |
| Sales Representative | Sales | 9800 |
| Shipping | 3500 | |
| Stock Clerk | Shipping | 2925 |
| Stock Manager | Shipping | 5800 |
| Executive | 19333.3333 | |
| Preesident | Executive | 24000 |
| Administration Vice President | Executive | 17000 |
| Marketing | 9500 | |
| Marketing Manager | Marketing | 13000 |
| Marketing Representative | Marketing | 6000 |
| Administration | 4400 | |
| Administration Assistant | Administration | 4400 |
③ grouping sets 연산
내가 원하는 그룹을 만들어 줌.
group by grouping sets ((a,b), (a)) => (a,b) (a) 의 그룹 결과만 얻을수 있다.
ex) - select job_title, department_name, avg(salary) Avg_salary
from employees e Join departments d on d.department_id=e.department_id Join jobs j on j.job_id=e.job_id
group by grouping sets ((department_name,job_title),(department_name))
| JOB_TITLE | DEPARTMENT_NAME | AVG_SALARY |
|---|---|---|
| Programmer | IT | 6400 |
| IT | 6400 | |
| Sales Manager | Sales | 10500 |
| Sales Representative | Sales | 9800 |
| Sales | 10033.3333 | |
| Stock Clerk | Shipping | 2925 |
| Stock Manager | Shipping | 5800 |
| Shipping | 3500 | |
| Preesident | Executive | 24000 |
| Administration Vice President | Executive | 17000 |
| Executive | 19333.3333 | |
| Marketing Manager | Marketing | 13000 |
| Marketing Representative | Marketing | 6000 |
| Marketing | 9500 | |
| Administration Assistant | Administration | 4400 |
| Administration | 4400 |
④ 계층적 질의
- employee_id과 manager_id 의 관계
(4) (3) (2) (1)
gietz → thggins,whaien → kochhar ┓
Hunold → dehaan ┫ → king
zlotkey ┫
hartstein ┛
ex) - select employee_id, last_name, manager_id from employees order by employee_id
| EMPLOYEE_ID | LAST_NAME | MANAGER_ID |
|---|---|---|
| 100 | King | |
| 101 | Kochhar | 100 |
| 102 | Dehaan | 100 |
| 103 | Hunold | 102 |
| 104 | Ernst | 103 |
| 107 | Lorentz | 103 |
| 124 | Mourgos | 100 |
| 141 | Rajs | 124 |
| 142 | Davies | 124 |
| 143 | Matos | 124 |
| 144 | Vargas | 124 |
| 149 | Zlotkey | 100 |
| 174 | Abel | 149 |
| 176 | Taylor | 149 |
| 178 | Grant | 149 |
| 200 | Whaien | 101 |
| 201 | Hartstein | 100 |
| 202 | Fay | 201 |
| 205 | Higgins | 101 |
| 206 | Gietz | 205 |
- select employee_id, last_name, manager_id, prior last_name
from employees start with employee_id=100 connect by prior employee_id=manager_id;
부모 자식
| EMPLOYEE_ID | LAST_NAME | MANAGER_ID | PRIORLAST_NAME |
|---|---|---|---|
| 100 | King | ||
| 101 | Kochhar | 100 | King |
| 200 | Whaien | 101 | Kochhar |
| 205 | Higgins | 101 | Kochhar |
| 206 | Gietz | 205 | Higgins |
| 102 | Dehaan | 100 | King |
| 103 | Hunold | 102 | Dehaan |
| 104 | Ernst | 103 | Hunold |
| 107 | Lorentz | 103 | Hunold |
| 124 | Mourgos | 100 | King |
| 141 | Rajs | 124 | Mourgos |
| 142 | Davies | 124 | Mourgos |
| 143 | Matos | 124 | Mourgos |
| 144 | Vargas | 124 | Mourgos |
| 149 | Zlotkey | 100 | King |
| 174 | Abel | 149 | Zlotkey |
| 176 | Taylor | 149 | Zlotkey |
| 178 | Grant | 149 | Zlotkey |
| 201 | Hartstein | 100 | King |
| 202 | Fay | 201 | Hartstein |
- select w.employee_id, lpad(last_name,length(last_name)+(level*2-2),'*') from employees
start with last_name='KING' connect by prior employee_id=manager_id
| EMPLOYEE_ID | LPAD(LAST_NAME,LENGTH(LAST_NAME)+(LEVEL*2-2),'*') |
|---|---|
| 100 | King |
| 101 | **Kochhar |
| 200 | ****Whaien |
| 205 | ****Higgins |
| 206 | ******Gietz |
| 102 | **Dehaan |
| 103 | ****Hunold |
| 104 | ******Ernst |
| 107 | ******Lorentz |
| 124 | **Mourgos |
| 141 | ****Rajs |
| 142 | ****Davies |
| 143 | ****Matos |
| 144 | ****Vargas |
| 149 | **Zlotkey |
| 174 | ****Abel |
| 176 | ****Taylor |
| 178 | ****Grant |
| 201 | **Hartstein |
| 202 | ****Fay |
- select employee_id, lpad(employee_id,level*3,'*'), sys_connect_by_path(last_name,'/')path
from employees start with last_name='King' connect by prior employee_id=manager_id
| EMPLOYEE_ID | LPAD(EMPLOYEE_ID,LEVEL*3,'*') | PATH |
|---|---|---|
| 100 | 100 | /King |
| 101 | ***101 | /King/Kochhar |
| 200 | ******200 | /King/Kochhar/Whaien |
| 205 | ******205 | /King/Kochhar/Higgins |
| 206 | *********206 | /King/Kochhar/Higgins/Gietz |
| 102 | ***102 | /King/Dehaan |
| 103 | ******103 | /King/Dehaan/Hunold |
| 104 | *********104 | /King/Dehaan/Hunold/Ernst |
| 107 | *********107 | /King/Dehaan/Hunold/Lorentz |
| 124 | ***124 | /King/Mourgos |
| 141 | ******141 | /King/Mourgos/Rajs |
| 142 | ******142 | /King/Mourgos/Davies |
| 143 | ******143 | /King/Mourgos/Matos |
| 144 | ******144 | /King/Mourgos/Vargas |
| 149 | ***149 | /King/Zlotkey |
| 174 | ******174 | /King/Zlotkey/Abel |
| 176 | ******176 | /King/Zlotkey/Taylor |
| 178 | ******178 | /King/Zlotkey/Grant |
| 201 | ***201 | /King/Hartstein |
| 202 | ******202 | /King/Hartstein/Fay |
--------------------------------------------------------------------------------------------------------
▒ 고급 서브 쿼리
① 비교 서브 쿼리 - 데이터의 일관성을 확인할 때 사용
[메인쿼리의 결과] <-- 서로 비교 --> [서브쿼리의 결과]
ex1) select employee_id, manager_id, department_id from employees
where (manager_id, department_id)
in (select manager_id, department_id from employees where employee_id in (174,178))
174 149 80
178 149 X
*서브쿼리에 null이 들어있으면 안나오게 됨.
| EMPLOYEE_ID | MANAGER_ID | DEPARTMENT_ID |
|---|---|---|
| 176 | 149 | 80 |
| 174 | 149 | 80 |
ex2) select employee_id, manager_id, department_id from employees
where (manager_id, department_id)
in (select manager_id, department_id from employees where employee_id in (174,101))
| EMPLOYEE_ID | MANAGER_ID | DEPARTMENT_ID |
|---|---|---|
| 102 | 100 | 90 |
| 101 | 100 | 90 |
| 176 | 149 | 80 |
| 174 | 149 | 80 |
ex3) 쌍비교 서브쿼리
select employee_id, manager_id, department_id from employees
where (manager_id, department_id)
in (select manager_id, department_id from employees where employee_id in (174,101))
and employee_id not in (174,101)
=> 비교하는 자기 자신은 제외하고 나오게 하게 할때.
ex4) 근무지가 토론토에서 일을 하는 사원과 옥스포드에서 일하는 사원의 department_id, location_id 가 같은 사원을 출력
select e.employee_id,e.last_name, d.department_id, l.location_id from employees e, departments d, locations l
where e.department_id=d.department_id and d.location_id=l.location_id and (d.department_id,l.location_id)
in (select d.department_id,l.location_id from departments d, locations l where d.location_id=l.location_id and
l.city in ('Toronto' ,'Oxford'))
| EMPLOYEE_ID | LAST_NAME | CITY | DEPARTMENT_ID | LOCATION_ID |
|---|---|---|---|---|
| 149 | Zlotkey | Oxford | 80 | 2500 |
| 174 | Abel | Oxford | 80 | 2500 |
| 176 | Taylor | Oxford | 80 | 2500 |
| 201 | Hartstein | Toronto | 20 | 1800 |
| 202 | Fay | Toronto | 20 | 1800 |
ex5) 비쌍비교 서브쿼리
select employee_id, manager_id, department_id from employees
where manager_id in (select manager_id from employees where employee_id in (142,101))
and department_id in (select department_id from employees where employee_id in (142,101))
142 (100) , 101 (124) <-----> 142 (90), 101 (50)
manager_id 100번과 124번에 대해 department_id 90 또는 50이 각각 대응되어 모두 출력.
| EMPLOYEE_ID | MANAGER_ID | DEPARTMENT_ID |
|---|---|---|
| 124 | 100 | 50 |
| 101 | 100 | 90 |
| 102 | 100 | 90 |
| 141 | 124 | 50 |
| 142 | 124 | 50 |
| 143 | 124 | 50 |
| 144 | 124 | 50 |
② 인라인 뷰 - 가상의 테이블을 만들어 줌
ex1) select e.last_name, e.department_id, e.salary, b.sal
from employees e, (select department_id, avg(salary) as sal from employees group by department_id) b
where e.salary > b.sal
| LAST_NAME | DEPARTMENT_ID | SALARY | SAL |
|---|---|---|---|
| Lorentz | 60 | 4200 | 3500 |
| Whaien | 10 | 4400 | 3500 |
| Mourgos | 50 | 5800 | 3500 |
| Ernst | 60 | 6000 | 3500 |
| Fay | 20 | 6000 | 3500 |
| Grant | 7000 | 3500 | |
| Gietz | 110 | 8300 | 3500 |
| Taylor | 80 | 8600 | 3500 |
| Hunold | 60 | 9000 | 3500 |
| Zlotkey | 80 | 10500 | 3500 |
| Abel | 80 | 11000 | 3500 |
| Higgins | 110 | 12000 | 3500 |
| Hartstein | 20 | 13000 | 3500 |
| Kochhar | 90 | 17000 | 3500 |
| Dehaan | 90 | 17000 | 3500 |
| King | 90 | 24000 | 3500 |
| Mourgos | 50 | 5800 | 4400 |
| Ernst | 60 | 6000 | 4400 |
| Fay | 20 | 6000 | 4400 |
| Grant | 7000 | 4400 | |
| Gietz | 110 | 8300 | 4400 |
| Taylor | 80 | 8600 | 4400 |
| Hunold | 60 | 9000 | 4400 |
| Zlotkey | 80 | 10500 | 4400 |
| Abel | 80 | 11000 | 4400 |
| Higgins | 110 | 12000 | 4400 |
| Hartstein | 20 | 13000 | 4400 |
| Kochhar | 90 | 17000 | 4400 |
| Dehaan | 90 | 17000 | 4400 |
| King | 90 | 24000 | 4400 |
| Grant | 7000 | 6400 | |
| Gietz | 110 | 8300 | 6400 |
| Taylor | 80 | 8600 | 6400 |
| Hunold | 60 | 9000 | 6400 |
| Zlotkey | 80 | 10500 | 6400 |
| Abel | 80 | 11000 | 6400 |
| Higgins | 110 | 12000 | 6400 |
| Hartstein | 20 | 13000 | 6400 |
| Kochhar | 90 | 17000 | 6400 |
| Dehaan | 90 | 17000 | 6400 |
| King | 90 | 24000 | 6400 |
| Gietz | 110 | 8300 | 7000 |
| Taylor | 80 | 8600 | 7000 |
| Hunold | 60 | 9000 | 7000 |
| Zlotkey | 80 | 10500 | 7000 |
| Abel | 80 | 11000 | 7000 |
| Higgins | 110 | 12000 | 7000 |
| Hartstein | 20 | 13000 | 7000 |
| Kochhar | 90 | 17000 | 7000 |
| Dehaan | 90 | 17000 | 7000 |
| King | 90 | 24000 | 7000 |
| Zlotkey | 80 | 10500 | 9500 |
| Abel | 80 | 11000 | 9500 |
| Higgins | 110 | 12000 | 9500 |
| Hartstein | 20 | 13000 | 9500 |
| Kochhar | 90 | 17000 | 9500 |
| Dehaan | 90 | 17000 | 9500 |
| King | 90 | 24000 | 9500 |
| Zlotkey | 80 | 10500 | 10033.3333 |
| Abel | 80 | 11000 | 10033.3333 |
| Higgins | 110 | 12000 | 10033.3333 |
| Hartstein | 20 | 13000 | 10033.3333 |
| Kochhar | 90 | 17000 | 10033.3333 |
| Dehaan | 90 | 17000 | 10033.3333 |
| King | 90 | 24000 | 10033.3333 |
| Zlotkey | 80 | 10500 | 10150 |
| Abel | 80 | 11000 | 10150 |
| Higgins | 110 | 12000 | 10150 |
| Hartstein | 20 | 13000 | 10150 |
| Kochhar | 90 | 17000 | 10150 |
| Dehaan | 90 | 17000 | 10150 |
| King | 90 | 24000 | 10150 |
| King | 90 | 24000 | 19333.3333 |
ex2) 부서별 평균보다 연봉을 많이 받는 사원
select e.last_name, e.salary, e.department_id, b.sal
from employees e, (select department_id,avg(salary) as sal from
employees group by department_id) b
where e.salary > b.sal and e.department_id=b.department_id;
| LAST_NAME | SALARY | DEPARTMENT_ID | SAL |
|---|---|---|---|
| King | 24000 | 90 | 19333.3333 |
| Hunold | 9000 | 60 | 6400 |
| Mourgos | 5800 | 50 | 3500 |
| Zlotkey | 10500 | 80 | 10033.3333 |
| Abel | 11000 | 80 | 10033.3333 |
| Hartstein | 13000 | 20 | 9500 |
| Higgins | 12000 | 110 | 10150 |
③ 상호관련 서브쿼리
[메인쿼리의 결과]가 [서브쿼리]로 들어가서 조건에 맞는 것을 검색
ex1) select employee_id, department_id, salary from employees a
where salary > (select avg(salary) from a.employees where department_id=a.department_id)
| EMPLOYEE_ID | DEPARTMENT_ID | SALARY |
|---|---|---|
| 100 | 90 | 24000 |
| 103 | 60 | 9000 |
| 124 | 50 | 5800 |
| 149 | 80 | 10500 |
| 174 | 80 | 11000 |
| 201 | 20 | 13000 |
| 205 | 110 | 12000 |
