📚복습문제

📖문제 1

SELECT NAME
     , TEL
     , POSITION
     , TO_CHAR(PAY,'999,999,999') "PAY"
     , RANK()OVER(PARTITION BY SUBSTR(TEL,1,INSTR(TEL,')')-1),
     POSITION  ORDER BY  PAY DESC) 
     FROM EMP2;

📖문제 2

SELECT NAME
     , BIRTHDAY
     , EMP_TYPE
     , TO_CHAR(PAY,'999,999,999')"PAY"
     , TO_CHAR(SUM(PAY) OVER(PARTITION BY EMP_TYPE),'999,999,999') "TOTAL"
FROM EMP2;

📖문제 3

SELECT
    GNAME,
    JUMIN,
    TO_CHAR(POINT, '999,999') "POINT",
    TO_CHAR(SUM(POINT)
            OVER(PARTITION BY SUBSTR(JUMIN, 1, 2)
                 ORDER BY POINT DESC),'999,999,999')    "TOTAL",
    ROUND(POINT / SUM(POINT)
                  OVER(PARTITION BY SUBSTR(JUMIN, 1, 2)) * 100,2) "RATIO(%)"
FROM customer
GROUP BY GNAME, JUMIN, POINT;

---

📚진도

📘(저번시간에 이어서)

저번 시간에 만든
SCORE2, SUBJECT2, STUDENT2 테이블 생성 및 데이터 추가 된 형태

• 조회

SELECT * FROM STUDENT2; -- A001 AA
SELECT * FROM SUBJECT2; -- 100 A1
SELECT * FROM SCORE2;

• SCORE2에 데이터 삽입

INSERT INTO SCORE2 VALUES('A000',111,60,'G'); -- 실행 안됨 G학번 없음
INSERT INTO SCORE2 VALUES('A000',111,60,'D'); -- 실행 안됨 111이란 소스 없음 100은 있음
INSERT INTO SCORE2 VALUES('A000',100,60,'D'); -- 실행안됨 A000안됨 A0001로 바꿈
INSERT INTO SCORE2 VALUES('A0001',100,60,'D'); -- 실행됨 SCORE2의 컬럼에 대한 값들 A0001 100 60 D

• 데이터 삭제를 시도

--데이터 삭제
DELETE FROM student2; -- 자식 데이터가 존재함.(SCORE2에 존재함) 
--그래서 하려면 SCORE2부터 DELETE 해야함
DELETE FROM SUBJECT2; -- 자식 데이터가 존재함.(SCORE2에 존재함) 
--그래서 하려면 SCORE2부터 DELETE 해야함
DELETE FROM SCORE2;

그러나
student2와 SUBJECT2는 삭제 안됨.(자식 데이터가 SCORE2에 존재하기 때문)
⇒ 삭제하려면 SCORE2를 삭제해아함.

이러는 과정이 너무 귀찮음.
굳이 자식을 지우고 부모를 지워야하는가? 한번에 부모, 자식데이터를 다 지우는 방법이 필요

📘ON DELETE CASCADE

DROP TABLE SCORE2; 실행하고,SCORE2 테이블 재생성한 다음

CREATE TABLE SCORE2(
    STU_ID CHAR(5) CONSTRAINT SCORE2_STU_ID_NN NOT NULL,
    SUB_ID NUMBER(3) CONSTRAINT SCORE2_SUB_ID_NN NOT NULL,
    SCORE NUMBER(3) CONSTRAINT SCORE2_SCORE_NN NOT NULL,
    GRADE CHAR(1) CONSTRAINT SCORE2_GRADE_CK CHECK(GRADE IN('A','B','C','D','F')),
    CONSTRAINT SCORE2_PK PRIMARY KEY(STU_ID, SUB_ID),
    --CONSTRAINT SCORE2_FK1 FOREIGN KEY (STU_ID) REFERENCES STUDENT2(STU_ID),
    --CONSTRAINT SCORE2_FK2 FOREIGN KEY (SUB_ID) REFERENCES SUBJECT2(SUB_ID),
    CONSTRAINT SCORE2_FK1 FOREIGN KEY (STU_ID) REFERENCES STUDENT2(STU_ID) ON DELETE CASCADE,
    CONSTRAINT SCORE2_FK2 FOREIGN KEY (SUB_ID) REFERENCES SUBJECT2(SUB_ID) ON DELETE CASCADE
);

실행

ON DELETE CASCADE 사용

실제로 DELETE FROM STUDENT2; 실행하면 STUDENT2의 데이터와 자식데이터를 가지고 있는 SCORE2의 데이터도 모두 사라짐

