SQL by Oracle # 5. group by와 join, union & 실습문제

group by와 join을 들어가며...

가장 중요한 것은 원하는 데이터를 어떻게 뽑을 지 미리 그려보는 것이다.
그렇지 않으면 엉뚱한 데이터를 불러오거나
원하는 데이터가 아니어서 쓸모 없게 된다.

특히 join은 여러 테이블을 합쳐 원하는 결과를 출력하는 것이기 때문에
어느 테이블에서 어떤 데이터가 어떻게 필요한지 꼭 생각해보고 접근해 보는 것이 좋다.
아래 쿼리들을 정독해보고 어떤 마인드로 데이터를 불러오는 구나 파악하며 공부해보자

group by 사용 시 주의

1. where절에 그룹함수 사용 불가

select deptno, avg(sal)
  from EMP
 where avg(sal) > 2500 
 -- 그룹 바이보다 먼저 오면 원하는 avg는 group by 연산 전에 수행되므로 허가되지 않는다.
 group by deptno;

그래서 where절 대신 having을 활용하는데 문법 순대로 써주는게 좋다.

 select deptno, avg(sal)
   from EMP
  group by deptno
 having avg(sal) > 2500; -- 정상수행
                         -- sal 평균이 2500보다 높은 10번 부서만 출력된다.

2. where절, having 절에 둘 다 사용 가능한 조건

: where 절에 사용하는 것이 성능상 유리

select deptno, avg(sal)
  from EMP
 where deptno != 10
 group by deptno
-- having deptno != 10; -- 성능상 불리

group by 절에 명시되지 않은 컬럼은
단일컬럼 형태로 select절에 사용할 수 없다
그룹함수 형태로만 가능

select grade, max(height), name
  from STUDENT
 group by grade; -- 불가

select grade, max(height), max(name)
  from STUDENT
  group by grade; -- 가능
  --max(name)은 여기서 아무 의미는 없지만 가능은 하다.

select grade, max(height), name
  from STUDENT
 group by grade, name; -- 이름별로 max 값을 노출하게 됨 (의미 없음)

문제3)

emp 테이블을 이용하여 업무별(JOB), 부서별(DEPTNO)로 그룹화하여
각각의 인원수와 급여평균을 구하고 급여평균이 많은 순으로 정렬하여라.

select deptno, job, count(empno), avg(sal)
  from EMP
 group by deptno, job
 order by avg(sal) desc;

문제4)

EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수,
급여의 합을 출력하여라.

select deptno, count(empno), sum(sal)
  from EMP
 group by deptno
 having count(empno) >4;
-- PK인 empno 컬럼을 count하여 부서인원 측정

문제5)

EMP 테이블에서 업무별 급여의 평균이 3000 이상인 업무에 대해서 \
업무명, 평균 급여, 급여의 합을 구하여라.

select job, avg(sal), sum(sal)
  from EMP
 group by job
 having avg(sal) >= 3000;

집합연산자

: 여러개의 select 결과(집합)에 대한 합집합, 교칩합, 차집합을 연산

1. union / union all : 합집합

예제 )

emp 테이블에서 10번, 20번 부서원을 각각 출력,
집합 연산자를 사용하여 하나의 집합으로 합하여 출력

select deptno, ename
  from EMP
 where deptno = 10
 union
select deptno, ename
  from EMP
 where deptno = 20;

예제)

emp테이블에서 10번, 20번 부서원의 job을 각각 출력,
집합 연산자를 사용하여 하나의 집합으로 합하여 출력

select job
  from EMP
 where deptno = 10
 union
select job
  from EMP
 where deptno = 20; --- job 끼리 중복 된 값은 지워버림

 select job
  from EMP
 where deptno = 10
 union all
select job
  from EMP
 where deptno = 20; -- job 끼리 중복된 값 까지 출력

union(좌) unionall(우)

UNION은 중복된 값을 제거하기 위해 정렬을 수행한다.
UNION ALL은 중복제거 없이 모두 출력하기 때문에 정렬 수행 x
따라서 UNION은 정렬을 시키기 위한 과정을 추가적으로 수행하므로 성능에 악영향
그렇기 때문에 굳이 중복된 값이 없다면 union all을 써주는 것이 바람직하다.

 select comm, sal
   from EMP
  where deptno = 10
 union all
 select SAL, comm 
 -- 대응하는 컬럼과 타입, 순서 모두 일치해야 원하는 결과가 나온다.
   from EMP
  where deptno = 20; 
  -- 타입만 일치해도 출력은 됨. 그러나 서로 다른 컬럼의 데이터가 섞인다.

