SQL-INLINE VIEW, SEQUENCE

* INLINE VIEW(인라인 뷰)

from절에 테이블이 아닌 서브쿼리문을 사용한 것

 

SELECT ...
FROM (SELECT 문장 : sub query) [별명]

 

[예제 1] 각 부서별 최소 급여자를 검색한다.

SELECT eno, ename, d.dno, sal, msal
FROM emp e,
(SELECT dno, MIN(sal) msal

FROM emp
GROUP BY dno) d // d는 sql문으로부터 나온 결과값을 d라는 테이블로 사용 
WHERE e.dno = d.dno
AND sal = msal;

 

ROWNUM을 이용한 Top-N 분석

row의 갯수. 부등호만을 사용한다. 최상위, 최하위를 검색하고 싶을때 사용.

SELECT ROWNUM, [컬럼, 컬럼, ...]
FROM (SELECT ... ORDER BY ...)
WHERE ROWNUM <= N;

 

[예제 2] Emp 테이블로부터 3행까지만 검색한다.

SELECT ROWNUM, eno, ename, job, mgr, hdate

FROM emp

WHERE ROWNUM <= 3;

SELECT ROWNUM, eno, ename, job, mgr, hdate

FROM emp;

SELECT eno, ename, job, mgr, hdate

FROM emp

WHERE ROWNUM <= 3;

 

[예제 3] 최상위 연봉을 지급 받는 3인을 검색한다.

SELECT ROWNUM, eno, ename, ansal
FROM (SELECT eno, ename, sal*12+NVL(comm,0) ansal
          FROM emp
          ORDER BY ansal DESC)
WHERE ROWNUM <= 3;

 

sub query문을 먼저 실행해주고 ROWNUM을 해줘야 출력이 된다. 

 

[실습] 
1. 4.5 환산 평점이 가장 높은 3인의 학생을 검색한다.

SELECT ROWNUM, sno, sname, avg_fourfive 환산평점
FROM (SELECT sno, sname, TO_CHAR(AVG(avr*4.5/4.0), '0.99') avg_fourfive
          FROM student
          GROUP BY sno, sname
          ORDER BY avg_fourfive DESC)
WHERE ROWNUM <= 3;

2. 화학과 학생 중 기말고사 유기화학 점수가 최하위인 학생 3명을 검색한다.

SELECT ROWNUM, sno, sname, major, cname, result
FROM (SELECT s.sno, s.sname, s.major, c.cname, r.result
          FROM student s, course c, score r
         WHERE s.sno = r.sno AND c.cno = r.cno
         AND cname = '유기화학' AND major = '화학'
         ORDER BY result)
WHERE ROWNUM <= 3; 

3. 기말고사 과목별 평균이 높은 3과목을 검색한다.

SELECT ROWNUM, cno, cname, avg_result
FROM (SELECT c.cno, c.cname, TO_CHAR(AVG(result), '00.99') avg_result
          FROM course c, score r
          WHERE c.cno = r.cno
          GROUP BY c.cno, c.cname
         ORDER BY avg_result DESC)
WHERE ROWNUM <= 3;

4. 학과별 학년별 기말고사 평균이 순위 3위까지를 검색.(학과, 학년, 평균점수를 검색한다.)

SELECT ROWNUM, cname, syear, avg_result
FROM (SELECT c.cname, s.syear, TO_CHAR(AVG(result), '00.99') avg_result
          FROM course c, student s, score r
          WHERE c.cno = r.cno AND s.sno = r.sno
          GROUP BY c.cname, s.syear
          ORDER BY avg_result DESC)
WHERE ROWNUM <= 3;

5. 기말고사 성적이 높은 과목을 담당하는 교수 3인을 검색.(교수 이름, 과목명과 평균점수를 검색한다.)

SELECT ROWNUM, pname, cname, avg_result 평점
FROM (SELECT pname, cname, TO_CHAR(AVG(result), '00.99') avg_result
          FROM professor p, course c, score r
          WHERE p.pno = c.pno AND c.cno = r.cno
          GROUP BY pname, cname
          ORDER BY avg_result DESC)
WHERE ROWNUM <= 3;

6. 교수별로 현재 수강중인 학생의 수가 많은 순서대로 10인을 검색한다.

SELECT ROWNUM, pname, 학생수
FROM (SELECT pname, COUNT(DISTINCT s.sno) 학생수 
          FROM professor p, student s, course c, score r
          WHERE p.pno = c.pno AND s.sno = r.sno AND c.cno = r.cno
          GROUP BY pname
          ORDER BY 학생수 DESC)
WHERE ROWNUM <= 10;

 

* SEQUENCE(시퀀스)

 

