[DB] 아빠도 따라하는 오라클 데이터베이스 4일차

Oracle db (2월 22일).txt

DB 테이블(scott).zip

  복습

□ Join

- 한 개 이상의 테이블로부터 데이터를 조회하는 것

- 하나의 테이블로는 원하는 정보를 얻을 수 없을 때 관련된 테이블들을 논리적으로 결합하여 원하는 컬럼을 얻어냄

- 논리적으로 결합되는 2개 이상의 테이블에는 반드시 공통 컬럼이 존재해야 함

- 주로 PK와 FK의 관계를 가진 컬럼을 소유하고 있는 테이블을 통한 검색시 사용

 

□ join 문법

- CROSS JOIN, NATURAL JOIN, SELF JOIN, OUTER JOIN

 

1. CROSS JOIN

   • 2개 이상의 테이블이 조인 될 때 wherewf에 의해 공통되는 컬럼에 의한 결합이 발생하지 않는 경우

2. NATURAL JOIN(INNER JOIN)

   • 2개 이상의 테이블이 공통되는 컬럼에 의해 논리적으로 결합되는 경우

3. SELF JOIN

   • 한 개의 테이블을 두개의 별도의 테이블처럼 사용 할 때 이용

4. OUTER JOIN

   • 2개 이상의 테이블이 조인될 때 어느 한 쪽의 테이블에는 해당하는 데이터가 존재하는데 다른 쪽의 테이블에는 데이터가 존재하지 않을 때 그 데이터는 출력되지 않는 문제를 해결하기 위해 사용되는 기법

   • 데이터가 존재하는 않는 쪽 테이블에 (+) 기호를 정의하면 모든 데이터가 출력됨

 

□ Sub Query

• 두 번의 질의를 수행해야만 얻을 수 있는 결과를 한번의 질의로 해결할 수 있는 문장

• 하나의 select 문 안에 부속 된 또 다른 select 문 사용

• 서브쿼리는 FROM 절에서도 사용될 수 있다. (but 보통 where 절에서 많이 사용)

• 서브쿼리는 메인 쿼리의 조건으로 사용

• 서브쿼리가 먼저 실행되고 그 결과를 메인 쿼리에 전달하여 사용

• 서브쿼리는 order by 절 사용 불가

 

○ 예시

• 사번이 7790인 사원과 같은 일을 하는 사원의 정보

- select job

From emp

Where empno = 7790; (클락)

- select * from emp where job ='clerck'

 

• 서브쿼리 예

- select *

from emp

where job = (select job

from emp

where empno = 7790);

 

□ 서브쿼리의 종류

1) 단일 행 서브쿼리

2) 복수 행 서브쿼리

3) 복수컬럼 서브쿼리

4) 상호관련 서브쿼리

 

○ 단일 행 서브쿼리

1) 하나의 컬럼, 하나의 행을 리턴 해 주는 경우

2) 결과를 비교해야 하기 때문에 =, <. , <=, => 등의 비교 연산자가 사용됨

 

• 예시

- select *

from emp

where salary = (select max(salary)

from emp);

 

- select empid, fname

from emp

where salary > (select avg(salary)

from emp);

 

- select city

from loc

where locid = (select locid

from dept

where deptid = '10');

 

- select deptname

from dept

where mgrid = (select mgrid

from emp

where empid = '107');

 

○ 복수 행 서브쿼리

• 서브쿼리의 결과가 여러 개인 경우

 

• 복수 행 서브쿼리 : IN

1. 여러 개의 행을 리턴 해 주는 경우
2. 결과를 비교해야 하기 때문에 IN 연산자가 사용됨

    

 

• 예시

- loc id 가 1700인 부서에 근무하는 사람들의 모든정보

 

select *

from emp

where deptid IN (select deptid

from dept

where locid = '1700');

 

• 복수 행 서브쿼리 : ANY

1. 여러 개의 행을 리턴 해 주는 경우
2. 리턴 되는 값 중 하나 이상을 만족하면 됨

    

• 복수 행 서브쿼리 : ALL

1. < ALL : 서브쿼리 결과 중의 최소값보다 작은 경우 만족
2. > ALL : 서브쿼리 결과 중의 최대값보다 큰 경우 만족

    

업무가 'MANAGER' 인 직원의 최고 sal 보다

많이 받는 사람의 직원번호, 이름, 업무, 급여 출력

 

select empid, fname, jobid, salary

from emp

where salary > ALL (select salary

from emp

where jobid = 'IT_PROG');

 

• 복수 행 서브쿼리 : EXISTS

- 서브쿼리에 의한 결과가 존재하는지의 여부 확인

 

select deptid, deptname

from dept

where exists (select *

from emp

where deptid = 30);

 

- exist에 대한 결과가 있느냐 없느냐에 따라 메인 쿼리문을 실행함 (서브쿼리를 전부다 실행하지 않고 true가 확인되면 바로 서브쿼리로 감)

 

[ 연습문제 ]

1. CHARACTERS 테이블에는 스타워즈에 등장하는 각 배역들의 정보가 들어 있다.

이들 중에 시스족 전사들만 조회하는 쿼리를 작성해 보자.

( 힌트: 시스족 전사들의 배역 이름은 '다쓰' 혹은 '다스' 로 시작된다. )

 

2. 스타워즈 에피소드 4에 출연한 배우들의 실명을 조회하는 쿼리를 작성해 보자.

 

3. 다음쿼리의 의미는 무엇인가?

SELECT a.real_name

FROM casting a, casting b

WHERE a.episode_id = 5

AND b.episode_id = 4

AND a.character_id = b.character_id;

 

4. 에피소드 5에는 출연했지만 4에는 출연하지 않은 배우 이름

- 5에 출연했던 사람들 중에 4에 출연한 사람들을 뺀 사람들

