210913(월) DB 5일차 - inner, self, cross, nonequi 조인 / 집합연산자

RDBMS

210913(월) DB 5일차 - inner, self, cross, nonequi 조인 / 집합연산자

구름이팡팡 2021. 9. 13. 10:39

728x90

ex9) inner join : 두 개의 컬럼이 일치 하는 경우 => using

부서ID와 매니저ID가 같은 사원을 연결 하시오 → 32 레코드

(관련 테이블 : departments, employees)

last_name department_id manager_id

방법1(오라클 전용 구문)

select e.last_name, d.department_id, d.manager_id

from employees e, departments d

where e.department_id=d.department_id and e.manager_id=d.manager_id;

방법2(Ansi표준)

select last_name, department_id, manager_id

from employees

inner join departments using(department_id, manager_id);

ex10) 내용은 같은데 컬럼명이 다른 경우에 조인으로 연결하기

departments(location_id), locations2(loc_id)

- 테이블 복사: create table locations2 as select * from locations;

select * from locations2;

- 컬럼명 변경: location_id -> loc_id

alter table locations2 rename column location_id to loc_id;

=> 내용은 같지만 컬럼명이 달라진다! : on 사용!!! using 아니다~!

cf) using은 컬럼명 같을때!

방법1(오라클 전용 구문)

select d.department_id, l.city

from departments d, locations2 l

where d.location_id=l.loc_id;

방법2(Ansi 표준)

select department_id, city

from departments

join locations2 on(location_id=loc_id);

방법3(Ansi 표준)

select department_id, city

from departments d

join locations2 l on(d.location_id=l.loc_id);

ex11) self 조인 : 자기 자신의 테이블과 조인하는 경우 사원과 관리자를 연결하시오

(셀프조인은 잘 안씀)

사원번호 사원이름 관리자

----------------------------------

101 Kochhar King

EMPLOYEES EMPLOYEES

-------------------------------------------------------------------

employee_id, last_name(사원이름) last_name(관리자)

조건 employee_id = manager_id

select employee_id, manager_id, last_name from employees; → e

select employee_id, last_name from employees; → m

방법1

select e.employee_id as 사원번호,

e.last_name as 사원이름,

m.last_name as 관리자

from employees e, employees m

where m.employee_id=e.manager_id;

방법2

select e.employee_id as 사원번호,

e.last_name as 사원이름,

m.last_name as 관리자

from employees e

join employees m on(m.employee_id=e.manager_id);

ex12) cross join : 모든 행에 대해 발생 가능한 모든 조합을 생성하는 조인 => 조인숫자가 기하급수적으로 많다

(웬만하면 안쓰는게 낫다)

select * from countries, locations; → 575레코드

select * from countries cross join locations;

ex13) Non Equijoin (넌 이큐조인)

컬럼값이 같은 경우가 아닌 범위에 속하는지 여부를 확인 할 때

on ( 컬럼명 between 컬럼명1 and 컬럼명2)

create table salgrade(

salvel varchar2(2),

lowst number,

highst number);

insert into salgrade values('A', 20000, 29999);

insert into salgrade values('B', 10000, 19999);

insert into salgrade values('C', 0, 9999);

commit;

select * from salgrade;

select last_name, salary, salvel

from employees

join salgrade on(salary between lowst and highst)

order by salary desc;

<[SET operator] - 집합연산자>

: 두개 이상의 쿼리결과를 하나로 결합시키는 연산자

1. UNION : 양쪽쿼리를 모두 포함(중복 결과는 1번만 포함) → 합집합

2. UNION ALL : 양쪽쿼리를 모두 포함(중복 결과도 모두 포함)

3. INTERSECT : 양쪽쿼리 결과에 모두 포함되는 행만 표현 → 교집합

4. MINUS : 쿼리1결과에 포함되고 쿼리2결과에는 포함되지 않는 행만 표현 → 차집합

=> 2번빼고는 첫번째열 기준으로 자동 sort

오라클의 집합연산자(SET operator) UNION, INTERSECT, MINUS 는 order by 한다

→ 컬럼이 많으면 order by 하므로 느려진다.

수가 작은 튜플로 가공 후 사용 하는게 좋다

→ UNION ALL 는 order by 하지 않고 무조건 합해준다

* Order by를 하려면 반드시 두번째 쿼리문장에 작성해야 한다

create table employees_role as select * from employees where 1=0;

← 테이블 구조만 복사: 내용x, 컬럼명만

select * from employees_role;

