Lecture 13 - SQL(3)

* 정확한 데이터를 출력
select                                      // 컬럼 선택
from                                       // 데이터 선택
[where          ]                        // 행선택
[group by     ]                        // 그룹화 -> 그룹함수
[having         ]                        // 그룹의 제한
[order by      ]                        // 정렬

내장 함수
    단일행함수(26p)
        문자 / 숫자 / 날짜 / 변환 / 일반
    복수행함수(그룹함수)

    * 함수 중첩(어떤 값을 출력할 지 잘 알고 있어야 한다.)

join - 두개 이상의 테이블을 조인해서 불러오는 기법
카르테시안 프로덕트 - 두개의 테이블을 묶는다.
    - Equi-Join 두 테이블의 특정 컬럼의 값이 같을때 검색한다.

select emp.deptno, dept.deptno, ename, dname
from dept, emp
where dept.deptno = emp.deptno;

select e.deptno, d.deptno, ename, dname
from dept d, emp e
where d.deptno = e.deptno;

짧게 줄여서 쓰는 법

예) 직책이 clerk인 사원에 대한 사원번호, 사원명, 부서명, 부서위치

예) 급여가 2000에서 3000 사이인 사원에 대한 사원번호, 사원명, 급여, 부서번호, 부서명, 부서위치

    - Non-Equijoin

위 정보를 결합하기위해 Equision을 써야하나 컬럼이 서로 다르기 때문에 non-Equijoin을 사용해야해서 부등호를 사용한다

위의 부등호를 between A and B 로 수정해줌

예) 급여가 2000에서 3000 사이인 사원에 대한
사원번호, 사원명, 급여, 부서번호, 부서명, 부서위치, 호봉

Outer-Join

(+)를 반대로 쓰니 Operation 부서가 안나옴

Outer-Join의 예 : 
    1. 쇼핑몰에서 상품 - 주문 -> 주문되지 않은 상품을 확인할 때.
    2. 강의 인원 - 출석 -> 출석하지 않은 사람을 알 수 있다.

Self-Join

outer join을 이용해서 사원 king을 넣어준다.

ANSI 1992
    카테시안 프로덕트
    1. equi
    2. non-equi
    3. outer
    4. self
    
ANSI 1999
    카테시안 프로덕트 - Cross Join

Equi join
    1. Natural Join - 같은 이름 컬럼을 자동 검색

    2. Using 구문을 이용한 Join - 컬럼 선택

컬럼명 다른 equi / non equi
    join + on

호봉을 구할 때

예) 사원번호, 사원명, 급여, 부서명, 부서위치, 호봉을 출력하는 1999년 문법

Outer Join
    1. Left Outer Join

    2. Right Outer Join

    3. Full Outer Join

55p
1. 부서 테이블과 사원 테이블에서 사번, 사원명, 부서코드, 부서명을 검색하시오. 단, 출력시, 사원명을 기준으로 오름차순으로 정렬하시오
2. 부서 테이블과 사원 테이블에서 사번, 사원며이 급여, 부서명을 검색하시오. 단, 급여가 2000 이상인 사원에 대하여 급여를 기준으로 내림차순으로 정렬하시오.
3. 부서 테이블과 사원 테이블에서 사번, 사원명, 업무, 급여, 부서명을 검색하시오. 단, 업무가 MANAGER이며 급여가 2500 이상인 사원에 대하여 사번을 기준으로 오름차순으로 정렬하시오.
4. 사원 테이블과 급여등급 테이블에서 사번, 사원명, 급여, 등급을 검색하시오. 단, 등급은 급여가 하한값과 상한값 범위에 포함되고 등급이 4이며 급여를 기준으로 내림차순으로 정렬하시오.
5. 부서 테이블, 사원 테이블, 급여등급 테이블에서 사번, 사원명, 부서명, 급여, 등급을 검색하시오. 단, 등급은 급여가 하한값과 상한값 범위에 포함되며 등급을 기준으로 내림차순으로 정렬하시오.
6. 사원 테이블에서 사원명과 해당 사원의 관리자명을 검색하시오.
7. 사원 테이블에서 사원명, 해당 사원의 관리자명, 해당 사원의 관리자의 관리자명을 검색하시오.
8. 7번 결과에서 상위 관리자가 없는 모든 사원의 이름도 사원명에 출력되도록 수정하시오.

서브 쿼리 I

예) Smith보다 많은 급여를 받는 사원의 이름을 검색하기
   1. Smith의 급여를 구한다
    2. 그 급여보다 많은 사원 이름 검색

서브쿼리를 이용하여 위와 같은 두개의 쿼리를 한번에 해결

예) 최고 급여를 받는 사원의 이름을 출력

단일행 서브 쿼리
=, >, >=, <, <=, <> (한개 데이터(1행 1열))

예) 평균 급여보다 많이 받는 사원에 대한 사원명, 급여 출력

예) 사원 테이블에서 사번이 7521번인 사원과 같은 업무를 수행하고 있으며, 사번이 7900번인 사원의 급여보다 만은 급여를 받는 사원의 이름, 업무, 급여는 다음과 같다.

예) 20번 부서의 최소급여보다 큰 부서

