📚복습

📖문제 1

SELECT
    email,
    decode(instr(email,'@'),8,(replace(email,substr(email,1,7),'???????'))
    ,7
    ,(replace(email,substr(email,1,7),'??????'))
    ,5
    ,(replace(email,substr(email,1,4),'????'))
    )"EMAIL)?"
    ,HPAGE
    ,DECODE(INSTR(HPAGE,'.',1,2)-(INSTR(HPAGE,'.',1,1)),4
     ,(replace(HPAGE,substr(HPAGE,INSTR(HPAGE,'.',1,1)+1,3),'***'))
     ,(replace(HPAGE,substr(HPAGE,INSTR(HPAGE,'.',1,1)+1,5),'*****'))
     )"HPAGE_*"
     FROM PROFESSOR
     WHERE HPAGE IS NOT NULL;

📖문제 2

SELECT EMPNO,
       ENAME,
       COMM,
       CASE 
           WHEN NVL(COMM,0) > 0 THEN '수당 : '||NVL(COMM,0)
           WHEN COMM IS NULL THEN '해당사항 없음'
           WHEN NVL(COMM,0) = 0 THEN '수당없음'
       END "COMM_TEXT"
       FROM EMP;

📖문제 3

SELECT
    NAME,
    HEIGHT,
    RANK() OVER(ORDER BY HEIGHT DESC)"RANK",
    DENSE_RANK() OVER(ORDER BY HEIGHT DESC)"RANK2",
    ROW_NUMBER() OVER(ORDER BY HEIGHT DESC)"RANK3"
FROM
    STUDENT
    WHERE HEIGHT >= 170;

📖문제 4

SELECT NAME,
       JUMIN,
       DEPTNO1,
       DECODE(SUBSTR(JUMIN,7,1),1,'M','F')"MF",
       WEIGHT,
       SUM(WEIGHT)OVER(PARTITION BY DEPTNO1, SUBSTR(JUMIN,7,1) ORDER BY WEIGHT DESC) "TOTAL"
FROM
    STUDENT;

📖문제 5

SELECT SUBSTR(EMAIL,INSTR(EMAIL,'@')+1)"DOMAIN",
        COUNT(*)"EA",
        SUM(COUNT(*)) OVER()"SUM_DOMAIN",
        COUNT(*)/SUM(COUNT(*))OVER()*100"%"
FROM PROFESSOR
group by SUBSTR(EMAIL,INSTR(EMAIL,'@')+1)
ORDER BY SUBSTR(EMAIL,INSTR(EMAIL,'@')+1);

📖문제 6

SELECT NAME,
       TEL,
       TO_CHAR(PAY,'999,999,999')"PAY",
       TO_CHAR(SUM(PAY)OVER(PARTITION BY SUBSTR(TEL,1,INSTR(TEL,')')-1)),'999,999,999')"TOTAL_AREA",
       ROUND(PAY/SUM(PAY)OVER(PARTITION BY SUBSTR(TEL,1,INSTR(TEL,')')-1))*100,2) "RATIO(%)"
FROM EMP2
GROUP BY NAME,TEL,PAY;

📖문제 7

CREATE TABLE PROFESSOR2
(PROFNO NUMBER(8)
,NAME VARCHAR2(30)
,ID VARCHAR2(20)
,POSITION VARCHAR2(50)
,PAY NUMBER(5)
,HIREDATE DATE
,BONUS NUMBER(5)
,DEPTNO NUMBER(3)
,EMAIL VARCHAR2(100)
,HPAGE VARCHAR2(100)
);

📖문제 8

CREATE TABLE PROFESSOR3
AS 
SELECT PROFNO
     , NAME
     , EMAIL FROM PROFESSOR;

📖문제 9

📖문제 10

ALTER TABLE PROFESSOR3
RENAME COLUMN CARD_YN TO CHECK_CARD_YN;

📖문제 11

ALTER TABLE PROFESSOR3
RENAME COLUMN CARD_YN TO CHECK_CARD_YN;

📖문제 12

ALTER TABLE PROFESSOR3
DROP COLUMN CHECK_CARD_YN;

---

📚데이터 사전

• 데이터베이스가 생성되는 시점에 자동으로 만들어짐
• 오라클은 DDL명령어(예. CREATE DATABASE, ALTER TABLE...)실행 될때마다 데이터 사전을 Access
• DB작업 동안 오라클은 데이터 사전을 읽어 객체의 존재여부와 사용자에게 적합한 Access 권한이 있는지 확인
• 오라클은 데이터 사전을 계속 갱신하여 DB구조, 감사, 사용자권한, 데이터등의 변경 사항을 반영한다.

📘데이터 사전의 종류

1. ALL_XXXX
   : 접속한 계정에 상관없이 사용 가능한 모든 객체 정보
   (현재 데이터베이스에 접속한 사용자가 소유한 객체 또는 다른 사용자가 소유한 객체 중 사용 허가를 받은 객체)

2. USER_XXXX
   : 현재 데이터베이스에 접속한 사용자가 소유한 객체 정보

3. DBA_XXXX
   : 데이터베이스 관리를 위한 정보(데이터베이스 관리 권한을 가진 SYSTEM, SYS 사용자만 열람 가능)