1) 에피소드 5 출연사람 출력, 4 출연한 사람 출력 후 minus 사용

 

[ 5~10, 서브쿼리 사용 ]

5.'제국의 역습'에 등장하는 배우 이름

- episode 5

 

6. 에피소드 별 출연 인원수 (group by)

- 다시 해볼것

 

7. '아미달라 여왕'이 등장했던 에피소드 id와 배우명

 

8. 등장인원 수가 10명 이상인 에피소드 id

 

9. 해리슨 포드가 등장했던 에피소드의 id와 에피소드 이름

 

10. 가장 적은 인원이 출연한 에피소드 id와 출연자 수

 

13. 가장 많은 인원이 출연한 에피소드의 배우명

- 배우명 (casting)

- 에피소드의 이름

- 가장많은 인원

 

[ 연습문제 정답]

 

1.

select *

from characters

where character_name like '다%';

 

2.

select real_name

from casting

where episode_id = (select episode_id

from star_wars

where episode_id = 4);

 

3. 에피소드 4,5 둘다 같은 배역으로 출연한 사람이름

SELECT a.real_name

FROM casting a, casting b

WHERE a.episode_id = 5

AND b.episode_id = 4

AND a.character_id = b.character_id;

 

4.

select real_name

from casting

where episode_id =5

 

minus

 

select real_name

from casting

where episode_id =4;

 

5.

select real_name

from casting

where episode_id = (select episode_id

from star_wars

where episode_name = '제국의 역습(The Empire Strikes Back)');

 

6.

 

select episode_id, count(*)

from casting

group by episode_id;

 

7.

select episode_id, real_name

from casting

where character_id = (select character_id

from characters

where character_name = '아미달라 여왕');

 

8.

select episode_id

from casting

group by episode_id

having count (*) > 10;

 

9.

select episode_id, episode_name

from star_wars

where episode_id IN (select episode_id

from casting

where real_name = '해리슨 포드');

 

10.

 

select episode_id, count (*)

from casting

group by episode_id

having count (*) <= ALL (select count (*)

from casting

group by episode_id);

 

13.

 

select real_name, episode_id

from casting

where episode_id IN (select episode_id

from casting

group by episode_id

having count (*) >= ALL (select count (*)

from casting

group by episode_id));

[ 오후 타임]

• 실습

: 부서별 부서 id, 평균 salary

 

- group by 예제

select deptid, avg(salary)

from emp group by deptid;

 

□ Index

- 테이블 컬럼의 빠른 검색을 위해 사용하는 독립된 객체

• 인덱스 생성기준

1. SQL 문의 where절에서 자주 사용되는 컬럼이 대상이 됨
2. 빈번하게 변경되지 않는 테이블에 적용
3. 데이터가 많은 테이블에 효과적

 

• 인덱스를 사용하지 말아야 하는 경우

1. 테이블에 행이 적은 경우
2. 찾는 컬럼이 where 조건에 의해 자주 사용될 때
3. 테이블이 자주 입력, 수정, 삭제 될 때

•Index의 생성

- Create index (인덱스 이름) on 테이블(컬럼명)

 

□ Index 생성 문법

○ Create [unique] index [index 이름]

On table이름 (컬럼1, 컬럼2,..)

 

□ View

○ 기존의 테이블을 바탕으로 생성되는 가상 테이블

• 데이터를 직접소유하지는않으며뷰를통해기존테이블내용이검색됨

• 보안상의 이유 혹은 자주 사용되는 복잡한 query를 보다 쉽고 간단하게 사용하기 위해 만듬

 

□ View의 생성 문법

- Create [or replace] view 뷰 이름

As sub-query

 

- View 만들기

 

emp_view

 

Create or replace view emp_view

AS select empid, fname, email, deptid

from emp;

 

desc emp_view;

select * from emp_view;

 

□ View의 주요 특징

• 물리적인 저장공간을가지지않음

• 하나 이상의 테이블로부터 만들어 짐

• 뷰를 access 하게 되면 간적적으로 테이블을 access하게 됨

• 선택된 컬럼 정보만 참조 가능

• DML 을 할때는 실제 테이블에서 insert, delete, update를 하기 때문에 제약사항을 지켜야 만 DML이 가능함

- insert into emp_view values(207, 'Brian','ABCC',110);

SQL 오류: ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."LNAME")

emp_view에 inerst 값을 넣을려고 하면 emp 테이블에 제한사항을 따르기 때문에 에러가 간다. NOT null 구문이 빠져 있기 때문이다.

 

□ Sequence

• 연속적으로 숫자를 만들어주는 객체

 

□ Sequence 생성

• Create seque seq이름

[ Increment by n

Start with n

Maxvalue n | nomaxvalue

Minvalue n | nominvalue

Cycle | nocycle ]

 

• Sequence 만들기

 

create sequence cus_seq

minvalue 1

increment by 1

start with 20;

 

• sequence 사용하기

 

select cus_seq.currval

from dual;

 

select * from customer order by num;

insert into customer values(cus_seq.nextval, 'rolly','la');

- 시퀀스는 게시판 글 번호 만들 때 사용가능

 

★중요★ [Months_Between(date1,date2) 를 이용한 날짜 알아내기

select months_between('2013-09-01','2013-01-01')

from dual;

 

★중요★ [Alexander의 근속 날짜 알아내기]

 

select round(months_between(sysdate,hdate)/12,0)

from emp

where empid = 115;

 

★중요★ [DECODE 예제]

select empid, fname, comm,

DECODE(comm, null,'no','yes') AS "커미션"

from emp;

 

select empid, fname, deptid,

decode(deptid,90,'seattle',60,'South Lake',100,'New yourk','else') AS 도시명

from emp

order by empid;