27일차: Oracle – 문자/날짜/형변환/그룹 함수/Join - 2021.07.21

목차

1.     그룹 함수

2.     Group By절

3.     Having 조건절

4.     Cross Join

5.     ANSI Join

 

1. 그룹 함수 <6장>                          → ★2021_07_21_01 참고

 

1) COUNT()

- 총 데이터 개수를 구해주는 함수

- null 값은 카운팅하지 않음

- 형식: count (컬럼명)

 count(*)

- ex) select count(sal) from emp;              → 14

       select count(comm) from emp;         → 4

       select count(empno) from emp;        → 14

 

* EMP 테이블에서 empno 컬럼은 primary key(기본키)로 설정되어 있음

 

- ex) Q. 사원 테이블에서 중복을 제거한 JOB의 개수

       select count(distinct job) from emp;  → 5

 

2. Group By절 <6장>                        → ★2021_07_21_01 참고

 

- 특정 컬럼을 기준으로 테이블에 존재하는 데이터를 그룹으로 구분하여 처리해주는 역할

- ex) Q1. 각 부서(10, 20, 30)의 급여의 합, 평균 급여, 최대 급여, 최소 급여를 구하는 SQL문 작성

       select sum(sal), avg(sal), max(sal), min(sal) from emp where deptno=10;

       select sum(sal), avg(sal), max(sal), min(sal) from emp where deptno=20;

       select sum(sal), avg(sal), max(sal), min(sal) from emp where deptno=30;

 

      → select sum(sal), avg(sal), max(sal), min(sal) from emp group by deptno;

          : group by절을 이용하여 한 줄로 처리 가능

 

- 그룹 함수와 일반 컬럼은 같이 사용할 수 없다.

select ename, max(sal) from emp;    → 오류 발생

 

- 일반적으로 그룹 함수와 일반 컬럼은 같이 사용할 수 없지만, 예외적으로 group by절에 사용되는 함수는 그룹 함수와 같이 사용할 수 있다.

select deptno, sum(sal), avg(sal) from emp group by deptno order by deptno asc;

 

3. Having 조건절 <6장>                              → ★2021_07_21_01 참고

 

- group by절이 사용되는 경우에 데이터를 제한하기 위해서는 where 조건절 대신 having 조건절을 사용

- ex) Q1. 각 부서별 평균 급여가 2000 이상인 부서만 출력하는 SQL문 작성

       select deptno, avg(sal) from emp group by deptno having avg(sal)>=2000;

       select deptno, avg(sal) from emp group by deptno where avg(sal)>=2000;  → 오류 발생

 

4. Cross Join <7장>                                                → ★2021_07_21_01 참고

 

- 2개 이상의 테이블을 결합해서 정보를 구해오는 것

- Cross Join과 ANSI Join 방법이 있음

 

1) Cross Join

- 특별한 키워드 없이 select문의 from절에 테이블을 콤마(,)로 연결하여 연속하여 기술하는 것

- 형식: select * from 테이블1, 테이블2

- ex) select * from emp, dept;                  → 14 * 4 = 56개

 

• Cross Join의 종류

① 등가 조인(Equi Join)

② 비등가 조인 (Non-Equi Join)

③ 자체 조인 (Self Join)

④ 외부 조인 (Outer Join)

 

① Equi Join (등가 조인)

- 두 테이블의 동일한 컬럼을 기준으로 조인

- ex) select * from dept, emp where dept.deptno = emp.deptno;     → 14개의 데이터 검색

 

- 공통 컬럼(deptno)은 ‘테이블명.공통 컬럼명’ 형식으로 출력해야 한다.

- ex) select deptno, ename, dname from dept, emp

           where dept.deptno=emp.deptno and ename='SCOTT';   

       → 오류 발생 : deptno가 어떤 테이블에 있는 컬럼인지 알 수 없음

 

- 공통 컬럼이 아닌 컬럼들은 앞에 테이블명을 생략할 수 있다.

