데이터베이스 5일차(집합연산자)

natural join : 동일한 컬럼/ 데이터 를 join한다.

using절 : 여러 컬럼이 있을때 특정 컬럼을 join하는것(조건을 주면 안된다)

on 절 : 두 테이블의 컬럼이 동등조건일 경우 값을 리턴

outer join : join에서 제외된 행들까지 표현하고 싶을경우(left / right / full)

self join : 한 테이블에서 join 하는것

 

집합연산자의 규칙

• 두 테이블의 컬럼수가 같아야 한다.
• subquery 컬럼의 이름도 같아야 하고 컬럼의 데이터 타입도 같아야 한다.
• ()를 통해서 실행의 우선순위를 정할 수 있다.
• order by 는 명령의 맨 마지막에 나온다.
• 중복행은 union all 로만 표현할수있다 (나머지는 제거)
• 기본값 오름차순

​

​

• union (합집합)
  • select 컬럼
  • from 테이블
  • union
  • select 컬럼
  • from 테이블
    • 두 컬럼의 이름이 달라도 사용가능(데이터만 같다면)

​

• union (합집합) = 중복된 값까지 출력
  • select 컬럼
  • from 테이블
  • union all
  • select 컬럼
  • from 테이블

​

• intersect (교집합)
  • select 컬럼
  • from 테이블
  • intersect
  • select 컬럼
  • from 테이블

​

• minus(차집합)
  • a (위의 테이블) - b(밑에 테이블)
  • select 컬럼
  • from 테이블
  • minus
  • select 컬럼
  • from 테이블

 

​- select문 일치

location에는 department_name이 없기 때문에 to_char(null)로 문자 타입의 컬럼을 강제로 하나를 만들어서 일치 시켜줘야 한다.

​

• order by
  • 전체에 한번 사용가능
  • 첫번쩨 select 쿼리의 열만 인식한다.
  • 오름차순 정렬

​

=====테이블 생성======

create table retired_employees(

employee_id number primary key,

FIRST_NAME VARCHAR2(20),

LAST_NAME VARCHAR2(25) NOT NULL ,

EMAIL VARCHAR2(25) NOT NULL ,

RETIRED_DATE DATE NOT NULL,

JOB_ID VARCHAR2(10) NOT NULL ,

SALARY NUMBER(8,2) ,

MANAGER_ID NUMBER(6) ,

DEPARTMENT_ID NUMBER(4)

);

​

insert into retired_employees

values(301, 'Rick', 'Dayle', 'RDAYLE', '10/03/18', 'AD_PRES', 8000, 124, 90);

insert into retired_employees

values(302, 'Meena', 'Rac', 'MRAC', '11/09/21', 'AD_VP', 11000, 149, 90);

insert into retired_employees

values(303, 'Mex', 'Haan', 'MHAAN', '10/01/13', 'AD_VP', 9500, 149, 80);

insert into retired_employees

values(304, 'Alexandera', 'Runold', 'ARUNOLD', '11/01/01', 'IT_PROG', 7500, 124, 60);

insert into retired_employees

values(305, 'Bruk', 'Ernst', 'BERNST', '10/05/21', 'IT_PROG', 6000, 149, 60);

insert into retired_employees

values(306, 'Dravid', 'Aust','DAUST', '09/06/25', 'IT_PROG', 4800, 124, 60);

insert into retired_employees

values(307, 'Raj', 'Patil', 'RPATIL', '12/02/05', 'IT_PROG', 4800, 201, 60);

insert into retired_employees

values(308, 'Rahul', 'Bose', 'RBOSE', '12/08/17', 'FI_MGR', 12008, 124, 100);

insert into retired_employees

values(309, 'Dany', 'Fav', 'DFAV', '11/08/16', 'FI_ACCOUNT', 9000, 101, 90);

insert into retired_employees

values(310, 'James', 'Ken', 'JKEN', '10/09/28', 'FI_ACCOUNT', 8200, 101, 90);

insert into retired_employees

values(311, 'Shana', 'Grag', 'SGRAG', '10/09/30', 'FI_ACCOUNT', 7700, 201, 100);

insert into retired_employees

values(312, 'Peter', 'Jois', 'PJOIS', '14/06/07', 'FI_ACCOUNT', 7800, 124, 100);

insert into retired_employees

values(313, 'Lui', 'Pops', 'LPOPS', '10/12/07', 'FI_ACCOUNT', 6900, 201, 100);

insert into retired_employees

values(314, 'Del', 'Raph', 'DRAPH', '12/12/07', 'PU_MAN', 11000, 101, 30);

insert into retired_employees

values(315, 'Alex', 'Khurl', 'AKHURL', '11/05/18', 'PU_CLERK', 3100, 149, 30);

​

COMMIT;

​

===subqurey===

