oracle - GROUP, JOIN, EXERD 다운로드

19. 11. 27. GROUP, JOIN, EXERD 다운로드

데이터베이스 구현

2019-12-02 22:37:20

---

▶ GROUP FUNCTION

-단위를 무엇으로 하냐에 따라 결과가 다르다.

-WHERE 절도 올 수 있음.

-특정 컬럼이나 표현으로 여러 행의 값을 한 행의 결과로 생성.

-FUNCTION : AVG, COUNT, MAX, MIN, SUM 

-GROUP BY 절에 작성된 컬럼 이외의 컬럼은 SELECT 절에 올 수 없다.

   (= GROUP BY 절에 작성되어야만 SELECT 절에 올 수 있다.)

 

SELECT [column, ], group function(column)
FROM table
[GROUP BY column,]
[HAVING group function condition]
[ORDER BY column]

 

--

SELECT MAX(sal), MIN(sal), ROUND(AVG(sal),2), SUM(sal), COUNT(sal), COUNT(*)

FROM emp;

=> 급여의 최대값, 최솟값, 평균 급여의 소수 셋째 자리 반올림, 급여의 합계, 급여의 행의 개수,

   emp테이블의 모든 행의 개수를 조회한다.

   COUNT 의 경우 null이 아닌 값이면 1로 카운트한다.

   * 은 특정컬럼의 건수가 아니라 행의 개수를 나타낸다.

 

--위 쿼리를 emp 테이블의 부서별로 써보자.

SELECT MAX(sal), MIN(sal), ROUND(AVG(sal),2), SUM(sal), COUNT(sal), COUNT(*)

FROM emp

GROUP BY deptno;

 =>부서별 최대급여, 부서별 최소 급여, 부서별 평균 급여의 반올림, 부서별 급여의 합,

   부서별 행의 개수, 부서별 모든 행의 개수.

   deptno 에는 10, 20, 30 총 3개의 값들이 있다. 이것을 각각 그루핑 해서 SELECT절에 오는

   모든 값들은 행이 3개이다. 10, 20, 30 3개니까! 중복된 값을 하나로 묶어 표현하기 때문에

   ename 이나, empno 같은 고유 값을 가지는 컬럼은 GROUP BY 절에 써도, 안 써도 같은 결과.

 

● 상수의 경우, GROUP BY절에 오지 않아도, SELECT 절에 사용 가능하다.

SELECT deptno, 1, 'ㅁ', SYSDATE, MAX(sal)

FROM emp

GROUP BY deptno;

  => 1, 'ㅁ' SYSDATE 는 GROUPBY 절과 상관없이 모든 절에 동일한 값이 들어간다. 

 

--

SELECT SUM (sal), SUM(sal + comm), SUM(sal + NVL(comm,0))

FROM emp;

  => sal+comm 을 하면 원래 나와야 하는 값만큼 나오지 않아. NULL은 연산 시 NULL 이기 때문.

   그래서 sal + NVL(comm,0))  으로 써준 값이 우리가 생각한 sum+comm 이다.

 

● WHERE 절에는 GROUP 함수를 표현할 수 없다.

따라서 HAVING 절을 사용해야 함.

--

SELECT deptno, MAX(sal)

FROM emp

WEHRE MAX(sal) > 3000

GROUP BY deptno;

  => 틀린 표현. 934에러가 난다.

--

SELECT deptno, MAX(sal)

FROM emp

GROUP BY deptno

HAVING MAX(sal)  > 3000;

  => 이게 올바른 표현.

 

 

▶ 테이블의 구조 변경(컬럼추가)과, 추가된 컬럼에 값 update 하기.

*DISTINCT 가 쓰인 쿼리는 잘못 작성됐을 확률이 굉장히 높다. 중복을 제거한다고 함.

 

*

ALTER TABLE emp DROP COLUMN dname;

 => emp 테이블의 dname 컬럼을 삭제했다.

 

*

ALTER TABLE emp ADD (dname VARCHAR2(14));

  => emp테이블에  데이터 타입 VARCHAR2, 데이터 크기 14 인 dname 컬럼을 만들었다.

    DESC 로 emp 와 dept 를 살펴보고 타입과 용량을 맞춰서 생성해야 했다.

 