복수행 서브 쿼리
in, any, all 연산자를 사용(1행 여러열)
    - in (목록)
예) 부서별 최소 급여를 받는 사원의 사번, 이름, 급여, 부서코드를 검색하라

예) 직책별 최대 급여를 받는 사원의 이름, 직책, 급여를 검색하라

    - all

    - any

< all (서브쿼리 : 목록) : 최소값보다 작은
> all (서브쿼리 : 목록) : 최대값보단 큰
< any (서브쿼리 : 목록) : 최대값보다 작은
> any (서브쿼리 : 목록) : 최소값보다 큰

예) 업무가 manager인 사원 중 최소 급여보다 급여가 많은 사원

복습(88p)
1. 부서 테이블에 부서코드가 99, 부서명은 '관리과', 위치는 '대구'인 행을 입력하시오
2. 부서 테이블에 99번 부서의 부서명을 '회계과'로 변경하시오
3. 부서 테이블의 99번 부서 행을 삭제하시오

테이블 생성 및 관리
DDL(Data Definition Language)
: 데이터를 저장하거나 관리할 객체를 생성, 수정, 삭제

create
alter
drop
..

절대 한글로 사용하지 말 것!!

create table 테이블 (
     컬럼명 데이터타입(크기),
     컬럼명 데이터타입(크기),
     컬럼명 데이터타입(크기)
)

데이터타입
문자
    varchar2 (가변형) - 10자리에서 4자리만 집어넣으면 나머지 6자리는 버린다
    char (고정형) - 10자리에서 4자리만 집어넣어도 나머지 6자리는 그대로 있는다
    long(2GB)/clob(4GB) - 긴 문자(고용량)
숫자
    number
        정수(값)
        실수(값(전체자리수), 값(소수점자리수))
날짜
    date
* 바이너리(실행파일)
    raw(최대 2000 바이트) / blob(4GB) - 이진데이터

create table tbl1 (
    col1 number(4),
    col2 varchar2(10),
    col3 char(5)
)

테이블 생성

테이블 이름, 타입 확인

테이블 이름만 확인

테이블 tbl1 확인

서브 쿼리를 이용한 테이블 생성

dept를 기반으로 dept2를 복제한다

dept 10 부서의 dname과 loc만 복제해서 dept3를 만든다

dname, loc에 별명을 부과한다

name만 리턴

sal*12+nvl(comm, 0)은 컬럼명으론 부적절하여 annsal로 변경

예) 사원번호, 사원명, 급여, 호봉이 있는 emp_sal 테이블 작성

데이터 없이 껍데기만 만드는법 1

데이터 없이 껍데기만 만드는 법 2(from emp where 1!=1);

테이블 수정 - 추가, 변경, 삭제

컬럼 생성

tbl2 생성

컬럼 추가

job 컬럼 추가 됨

컬럼 변경

컬럼 명 변경

컬럼 삭제

테이블 삭제

테이블 삭제 - drop

101p
1. 다음과 같은 테이블을 작성하시오
테이블명 : JUSO

2. 전화번호(PHONE) 컬럼을 VARCHAR2(10) 타입으로 추가하시오
3. 이메일(EMAIL) 컬럼을 VARCHAR2(20) 타입으로 변경하시오
4. 주소(ADDR) 컬럼을 삭제하시오
5. 테이블을 삭제하시오

휴지통 기능이 있어서 바로 테이블이 삭제되지 않고 이상한 글귀가 남아있음

휴지통 비우기 - purge recyclebin;

휴지통에 있는 테이블이 삭제 된다

휴지통 안에 있는 내용

테이블 복구 - flashback

완전 삭제

기본으로 되돌리기 위해 나머지들은 다 지운다

테이블
    사용자 정의 테이블
        - 사용자 데이터 저장
    데이터 딕셔너리(SYS)
        - 관리자 / 사용자 데이터베이스 관리할 목적으로 사용하는 테이블
        - 데이터 딕셔너리 뷰(read only - 일반 사용자들이 이걸 참조하여 어떤 상태이지 파악)

내장 테이블

User_         // 사용자 본인( * )
All_           // 모든 사용자가 공통으로 접근
Dba_         // dba
V$            // 시스템 성능

사용자 테이블 전체

User 테이블

User 카탈로그 - 객체 정보 모두

아래와 같이 하면 휴지통에 있는 모르는 객체 정보를 알 수 있다.

데이터 조작
DML(Data Manipulation Language)
    table에서 데이터 추가, 수정, 삭제
    insert
    update
    delete

insert into 테이블
value (값, 값, 값, 값);
* 값의 순서는 - 컬럼의 순서와 동일
* '문자열'
* not null - 값을 반드시 넣어야 함

insert

형식에 맞지 않게 넣었을때, 처치방법은 에러코드를 구글에 검색(아는건 바로 조치)

빈공간을 만들고 싶을땐 null을 넣는다(3항목 다 null 해도 됨)

해당하는 컬럼에다 직접 값 입력 가능

Not null 입력

에러 발생

반드시 입력해야 하는 컬럼에는 값을 입력해야 한다. null 안됨

날짜 넣는 법

날짜 넣는 법 2

insert into 테이블
(as) select 문

bulk insert