KTDS_자바&파이썬 웹애플리케이션전문가 양성과정교육_22일차(DB)

자바&파이썬 웹애플리케이션 전문가 양성과정 채용연계형 교육과정 22일차

KT 자바교육 후기 (2021-07-22 ~ 2021-12-03) 

2021-08-23 (참고, 8월 16일 ~ 9월 3일 비대면 주간, 4단계 거리두기로 인하여)

SQL Oracle HAVING, ROLLUP, CUBE, GROUPING SET, GROUP BY, PIVOT, JOIN, ANSI JOIN, 등가조인, 비동기조인, 조인, 외부조인, 자체조인,

---

[오전]

HAVING 절 

 SELECT문에 GROUP BY 절이 존재할 때만 사용 가능

 GROUP BY 절을 통해 그룹화된 결과 값의 범위를 제한하는 데 사용

 GROUP BY 절과 동일하게 별칭 사용 불가

SELECT DEPTNO, JOB, AVG(SAL)
  FROM EMP
  GROUP BY DEPTNO, JOB
  HAVING AVG(SAL) >= 2000
  ORDER BY DEPTNO, JOB;

 

 

 

ROLLUP, CUBE, GROUPING SETS 는 GROUP BY 절에 지정할 수 있는 특수 함수

ROLLUP : 명시한 열에 한 해 결과 출력

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
  FROM EMP
  GROUP BY ROLLUP(DEPTNO, JOB);

 

 

 

CUBE : 지정한 모든 열에서 가능한 조합의 결과를 모두 출력

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
  FROM EMP
  GROUP BY CUBE(DEPTNO, JOB);

 

 

 

GROUPING SETS : 지정한 열을 각각 그룹화하여 결과 값 출력

SELECT DEPTNO, JOB, COUNT(*)
  FROM EMP
  GROUP BY GROUPING SETS(DEPTNO, JOB);

 

 

 

LISTAGG ~ WITHIN GROUP : 가로로 나열할 열을 지정하고, 데이터 사이 넣을 구분자 지정 및 정렬

SELECT DEPTNO, LISTAGG(ENAME, ‘,’) WITHIN GROUP(ORDER BY SAL DESC) AS ENAMES
  FROM EMP
  GROUP BY DEPTNO;

 

 

 

PIVOT : 기존 테이블 행을 열로 바꿈

 11g버전부터 사용되는 함수이고, 이전 버전에서는 DECODE를 활용하여 같은 결과를 출력

SELECT DEPTNO, JOB, MAX(SAL)
  FROM EMP
  GROUP BY DEPTNO, JOB
  ORDER BY DEPTNO, JOB;
  
SELECT *
  FROM (SELECT DEPTNO, JOB, SAL FROM EMP)
  PIVOT(MAX(SAL) FOR DEPTNO IN (10, 20, 30))
  ORDER BY JOB;

 

 

UNPIVOT : PIVOT 함수와 반대 기능

SELECT * 
FROM ( SELECT DEPTNO,
  MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
  MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
  MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
  MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
  MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST"
  FROM EMP
  GROUP BY DEPTNO
  ORDER BY DEPTNO
  )
UNPIVOT(
  SAL FOR JOB IN (CLERK, SALESMAN, PRESIDENT, MANAGER, ANALYST)
  )
ORDER BY DEPTNO, JOB;

 

 

---

아 다 귀찮아지기 시작했어.

[오후]

 

 오후 실습 예제───────────────

1. EMP 테이블을 이용하여 부서번호, 평균급여, 최고급여, 최저급여, 사원수를 출력합니다.

단, 평균급여를 출력할 때 소수점을 제외하고 각 부서번호별로 출력하세요

 

>> DB_Oracle,SQL EMP테이블을 이용한 조건 출력 예제 :: HappyEndingIsMine (tistory.com)

 

 

 

2. 같은 직책에 종사하는 사원이 3명이상인 직책과 인원수를 출력하세요.

 

>> DB_Oracle,SQL EMP테이블을 이용한 조건 출력 예제(2) :: HappyEndingIsMine (tistory.com)

 

 

 

3. 사원들의 입사년도 기준으로 부서별로 몇 명이 입사했는지 출력하세요.

 

>> DB_Oracle,SQL EMP테이블을 이용한 조건 출력 예제(3), ORA-00979: not a GROUP BY expression :: HappyEndingIsMine (tistory.com)

 

 

 

──────────────────────

 

 ++추가 쿼리 공부