시퀀스 생성과 확인
CREATE SEQUENCE 시퀀스
[ START WITH 시작_값
INCREMENT BY 증가_값
MAXVALUE [상한_값 | NOMAXVALUE]
MINVALUE [하한_값 | NOMINVALUE]
CYCLE | NOCYCLE
CACHE [cache_개수| NOCACHE]];

 

ALTER SEQUENCE 시퀀스

INCREMENT BY 증가_값

MAXVALUE [상한_값 | NOMAXVALUE]

MINVALUE [하한_값 | NOMINVALUE]

CYCLE | NOCYCLE

CACHE cache_개수| NOCACHE;

 

DROP SEQUENCE 시퀀스;

 

SELECT sequence_name, max_value, min_value, increment_by,

          cache_size, last_number, cycle_flag

FROM user_sequences;

 

... 시퀀스.NEXTVAL ...
... 시퀀스.CURRVAL ...

 

[예제 1]실습용 시퀀스를 생성하고 생성된 시퀀스의 설정을 검색한다.
CREATE SEQUENCE emp_eno_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
CACHE 20;

// 거의 사용하지 않는다.

 

CREATE SEQUENCE dept_dno_seq;

// 이렇게만 써줘도 위의 명령어와 똑같이 생성.

 

SELECT sequence_name, max_value, min_value, increment_by,

          cache_size, last_number, cycle_flag

FROM user_sequences; 

-> 시퀀스를 조회하는 명령어

 

[예제 2]순환 시퀀스를 생성하고 생성된 시퀀스의 설정을 검색.
CREATE SEQUENCE co_cno_seq
MAXVALUE 5
CYCLE;

// cache값보다 크게 만들어야 함. 

 

CREATE SEQUENCE co_cno_seq

MAXVALUE 50

CYCLE; // cache값보다 더 커지니까 만들어 짐.

 

SELECT sequence_name, max_value, min_value, increment_by,
          cache_size, last_number, cycle_flag
FROM user_sequences;

 

[예제 3] [예제 1]에서 생성한 emp_eno_seq 시퀀스에게 값을 할당 받아 사용. 실습전 emp 테이블의 모든 데이터 삭제

DELETE FROM emp;

COMMIT;

 

SELECT sequence_name, max_value, min_value, increment_by,
          cache_size, last_number, cycle_flag
FROM user_sequences; // 시퀀스는 그대로 존재함.

 

INSERT INTO emp (eno, ename) VALUES (emp_eno_seq.nextval, '첫 번째');

 

SELECT emp_eno_seq.currval FROM dual;

 

SELECT eno, ename FROM emp;

 

ROLLBACK;

 

INSERT INTO emp (eno, ename) VALUES (emp_eno_seq.nextval, '두 번째');

 

COMMIT;

 

SELECT eno, ename FROM emp;

 

SELECT sequence_name, max_value, min_value, increment_by,
          cache_size, last_number, cycle_flag

FROM user_sequences; 

// emp_eno_seq의 값은 2개밖에 안 썼지만 LAST_NUMBER는 21이 나온다.

  내가 사용한 값이 아닌 내가 할당 받을 수 있는 번호중 가장 마지막 번호가 나옴.

 

* SQL에서 반드시 활용할 수 있어야 하는 것

1. 조인

 

2. ERD와 테이블 상세 도표를 보고 -> SQL문을 작성할 수 있어야함.

 

3. TO_CHAR : 날짜나 숫자를 문자로 변환. 출력 서식을 맞추고 싶을때, 내가 원하는 형태로 출력하고 싶을때 사용.

                  주로 SELECT 에서 많이 사용. 출력 서식을 지정하고 싶을때

   TO_DATE : 데이터를 날짜형으로 해석. 주로 INSERT나 WHERE절에서 많이 사용. 정확히 날짜로 해석할 수 있도록 사용

 

4. 그룹

 

* dictionary

user_tables : 테이블의 정보를 조회해 줌

user_constraints : 유저가 소유한 제약조건을 모두 조회

user_cons_columns : 컬럼에 할당된 제약조건을 조회

dba_tablespaces : 테이블의 스페이스를 확인할때 사용.

dba_users : DB에 있는 모든 사용자 목록을 조회

user_tab_columns  : 테이블, 뷰, 클러스터의 칼럼과 관련된 정보 조회

user_indexes : 테이블이 가지고 잇는 인덱스 목록 조회

user_ind_columns : 인덱스의 컬럼 조회

user_ind_experession : 함수 기반 인덱스의 컬럼 정보 확인

user_view : 뷰에 대한 정보를 조회

user_sequences : 생성된 시퀀스의 정보를 조회