Equi join : 두개의 테이블에서 공통 컬럼을 기준으로 검색
select count(*)
from employees;
select a.employee_id, a.last_name, a.department_id, b.department_name
from employees a, departments b
where a.department_id = b.department_id;
==> 결과 rows : 19
select employee_id, last_name, department_id
from employees;
==>부서번호가 null인 레코드가 존재한다.
equi join 수행시 join컬럼값이 null인 레코드는 join이 수행되지 못하므로 결과에서 누락된다.
SQL3 (1999) >>
select a.employee_id, a.last_name, a.department_id, b.department_name
from employees a join departments b
on (a.department_id = b.department_name);
desc locations – 컬럼 확인할 때 사용
>> 사원이름, 부서 이름, 부서위치 도시를 검색
select a.last_name, b.department_name, b.location_id, c.city
from employees a, departments b, locations c
where a.department_id = b.department_id and b.location_id = c.location_id;
SQL3 (1999) >>
select a.last_name, b.department_name, b.location_id, c.city
from employees a join departments b
on (a.department_id = b.department_name)
join locations c
on(b.location_id = c.location_id);
Natural Join : oracle server가 두 테이블에서 동일한 이름과 동일한 타입인 컬럼 값들이 일치할 때 조인 수행.
select a.employee_id, a.last_name, a.department_id, b.department_name
from employees a natural join departments b;
è 오류 : 공통 컬럼에 소유자 테이블이나 alias (별명) 선언하지 말 것.
select a.employee_id, a.last_name, department_id, b.department_name
from employees a natural join departments b;
à 결과 12행.
select a.employee_id, a.last_name, a.department_id, b.department_name
from employees a , departments b
where a.department_id = b.department_id
and a.manager_id = b.manager_id;
Join~using : 두 테이블에서 하나의 컬럼으로만 조인할 때 사용할 수 있다.
공통 컬럼에 소유자 테이블이나 alias (별명) 선언하지 말 것.
select a.employee_id, a.last_name, department_id, b.department_name
from employees a join departments b using(department_id); // 결과 19행
non-equi join : 두 테이블에서 공통 컬럼이 없을 때 조인 수행
select a.employee_id, a.last_name, a.salary, b.grade
from employees a, salgrade b
where a.salary between b.losal and b.hisal;
SQL3 (1999)
select a.employee_id, a.last_name, a.salary, b.grade
from employees a join salgrade b
on (a.salary between b.losal and b.hisal); // 결과 39열, 동일
self join : 하나의 테이블에서 컬럼들간에 참조 관계가 없는 경우에는 자신의 테이블을 조인
문제) 사원번호, 사원이름, 관리자 번호, 관리자 이름 검색
TIP) 관리자번호 -> 해당 사원 번호 -> 사원번호에 맞는 이름을 검색해야한다.
select a.employee_id, a.last_name, a.manager_id, b.last_name "manager_name" // 결과를 보일 컬럼 선택.
from employees a, employees b
where a.manager_id = b.employee_id;
SQL3 (1999) 일때
select a.employee_id, a.last_name, a.manager_id, b.last_name "manager_name"
from employees a join employees b
on(a.manager_id = b.employee_id); // 위와 결과 같다.
Outer join : outer 연산자를 이용해서 null 조인 컬럼과 조인할 레코드가 없는 테이블에 null 레코드를 추가해서 조인 수행한다.
문제) 소속 부서가 없는 사원을 포함해서 전체 사원의 사원번호, 이름, 부서번호, 부서이름을 검색결과 생성한다.
TIP) 사원, 부서 테이블에서 사원 부서번호가NULL 하지만 부서테이블에서 부서번호(PK)는 NULL이 아니기에 조인할 수있다.
select a.employee_id, a.last_name, a.department_id, b. department_name
from employees a, departments b
where a.department_id = b.department_id(+); // 결과 20행, null값 포함
SQL3 (1999) 일때
select a.employee_id, a.last_name, a.department_id, b. department_name
from employees a left outer join departments b
on(a.department_id = b.department_id); // 위와 결과 같다. 기준이 되는 테이블만 확인( left, right)
select distinct department_id
from employees;
-- 결과 7
select department_id
from departments;
-- 결과 8, 190이 없음.
문) 부서별로 소속사원 정보를 검색 (단, 소속사원 없는 부서정보로 결과에 포함하시오)
select a.department_id , b. department_name , a.employee_id, a.last_name
from employees a, departments b
where a.department_id (+)= b.department_id
order by 1;
SQL3 (1999)
select a.department_id , b. department_name , a.employee_id, a.last_name
from employees a right outer join departments b
on (a.department_id = b.department_id)
order by 1;
문) 소속부서가 없는 사원을 포함, 소속사원 없는 부서정보로 결과에 포함해서 부서별로 소속사원 정보를 검색
select a.department_id , b. department_name , a.employee_id, a.last_name
from employees a, departments b
where a.department_id (+)= b.department_id(+)
order by 1; à 오류
select a.department_id , b. department_name , a.employee_id, a.last_name
from employees a full outer join departments b
on( a.department_id= b.department_id)
order by 1;
- 집합 연산자
하나 이상의 select문을 수행한 결과 집합에 대해서 단일 결과 집합으로 만드는 것.
union (합집합-교집합): 하나 이상의 select의 결과 집합에서 중복된 데이터(row)를 제외하고 단일 결과 집합으로 리턴
union all (전체): append 방식, 중복된 데이터(row)를 제외하지 않고 단일 결과 집합으로 리턴
minus(A집합-B집합): 첫번째 select의 결과 집합에서 두번째 select의 결과 집합에 속하는 데이터를 제외하고 결과집합을 리턴.
intersect (교집합): 두 select 문의 중복 데이터 결과 집합을 리턴
집합 연산자는
select ~ [union|union all|minus|intersect] select~
각각의 select문에서 컬럼 개수와 컬럼타입은 일치해야한다.
desc job_history à 사원이 입사한 이후에 근무이력(기간, 부서번호, 직무)
desc employees à현재 근무하는 사원의 현재소속부서, 현재 담당 업무
문) 현재 근무하는 사원에 대해서 현재 근무하는 정보, 과거 이력을 검색하시오.
TIP) 과거 근무했던 중복 데이터를 제거한다.
select employee_id, department_id, job_id
from employees
union
select employee_id, department_id, job_id
from job_history
order by 1;
문) 현재 근무하는 사원들중에서 과거에 해당부서에 동일한 직무를 담당했던 사원을 검색하시오
TIP) 현재 근무경력과 과거 근무경력의 공통점, 교집합.
select employee_id, department_id, job_id
from employees
intersect
select employee_id, department_id, job_id
from job_history
order by 1;
문) 현재 담당했던 직무를 과거에도 담당한 적이 있는 사원을 검색
select employee_id, job_id
from employees
intersect
select employee_id, job_id
from job_history;
문) 입사한 이후에 한번도 직무를 변경한 적이 없는 사원을 검색
select employee_id
from employees
minus
select employee_id
from job_history
order by 1;
문) 전체 사원의 월급 평균, 각 부서별 사원의 월급 평균, 각 직무별 사원들의 월급 평균을 단일검색결과로.
select to_char(null), avg(salary)
from employees
union all
select to_char(department_id), avg(salary)
from employees
group by department_id
union all
select job_id, avg(salary)
from employees
group by job_id;
교재 286페이지
1] select a.order_id, a.product_code , b.product_name, a.order_qty
, a.cmoney "주문금액"
from ec_order a, ec_product b
where a.product_code =b.product_code
and a.gubun ='미결';
select a.order_id, product_code , b.product_name, a.order_qty
, a.cmoney
from ec_order a natural join ec_product b
and a.gubun ='미결';
select a.order_id, product_code , b.product_name, a.order_qty
, a.cmoney
from ec_order a natural join ec_product b
where a.gubun ='미결';
select a.order_id, a.product_code , b.product_name, a.order_qty
, a.cmoney
from ec_order a join ec_product b using(product_code)
and a.gubun ='미결';
select a.order_id, a.product_code , b.product_name, a.order_qty
, a.cmoney
from ec_order a join ec_product b using(product_code)
where a.gubun ='미결';
2] select a.name , a.telephone, a.address,
b.product_name "배달상품", c.order_qty "수량"
from ec_member a, ec_product b, ec_order c
where a.userid = c.order_id
and b.product_code = c.product_code
and c.gubun ='결재';
select a.name , a.telephone, a.address,
b.product_name "배달상품", c.order_qty "수량"
from ec_member a join ec_order c
on (a.userid = c.order_id )
join ec_product b
on ( b.product_code = c.product_code )
where c.gubun ='결재';
3] select a.product_code||a.product_name "주문상품"
, b.order_qty , a.unit_price, b.order_id, b.cmondy "주문금액"
from ec_product a , ec_order b
where a.product_code = b.product_code(+)
order by 4 ;
select a.product_code||a.product_name "주문상품"
, b.order_qty , a.unit_price, b.order_id, b.cmondy "주문금액"
from ec_product a left outer join ec_order b
on( a.product_code = b.product_code)
order by 4 ;
4] select product_code, product_name, unit_price, left_qty
from ec_product
where product_code in (select product_code
from ec_product
minus
select product_code
from ec_order) ;
5] select userid
from ec_member
minus
select order_id
from ec_order ;
PDF p262 10번
select b.department_name, b.location_id, count(employee_id), round(avg(salary),2)
from employees a join departments b using(department_id)
group by b.department_name, b.location_id
order by 1;
PDF 314pg
-- 3번
define low_date = '98/01/01'
define high_date = '99/01/01'
select last_name||', '||job_id "employees", hire_date
from employees
where hire_date between '&low_date' and '&high_date'
--4번 치환변수 사용하여 실행시마다 값을 입력받아서 동적으로 실행 결과
select a.last_name, a.job_id, b.department_name
from employees a, departments b
where a.department_id = b.department_id
and b.location_id=&loc;
--5번
select b.department_name, a.last_name, to_char(a.hire_date,'dd-mon-rr'), to_char(a.salary,'$999,999.00'), a.salary*12
from employees a, departments b
where a.department_id = b.department_id
and b.location_id=&loc
order by b.department_name;
select department_name
from departments
minus
select department_name
from departments;
- 서브쿼리
하나이상의 select 들을 수행해서 검색결과를 생성해야 할 때 먼저 수행해야 하는 select 문을 다른 select 문안에 nested 할 수 있다.
Nested select 문을 subquery, Nested select 문의 결과를 받아서 수행하는 select를 main query
Single row subquery는 1 row를 리턴하는 subquery, Multiple row subquery는 하나 이상의 rows를 리턴하는 subquery
where절에 연산자와 함께 사용할 때 in, any, all 연산자와 함께 사용해야 한다.
where절에 연산자와 함께 사용할 때 "=, >, >=, <, <=, !=" 연산자와 함께 사용한다.
**주의점 : where절에 연산자 오른쪽에 ()로 감싸주고 선언해야 한다.
Select의 from절, having절에 subquery 사용할 수 있다.
문) Abel사원보다 급여가 많은 사원의 번호, 이름, 직무, 부서 월급 검색
select employee_id, last_name, job_id, department_id, salary
from employees
where salary > (select salary
from employees
where last_name = 'Abel');
문) 141번 사원과 같은 직무를 담당하는 사원 정보 검색
select employee_id, last_name, job_id, department_id, salary
from employees
where employee_id = (select employee_id
from employees
where employee_id = '141');
문) 141번 사원와 같은 직무를 담당하면서 143번 사원보다 월급을 더 많이 받는 사원 검색
select employee_id, last_name, job_id, department_id, salary
from employees
where job_id = (select job_id
from employees
where employee_id = 141)
and salary > (select salary
from employees
where employee_id = 143);
문] 사원들중에서 최소월급을 받는 사원의 번호, 이름, 월급을 검색
select employee_id, last_name, salary
from employees
where salary > (select min(salary)
from employees
);
문] 각 부서별 최소월급중 50번부서의 최소월급보다 최소 월급이 더 높은 부서 번호와 // 최소 월급 검색
select department_id, min(salary)
from employees
group by department_id
having min(salary) > (select min(salary)
from employees
where department_id = '50');
문] 부서별 평균월급중에서 평균월급이 가장 작은 부서의 번호와 평균월급 검색
select department_id, avg(salary)
from employees
group by department_id
having avg(salary) = (select min(avg(salary))
from employees
group by department_id);
문] 각 부서별로 최소월급을 받은 사원들 검색 in(각 리스트가 같거나 다르거나.. 비교// 각 부서별이기에 in사용 =사용 안한다.)
select last_name, department_id, salary
from employees
where (department_id, salary) in (select department_id, min(salary)
from employees
group by department_id);
문]IT_PROG 직무를 담당하는 사원들중 하나 보다 월급을 많이 받는 사원들을 검색
>4800 or >6000 or >9000 ==> subquery의 여러 결과의 최소값보다 크면 결과 만족
>any 연산자를 사용합니다.
select last_name, job_id, salary
from employees
where salary >any (select salary
from employees
where job_id = 'IT_PROG')
문]모든 IT_PROG 직무를 담당하는 사원들보다 월급을 많이 받는 사원들을 검색
è subquery의 여러결과 모두보다 커야 하므로 (subquery의 여러 결과의 최대값 보다 커야 한다.)
>4800, and >6000, and >9000
select last_name, job_id, salary
from employees
where salary > all (select salary
from employees
where job_id = 'IT_FROG');
문] 관리자인 사원들을 검색
select employee_id, last_name, job_id
from employees
where employee_id in (select manager_id
from employees);
문] 관리자가 아닌 사원들을 검색
select employee_id, last_name, job_id
from employees
where employee_id not in (select manager_id
from employees
where manager_id is not null);
** 주의점multiple row subuery의 결과에 null이 하나라도 포함되어 있으면
main query의 비교연산자가 null을 비교 못하므로 null을 리턴한다.
