2. SQL(2)

 

 

6) 여러 테이블에서의 데이터 조회

- Join 이란?
  : 하나의 SQL문으로 여러개 테이블에 저장된 데이터를 한번에 조회할 수 있는 기능
    한개 이상의 테이블로 부터 데이터를 조회하는 것
    두개의 테이블에는반드시 공통 컬럼이 존재해야 함

    
- Join의 유형
  · Equi Join : 조인 대상 테이블에서 공통 칼컴을 '='(equal) 비교를 통해 같은 값을 가지는 행을 연결하여

                    결과를 생성하는 조인
  · NonEqui Join : <, Between a And b와 같이 '=' 조건이 아닌 연산자를 사용하여 조인
  · Outer Join :  Equi Join에서 양측 칼럼 값 중의 하나가 Null이지만 결과에 누락되지 않도록 포함하기 위하여 조인
                      2개 이상의 테이블이 조인이 될 때 어느 한쪽의 테이블에는 해당하는 데이터가 존재하는 데

                      한쪽에는 존재하지 않을 때 
                      존재하지 않는 쪽 테이블에 (+) 기호를 정의하면 모든 데이터가 출려됨

  · Self Join : 하나의 테이블 내에 있는 컬럼끼리 연결하여 결과를 얻고자 할 때 조인

  · Cross Join : 2개 이상의 테이블이 조인될 때 Where절에 의해 공통되는 컬럼의 결합이 발생하지 않는 경우(사용 X)
                      (가능한 모든 조합을 다 만들어내기 때문에 의미가 없음)
  · Natural Join(=Equi Join) : 2개이상의 테이블이 공통되는 컬럼에 의해 논리적으로 결합되는 경우

  · Full Outer Join : Left Outer Join과 Right Outer Join을 동시에 실행한 결과를 출력, 오라클은 지원하지 않음
  · Self Join : 한 테이블을 두번 조인하는 유형, 한 테이블 내에 있는 칼럼끼리 연결하는 조인이 필요한 경우 사용
                   테이블 별칭을 사용해야 함

-- Equi Join 
SELECT     table1.column, table2.column
FROM       table1, table2
WHERE     table1.column = table2.column ;

FROM절 : 별칭 사용 가능
WHERE절 : 조인 조건 기술, 공통 컬럼을 '=' 연산자를 이용하여 조건 기술

 

 

-- NonEqui Join
SELECT     table1.column, table2.column
FROM       table1, table2
WHERE     table1.column  BETWEEN table2.column AND table2.column...

-- OUTER JOIN
select     e.empid, e.fname, e.deptid, d.deptname
from        emp e, dept d
where     e.deptid = d.deptid(+) ;

--
select   e.empid, e.fname, d.deptname, l.city
from  emp e, dept d, loc l
where  e.deptid = d.deptid(+)
and  d.locid = l.locid(+) ;

--
select  e.empid, e.fname, d.deptname, l.city, j.jobtitle
from  emp e, dept d, loc l, job j
where  e.deptid = d.deptid(+)
and  d.locid = l.locid(+)
and  e.jobid = j.jobid ;

--
--[Oracle]
select e.empid, e.fname, d.deptname
from emp e, dept d
where e.deptid = d.deptid(+) ;

--[ANSI]
select e.empid, e.fname, d.deptname
from emp e left outer join dept d
on e.deptid = d.deptid ;

=================================================

 

7) 그룹함수를 사용한 데이터 조회

- 그룹 함수 : 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 그룹화하여 그룹 별로 결과를 출력하는 함수
                  통계적인 결과를 추출할 때 자주 사용
                  NULL을 계산하지 않음

- 그룹 함수의 종류
  · COUNT : 행의 개수 출력
  · MAX : NULL을 제외한 모든 행에서 최대 값
  · MIN : NULL을 제외한 모든 행에서 최소 값
  · SUM : NULL을 제외한 모든 행의 합
  · AVG : NULL을 제외한 모든 행의 평균
  · STDDEV : NULL을 제외한 모든 행의 표준 편차

- GROUP BY절
  : 결과 집합의 하위 데이터 그룹을 만듦, 특정 칼럼을 기준으로 테이블의 전체 행을 그룹별로 나눔
    각 그룹별로 SELECT 절에 기술한 그룹 함수가 적용, WHERE절에는 그룹 함수를 사용할 수 없음
    Group by절에는 컬럼 이름만 사용 가능(별칭, 순서 사용 불가)

 

- Having 절
  : Group by 절에 의해 생성된 그룹을 대상으로 조건을 적용하여 Filtering하기 위해 사요
 
 -Having절의 실행 과정
  · 테이블에서 Where 절에 의해 조건을 만족하는 행 집합을 선택
  · 행 집합을 group by절에 의해 grouping
  · having 절에 의해 조건을 만족하는 그룹을 선택

 

 

 

<< 실 습 하 기 >>

 

--0. emp테이블의 모든 레코드
select * from emp ;

--1. 직원의 id가 158인 레코드의 fname, lanme, phone 검색
select fname, lname, phone from emp
where empid = 158 ;

--2. fname이 'D'로 시작하는 직원의 id와 이름, 부서ID 검색
select empid, fname, lname, deptid from emp
where fname like('D%');

--3. 부서번호가 30인 사원들의 사번과 fname, jobid, sal, deptid 출력
select empid, fname, jobid, sal, deptid from emp
where deptid = 30 ;

--4. comm이 null이 아닌 직원 중 그 값이 0.3 이상인 레코드의 사번과 fname, comm 출력
--(comm으로 정렬)
select empid, fname, comm from emp
where comm is not null
and
comm >= 0.3
order by comm ;

--5. 입사일이 1999년 12월 31일 이후인 직원의 사원 번호와
--    fname, 입사일 출력(입사일로 오름차순)
select empid, fname, hdate from emp
where hdate >= '99/12/31'
order by hdate ;


--6. 부서번호 20에서 50번 사이의 직원 이름과 부서id를 검색(부서id로 오름차순 정렬)
select fname, lname, deptid from emp
where deptid between 20 and 50
order by deptid ;

--7. 부서번호가 없는 직원의 id와 email, 이름 검색
select empid, email, fname, lname from emp
where deptid is null ;

--8. jobid가 'ST_CLERK', 'SA_MAN', 'SA_REP'인 직원의 id와 이름, jobid
select empid, fname, lname, jobid from emp
where jobid in ('ST_CLERK', 'SA_MAN', 'SA_REP');

--9. 전화번호가 650으로 시작하는 직원의 id와 이름, 부서ID, 전화번호 검색
select empid, fname, lname, deptid, phone from emp
where phone like('650' || '%') ;