비정상적인 결과 출력

2. interest : 교집합

 select job
   from EMP
  where deptno = 10
 intersect
 select job
   from EMP
  where deptno = 20;
  --교집합이므로 중복된 값은 자연스레 제거

3. minus : 차집합

순서가 중요한 집합 연산자 : 3-5와 5-3은 다른 것과 같다.

 select job
   from EMP
  where deptno = 10
 minus
 select job
   from EMP
  where deptno = 20;

select ename
from scott.emp
union all
select first_name
from hr.employees;

[참고]

:scott계정에서 hr계정의 employees 테이블
조회 가능하도록 작업(systemo 계정에서 아래 명령어 수행)

create public synonym employees for hr.EMPLOYEES; -- hr만의 employees 테이블을 공용으로 쓰게함
grant select on hr.EMPLOYEES to scott; --
-- scott에게 hr의 employees 테이블 권한을 부여하는 작업.

select * from employees; -- 가능해진다.

연습문제)

emp 테이블과 employees 테이블을 합쳐
사원번호, 이름, job, sal 정보를 출력하여라
employees에서 사원번호는 employees_id,
이름은 first_name, job은 job_id, sal은 salary 사용
단, 사원번호는 모두 4자리로 통일하도록 한다.

select to_char(empno), ename, job, SAL
  from emp
 union all
select lpad(employee_id,4,0), first_name, job_id, salary
  from employees;

조인

: 서로 다른 테이블에 분산되어 있는 정보를 하나로 다시 합치는 경우
(특정 컬럼에 대한 추가 정보를 타테이블에서 참조)
조인의 방법에는 오라클 표준과 ANSI 표준 두가지가 있다.
참조할 정보를 알려주지 않으면 각 테이블에 있는 정보를 다 매칭 시켜버린다.
----> Cartesian product가 발생했다고 표현
emp 14 X dept 4 = 56건 보여줌

select *
from emp e join DEPT d
  on e.deptno = d.DEPTNo;
# 여기서 deptno가 참조 정보

연습문제) Student와 Department 테이블을 사용하여
학생번호, 이름, 학년, 학과 이름 출력

select studno, name, grade, dname
  from student s inner join department d
    on s.deptno1 = d.deptno;

연습문제) student와 exam_01을 사용하여
학생번호, 이름, 학년, 시험성적을 출력

select s.studno, name, grade, total 
  from student s inner join exam_01 e
    on s.studno = e.studno;

select * 
  from exam_01;
# 를 활용하여 테이블 간 join 시켜야 할 정보를 확인한다.

예제 ) 위 정보를 활용하여 학년별 시험성적 평균 출력

select grade, avg(total)
  from student s inner join exam_01 E
    on s.studno = e.studno
 group by s.grade;  -- 선 조인 후 그룹 바이 연산자에 의해 그루핑 수행

연습문제 ) emp 테이블에서 부서별 평균 연봉을 구하고
부서명, 평균 연봉 형태로 출력

select d.deptno, dname, d.loc, round(avg(sal)) 평균
  from emp e inner join dept d
    on e.deptno = d.deptno
 group by d.deptno, dname, d.LOC;

연습문제 ) student 테이블과 exm_01 테이블 그리고 department 테이블을 이용하여
학번, 이름, 학년, 시험성적, 학과명을 출력

select s.studno, name, grade, total, dname
  from student s join exam_01 e
    on s.studno = e.STUDNo
  join department d on s.deptno1 = d.deptno;

gogak과 gift 테이블을 활용한 non-equi join 연습

select * from gogak;
select * from gift;

non - equi join : 동치가 아닌 범위 설정을 조건으로 주는 것

연습문제 : 고객이 가져갈 수 있는 모든 종류의 상품을 출력

select gg.gname, point, gf.gname
  from gift gf join gogak gg
    on point >= G_start
  order by gg.gname, g_end;
# 고객의 포인트가 g_start 이상인 경우만 join하여 출력

연습문제 ) Gogak 과 Gift 테이블을 이용하여
이벤트시 준비해야 할 각 상품의 최대 수량 출력

select gf.gname, count(gf.gname)
  from gift gf join gogak gg
    on point >= G_start
 group by gf.gname;

연습문제) student, exam_01, Hakjum 테이블을 사용하여
각 학생의 이름, 학년, 시험점수, 학점 출력

select *
from student;
select name, s.grade, total, h.grade
  from exam_01 e join student s
    on e.studno = s.STUDNO
  join Hakjum h
    on e.total between min_point and max_point;