ROLLBACK;후 DELETE FROM SUBJECT2; 실시

그러면 SUBJECT2와 SCORE2의 값만 사라짐
(여기서 혹시나 싶어서 이야기 하지만 ROLLBACK;해서 STUDENT2는 데이터는 존재함)

📘브라우저

보기의 Data Modeler ⇒ 브라우저

브라우저에서 우클릭 ⇒ 데이터 저장 (D:\model생성하여 model로 저장)

그러면

관계형 모델에 Relationa_1이 생성됨

! 만약에 이 과정이 안되면 오라클 홈페이지에서 SQL Developer Data Modeler 를 다운 받아야함 !

새테이블 생성

그 후, Relationa_1의 우클릭 , 표시 클릭하면 보지 못한 메뉴들이 나옴


여기서 새테이블 클릭

테이블 명 userTBL을 작성한 뒤 적용 ⇒ 왼쪽 열 클릭하여 컬럼 추가 후 입력

이런식으로

사진과 같이 컬럼 생성 적용 후 확인 하면 아래 사진처럼 테이블이 생성 된다.

위의 방법과 동일하게 구매테이블도 생성

외래키 추가

사진과 같이 새 외래키 클릭후 userTBL 클릭 후 buyTBL 클릭 (드래그 아님!!!)

판별자 열을 userId로 바꾼 후
적용 후 확인

이제 필수가 아니라 선택사항이 됨

(쉽게 생각하면 그전에는 가입하면 무조건 물건 구매해야했는데,회원가입하고 무조건 물건을 구매하지 않아도 되게끔 만들었음.)

여기서 사진과 같이 우클릭해서 DDL 미리보기를 하면 테이블 생성 쿼리가 보여짐

이 쿼리로 테이블 생성해도 됨~!

DDL 생성

DDL 생성 클릭 후, 생성 클릭

그럼 Relationa_1이 나오는데, 체크후, 확인 누르면 아래와 같이 쿼리가 나옴