📘SYSTEM 계정 만들기 및 예제

SELECT * FROM dba_tablespaces;

등등을 볼수있음

SELECT * FROM USER_TABLES;

📚테이블스페이스(TableSpace)

• 테이블이 저장되는 논리적인 공간(파일은 물리적인 공간)

-테이블스페이스를 생성하면 정의된 용량만큼 미리 확보한 테이블스페이스가 생성

-게시판 테이블

• 기본용량/ 자동확장
• 게시판 데이터 저장
• 용량 초과시 자동적으로 확장

• TABLESPACE는 자신의 용량 이상의 데이터를 가지면 자동 확장을 함. 자동 확장된 용량은 데이터의 용량이 줄어도 늘어난 상태를 유지한다.
• 기본적 테이블 스페이스, 템프테이블 스페이스
• Create tablespace 테이블 스페이스 DATAFILE ‘경로/파일명’

📘공간 관리

블록<익스텐트<세그먼트 < 데이터스페이스
(논리 단위의 규모)

• 블록
  데이터 파일 I/O의 가장작은 단위
  DB를 구성하는 가장 최소 저장 공간으로 실제 데이터가 저장되는 공간

• 익스텐트
  익스텐트는 여러 연속적인 블록으로 하나의 세그먼트에 할당된 공간을 말하기도 한다.
  각 테이블에 대해 하나 이상의 익스텐트가 모인 것
  인덱스에 대해서는 하나이상의 익스텐트가 모인 것

• 세그먼트
  익스텐트의 집합
  세그먼트에서 기존 공간이 완전히 사용되면 새로운 익스텐스를 생성

📘테이블 스페이스 생성 명령

CREATE TABLESPACE storm
DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\app_data.dbf' 
SIZE 100M autoextend on next 10M maxsize 500M
DEFAULT STORAGE 
(
  INITIAL    10K
  NEXT      10K
  MINEXTENTS 2
  MAXEXTENTS 50
  PCTINCREASE 50
);

DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\app_data.dbf' : 테이블스페이스를 저장할 데이터 파일의 이름과 데이터 경로지정

SIZE 100M : 기본 테이블스페이스 사이즈 100M로 지정함

autoextend : 공간이 부족할 때 자동으로 확장

📘USER 생성 명령

(SYSTEM 계정에서 실행)

CREATE USER [사용자 계정]
IDENTIFIED BY [비밀번호]
DEFAULT TABLESPACE [기본적으로 사용할 테이블 스페이스 이름]
TEMPORARY TABLESPACE [임시로 사용한 기억공간](위의 테이블스페이스이름이 날아가는 것을 방지하기 위해 사용)
QUOTA [용량] ON [테이블스페이스명];

📘테이블스페이스 수정 명령

예시

ALTER DATABASE DATAFILE 
'데이터 파일경로' 
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

: 자동 확장 100M 최대 공간은 제약 없음

ALTER TABLESPACE STORM ADD DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\app_data2.dbf' SIZE 50M;

: 앞의 데이터가 꽉차면 2번 파일로 이동

🔍 테이블 스페이스를 설정하지 않고 테이블을 만들면 스키마 생성 유저의 default tablespace에 생성이 되고, 해당 디폴트 테이블 스페이스가 수용가능한 영역까지 데이터를 넣을 수있다.

📘접속 권한 부여와 삭제

• 접속 권한

--접속 권한 부여
GRANT CREATE SESSION TO TEST1;

그러나 위의 코드만 입력하고 테이블 생성하고자 하면 테이블 생성이 안되는걸 확인할수있음. 그러면 어떻게 해야하나?

테이블도 권한을 주면 가능한

--테이블 생성권한 부여
GRANT CREATE TABLE TO TEST1;

• 접속 권한 해제

-- 권한 해제
REVOKE CREATE TABLE FROM TEST1;

• 권한 확인

SELECT * FROM DBA_SYS_PRIVS 
WHERE GRANTEE = 'TEST1';(권한을 부여받은 테이블명)

📚제약조건(PPT Chapter 07)❗

• 매우 중요하지만 어려움. 이해를 잘해야함.

📘제약 조건의 종류

1. NOT NULL(NN)
   NULL 안됨(한번은 허용하나, 두번부터는 안됨)(필수입력사항)

2. CHECK(CK)
   입력범위을 직접 지정해 주는 기능
   (주어진 조건에 해당하는 값만 입력가능)

3. UNQUE
   중복성 배제, 즉 '유일한 값'으로 존재해야함.

4. PRIMARY KEY(PK)
   NOT NULL+UNIQE로, 테이블에서 대표되는 컬럼
   보통 중요한 데이터는 PK로 설정함
   (PK로 하지 않으면 중복 데이터를 넣을 수있기에 오류가 생길 수 있음)

5. FOREIGN KEY
   참조하는 테이블에서 존재하는 값만 사용가능
   만약에 참조하는 테이블에 해당되는 값이 없을땐 오류가 날수있음
   중복적으로 적용가능

📖예제

