Database #5 SQL : 비등가조인 & 셀프조인 & 그룹함수(다중행함수)

190404

저번 시간 조인에 이어서 비등가조인과 셀프조인,

그리고 그룹함수에 대해서 설명하겠습니다.

---

*주석처리는 설명, 주석이 아닌 행은 소스입니다.

<비등가조인 & 셀프조인 >

-- 비등가조인 실습을 위한 테이블 생성
create table job_grades(
    grade char(1) not null PRIMARY KEY,
    lowest_salary number(7),
    highest_salary number(7)
);
 
drop table job_grades;        -- 테이블 삭제
 
insert into job_grades(grade, lowest_salary, highest_salary) values('A', 1000, 2999);    -- 데이터 입력
insert into job_grades(grade, lowest_salary, highest_salary) values('B', 3000, 5999);
insert into job_grades(grade, lowest_salary, highest_salary) values('C', 6000, 9999);
insert into job_grades(grade, lowest_salary, highest_salary) values('D', 10000, 14999);
insert into job_grades(grade, lowest_salary, highest_salary) values('E', 15000, 24999);
insert into job_grades(grade, lowest_salary, highest_salary) values('F', 25000, 40000);
commit;        -- Database에 영구 저장
 
-- 비등가 조인
-- = 연산자가 아닌 다른 연산자를 사용해서 조인조건을 정의하는 조인
 
-- 사원의 이름, 사원의 급여, 급여등급을 조회하기
-- 등호(=)를 사용하는 것이 아니라, 급여가 최소급여와 최대급여 사이에 해당하는 등급관련행이 조인되어야함.
select *
from EMPLOYEES A, JOB_GRADES B
where A.salary >= B.lowest_salary
and A.salary <= B.highest_salary
order by A.SALARY desc;
 
-- 포괄조인(아우터조인:Outer Join)
-- 포괄조인을 사용하면 조인 조건을 만족하지 않는 행도 조회된다.
-- 포괄 조인 연산자는 (+) 기호다.
 
-- 사원이름, 부서아이디, 부서이름 조회하기
select E.first_name, E.DEPARTMENT_ID, D.department_name
from EMPLOYEES E, DEPARTMENTS D
where E.department_id = D.DEPARTMENT_ID(+);
 
-- 부서아이디, 부서이름, 부서담당자아이디, 부서담당자이름, 부서담당자 직종아이디를 조회하기
select D.department_id, D.department_name, D.MANAGER_ID, E.first_name, E.job_id
from EMPLOYEES E, DEPARTMENTS D
where E.employee_id(+) = D.manager_id;
 
-- 부서아이디, 부서이름, 부서담당자아이디, 부서담당자이름, 그 부서의 주소를 조회하기
select D.department_id, D.department_name, D.manager_id, E.first_name, L.city
from departments D, EMPLOYEES E, LOCATIONS L
where D.MANAGER_ID = E.EMPLOYEE_ID(+)
and D.LOCATION_ID = L.LOCATION_ID
order by D.DEPARTMENT_ID;
 
-- 사원의 이름, 사원의 급여, 급여등급, 소속부서이름을 조회하기(107명이 되어야함)
select E.first_name, E.salary, JG.grade, D.department_name
from EMPLOYEES E, JOB_GRADES JG, DEPARTMENTS D
where E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
and E.salary >= JG.lowest_salary
and E.salary <= JG.highest_salary
order by E.first_name asc;
 
-- 셀프조인
-- 자기 자신의 테이블과 조인을 해야하는 경우 사용된다.
-- 상하관계(재귀관계)가 있는 조직도, 메뉴구성정보, 카테고리구성정보 등이 셀프조인을 통해서
-- 필요한 데이터를 조회할 수 있는 경우다.
-- 같은 테이블을 서로 조인하기 때문에 테이블마다 적절한 역할을 부여해서 조인에 참여시켜야 한다.
 
