VIEW
1. 인덱스 생성 안됨
CREATE INDEX IDX_V_EMP1_ENAME(인덱스명)
ON V_EMP1(ENAME)(테이블명(칼럼명));2. 단순 뷰(Simple View)와 복합 뷰(Complex View)
--단순 뷰(Simple View)
CREATE OR REPLACE FORCE VIEW "SCOTT"."V_MEP1" ("EMPNO","ENAME","HIREDATE","DEPTNO")AS
SELECT EMPNO, ENAME, HIREDATE, DEPTNO
FROM EMP;
//테이블 하나
--복합 뷰(Complex View)
CREATE OR REPLACE VIEW V_EMP2
AS
SELECT E.EMPNO,
D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
//테이즐 2개예제1
⭐ 내가 한 방법
SELECT D.DEPTNO,
D.DNAME,
MAX(E.SAL) AS SAL
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
group by D.DEPTNO, D.DNAME;⭐ 강사님 방법(인라인 뷰)
--인라인 뷰(INLINE VIEW)
SELECT E.DEPTNO, D.DNAME, E.SAL
FROM (SELECT DEPTNO, MAX(SAL) SAL
FROM EMP
GROUP BY DEPTNO
)E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;인라인 뷰(INLINE VIEW)
인라인 뷰
--인라인 뷰(INLINE VIEW)
SELECT E.DEPTNO, D.DNAME, E.SAL
FROM (SELECT DEPTNO, MAX(SAL) SAL
FROM EMP
GROUP BY DEPTNO
)E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
### 예제2
문제 2
`SELECT DEPTNO, PROFNO, NAME FROM PROFESSOR;` 를 이용하여 그림과 같이 나타내라
⭐ 내방법
```sql
SELECT DECODE(LAG(DEPTNO)OVER(ORDER BY DEPTNO)
,DEPTNO,NULL,DEPTNO)"DEPTNO",
PROFNO,
NAME
FROM PROFESSOR;⭐ 강사님 방법
SELECT DECODE(DEPTNO,NDEPTNO,NULL,DEPTNO) "DEPTNO"
,PROFNO
,NAME
FROM (SELECT LAG(DEPTNO)OVER(ORDER BY DEPTNO)NDEPTNO,
DEPTNO,
PROFNO,
NAME
FROM PROFESSOR
);예제3
PROFESSOR 테이블과 DEPARTMENT 테이블을 JOIN하여 교수번호와 교수명, 소속 학과이름을 조회하는 VIEW를 생성하시오.(VIEW이름은 V_PROF_DEPT)
⭐ 내방법
CREATE OR REPLACE VIEW V_PROF_DEPT
AS
SELECT P.PROFNO,
P.NAME,
D.DNAME
FROM PROFESSOR P, DEPARTMENT D
WHERE P.DEPTNO = D.DEPTNO;⭐ 교수님 방법
CREATE OR REPLACE VIEW V_PROF_DEPT
AS
SELECT P.PROFNO "교수번호",
P.NAME "교수명",
D.DNAME "소속학과명"
FROM PROFESSOR P, DEPARTMENT D
WHERE P.DEPTNO = D.DEPTNO;예제4
⭐ 내방법(인라인뷰 아님)
SELECT D.DNAME,
MAX(S.HEIGHT) AS MAX_HEIGHT,
MAX(S.WEIGHT) AS MAX_WEIGHT
FROM STUDENT S JOIN DEPARTMENT D
ON S.DEPTNO1 = D.DEPTNO group by D.DNAME;⭐ 교수님 방법
SELECT D.DNAME,
S.MAX_HEIGHT,
S.MAX_WEIGHT
FROM (SELECT DEPTNO1, MAX(HEIGHT) MAX_HEIGHT ,MAX(WEIGHT) MAX_WEIGHT
FROM STUDENT group by DEPTNO1
)S, DEPARTMENT D
WHERE S.DEPTNO1 = D.DEPTNO;예제5
SELECT D.DNAME,
A.MAX_HEIGHT,
S.NAME,
S.HEIGHT
FROM (SELECT DEPTNO1
, MAX(HEIGHT) MAX_HEIGHT
FROM STUDENT group by DEPTNO1) A, STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = A.DEPTNO1
AND S.HEIGHT = A.MAX_HEIGHT
AND S.DEPTNO1 = D.DEPTNO;예제6
⭐ 내방법
SELECT A.GRADE,
S.NAME,
S.HEIGHT,
A.AVG_HEIGHT
FROM (SELECT GRADE
, AVG(HEIGHT) AVG_HEIGHT
FROM STUDENT GROUP BY GRADE) A, STUDENT S
WHERE S.GRADE = A.GRADE
AND S.HEIGHT > A.AVG_HEIGHT
ORDER BY A.GRADE ASC;⭐ 강사님 방법
SELECT S.GRADE,
S.NAME,
S.HEIGHT,
A.AVG_HEIGHT
FROM (SELECT GRADE
, AVG(HEIGHT) AVG_HEIGHT
FROM STUDENT GROUP BY GRADE) A, STUDENT S
WHERE S.GRADE = A.GRADE
AND S.HEIGHT > A.AVG_HEIGHT
ORDER BY 1;예제7
⭐내 방법
SELECT A.Ranking,
A.NAME,
B.PAY
FROM (SELECT NAME, RANK()OVER(ORDER BY PAY DESC) Ranking
FROM PROFESSOR) A, PROFESSOR B
WHERE A.NAME = B.NAME
AND Ranking<6 ORDER BY RANKING;⭐강사님 방법
SELECT rownum "Ranking"
,NAME, PAY
FROM (SELECT NAME
, PAY
FROM PROFESSOR
ORDER BY 2 DESC)
WHERE ROWNUM BETWEEN 1 AND 5;SEQUENCE(PPT 11장)
SEQUENCE생성
CREATE SEQUENCE JNO_SEQ
INCREMENT BY 1
START WITH 100
MAXVALUE 110
MINVALUE 90
CYCLE
CACHE 2;SELECT JNO_SEQ.CURRVAL FROM DUAL; --100,101,102
-- CURRVAL : 현재 값을 알려줌
SELECT JNO_SEQ.NEXTVAL FROM DUAL; --100,101,102
-- NEXTVAL : 다음값을 알려줌SEQUENCE 변경
--시퀀스 변경
ALTER SEQUENCE JNO_SEQ
MAXVALUE 120 --최댓값변경
NOCYCLE --반복X
CACHE 10; --캐시사이즈SEQUENCE 삭제
--시퀀스 삭제
DROP SEQUENCE JNO_SEQ;SYNONYM
동의어
생성시 권한 필요!!!
- CMD 창 쳐서
sqlplus / as sysdba: 시스템 계정으로 접속
grant create synonym to scott; 입력
cott계정에게 synonym 를 생성할 수있는 권한을 부여
CREATE SYNONYM E FOR EMP; -- SYNONYM : 동의어
SELECT * FROM E;
데이터 사전으로 확인
-- 데이터 사전으로 확인
SELECT * FROM USER_SYNONYMS;
- SYNONYM 삭제
-- SYNONYM 삭제
DROP SYNONYM E;Sub Query(PPT 10장)
: 조건절 안에 괄호으로 들어온 쿼리
보통 인라인 뷰로 많이 사용
연산자 종류
-
=,<>,>,>=,<,<=
-
IN, EXISTS, >ANY , <ANY, <ALL, >ALL
>ANY 최소값보다 큰
<ALL 최소값보다 작은
<ANY 최대값보다 작다
>ALL 최대값보다 크다.
예제 1
SELECT NAME,
POSITION,
TO_CHAR(PAY,'$999,999,999') "SALARY"
FROM EMP2 A WHERE PAY >= (SELECT AVG(PAY)
FROM EMP2 B
WHERE A.POSITION = B.POSITION);예제 2
SELECT S.NAME "STUD_NAME"
, D.DNAME "DEPT_NAME"
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = D.DEPTNO
AND S.DEPTNO1 = (SELECT DEPTNO1
FROM STUDENT S
WHERE NAME='Anthony Hopkins');예제 3
SELECT P.NAME "PROF_NAME",
P.HIREDATE,
D.DNAME "DEPT_NAME"
FROM PROFESSOR P, DEPARTMENT D
WHERE P.DEPTNO = D.DEPTNO
AND P.HIREDATE > (SELECT HIREDATE
FROM PROFESSOR P
WHERE NAME='Meg Ryan');예제 4
⭐ 내방법
SELECT NAME,
WEIGHT
FROM STUDENT A
WHERE A.WEIGHT > (SELECT AVG(WEIGHT)
FROM STUDENT B
WHERE DEPTNO1 = 201);⭐ 강사님 방법
SELECT NAME,
WEIGHT
FROM STUDENT
WHERE WEIGHT > (SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 201);입력창
&DNO
SELECT DEPTNO
FROM DEPT
WHERE DEPTNO = &DNO;
입력창에 10입력
SELECT *
FROM DEPT
WHERE DEPTNO =(SELECT DEPTNO
FROM DEPT
WHERE DEPTNO = &DNO);
서브 쿼리로도 입력 받을 수있음
++ EXISTS
-- EXISTS
SELECT *
FROM DEPT
WHERE EXISTS (SELECT DEPTNO
FROM DEPT
WHERE DEPTNO = &DNO);
EXISTS(): 서브 쿼리의 결과가 "한 건이라도 존재하면" TRUE, 없으면 FALSE를 리턴
여기서는 앞의 쿼리가 SELECT * FROM DEPT이므로 모든 데이터를 출력함예제 1
SELECT EMPNO,
NAME,
DEPTNO
FROM EMP2
WHERE DEPTNO IN (SELECT DCODE
FROM DEPT2
WHERE AREA = 'Pohang Main Office');예제 2
SELECT NAME,
POSITION,
TO_CHAR(PAY,'$999,999,999')"SALARY"
FROM EMP2
WHERE PAY >ANY (SELECT PAY
FROM EMP2
WHERE POSITION ='Section head');예제 3
SELECT NAME,
GRADE,
WEIGHT
FROM STUDENT
WHERE WEIGHT <ALL(SELECT WEIGHT
FROM STUDENT
WHERE GRADE = 2);예제 4
SELECT D.DNAME,
E.NAME,
TO_CHAR(E.PAY,'$999,999,999') "SALARY"
FROM EMP2 E,DEPT2 D
WHERE E.DEPTNO = D.DCODE
AND E.PAY <ALL (SELECT AVG(PAY)
FROM EMP2
GROUP BY DEPTNO)
ORDER BY 3;예제 5
⭐ 내방법
SELECT GRADE,
NAME,
WEIGHT
FROM STUDENT
WHERE WEIGHT IN (SELECT MAX(WEIGHT)
FROM STUDENT
GROUP BY GRADE)
ORDER BY 1;⭐ 강사님 방법
SELECT GRADE,
NAME,
WEIGHT
FROM STUDENT
WHERE (GRADE,WEIGHT) IN (SELECT GRADE,
MAX(WEIGHT)
FROM STUDENT
GROUP BY GRADE)
ORDER BY 1;예제 6
SELECT P.PROFNO "PROFNO",
P.NAME "PROF_NAME",
P.HIREDATE,
D.DNAME "DEPT_NAME"
FROM PROFESSOR P, DEPARTMENT D
WHERE P.DEPTNO = D.DEPTNO
AND (P.DEPTNO,P.HIREDATE) IN (SELECT DEPTNO,MIN(HIREDATE)
FROM PROFESSOR
GROUP BY DEPTNO)
ORDER BY 3;