- ex) select dept.deptno, ename, dname from dept, emp

           where dept.deptno=emp.deptno and ename='SCOTT';

 

• 테이블에 별칭 부여하기

- 테이블에 대한 별칭이 부여된 이후에는 별칭명만 사용 가능하고 테이블명은 사용할 수 없다.

- 별칭명은 대소문자를 구분하지 않는다.

- 공통 컬럼(ex. deptno)은 별칭명.공통컬럼명 형식으로 사용해야 한다. ex) D.deptno

- 공통 컬럼이 아닌 컬럼들은 앞에 별칭명을 생략할 수 있다.

 

- ex) select d.deptno, ename, dname from dept D, emp E

          where D.deptno=E.deptno and ename='SCOTT';

     → 공통 컬럼인 deptno 이외의 일반 컬럼에는 테이블명 생략

     → 별칭명은 대소문자 구별하지 않음

 

② Non-Equi Join (비등가 조인)

- 공통되는 컬럼 없이 다른 조건을 사용하여 조인

- ex) Q. 사원 테이블에 있는 각 사원들의 급여가 몇 등급인지를 출력하는 SQL문 작성

       EMP (sal) - SALGRADE (grade)

      select ename, sal, grade from emp, salgrade

           where sal >= losal and sal <= hisal;

 

      select ename, sal, grade from emp, salgrade

           where sal between losal and hisal;

 

③ Self Join (자체 조인)

-  한 개의 테이블 내에서 컬럼과 컬럼 사이의 관계를 이용해서 조인

- ex) Q. 자체 조인(Self Join)을 이용하여 사원 테이블에서 각 사원들의 사원명과

       매니저(=직속 상관)를 출력하는 SQL문 작성 - EMP (empno) - EMP (mgr)

       select employee.ename ||'의 매니저는 '|| manager.ename

              from emp employee, emp manager

              where employee.mgr=manager.empno;

→ 13개의 검색 결과가 출력된다.

→ KING 사원은 직속 상관이 없기 때문에 출력되지 않는다.

 

④ Outer Join (외부 조인)

- 조인 조건을 만족하지 않는 데이터를 출력해주는 조인

- 테이블을 조인할 때 어느 한쪽의 테이블에는 데이터가 존재하지만, 다른 테이블에는 데이터가 존재하지 않는 경우에, 그 데이터가 출력되지 않는 문제를 해결하기 위해서 사용되는 조인 방법

- 정보가 부족한 곳에 (+)를 추가한다.

- ex) Q1. 위의 자체 조인(Self Join)의 결과, KING 사원은 직속 상관이 없기 때문에

       출력되지 않았는데 KING 사원도 외부 조인을 이용해서 출력하기

       select employee.ename ||'의 매니저는 '|| manager.ename

               from emp employee, emp manager

               where employee.mgr=manager.empno(+);

 

5. ANSI Join <7장>                                                 → ★2021_07_21_01 참고

- ANSI(미국 표준 협회) 표준안에 따라서 만들어진 JOIN 방법

 

• ANSI CROSS JOIN

select * from emp cross join dept;   → 14 * 4 = 56개 데이터 검색

select * from dept cross join emp;   → 4 * 14 = 56개 데이터 검색

 

1) ANSI Inner Join

• ANSI Inner Join

Q. SCOTT 사원이 소속된 부서명을 출력하는 SQL문 작성

select ename, dname from emp inner join dept

      on emp.deptno = dept.deptno

      where ename='SCOTT';

 

• using을 이용해서 조인

select ename, dname from emp inner join dept

      using(deptno)

      where ename='SCOTT';

 

• ANSI Natural Join

EMP와 DEPT 사이의 공통 컬럼이 같다는 의미를 가지고 있음

select ename, dname from emp natural join dept

      where ename='SCOTT';

 

2) ANSI Outer Join

- 형식: select * from table1 [left | right | full] outer join table2;