*

UPDATE emp SET dname =  CASE

                                       WHEN deptno = 10 THEN 'ACCOUNTING'

                                       WHEN deptno = 20 THEN 'RESEARCH'

                                       WHEN deptno = 30 THEN 'SALES'

                                       END;

COMMIT;

  => emp 테이블의 dname 컬럼에 값을 넣고, 커밋했다.

 

*

UPDATE emp SET dname = 'MARKET SALES'

WHERE dname = 'SALES';

 => dname 이 'SALES' 인 것의 dname 을 'MARKER SALES' 로 바꿨다.

▶ JOIN

ANSI SQL , ORACLE SQL

표현 방법이 다르다.

● ANSI SQL 

1. NATURAL JOIN 

  : 조인 테이블 간 같은 타입, 같은 이름의 컬럼으로 같은 값을 가질 경우 조인.

2. JOIN USING     

  : 조인하려 하는 테이블 간 동일한 이름의 컬럼이 두개 이상일때. 조인컬럼 하나만 사용하고 싶을 때.

3. JOIN with ON

  : 조인하고자 하는 테이블의 컬럼 이름이 다를 때, 개발자가 조인 조건을 직접 제어할 때.

4. SELF JOIN

  : 같은 테이블간 조인. 계층구조의 상위, 하위 내용을 탄 테이블에서 관리하고자 할 때.

5. NONEQUI JOIN

● ORACLE SQL

FROM 절에 조인할 컬럼을 , 로 구분해 적고, WHERE 절에 그 조건을 쓴다. 

 

 

 

--1. NATURAL JOIN

SELECT  deptno, emp.empno, ename

FROM emp NATURAL JOIN dept;

---ORACLE SQL

SELECT deptno.deptno, emp.empno, emp.ename

FROM emp, dept

WHERE emp. deptno = dept. deptno;

  => 조인하는 두 테이블 중 한쪽에만 있는 컬럼은 어느 테이블에 속해있는지 앞에 적지 않아도 됨.

   하지만 공동작업 시 불편함을 느낄 수 있으니 적도록 하자.

   ANSI SQL 에서는 조인하고있는 테이블의 이름을 적지 않아야 하지만,

   ORACLE SQL 에서는 적어야 한다.

 

--2. JOIN USING

SELECT *

FROM emp JOIN dept USING (deptno);

---ORACLE SQL

SELECT *

FROM emp, dept

WHERE emp.deptno = dept.deptno;

 

--3. JOIN with ON

SELECT *

FROM emp JOIN dept ON (emp.deptno = dept.deptno);

---ORACLE SQL

SELECT *

FROM emp, dept

WHERE emp.deptno = dept.deptno;

 

--4. SELF JOIN

SELECT e.ename, m.ename

FROM emp e JOIN emp m ON(e.mgr = m.empno);

---ORACLE SQL

SELECT e.ename, m.ename

FROM emp e, emp m

WHERE e.mgr = m.empno;

 

--5. NONEQUI JOIN

SELECT emp.ename, emp.sal, salgrade.grade

FROM emp JOIN salgrade ON (emp.sal BETWEEN salgrade.losal AND salgrade.hisal);

  => emp 의 급여에 salgrade의 등급을 붙였다. ON 뒤에 true 가 오면 되는 듯하다.

---ORACLE SQL

SELECT emp.ename, emp.sal, salgrade.grade

FROM emp, salgrade

WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal; 

 

 

 

 

 

▶EXERD

exerd 다운받았다. 설치하고
선생님께서 주신 model 폴더 배경화면에 넣고
exerd 켠 다음 새로만들기>일반>프로젝트에서 model 프로젝트 만든 후
폴더에 있는 것을 이 프로젝트 속에 붙여 넣음.

exerd 보면 scott 폴더에서 사원 테이블과 부서 테이블을 볼 수 있다.
관계선이 있는 것, 없는 것.
관계선이 있으면 관계가 있으니 수정. 변경에 제한이 있음.