예제) student, exam_01, Hakjum 테이블을 사용하여
각 학점별 학생 수, 평균 점수, 최대점수, 최소점수 출력

select substr(h.grade, 1, 1) 학점,
       count(s.studno) 학생수,
       avg(total) 평균점수,
       max(total) 최대점수,
       min(total) 최소점수
  from exam_01 e join student S
    on e.studno = s.STUDNO
  join hakjum h on e.total between min_point and max_point
 group by substr(h.grade, 1, 1)
 order by 학점 asc;

실습문제

1. emp2 테이블과 p_grade 테이블을 조회하여 사원의 이름과 직급, 현재 연봉,

--해당 직급의 연봉의 하한금액과 상한 금액을 출력하세요.

select name, e.position, pay, s_pay, e_pay
  from emp2 e join P_GRADE p
    on e.position = p.position;
select *
  from p_grade;

2. 1전공(deptno1)이 101번인 학생들의 학생 이름과 지도교수 이름을 출력하세요.

   select deptno1, s.name, p.name
   from student s join professor p
    on s.profno = p.profno
   where deptno1 = 101;

3. emp2 테이블과 p_grade 테이블을 조회하여 사원들의 이름과 나이, 현재직급,
   예상직급을 출력하세요. 예상직급은 나이로 계산하며 소수점이하는 생략하세요.

   select e.name, round(months_between(sysdate, e.BIRTHDAY)/12) as 나이,
       nvl(e.position,'사원') as 현재직급,
       p.POSITION       
   from emp2 e left join p_grade p
    on months_between(sysdate, e.birthday)/12 between S_AGE and E_AGE;

   

   left join을 활용해 emp2 테이블의 모든 사원들이 보이게 했다.
   사장의 예상직급이 출력되지 않는 이유는 뭘까?
   그건 바로 사장의 나이가 p_grade 테이블의 범위 밖이라 조인이 불가능하기 때문이다.
   따라서 공백처리로 출력

4. Student 테이블의 jumin 컬럼을 참조하여 학생들의
   이름과 태어난 달, 그리고 분기를 출력하라.
   태어난 달이 01-03월 은 1/4분기, 04-06월은 2/4 분기,
   07-09 월은 3/4 분기, 10-12월은 4/4 분기로 출력하라.

   select name, substr(jumin, 3, 2) 태어난달
           , case when substr(jumin, 3, 2) between '01' and '03' then '1/4분기'
                  when substr(jumin, 3, 2) between '04' and '06' then '2/4분기'
                  when substr(jumin, 3, 2) between '07' and '09' then '3/4분기'
                  else '4/4분기' end as 분기
   from student;

5. EMP2 테이블에서 출생년도(1960,1970,1980,1990)별로
   평균연봉을 구하라.

   select trunc(to_char(birthday, 'YYYY')/10)*10 출생년도, round(avg(pay)) 평균연봉
    -- -1 하면 정수 1의자리에서 버림 가능
   from emp2
   group by trunc(to_char(birthday, 'YYYY')/10)*10
   order by trunc(to_char(birthday, 'YYYY')/10)*10;

6. emp 테이블에서 인상된 연봉을 기준으로 2000 미만은
   'C', 2000이상 4000미만은 'B', 4000이상은 'A' 등급을
   부여하여 각 직원의 현재 연봉과 함께 출력\

**인상된 연봉 = 기존 연봉 15% 인상 + 보너스(comm)

select ename, sal, case when sal * 1.15 + nvl(comm, 0) < 2000 then 'C' 
                        when sal * 1.15 + nvl(comm, 0) >= 2000 
                         and sal * 1.15 + nvl(comm, 0) < 4000 then 'B'
                        else 'A' end as 등급
from emp;   # case문을 활용한 풀이

select ename, sal, decode(sign(sal * 1.15 + nvl(comm, 0) - 2000), -1, 'C',
                                                                   0, 'B',
                                                                   1, decode(sign(sal * 1.15 + nvl(comm, 0) - 4000), -1, 'B', 'A'))
from emp;     # decode를 활용한 풀이

7. emp 테이블을 사용하여 연봉기준 등급을 아래의 기준에 맞게 표현하세요.
2000미만 'C', 2000이상 3000이하 'B', 3000초과 'A'
decode문으로 작성

select ename, sal, decode(
                   sign(floor((sal/1000)-2)), -1, 'C'
                                             , 0, decode((sal-2000), 0, 'A', 'B')
                                             , 1, decode((sal-3000), 0, 'B', 'A')
                                             ,'A')
  from emp;

  select sal from emp;