그후, 저장(D:\model에 modelSchema.ddl로 저장하고 새로운계정으로 생성한 ddl를 만들거임.

사용자 생성 (사용자 명 : MODEL , 기본테이블스페이스 USERS, 임시 테이블스페이스 TEMP로 지정)
그후, 부여된 룰에서 CONNECT, RESOURCE를 체크(권한이 부여됨, 기본값에만!)
그리고 할당량에 USERS 부분에 할당량 10, 단위 M 입력(10M)
그러면 다른 사용자에 MODEL이 생성된것을 확인 할 수있다.

그 후, 접속을 눌러서(아래사진) 저장후, 접속

그후 저장했던 modelSchema.ddl를 부름(local-model 접속)
그러면 테이블이 생성된것을 확인 할 수 있음

이렇게 쿼리를 파일로 만들어주면 다른 사람들에게 파일을 주거나 아니면 테이블 생성 및 옮기기 할때 유용하게 사용가능(굳이 쿼리 오타내면서 작성하지 안해도 됨)

또한 요약을 보고, 오라클 데이터 모델러 디자인을 생성할 수도 있음
(아래 사진 순서대로 따라하면 생성됨




(완료 클릭)
병합 클릭
그러면 Relationa_1에 기존과 동일한 새로운 테이블을 생성 할 수 있음

📘정규화

: 테이블의 속성들은 서로 종속적인 관계로, 종속관게의 특성을 활용하여 최적의 테이블로 분해하는 과정

• 목적 : 중복 제거하여 이상현상(삽입/삭제/갱신)의 발생을 줄임

• 정규화 단계

1. 1정규화(1NF) : 도메인이 원자값(더이상 쪼개지지 않는)
2. 2정규화(2NF) : 부분함수적 종속 제거(완전함수적 종속)
3. 3정규화(3NF) : 이행함수적 종속 제거

주문도서 확인 예시

위와 같이 테이블 생성
1.

: 고객은 무조건 주문하지 않아도 괜찮음(점선표시)

2.

주문정보가 있다는건 주문 회원이 있다는 말!
그래서 무조건 필수(실선)로 체크되어있음

3. 
   

책이 들어와야지 주문번호가 있음(점선)
주문을 하였지만 도서 재고가 없는 경우도 있을 수 있음

📚 JOIN (PPT Chapter 4)

Join

: 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것

SELECT COUNT(*) FROM EMP;  --12건
SELECT COUNT(*) FROM DEPT;  --4건

SELECT * FROM EMP;
SELECT * FROM DEPT;

--카티션 곱(Cartesian Product)
SELECT * FROM EMP,DEPT; --48건 (12*4)
-- 연결된 key가 없기 때문에 이렇게 나옴.

-- 등가 조인(EQUI Join)
SELECT * 
  FROM EMP A, DEPT B 
  WHERE A.DEPTNO = B.DEPTNO;  --12건
  -- 테이블간의 공통 컬럼을 활용하여 
  -- 각 테이블의 특정 컬럼에 일치한 데이터를 기준으로 연결(교집합)

-- Oravle Join (이면서 등가 조인)
SELECT ENAME, DNAME
  FROM EMP A, DEPT B 
  WHERE A.DEPTNO = B.DEPTNO;  --12건

-- ANSI JOIN (이면서 등가 조인)
SELECT ENAME, DNAME
  FROM EMP A JOIN DEPT B 
  ON A.DEPTNO = B.DEPTNO;  --12건

📖예제 문제 1

SELECT A.NAME "STU_NAME", B.NAME "PROF_NAME"
  FROM STUDENT A, PROFESSOR B 
  WHERE A.PROFNO = B.PROFNO;

둘 다 동일함

SELECT A.NAME "STU_NAME", B.NAME "PROF_NAME"
  FROM STUDENT A JOIN PROFESSOR B 
  ON A.PROFNO = B.PROFNO;

📖예제 문제 2

-- 2가지 케이스(학생 부서번호1, 교수 부서번호)
SELECT A.NAME "STU_NAME"
     , B.NAME "PROF_NAME"
     , C.DNAME DEPARTMENT
  FROM STUDENT A
  JOIN PROFESSOR B 
  ON A.PROFNO = B.PROFNO
  JOIN DEPARTMENT C
  ON  A.DEPTNO1 = C.DEPTNO; --15건 , 기준 테이블을 STUDENT로 잡음

SELECT A.NAME "STU_NAME", B.NAME "PROF_NAME"
  FROM STUDENT A , PROFESSOR B, DEPARTMENT C
  WHERE A.PROFNO = B.PROFNO
  AND  B.DEPTNO = C.DEPTNO
  ORDER BY A.NAME; --15건 , 기준 테이블을 PROFESSOR로 잡음

SELECT A.NAME "STU_NAME", B.NAME "PROF_NAME"
  FROM STUDENT A , PROFESSOR B, DEPARTMENT C
  WHERE A.PROFNO = B.PROFNO
  AND A.DEPTNO1 = C.DEPTNO
  AND B.DEPTNO = C.DEPTNO 
  ORDER BY A.NAME; --14건(사진과 다름)

-- 이런 데이터 차이가 나는 이유 : 기준 테이블이 차이 나기 떄문
SELECT * FROM STUDENT;  --Steve Martin 201 4003
SELECT * FROM DEPARTMENT;
SELECT * FROM PROFESSOR; --Nicole Kidman 202
-- 여기서 차이가 남. 그래서 기준 테이블에 따라서 값이 다르게 나올 수있다.

본인은

SELECT A.NAME "STU_NAME"
     , B.NAME "PROF_NAME"
     , C.DNAME DEPARTMENT
  FROM STUDENT A
  JOIN PROFESSOR B 
  ON A.PROFNO = B.PROFNO
  JOIN DEPARTMENT C
  ON  A.DEPTNO1 = C.DEPTNO;

방법 사용

📖예제 문제 3

SELECT S.NAME "STU_NAME"
     , P.NAME "PROF_NAME"
  FROM STUDENT S
  JOIN PROFESSOR P
    ON  S.PROFNO = P.PROFNO
	 -- WHERE S.DEPTNO1=101 ;
    AND  S.DEPTNO1=101;

SELECT S.NAME "STU_NAME"
     , P.NAME "PROF_NAME"
  FROM STUDENT S
  , PROFESSOR P
   WHERE  S.PROFNO = P.PROFNO
    AND  S.DEPTNO1=101;

📖예제 문제 4

내 방법

SELECT C.GNAME "CUST_NAME"
     , TO_CHAR(POINT,'999,999')"POINT"
     , G.GNAME "GIFT_NAME"
  FROM CUSTOMER C, GIFT G 
  WHERE G_START< POINT AND POINT<G_END;

SELECT C.GNAME "CUST_NAME"
     , TO_CHAR(POINT,'999,999')"POINT"
     , G.GNAME "GIFT_NAME"
  FROM CUSTOMER C JOIN GIFT G 
  ON G_START< POINT AND POINT<G_END;

강사님 방법(BETWEEN ..AND )

-- 비등가 조인 (Non-Equi Join)
SELECT C.GNAME "CUST_NAME"
     , TO_CHAR(POINT,'999,999')"POINT"
     , G.GNAME "GIFT_NAME"
 FROM CUSTOMER C , GIFT G 
 WHERE C.POINT BETWEEN G_START AND G_END;

📖예제 문제 5

SELECT S.NAME "STU_NAME"
     , C.TOTAL
     , H.GRADE "CREDIT"
  FROM STUDENT S, SCORE C ,HAKJUM H
  WHERE S.STUDNO = C.STUDNO 
  AND C.TOTAL BETWEEN MIN_POINT AND MAX_POINT;

SELECT S.NAME "STU_NAME"
     , C.TOTAL
     , H.GRADE "CREDIT"
  FROM STUDENT S, SCORE C ,HAKJUM H
  WHERE S.STUDNO = C.STUDNO 
  AND C.TOTAL >= MIN_POINT 
  AND C.TOTAL <= MAX_POINT;

📖LEFT (OUTER) JOIN

-- LEFT (OUTER) JOIN
SELECT S.NAME "STU_NAME"
     , P.NAME "PROF_NAME"
  FROM STUDENT S, PROFESSOR P
  WHERE S.PROFNO = P.PROFNO(+);
  -- (+)를 하면 NULL이 출력됨.

📖LEFT (OUTER) JOIN

SELECT S.NAME "STU_NAME"
     , P.NAME "PROF_NAME"
  FROM STUDENT S, PROFESSOR P
  WHERE S.PROFNO = P.PROFNO(+);
  -- (+)를 하면 NULL이 출력됨.

📖(INNER) JOIN = EQUI JOIN

-- (INNER) JOIN = EQUI JOIN
SELECT S.NAME "STU_NAME"
     , P.NAME "PROF_NAME"
  FROM STUDENT S, PROFESSOR P
  WHERE S.PROFNO = P.PROFNO;

📖예제 문제 6

위 사진 처럼 쿼리 짜라
(참고 쿼리)

SELECT NAME
  FROM STUDENT;  --20건
  
SELECT NAME
  FROM PROFESSOR;  --16건

내가 한 방법(서치함.)

SELECT S.NAME "STU_NAME"
     , P.NAME "PROF_NAME"
  FROM STUDENT S FULL JOIN PROFESSOR P
  ON S.PROFNO = P.PROFNO
  ORDER BY S.NAME ;

교수님 방법

SELECT S.NAME "STU_NAME"
     , P.NAME "PROF_NAME"
  FROM STUDENT S ,PROFESSOR P
  WHERE S.PROFNO = P.PROFNO(+)
 UNION
SELECT S.NAME "STU_NAME"
     , P.NAME "PROF_NAME"
  FROM STUDENT S ,PROFESSOR P
  WHERE S.PROFNO(+) = P.PROFNO;

또 다른 방법

SELECT S.NAME "STU_NAME"
     , P.NAME "PROF_NAME"
  FROM STUDENT S FULL OUTER JOIN PROFESSOR P
  ON S.PROFNO = P.PROFNO ;

📖예제 문제 7

SELECT * FROM EMP; 참고하여 아래 사진처럼 출력하시오

-- SELF JOIN
SELECT A.EMPNO  "ENO"
     , A.ENAME "ENAME"
     , B.EMPNO "MGRNO"
     , B.ENAME "MGR_NAME"
  FROM EMP A ,EMP B
  WHERE A.MGR = B.EMPNO;

📖예제 문제 8

-- ANSI JOIN
SELECT NAME "STU_NAME"
     , DEPTNO1
     , DNAME "DEPT_NAME"
  FROM STUDENT A JOIN DEPARTMENT B 
  ON A.DEPTNO1 = B.DEPTNO;

-- Oravle Join
SELECT NAME "STU_NAME"
     , DEPTNO1
     , DNAME "DEPT_NAME"
  FROM  STUDENT A, DEPARTMENT B 
  WHERE A.DEPTNO1 = B.DEPTNO;

📖예제 문제 9

SELECT A.NAME
     , B.POSITION
     , TO_CHAR(A.PAY,'999,999,999')"PAY"
     , TO_CHAR(B.S_PAY,'999,999,999')"Low PAY"
     , TO_CHAR(B.E_PAY,'999,999,999')"High PAY"
  FROM EMP2 A ,P_GRADE B 
  WHERE A.POSITION = B.POSITION;