CREATE TABLE NEW_EMP2
(NO NUMBER(4) PRIMARY KEY
,NAME VARCHAR2(20) NOT NULL
,JUMIN VARCHAR2(13) NOT NULL UNIQUE
,LOC_CODE NUMBER(1) CHECK(LOC_CODE<5)
,DEPTNO VARCHAR2(6) REFERENCES DEPT2(DCODE)
);

📘제약 조건 추가 (ADD,MODIFY)

1. ADD

ALTER TABLE NEW_EMP2
 ADD CONSTRAINT NEW_EMP2_NAME_UK UNIQUE(NAME);
 -- NEW_EMP2_NAME_UK는 EMP2의 네임에 UK를 걸었다라고 의미 부여하는거임(자기가 알아서 작성하는 부분임)

추가됨을 확인 할수있음

2. MODIFY

ALTER TABLE NEW_EMP2
MODIFY(LOC_CODE CONSTRAINT NEW_EMP2_LOC_CODE_NN NOT NULL); 
 -- NEW_EMP2_LOC_CODE_NN는 LOC칼럼을 NN으로 바꾸겠다는 뜻

근데 보면 변경이 되는게 아니라 추가됨을 확인 할 수있다.(기존 데이터가 존재함)

그 말은 삭제코드가 따로 존재함. 근데 굳이 데이터 삭제하고 축가하기 귀찮으니깐 보통은 테이블을 삭제하고(DROP TABLE 테이블명;) 다시 제약 조건에 따라서 테이블을 다시만드는게 효율적임

📘PRIMARY KEY 복사

CREATE TABLE T_ENABLE2
AS 
SELECT * FROM T_ENABLE;

보면 PK로 한 데이터는 복사되지 않음

📘제약 조건 활성화/비활성화

원래

INSERT INTO T_ENABLE2 VALUES(3,''); 하면 오류 발생(이유 : NULL값이니깐) 하는데 제약조건을 사용못하게 만들어봄

1. DISABLE (제약조건 사용못하게 설정)

ALTER TABLE T_ENABLE2
DISABLE CONSTRAINT SYS_C007110;
-- 제약조건 사용못하게 변경

INSERT INTO T_ENABLE2 VALUES(3,''); -- 실행

DELETE FROM T_ENABLE2
 WHERE NO=3;
--기본 3번 데이터 삭제

2.ENABLE (제약조건 다시 살리기)

ALTER TABLE T_ENABLE2
ENABLE VALIDATE CONSTRAINT SYS_C007110; --실행됨(기존 3번 데이터 삭제안하면 오류남

📘제약조건 삭제

--제약조건 삭제
ALTER TABLE T_ENABLE2
DROP CONSTRAINT SYS_C007110;

📘제약조건을 가진 칼럼 찾기

(조금 복잡하긴 복잡한데...

SELECT COLUMN_NAME
    FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME
                           FROM USER_CONSTRAINTS
                        WHERE TABLE_NAME = 'EMP'
                           AND CONSTRAINT_TYPE = 'P');

한번에 찾고자 하는 값을 찾을 수 있다.(원래라면 접속 테이블 열어서 해당 테이블 열고 제약 조건 열고 해당 컬럼 데이터 눌러야지 데이터 확인이 가능)

SELECT COLUMN_NAME
    FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME
                           FROM USER_CONSTRAINTS
                        WHERE TABLE_NAME = 'EMP'
                           AND CONSTRAINT_TYPE = 'R');

📘📖제약조건 관련 예제

CREATE TABLE STUDENT2(
    STU_ID CHAR(5) CONSTRAINT STUDENT_STU_ID_PK PRIMARY KEY
                -- CONSTRAINT STUDENT_STU_ID_CK CHECK(LENGTH(STU_ID) = 5),
                   CONSTRAINT STUDENT_STU_ID_CK CHECK(LENGTH(TRIM(' ' FROM STU_ID)) = 5),
    STU_NAME VARCHAR2(10) CONSTRAINT STUDENT_STU_NAME_NN NOT NULL
);

테이블 생성함.

INSERT INTO STUDENT2 VALUES('A1','AA');     -- 실행 안됨
INSERT INTO STUDENT2 VALUES('A01','AA');    -- 실행 안됨
INSERT INTO STUDENT2 VALUES('A001','AA');   -- 실행 안됨
INSERT INTO STUDENT2 VALUES('A0001','AA');  -- 실행됨(5자 맞추기)
INSERT INTO STUDENT2 VALUES('A00001','AA'); -- 실행 안됨

CREATE TABLE SUBJECT2 (
    SUB_ID NUMBER(3) CONSTRAINT SUBJECT2_SUB_ID_PK PRIMARY KEY,
    SUB_NAME VARCHAR2(20) CONSTRAINT SUBJECT2_SUB_NAME_NN NOT NULL
);
--테이블 생성

INSERT INTO SUBJECT2 VALUES(100,'A1');
--데이터 삽입

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)
);

📚Reference

참고한 다른 분들의 블로그 또는 관련 링크들

• https://nanarin.tistory.com/276
  https://itragdoll.tistory.com/67
• https://blog.naver.com/regenesis90/222197289871
• https://runtoyourdream.tistory.com/129