[문제]

1. 평균 급여 이상을 받는 모든 사원의 사원 번호, 성 및 급여를 표시하는 보고서를 작성합니다. 급여의 오름차순으로 결과를 정렬합니다.

select department_id,last_name,salary

from employees

where salary > (select round(avg(salary),0)"AVG" from employees)

​

2. 성에 문자 "u"가 포함된 사원과 같은 부서에 근무하는 모든 사원의 사원 번호와 성을 표시하는 query를 작성합니다.

select department_id,last_name

from employees

where last_name like '%u%'

order by 1

​

3. HR 부서에서 부서 위치 ID가 1700인 모든 사원의 성, 부서 ID 및 작업 ID를 표시하는 보고서를 요구합니다.

select e.last_name,e.department_id,e.job_id,d.location_id

from employees e join departments d

on location_id > 1700

order by 4

​

4. HR을 위해 King에게 보고하는 모든 사원의 성과 급여를 표시하는 보고서를 작성합니다.

​

5. HR을 위해 Executive 부서의 모든 사원에 대해 부서 ID, 성 및 직무 ID를 표시하는 보고서를 작성합니다.

select e.department_id,e.last_name,d.e.job_id,d.department_name

from employees e join departments d

on (e.department_id = d.department_id)

where department_name like 'Executive'

order by 1

​

6. 부서 60의 사원보다 급여가 많은 모든 사원 리스트를 표시하는 보고서를 작성합니다.

select department_id,salary

from employees

where salary > all(select salary from employees where department_id = 60)

order by 1

​

7. 평균보다 많은 급여를 받고 성에 문자 "u"가 포함된 사원이 속한 부서에서 근무하는 모든

사원의 사원 번호, 성 및 급여를 표시하세요.

select department_id,last_name,salary

from employees

where last_name like '%u%'

and salary > (select avg(salary) from employees)

​

8. 성이 Davies의 상사와 같은 상사를 모시는 사원들의 성과 이름을 출력하는 보고서를 작성하세요.

select last_name,first_name

from employees

where manager_id = (select manager_id

from employees

where last_name like 'Davies')

​

9. 성이 Davies가 근무하는 부서와 동일한 부서에서 근무하는 사원들의 성과 부서명을 출력하는 보고서를 출력하세요.

select last_name,department_name

from employees join departments

using (department_id)

where department_name = (select department_name

from departments join employees

using (department_id)

where last_name like 'Davies');

​

10. 성이 Davies의 급여보다 적게 받는 사원의 성과 이름 그리고 급여를 출력하는 보고서를 작성하세요.

select last_name,first_name,salary

from employees

where salary < (select salary

from employees

where last_name like 'Davies')

​

11. 성이 Davies의 급여보다 적게 받고 동일한 직업을 가진 사원들의 사원의 성과 이름 그리고 급여, 직업를 출력하는 보고서를 작성하세요.

select last_name,first_name,salary,department_id

from employees

where department_id = (select department_id

from employees

where last_name like 'Davies')

and

salary < (select salary

from employees

where last_name like 'Davies')

​

​

​

=====오늘꺼_

​

[문제]

1. HR 부서에서 직무 ID ST_CLERK를 포함하지 않는 부서에 대한 부서 ID 리스트를 요구합니다. 집합 연산자를 사용하여 이 보고서를 작성합니다.

select department_id,department_name

from employees join departments

using (department_id)

where department_name <> 'ST_CLERK'

​

2. HR 부서에서 부서가 소재하지 않는 국가의 리스트를 요구합니다. 해당 국가의 국가 ID 및 이름을 표시합니다. 집합 연산자를 사용하여 이 보고서를 작성합니다.

select location_id,city

from locations

minus

select location_id,to_char(null)

from departments

​

3. 부서 50 및 80에 근무하는 모든 사원의 리스트를 생성합니다. 집합 연산자를 사용하여 사원 ID, 직무 ID 및 부서 ID를 표시합니다.

select department_id,to_char(null)"job_id",to_number(null)"employee_id"

from departments

where department_id like 50

or department_id like 80

union

select department_id,job_id,employee_id

from employees

where department_id like 50

or department_id like 80

​

4. 영업 담당자이며 현재 영업 부서에서 근무 중인 모든 사원의 세부 정보를 나열하는 보고서를 생성합니다.

select *

from employees

where department_id = 80

​

5. HR 부서에서 다음과 같은 사양의 보고서를 요구합니다.

-해당 사원이 부서에 소속되었는지 여부에 관계없이 EMPLOYEES 테이블에 있는 모든 사원의 성 및 부서 ID.

-해당 부서에 근무 중인 사원이 있는지 여부에 관계없이 DEPARTMENTS 테이블에 있는 모든 부서의 부서 ID 및 부서 이름.

​