insert into employees_role values(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(101, 'Nee', 'Ko', 'NKOCHHAR', '515.123.4568', '1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(200, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(200, 'Nee', 'Kochhar', 'NKOCHHAR', '515.123.4568', '1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(300, 'GilDong', 'Conan', 'CONAN', '010-123-4567', '2009-03-01', 'IT_PROG', 23000.00, NULL, 100, 90);

ex1) union

employee_id, last_name이 같을 경우 중복제거 하시오 → 110 레코드(개)

select employee_id, last_name from employees

union

select employee_id, last_name from employees_role;

=> 테이블1 + 테이블2에 있는게 테이블1에 있다면 안센다

ex2) union all

employee_id, last_name이 같을 경우 중복을 허용 하시오 → 113 레코드

select employee_id, last_name from employees

union all

select employee_id, last_name from employees_role;

select salary from employees where department_id=10

union all

select salary from employees where department_id=30 order by 1;

ex3) minus -> 차집합

employees_role과 중복되는 레코드는 제거하고 employees에만 있는 사원명단을 구하시오 (단, employee_id, last_name만 표시) → 106 레코드

select employee_id, last_name from employees

minus

select employee_id, last_name from employees_role;

ex4) intersect -> 교집합

employees와 employees_role에서 중복되는 레코드의 사원명단을 구하시오

(단, employee_id, last_name만 표시) → 1 레코드

select employee_id, last_name from employees

intersect

select employee_id, last_name from employees_role;

[문제1] employees와 employees_role에서 레코드의 사원명단을 구하시오

조건1) 사원이름, 업무ID, 부서ID을 표시하시오

조건2) employees 에서는 부서ID가 10인사원만

employees_role에서는 업무ID가 IT_PROG만 검색

조건3) 중복되는 레코드는 제거

문 1 답 : 각각 구한 후 union

cf) as는 1번 쿼리에만 적용가능!!

select last_name as 사원이름,
job_id as 업무ID,
department_id as 부서ID
from employees
where department_id=10
union
select last_name as 사원이름,
job_id as 업무ID,
department_id as 부서ID
from employees_role where job_id='IT_PROG';

ex5) SET operator과 IN operator관계

job_title이 'Stock Manager' 또는 'Programmer'인 사원들의 사원명과 job_title을 표시하시오

last_name job_title

--------------------------------

Kaufling StockManager

Hunlod Programmer

방법1 (join, in연산자 이용)

select last_name, job_title

from employees

join jobs using(job_id)

where job_title in('Stock Manager', 'Programmer');

방법2 (join, union 이용)

select last_name, job_title

from employees

join jobs using(job_id)

where job_title='Stock Manager'

union

select last_name, job_title

from employees

join jobs using(job_id)

where job_title='Programmer'

order by 2;

ex6) 컬럼명이 다른 경우의 SET operator => 더미컬럼 사용예제

* 쿼리1과 쿼리2의 select 목록은 반드시동일(컬럼개수, 데이터타입)해야 하므로 이를 위해 Dummy Column을 사용할 수 있다

select last_name, employee_id, hire_date

from employees

where department_id=20

union

select department_name, department_id, NULL

from departments

where department_id=20;

[문제1] [MEMBERS] 테이블에서 부서별 급여 평균이 5000 이상인 레코드의 부서명(DEPARTMENT_NAME), 급여(SALARY) 평균을 조회하려고 한다. SQL문을 작성하시오?

문 1 답: select department_name as 부서명,
avg(salary) as 급여
from employees
join departments using(department_id)
group by department_name
having avg(salary)>=5000;

[문제2] [EMPLOYEES] 테이블에서 부서별(DEPARTMENT_ID) 급여의 합계, 평균, 최대값, 최소값, 개수를 구하시오
조건) 평균은 소수 이하 둘째 자리까지 표시하며 반올림한다

문 2 답: select to_char(sum(salary),'L9,999,999'),
round(avg(salary),2),
max(salary), min(salary), count(salary)
from employees
group by department_id;

[문제3]
EMPLOYEES                             DEPARTMENTS
-------------------------------------------------------------------------------------
EMPLOYEE_ID                           DEPARTMENT_ID
LAST_NAME                             DEPARTMENT_NAME
SALARY
DEPARTMENT_ID

EMPLOYEES 테이블과 DEPARTMENTS 테이블을 조인하시오?
조건1) 타이틀은 사원이름, 부서명, 급여으로 출력하시오
조건2) 급여은 ￦25,000 형식으로 하시오

문 3 답: select last_name as 사원이름, department_name as 부서명,to_char(salary, 'L99,999') as 급여
from employees
join departments using(department_id);

cf) L은 원화표시

728x90

저작자표시 비영리 변경금지 (새창열림)