-- 100번부서에 근무하는 사원의 아이디, 사원의 이름, 그 사원의 상사아이디, 그 사원의 상사이름을 조회하기
select 직원.EMPLOYEE_ID 직원아이디,
       직원.FIRST_NAME 직원이름,
       매니저.EMPLOYEE_ID 매니저아이디,
       매니저.FIRST_NAME 매니저이름
from EMPLOYEES 직원, EMPLOYEES 매니저
where 직원.MANAGER_ID = 매니저.EMPLOYEE_ID
and 직원.DEPARTMENT_ID = 100;
 
-- 90번 부서에 근무하는 직원에게 보고하는 직원들의 아이디, 이름, 급여, 부서명을 조회하기
select 직원.EMPLOYEE_ID 직원아이디,
       직원.FIRST_NAME 직원이름,
       직원.SALARY 급여,
       직원부서.DEPARTMENT_NAME 부서명
from EMPLOYEES 직원, EMPLOYEES 매니저, DEPARTMENTS 직원부서
where 직원.MANAGER_ID = 매니저.EMPLOYEE_ID
and 직원.DEPARTMENT_ID = 직원부서.DEPARTMENT_ID
and 매니저.DEPARTMENT_ID = 90;

<그룹함수 (다중행함수) >

-- 다중행함수 (그룹함수)
-- 그룹함수는 행 집합(행 그룹)에 작용해서 당 하나의 결과를 생성한다.
-- 행 집합은 테이블 전체 행일 수도 있고, 
-- 테이블에서 특정 컬럼의 값이 동일한 것끼리 그룹화된 행일 수도 있다.
-- 그룹함수
-- count(*), count(컬럼명) 행의 갯수             - *일때는 모든 행의 갯수, 컬럼명을 지정하면 null이 아닌 행의 갯수
-- sum(컬럼명)                                           - 합계의 계산, null값은 무시한다.
-- avg(컬럼명)                                        - 평균을 계산, null값은 무시한다.
-- min(컬럼명)                                       - 최소값을 계산, null값은 무시한다.
-- max(컬럼명)                                        - 최대값을 계산, null값은 무시한다.
-- variance(컬럼명)                                  - 분산을 계산, null값은 무시한다.
-- stddv(컬럼명)                                  - 표준편차를 계산, null값은 무시한다.
 
-- count(*)
-- 직원의 수를 조회하기
select count(*)
from EMPLOYEES;
 
--커미션을 받는 직원의 수를 조회하기
select count(*)
from employees
where commission_pct is not null;
 
-- count(컬럼명)은 해당 컬럼의 값이 null이 아닌 행의 갯수만 센다.
select count(commission_pct)
from employees;
 
-- 80번 부서에 근무하는 사원수 조회하기
select count(*)
from EMPLOYEES
where DEPARTMENT_ID = 80;
 
-- 커미션을 받는 사원중에서 급여가 10000달러 이상인 사원의 수 조회하기
select count(*)
from EMPLOYEES
where COMMISSION_PCT is not NULL
and SALARY >= 10000;
 
-- 사원들 중에서 급여등급이 B등급인 사원수 조회하기
select count(*)
from EMPLOYEES 사원, JOB_GRADES 급여등급
where 사원.SALARY >= 급여등급.LOWEST_SALARY
and 사원.salary <= 급여등급.HIGHEST_SALARY
and 급여등급.GRADE = 'B';
 
-- 80번 부서에 소속된 사원들의  급여 총합을 조회하기
select sum(salary)
from employees
where department_id = 80;
 
-- 커미션의 평균을 조회하기
select AVG(commission_pct)
from employees;
 
select sum(commission_pce/35
from EMPLOYEES;
 
-- 최저 급여 조회하기
select min(salary)
from EMPLOYEES;
 
-- sub_query(서브 쿼리)
select first_name
from EMPLOYEES
where salary = (select min(SALARY)
                from EMPLOYEES);

---

비등가조인, 셀프조인과 그룹함수들을 응용한 과제와, 정답입니다.

190404_sql_quiz.txt

190404_과제.sql