04/15 4일차 JOIN, GROUP BY

Lecture 4

JOIN

SELECT * FROM emp,emp; // 모호한 정의
SELECT e1.empno, e2.ename FROM emp e1, emp e1;
SELECT e.empno, d.dname FROM emp e, dept d;
SELECT e.name, d.deptno FROM emp e, dept d;

 

1.Cardition Product

SELECT e.ename, d.deptno FROM emp e, dept d;
SELECT e.ename, d.deptno
FROM emp e, dept d
WHERE e.deptno = d.deptno; // 조인 PK=FK

 

2.ORACLE : OUTER JOIN

SELECT e.ename, d.deptno
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;

OUTER JOIN은 기준을 잘 잡아야 한다. 더 정보가 많은 쪽으로 +를 붙여야 한다. 해당 문법은 오라클에서만 사용가능하다.

 

SELECT e.ename, d.deptno
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);

잘못된 예시이다. SELECT을 잘못뽑았다.

 

3.ANSI : OUTER JOIN

SELECT e.ename, d.deptno
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

SELECT e.ename, d.deptno
FROM dept d LEFT OUTER JOIN emp e ON e.deptno = d.deptno;

 

4.SELF JOIN
엄청 많이 쓰인다. 카테고리 대, 중, 소로 나뉘어져 있을때 검색하기 위해 사용된다. 

SELECT e.empno, e.ename, m.empno as mno, m.ename as mname
FROM emp e, emp m
WHERE e.mgr = m.empno; // ORACLE 문법

SELECT e.empno, e.ename, m.empno as mno, m.ename as mname
FROM emp e JOIN emp m ON e.mgr = m.empno; // ANSI 문법

 

5.ERD

기본키는 점선으로 표시한다. 복합키는 실선으로 표시한다. ... 차이점이 뭘까?

부서테이블에 사원없어도 부서 테이블 만들 수 있다면 ---> 밑에랑 점선으로 나타낸다

부서테이블에 사원없으면 부서 테이블 만들지마 ---> 동일선상과 실선으로 나타낸다.

https://inpa.tistory.com/entry/DB-%F0%9F%93%9A-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%AA%A8%EB%8D%B8%EB%A7%81-1N-%EA%B4%80%EA%B3%84-%F0%9F%93%88-ERD-%EB%8B%A4%EC%9D%B4%EC%96%B4%EA%B7%B8%EB%9E%A8

📋 데이터 모델링 개념 & ERD 다이어그램 작성 💯 총정리

 

 

6. non-Equijoins

은행 회원 조회 같은 곳에 쓰인다.

SELECT e.empno, sg.grade
FROM emp e, salgrade sg
WHERE e.sal >= sg.losal AND e.sal <= sg.hisal;

SELECT e.empno, sg.grade
FROM emp e, salgrade sg
WHERE e.sal BETWEEN sg.losal AND sg.hisal;

 

 

Lecture 5

GROUP BY

 

1. 그룹함수의 종류

-SUM(컬럼) : 총합

-AVG(컬럼) : 평균

-MIN(컬럼) : 최소

-MAX(컬럼) : 최대

-COUNT(컬럼), COUNT(1), COUNT(*) : 갯수

 

반드시 암기사항****

일반컬럼, 그룹함수가 있다면 반드시 GROUP BY에 일반컬럼을 표시해야 한다.

 

10번 부서 봉급 정보 나타내기

SELECT SUM(sal), AVG(sal), MIN(sal), MAX(sal)
FROM emp
WHERE deptno = 10;

 

부서별로 봉급 정보 나타내기

SELECT deptno, ROUND(AVG(sal),1), MAX(sal), MIN(sal), SUM(sal)
FROM emp
GROUP BY deptno
ORDER BY deptno ASC;

 

--ORA-00937: not a single-group group function : GROUP BY를 넣지 않은 경우(매우매우 중요)

SELECT deptno, ROUND(AVG(sal),1), SUM(sal), MIN(sal), MAX(sal)
FROM emp
ORDER BY deptno ASC;

 

각 부서별 평균급여가 2000이상인 경우만 나타내기
00904. 00000 -  "%s: invalid identifier" where은 from에서 오는 테이블을 받는다

SELECT deptno, ROUND(AVG(sal),1) as sal_avg
FROM emp
WHERE sal_avg>=20
GROUP BY deptno;

 

ORA-00934: group function is not allowed here : 그자리에 그룹함수 쓰지마시오
그룹함수는 WHERE절에 못온다.

SELECT deptno, ROUND(AVG(sal),1) as sal_avg
FROM emp
WHERE AVG(sal)>=2000
ORDER BY deptno;

 

HAVING을 사용하여 그룹함수를 필터링한다

SELECT deptno, ROUND(AVG(sal),1) as sal_avg
FROM emp
GROUP BY deptno
HAVING AVG(sal)>=2000;

 

COUNT
그룹함수는 null은 세지 않는다.
COUNT(1)이 COUNT(*)보다 빠르다. 이유는 레코딩 고유 카운팅을 하였다. rowid를 셌다.

SELECT COUNT(comm), COUNT(1), COUNT(*) FROM emp;
SELECT * FROM emp WHERE comm IS NULL;
SELECT rowid,rownum,empno,ename FROM emp;

 

NVL(컬럼,널값 대체값) --> 매우매우매우중요

SELECT NVL(comm,999999) FROM emp;
SELECT AVG(NVL(comm,999999)) FROM emp;

 

직업별 평균급여, 단 이때 직업은 세일즈, 클럭만
WHERE, HAVING 절 중에서 WHERE절 쓰는게 좋다. HAVING은 전체 데이터를 부르고 필요한 데이터만 남기는 것이고, WHERE절은 필요한 데이터만 부른다. WHERE절이 효율적이다.

SELECT job, ROUND(AVG(sal),1) as savg
FROM emp
GROUP BY job
HAVING job IN ('CLARK','SALESMAN');

SELECT job, ROUND(AVG(sal),1) as savg
FROM emp
WHERE job IN ('CLARK','SALESMAN')
GROUP BY job;

 

각 부서별 평균 급여 중 최대 평균 급여를 구하시오
ORA-00937: not a single-group group function // 서브쿼리에 필요성, deptno:3개 max(avg(sal)) : 1개 갯수가 달라 나오지 않는다.

SELECT deptno, MAX(AVG(sal))
FROM emp
GROUP BY deptno; //err

SELECT deptno, MAX(AVG(sal))
FROM emp
GROUP BY deptno
HAVING AVG(sal) IN 2916 // 서브쿼리 중간과정

SELECT deptno, MAX(AVG(sal))
FROM emp
GROUP BY deptno
HAVING AVG(sal) IN (SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno); // 서브쿼리 완성

SELECT MAX(AVG(sal))
FROM emp
GROUP BY deptno;

 

퀴즈

 

DALLAS에서 근무하는 사원의 이름, 직업,부서번호, 부서이름을 출력하라

SELECT e.ename, e.job, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND
	  d.loc = 'DALLAS';

 

직업이 'SALESMAN'인 사원들의 직업과 그 사원이름, 부서 이름을 출력하라

SELECT e.ename, e.job, d.dname
FROM emp e, dept d
WHERE e.deptno = e.deptno AND
	  d.job = 'SALESMAN';

 

부서번호가 10번, 20번인 사원들의 부서번호, 부서이름, 사원이름, 급여를 출력하라 그리고 그 출력된 결과물을 부서번호가 낮은 순으로, 급여가 높은 순으로 정렬하라.

test.txt

0.00MB