localhost_STUDENT_220310.sql

SELECT * FROM USER_TABLES;

SELECT * FROM ALL_TABLES;

SELECT * FROM DBA_TABLES; -- 관리자권한이 있는면 볼 수 있음

SELECT * FROM USER_SEQUENCES;

SELECT * FROM USER_VIEWS; -- 아직 만든게 없어서 아무것도 안나옴

SELECT * FROM USER_INDEXES;

SELECT * FROM USER_CONSTRAINTS;

DESCRIBE STUDENT;

SELECT * FROM DICTIONARY;

SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'USER%'; 

SELECT 'SELECT ' || column_name || ' FROM TBL_BOARD;' FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TBL_BOARD';-- 테이블 까먹을 때 한번에 찾기 편함

SELECT BNO FROM TBL_BOARD;
SELECT TITLE FROM TBL_BOARD;
SELECT CONTENT FROM TBL_BOARD;
SELECT HITCOUNT FROM TBL_BOARD;
SELECT REGDATE FROM TBL_BOARD;
SELECT WRITER FROM TBL_BOARD;


-- MEMBER회원
-- 친구 등록
-- REQUESTER(요청자), RESPNSER(응답자) 이 두개를 묶어서 고유키로 만들어야함
-- ID1                ID2 요청 상태
-- ID2                ID1 승인 상태
-- ID1                ID4
-- ID1                ID3
-- ID3                ID1

-- 쪽지 발송
-- SENDER(받는사람), RECEIVER(보낸사람), CONTENT(내용), REGDATE, RECEIVERDATE

SELECT * FROM STUDENT;
SELECT * FROM PROFESSOR;

CREATE TABLE SUBJECT (
    SUBNO NUMBER(5) CONSTRAINT PK_SUBJECT
    PRIMARY KEY DEFERRABLE INITIALLY DEFERRED,
--    USING INDEX TABLESPACE INDX,
    SUBNAME VARCHAR2(20)
    CONSTRAINT NN_SUBNAME NOT NULL,
    TERM VARCHAR2(1)
    CONSTRAINT CK_TERM CHECK (TERM IN ('1', '2')),
    TYPE VARCHAR2(1)
);

SELECT * FROM SUBJECT;

ALTER TABLE STUDENT
    ADD CONSTRAINT PK_STUDENT PRIMARY KEY(STUDNO);

CREATE TABLE SUGANG (
    STUDNO CONSTRAINT FK_SUGANG_STUDNO REFERENCES STUDENT(STUDNO),
    SUBNO CONSTRAINT FK_SUGANG_SUBNO REFERENCES SUBJECT(SUBNO),
    REGDATE DATE DEFAULT SYSDATE,
    RESULT NUMBER(3),
    CONSTRAINT PK_SUGANG PRIMARY KEY(STUDNO, SUBNO)
);
SELECT * FROM SUGANG;

-- 학생테이블인스턴스를참조하여studno에기본키, idnum에고유키,name에NOT NULL 무결성제약조건을추가하여라.
ALTER TABLE STUDENT ADD CONSTRAINT UK_IDNUM UNIQUE(IDNUM);
ALTER TABLE STUDENT ADD CONSTRAINT UK_IDNUM UNIQUE(USERID);
ALTER TABLE STUDENT MODIFY (NAME CONSTRAINT STUD_NAME_NN NOT NULL);

-- 학과테이블에 PK
ALTER TABLE DEPARTMENT MODIFY(DEPTNO CONSTRAINT PK_DEPARTMENT PRIMARY KEY);
-- DEPTNO 외래키 추가
ALTER TABLE STUDENT MODIFY(DEPTNO CONSTRAINT FK_DEPTNO REFERENCES DEPARTMENT(DEPTNO));


-- 학과 테이블에 COLLEGE에 FK추가 FK_COLLEGE
ALTER TABLE DEPARTMENT MODIFY(COLLEGE CONSTRAINT FK_COLLEGE REFERENCES DEPARTMENT(DEPTNO));

-- 학과 테이블에 DNAME NN 추가 NN_DNAME
ALTER TABLE DEPARTMENT MODIFY (DNAME CONSTRAINT NN_DNAME NOT NULL);


-- 교수 테이블에 PROFNO PK 추가 PK_PROFESSOR
ALTER TABLE PROFESSOR MODIFY(PROFNO CONSTRAINT PK_PROFESSOR PRIMARY KEY);

-- 교수 테이블에 NAME NN 추가
ALTER TABLE PROFESSOR MODIFY(NAME CONSTRAINT NN_PROF_NAME NOT NULL);

-- 교수 테이블에 DEPTNO FK 추가
ALTER TABLE PROFESSOR MODIFY(DEPTNO CONSTRAINT FK_PROF_DEPTNO REFERENCES DEPARTMENT(DEPTNO));