select * from subway_statistics

 지하철 정보 DB를 조회한다.

 

 잠실이 들어간 역들을 조회하고 싶다면?

  station_name like '잠실%'

 

 

 

 잠실이 들어간 역들이면서 탑승 시간이 7이거나 9인 데이터를 조회하고 싶다면?

  and (boarding_time = 7 or boarding_time=9)

  and boarding_time in (7, 9)

 

 

 

 구분을 승차한 사람을 보고 싶다면?

  gubun = '승차'

 

 

 

 탑승일(YY/DD/MM)에서 요일까지 출력되기를 원한다면?

  to_char(boarding_date,'DY')as day

 

 

 

 잠실역에서 가장 많은 승객수를 기준으로 정렬되기를 원한다면?

  select * from subway_statistics
    where station_name like '잠실(216)' 
    ORDER BY passenger_number desc

 

 

 

 정렬된 값을 기준으로, 조건을 넣고 싶다면?

  group by ~~

  having sum(~~) between ~~ and ~~

  order by ~~

 

 

 

  ++++++++++

  왜 GROUP BY, ORDER BY 등은 별칭을 인지하지 못할까?

 

  ▶ 일반적으로 쿼리의 순서상 (시스템 처리 순서)

  from -> group by -> having -> order by -> select 으로 진행이 되기 때문이다.

  즉, 별칭을 마지막에 지어주기 때문에, 시스템 상으로 별칭이 아직 없기 때문에. 인지하지 못하기 때문이다.

  쿼리문을 작성할 때 자주 하는 실수가 될 수 있으므로 유의해야만 한다.

 

 

 

---

조인

 두 개 이상의 테이블을 연결하여 하나의 테이블처럼 결과를 출력할 때 사용하는 방식

 FROM 절에 여러 개의 테이블 지정 가능

SELECT * 
FROM EMP, DEPT
ORDER BY EMPNO;

데카르트 곱(카테시안 곱: Cartesian product). cross join 혹은 교차조인이라고 함.

 

 

 

 FROM 절에 명시 된 테이블의 조합 결과에 WHERE 절을 사용해 출력 기준을 지정 

 테이블에 Alias를 지정하여, 테이블 이름을 간결하게 사용

SELECT * 
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO;

 

 

 

등가조인(equi join)

테이블 연결 후 출력 행을 특정 열에 일치한 데이터를 기준으로 선정하는 방식

내부조인(inner join) 혹은 단순 조인(simple join) 이라고도 함

가장 많이 쓰이는 조인 방법 

WHERE절의 조건식을 사용하여 테이블 조인 시 반드시 각 테이블을 연결하는 조건식이 최소한 전체 테이블 수 보 다 하나 적은 수 만큼은 있어야 함

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO;

 

 

 

비등가조인(non-equi join) 

등가 조인 방식 외의 방식 

특정 열이 일치하지 않는 경우 다른 방식으로 사용이 가능

SELECT *
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

 

 

 

자체조인(self join) 

하나의 테이블을 여러 개의 테이블처럼 활용하여 조인하는 방식

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;

 

 

 

외부 조인(outer join) 

조인 기준 열의 어느 한쪽이 NULL이어도 강제 출력하는 방식

왼쪽 외부 조인(Left outer join)	WHERE TABLE1.COL1 = TABLE2.COL1(+)
오른쪽 외부 조인(Right outer join)	WHERE TABLE1.COL1(+) = TABLE2.COL1

SELECT E1.EMPNO, E1.ENAME, E1.MGR, 
	E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO(+)
ORDER BY E1.EMPNO;
---------------------------------------------------
SELECT E1.EMPNO, E1.ENAME, E1.MGR, 
	E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR(+) = E2.EMPNO
ORDER BY E1.EMPNO;

 

 

 

벤 다이어그램으로 표현한 조인

 

 

ANSI JOIN(SQL-99 표준문법 조인) 

DBMS 공통 표준 문법 

JOIN ~ USING

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
  DEPTNO, D.DNAME, D.LOC
FROM EMP E JOIN DEPT D USING (DEPTNO)
WHERE SAL > = 3000
ORDER BY DEPTNO, E.EMPNO;

 

 

 

JOIN ~ ON : 기존 WHERE절 조인 조건식을 ON 옆에 작성

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
	E.DEPTNO, D.DNAME, D.LOC
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE SAL > = 3000
ORDER BY E.DEPTNO, EMPNO;

 

 

 

OUTER JOIN : FROM 절에서 외부 조인 선언 (각각 LEFT, RIGHT, FULL ~ ON 으로 확인할 수 있다.)

SELECT E1.EMPNO, E1.ENAME, E1.MGR, 
	E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1 LEFT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;

SELECT E1.EMPNO, E1.ENAME, E1.MGR, 
	E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1 RIGHT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO, MGR_EMPNO;

SELECT E1.EMPNO, E1.ENAME, E1.MGR, 
	E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1 FULL OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;

 

 

 

OUTER JOIN : FROM 절에서 외부 조인 선언

 *UNION 중복 제거 / UNION ALL 중복제거 X

 

 

---