SQL 실습 정리

본 실습은 Oracle9i introduction to Oracle9i SQL vol2의 '14장 sql 강습' 을 토대로 진행했습니다.

※시퀀스(sequence)

지정 정수로부터 시작하여, 지정한 만큼 증가하여 다음 정수를 반환, 지정 정수까지만 진행하여 독립적인 값을 생성하는 객체

CREATE SEQUENCE sequence 명
                                       INCREMENT BY 증가 값 -- 해당 값만큼 증가
                                       START WITH 시작 값 -- 해당 값부터 시작
                                       MAXVALUE 끝 값 -- 해당 값까지 증가
                                       NOCACHE --시퀀스의 캐시 메모리 저장 여부
                                       NOCYCLE; -- MAXVALUE 초과 시 다시 START WITH 값으로 시작 여부

※CACHE 옵션 사용 시 호출 속도는 향상 될 여지가 있으나, 현 예제에서의 효율은 좋지 못함

시퀀스의 값은 다음과 같이 호출한다.

시퀀스 명.NEXTVAL :시퀀스의 다음 값 호출

시퀀스 명.CURRVAL : 시퀀스의 현재 값 호출

※주의 사항

시퀀스는 일방향으로 증가하여 끝 값을 향해서만 증가한다. 이 과정에서 중간 값이 공유되거나 기존의 값이 삭제되는 경우 공백이 발생하며, ORACLE 에서는 값 생성과 동시에(NEXTVAL) 자체 COMMIT이 적용 되므로 주의가 필요하다.

3 . MEMBER 테이블 및 TITLE 테이블의 각 행(row)을 고유하게 식별하는 시쿼스를 생성하십시오.

a.MEMBER 테이블의 회원번호는 101부터 시작하고 값이 캐시되지 않도록 하십시오.

시퀀스 이름은 MEMBER_ID_SEQ 로 지정하십시오.

-생성 스크립트

create sequence title_id_seq increment by 1 start with 92 nocache nocycle;

b.TITLE 테이블의 제목 번호는 92부터 시작하고 캐시되지 않도록 하십시오. 시퀀스 이름은 TITLE_ID_SEQ 로 지정하십시오.

-생성 스크립트

create sequence member_id_seq increment by 1 start with 101 nocache nocycle;

※SELECT

-SQL 기본적인 '조회'를 위한 구문

기본 형식

SELECT 열 이름1[, 열 이름2, ... ] FROM 테이블 이름 -- 해당 테이블로부터 필요한 열에 대해 조회

[WHERE 조건식 --해당 조건 식에 대해 만족하는 열에 대해서만 조회

GROUP BY 대상 열 이름 --조회 된 열 들에 대해 그룹회

HAVING 집계 함수 관련 조건식 --조회된 열들에 대해 그룹화한 열에 대해 조건 적용

ORDER BY 기준 열 ... ] --모든 열 출력 후 정렬

실행 순서 : FROM -> WHERE-> GROUP BY -> HAVING -> SELECT -> ORDER BY

SELECT 문으로 조회 된 값은 SELECT, FROM, WHERE 절에서의 사용이 가능하다.

※서브쿼리

-SELECT , FROM, WHERE 에서 다른 테이블에서부터 조회된 결과를 사용하는 방식

FROM 절에서 사용된 서브쿼리는 '인라인 뷰'로 테이블처럼 사용이 가능하다.

사용 조건

-단일행 연산, SELECT 절에서의 서브쿼리에는 반드시 단일 행 이 반환 되어야 한다.

-'인라인 뷰' 서브쿼리에서의 집계함수 사용 열은 별칭을 지정해 줘야 한다.

(SELECT ..., 열 이름 AS 별칭 ... -> 별칭ALIAS)

추가 참고사항

예제에서 사용된 ORACLE 의 시간 기본 형식은 '한글'로 되어있기 때문에 월에 해당하는 형식은 '월'표기가 된다.

TO_DATE('05-OCT-1995', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American')

(TO_DATE : 문자열을 날짜 형식으로 변환)

값 삽입시 다음의 조치를 취하거나, ORACLE 내부 설정 변수를 통한 설정이 필요하다.

4. 테이블 데이터 추가

-TITLE table 데이터 추가 스크립트

insert into title values(title_id_seq.nextval, 'Willie and Christmas Too',

'All of Willie''s friends make a Christmas list for Santa, but Willie has yet to add his own wish list',

'G', 'CHILD', TO_DATE('05-OCT-1995', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

insert into title values(title_id_seq.nextval, 'Alien Again',

'Yet another installation of science fiction history. Can the heroine save the planet from the alien like form?',

'R','SCIFI',TO_DATE('19-MAY-1995', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

insert into title values(title_id_seq.nextval, 'The Glob',

'A meteor crashes near a small American town and unleashes carnivorous foo in this classic',

'NR','SCIFI',TO_DATE('12-AUG-1995', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

insert into title values(title_id_seq.nextval,'My Day Off',

'With a little luck and a lot of ingenuity, a teenager skips school for a day in New York',

'PG','COMEDY',TO_DATE('12-JUL-1995', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

insert into title values(title_id_seq.nextval, 'Miracles on Ice',

'A six-year-old has doubts about Santa Claus, but she discovers that miracles really do exist',

'PG','DRAMA',TO_DATE('12-SEP-1995', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

insert into title values(title_id_seq.nextval, 'Soda Gang',

'After discovering a cache of drugs, a young couple find themselces pitted against a vicious',

'NR','ACTION',TO_DATE('01-JUN-1995', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

-MEMBER table 데이터 추가 스크립트

insert into member values(member_id_seq.nextval, 'Carmen','Velasquez','283 King Street', 'Seattle',

'206-899-6666',TO_DATE('08-MAR-1990', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

insert into member values(member_id_seq.nextval, 'LaDoris','Ngao','5 Modrany', 'Bratislava',

'586-355-882',TO_DATE('08-MAR-1990', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

insert into member values(member_id_seq.nextval, 'Midori','Nagayama','68 Via Centale', 'Sao Paolo',

'254-852-5764',TO_DATE('17-JUN-1991', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

insert into member values(member_id_seq.nextval, 'Mark','Quick-to-See','6921 King Way', 'Lagos',

'63-559-7777',TO_DATE('07-APR-1990', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

insert into member values(member_id_seq.nextval, 'Audry','Ropeburn','86 Chu Street', 'Hong Kong',

'41-559-87',TO_DATE('18-JAN-1991', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

insert into member values(member_id_seq.nextval, 'Molly','Urguhart','3035 Laurier', 'Quebec',

'418-542-9988',TO_DATE('18-JAN-1991', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'));

-TITLE_COPY table 데이터 추가 스크립트

insert into title_copy values(1,(select title_id from title where title = 'Willie and Christmas Too'),'AVAILABLE');

insert into title_copy values(1,(select title_id from title where title = 'Alien Again'),'AVAILABLE');

insert into title_copy values(2,(select title_id from title where title = 'Alien Again'),'RENTED');

insert into title_copy values(1,(select title_id from title where title = 'The Glob'),'AVAILABLE');

insert into title_copy values(1,(select title_id from title where title = 'My Day Off'),'AVAILABLE');

insert into title_copy values(2,(select title_id from title where title = 'My Day Off'),'AVAILABLE');

insert into title_copy values(3,(select title_id from title where title = 'My Day Off'),'RENTED');

insert into title_copy values(1,(select title_id from title where title = 'Miracles on Ice'),'AVAILABLE');

insert into title_copy values(1,(select title_id from title where title = 'Soda Gang'),'AVAILABLE');

-RENTAL table 데이터 추가 스크립트