-- 학생 테이블에 PROFNO FK 추가
ALTER TABLE STUDENT MODIFY(PROFNO CONSTRAINT FK_STUD_PROFNO REFERENCES PROFESSOR(PROFNO));

-- 학생 테이블에 GRADE CK 추가(1~4)
ALTER TABLE STUDENT MODIFY(GRADE CONSTRAINT CK_GRADE CHECK(GRADE BETWEEN '1' AND '4'));



SELECT * FROM USER_CONSTRAINTS;

INSERT INTO SUBJECT VALUES (1001, '자바', '1', NULL);
INSERT INTO SUBJECT VALUES (1002, '오라클', '2', NULL);
SELECT * FROM SUBJECT;
-- 지연 상태에서는 중복값(EX >> 1001)이 들어갈 수 있어서 조회는 가능하지만 COMMIT시  롤백이됨
COMMIT;

SELECT * FROM USER_CONS_COLUMNS;
SELECT * FROM USER_CONSTRAINTS;


-- 부서테이블에서name 칼럼을고유인덱스로생성하여라.
-- 단, 고유인덱스의이름을idx_dept_name으로정의한다

CREATE UNIQUE INDEX IDX_DEPT_NAME ON DEPARTMENT(DNAME);

SELECT * FROM USER_INDEXES;

-- 기본키 생성 : 해당 컬럼으로 NN,UK, UNIQUE INDEX 추가

SELECT /* + INDEX(D PK_DEPARTMENT) */ * FROM DEPARTMENT D;

DESC TBL_BOARD;

-- PK_BOARD 기본키를 추가 BNO
ALTER TABLE TBL_BOARD MODIFY(BNO CONSTRAINT PK_BOARD PRIMARY KEY);
ALTER TABLE TBL_BOARD DROP CONSTRAINT PK_BOARD;

-- 기본키가 없으면 코스트가 엄청나게 높아짐
SELECT * FROM (
SELECT A.*, ROWNUM RN FROM (
    SELECT B.* FROM TBL_BOARD B ORDER BY BNO DESC
    ) A WHERE ROWNUM <= 30
)
WHERE RN > 20;

-- 인덱스 힌트 사용 시, 코스트도 낮고 코드 수도 줄고 굳굳
SELECT * FROM (
    SELECT /*+ INDEX_DESC(A PK_BOARD) */ A.*, ROWNUM RN FROM TBL_BOARD A WHERE ROWNUM <= 30
)
WHERE RN > 20;

-- 학생테이블의birthdate 칼럼을비고유인덱스로생성하여라.
-- 비고유인덱스의이름은idx_stud_birthdate로정의한다

CREATE INDEX IDX_STUD_BIRTHDATE ON STUDENT(BIRTHDATE);
SELECT * FROM STUDENT WHERE BIRTHDATE = '79/07/02';

-- 학생테이블의deptno, grade 칼럼을결합인덱스로생성하여라.
-- 결합인덱스의이름은idx_stud_dno_grade 로정의한다
DROP INDEX IDX_STUD_DNO_GRADE;
CREATE INDEX IDX_STUD_DNO_GRADE ON STUDENT(DEPTNO, TO_CHAR(GRADE));
SELECT * FROM STUDENT WHERE DEPTNO = 101 AND GRADE = 4;

-- 학생테이블의deptno와name 칼럼으로결합인덱스를생성하여라.
-- 단, deptno 칼럼을내림차순으로name 칼럼은오름차순으로생성하여라
CREATE INDEX IDX_STUD_DNO_NAME ON STUDENT(DEPTNO DESC, NAME);
SELECT * FROM STUDENT WHERE DEPTNO > 0 AND NAME > '가'; -- 제대로 안탈경우에는 힌트로!!

SELECT /*+ INDEX_DESC(STUDENT IDX_STUD_DNO_NAME)*/ * FROM STUDENT;

SELECT * FROM USER_INDEXES;
SELECT * FROM USER_IND_COLUMNS;

SELECT *
FROM USER_INDEXES
JOIN USER_IND_COLUMNS USING(INDEX_NAME);

SELECT * FROM DICT WHERE TABLE_NAME LIKE 'USER%IND%';
SELECT * FROM USER_OBJECTS;

-- 학번, 이름, 학관번호, 교수번호를 가지는 STUD_TMP라는 테이블을 생성하여라
DROP TABLE STUD_TMP;
CREATE TABLE STUD_TMP AS
SELECT STUDNO, NAME, DEPTNO, PROFNO
FROM STUDENT;

-- 학번, 이름, 학관번호, 교수번호를 가지는 STUD_TMP라는 뷰를 생성하여라
DROP VIEW STUD_TMP;
CREATE VIEW STUD_TMP AS
SELECT STUDNO, NAME, DEPTNO, PROFNO
FROM STUDENT;

