원래는 js나 jQurey를 해야하는데 일차적으로 진도를 나감 + 평가시험 실시로 인해서 jsp와 오라클 진행

📚Oracle 복습 문제

📘문제 1

SELECT NAME,
       EMAIL,
       SUBSTR(EMAIL,INSTR(EMAIL,'@')+1) "DOMAIN",
       PAY,
       RANK()OVER(PARTITION BY SUBSTR(EMAIL,INSTR(EMAIL,'@')+1)
                  ORDER BY PAY DESC)"RANK"
FROM PROFESSOR
WHERE EMAIL IS NOT NULL;

📘문제 2

SELECT NAME,
       HPAGE,
       SUBSTR(EMAIL,INSTR(EMAIL,'@')+1) "DOMAIN",
       PAY,
       SUM(PAY)OVER(PARTITION BY SUBSTR(EMAIL,INSTR(EMAIL,'@')+1) 
                    ORDER BY NAME )"TOTAL"
FROM PROFESSOR
WHERE HPAGE IS NOT NULL;

강사님 방법

SELECT NAME,
       HPAGE,
       SUBSTR(HPAGE,INSTR(HPAGE,'.')+1) "DOMAIN",
       PAY,
       SUM(PAY)OVER(PARTITION BY SUBSTR(EMAIL,INSTR(EMAIL,'@')+1) 
                    ORDER BY NAME )"TOTAL"
FROM PROFESSOR
WHERE HPAGE IS NOT NULL;

📘문제 3

SELECT NAME,
       TEL,
       WEIGHT,
       SUM(WEIGHT)OVER(PARTITION BY  SUBSTR(TEL,1,INSTR(TEL,')')-1)
                       ORDER BY WEIGHT DESC )"TOTAL",
       ROUND(WEIGHT/SUM(WEIGHT)
                    OVER(PARTITION BY SUBSTR(TEL,1,
                                      INSTR(TEL,')')-1))*100,2)"RATIO(%)"
FROM STUDENT
GROUP BY NAME,TEL,WEIGHT;

강사님 방법

SELECT NAME,
       TEL,
       WEIGHT,
       SUM(WEIGHT)OVER(PARTITION BY  SUBSTR(TEL,1,INSTR(TEL,')')-1)
                       ORDER BY WEIGHT DESC )"TOTAL",
       ROUND(RATIO_TO_REPORT(SUM(WEIGHT))
                    OVER(PARTITION BY SUBSTR(TEL,1,
                                      INSTR(TEL,')')-1))*100,2)"RATIO(%)"
FROM STUDENT
GROUP BY NAME,TEL,WEIGHT;

📘문제 4

SELECT NAME,
       BIRTHDAY,
       TRUNC((SYSDATE-BIRTHDAY)/365,0) "AGE",
       A.POSITION "CURR_POSITION",
       B.POSITION
FROM EMP2 A, P_GRADE B 
WHERE TRUNC((SYSDATE-BIRTHDAY)/365,0) BETWEEN S_AGE AND E_AGE
ORDER BY BIRTHDAY DESC;

📘문제 5

내 방법

SELECT C.GNAME "CUST_NAME" ,
       C.POINT "POINT" ,
       G.GNAME "GIFT_NAME"
FROM CUSTOMER C, GIFT G 
WHERE POINT >= 600001
AND G.GNAME = 'Notebook';

강사님 방법

SELECT C.GNAME "CUST_NAME" ,
       C.POINT "POINT" ,
       G.GNAME "GIFT_NAME"
FROM CUSTOMER C, GIFT G 
WHERE G.G_START<C.POINT
AND G.GNAME = 'Notebook';

📘문제 6

SELECT P1.PROFNO ,
       P1.NAME ,
       TO_CHAR(P1.HIREDATE,'YYYY/MM/DD') "HIREDATE",
       COUNT(P2.HIREDATE) "COUNT"
FROM PROFESSOR P1,PROFESSOR P2
WHERE P2.HIREDATE (+)< P1.HIREDATE
GROUP BY P1.PROFNO,P1.NAME,P1.HIREDATE
ORDER BY 4;

📘문제 7

SELECT E1.EMPNO ,
       E1.ENAME ,
       TO_CHAR(E1.HIREDATE,'YY/MM/DD') "HIREDATE",
       COUNT(E2.HIREDATE) "COUNT"
FROM EMP E1,EMP E2
WHERE E1.HIREDATE > E2.HIREDATE(+)
GROUP BY E1.EMPNO ,E1.ENAME ,E1.HIREDATE
ORDER BY 4;

📚VIEW(Chapter 9 ppt)

view

: 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블.(저장장치내엔 물리적으로 존재x)

view생성

중요한게 처음엔 권한이 없어서 view 생성이 되지 않음. 그래서 권한을 주기 위해서
cmd창에 sqlplus / as sysdba;으로 연결후 grant create view(이름) to user명;하여 권한 부여

CREATE VIEW V_EMP1
AS 
SELECT EMPNO, ENAME, HIREDATE, DEPTNO
  FROM EMP;

view수정

수정할때마다 VIEW를 삭제하고 다시 만들어야 하는가?
DEPTNO를 없앤다고 한다면

CREATE OR REPLACE VIEW V_EMP1
AS 
SELECT EMPNO, ENAME, HIREDATE
  FROM EMP;

실행

그럼 DEPTNO가 사라짐을 확인 할 수 있음
이때, 만약에 권한을 뺏고싶으면
REVOKE CREATE VIEW FROM user명;
을 사용함

(REVOKE CREATE VIEW FROM user명; 사용시

오류 발생함)

📚References

• https://hoon93.tistory.com/32