INSERT INTO STUD_TMP VALUES(12345, '김김김', 101, NULL);
INSERT INTO STUD_TMP VALUES(12346, '김김김', NULL, NULL);
-- INSERT INTO STUD_TMP VALUES(NULL, '김김김', 101, NULL); 학번에 NULL이 들어갈 수 없음
--INSERT INTO STUD_TMP VALUES(10101, '김김김', 101, NULL); 중복값도 들어갈 수 없음

SELECT * FROM STUDENT;
SELECT * FROM STUD_TMP;

SELECT * FROM USER_VIEWS;


CREATE OR REPLACE VIEW STUD_TMP AS
SELECT STUDNO, NAME, DEPTNO, PROFNO
FROM STUDENT;

SELECT * FROM HR.EMPLOYEES;
SELECT * FROM HR.EMPLOYEES A JOIN DEPARTMENT B ON A.EMPLOYEE_ID = B.DEPTNO;

-- 학생테이블과부서테이블을조인하여102번학과학생들의학번, 이름, 학년, 학과이름으로정의되는복합뷰를생성하여라.
CREATE OR REPLACE VIEW VIEW_COMP_TEST AS
SELECT STUDNO, NAME, GRADE, DNAME FROM STUDENT
NATURAL JOIN DEPARTMENT;

SELECT * FROM VIEW_COMP_TEST;

INSERT INTO VIEW_COMP_TEST VALUES(12346, '이이이', '3', '컴퓨터공학과'); 
-- 복합뷰는 INSERT를 할 수 없음
DELETE FROM VIEW_COMP_TEST WHERE DNAME IS NULL;
SELECT * FROM STUDENT;

-- 학생의 탄생연도별 인원수를 구하여라. 그리고 이 데이터를 사용해 VIEW_COMP_TEST2라는 VIEW를 생성하여라.
SELECT BIRTHDATE, SUBSTR(BIRTHDATE, 1,2) FROM STUDENT;

SELECT COUNT(SUBSTR(BIRTHDATE, 1,2)) FROM STUDENT
GROUP BY SUBSTR(BIRTHDATE, 1,2);

CREATE OR REPLACE VIEW VIEW_COMP_TEST2 AS
SELECT COUNT(SUBSTR(BIRTHDATE, 1,2)) FROM STUDENT
GROUP BY SUBSTR(BIRTHDATE, 1,2); -- VIEW에서 실패애...
-- 산술 같은게 들어가면 별칭을 줘야함!!! 

CREATE OR REPLACE VIEW VIEW_COMP_TEST2 AS
SELECT TO_CHAR(BIRTHDATE, 'YYYY') BIRTH, COUNT(*) CNT
FROM STUDENT 
GROUP BY TO_CHAR(BIRTHDATE, 'YYYY');

SELECT * FROM VIEW_COMP_TEST2;

DELETE VIEW_COMP_TEST2 WHERE BIRTH = 1980;

--인라인뷰를 사용하여 학과별로 학생들의 평균키와 평균몸무게, 학과이름을 t력하여라.
SELECT  DNAME, AVG(WEIGHT), AVG(HEIGHT) 
FROM (
    SELECT DEPTNO, AVG(WEIGHT), AVG(HEIGHT) 
    FROM STUDENT GROUP BY DEPTNO)
JOIN DEPARTMENT USING(DEPTNO); -- S 실패애...

SELECT AH, AW, DNAME 
FROM ( 
    SELECT DEPTNO, AVG(HEIGHT) AH, AVG(WEIGHT) AW
    FROM STUDENT
    GROUP BY DEPTNO
) NATURAL JOIN DEPARTMENT;
    
GRANT SELECT ON DEPARTMENT TO HR;

SELECT * FROM HR.EMPLOYEES;

CREATE SYNONYM EMPS FOR HR.EMPLOYEES;
SELECT * FROM EMPS;

-- 사용예계층적질의문을사용하여부서테이블에서학과,학부,단과대학을검색하여단대,
-- 학부학과순으로top-down 형식의계층구조로출력하여라. 단, 시작데이터는10번부서이다.
SELECT --DEPTNO, DNAME, COLLEGE, LEVEL
LPAD(' ', LEVEL * 2) || DNAME D
FROM DEPARTMENT
START WITH DEPTNO = 10
CONNECT BY PRIOR DEPTNO = COLLEGE;


-- 사용예계층적질의문을사용하여부서테이블에서학과,학부,단과대학을검색하여학과,
-- 학부단대순으로bottom-up 형식의계층구조로출력하여라. 단, 시작데이터는102번부서이다
SELECT --DEPTNO, DNAME, COLLEGE, LEVEL
LPAD(' ', LEVEL * 2) || DNAME D
FROM DEPARTMENT
START WITH DEPTNO = 101
CONNECT BY PRIOR COLLEGE = DEPTNO;