오라클 SQL 13~14강

--CONSTRAINT
--NOT NULL / UNIQUE KEY / PRIMARY KEY(NOT NULL + UNIQUE KEY) / FOREIGN KEY / CHECk
--NOT NULL
create table t_student(
stu_no char(9),
stu_name varchar2(12),
stu_dept varchar2(20) constraint n_stu_dept not null,
stu_grade number(1),
stu_class char(1),
stu_gender char(1),
stu_height number(5,2),
stu_weight number(3,1)
);

desc t_student;

--UNIQUE KEY
create table t_student(
stu_no char(9),
stu_name varchar2(12) constraint U_stu_name UNIQUE,
stu_dept varchar2(20) constraint n_stu_dept not null,
stu_grade number(1),
stu_class char(1),
stu_gender char(1),
stu_height number(5,2),
stu_weight number(3,1)
);

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'T_STUDENT';

--PRIMARY KEY / NOT NULL + UNIQUE KEY
create table t_student(
stu_no char(9),
stu_name varchar2(12) constraint U_stu_name UNIQUE,
stu_dept varchar2(20) constraint n_stu_dept not null,
stu_grade number(1),
stu_class char(1),
stu_gender char(1),
stu_height number(5,2),
stu_weight number(3,1),
CONSTRAINT p_stu_no primary key(stu_no)
);

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'T_STUDENT';

--FOREIGN KEY
ALTER TABLE SUBJECT
MODIFY(SUB_NO PRIMARY KEY);
ALTER TABLE STUDENT
MODIFY(STU_NO PRIMARY KEY);

CREATE TABLE T_ENROL(
SUB_NO NUMBER(5),
STU_NO VARCHAR2(26),
ENR_GRADE NUMBER(3),
CONSTRAINT ENR_SUB_NO_FK1 FOREIGN KEY(SUB_NO) REFERENCES SUBJECT(SUB_NO),
CONSTRAINT ENR_STU_NO_FK1 FOREIGN KEY(STU_NO) REFERENCES STUDENT(STU_NO),
CONSTRAINT ENR_PK1 PRIMARY KEY (SUB_NO, STU_NO)
);

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'T_ENROL';

--CHECK
create table t_student(
stu_no char(9),
stu_name varchar2(12) constraint U_stu_name UNIQUE,
stu_dept varchar2(20) constraint n_stu_dept not null,
stu_grade number(1),
stu_class char(1),
stu_gender char(1) CONSTRAINT C_STU_GENDER CHECK(STU_GENDER IN ('M','F')),
stu_height number(5,2),
stu_weight number(3,1),
CONSTRAINT p_stu_no primary key(stu_no)
);

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'T_STUDENT';

--VIEW
CREATE OR REPLACE VIEW V_STUDENT1
AS
SELECT *
FROM STUDENT
WHERE STU_DEPT = '컴퓨터정보';

CREATE OR REPLACE VIEW V_ENROL1
AS
SELECT SUB_NAME, A.SUB_NO, STU_NO, ENR_GRADE
FROM ENROL A, SUBJECT B
WHERE A.SUB_NO = B.SUB_NO;

--INDEX
CREATE INDEX I_STU_NAME ON STUDENT(STU_NAME);
CREATE INDEX I_STU_NO_NAME ON STUDENT(STU_NO, STU_NAME);
DROP INDEX I_STU_NAME;
CREATE UNIQUE INDEX I_STU_NAME ON STUDENT(STU_NAME);

--시퀀스 SEQUENCE
CREATE SEQUENCE SEQ1
INCREMENT BY 2
START WITH 1000
MAXVALUE 10000;

SELECT SEQ1.NEXTVAL FROM DUAL;
DROP SEQUENCE SEQ1;

--SQL : VIEW / INDEX / SEQUENCE //INLINE VIEW /TOP-N질의문
--인라인뷰란?
--FROM절에서 SELECT문을 이용한 테이블이 구성되서 사용되는 경우
--학과별 평균신장보다 큰 학생들의 학번,이름,신장을 검색하시오
SELECT STU_NO,STU_NAME,A.STU_DEPT,STU_HEIGHT
FROM STUDENT A,(SELECT STU_DEPT,AVG(STU_HEIGHT) AS AVG_HEIGHT
FROM STUDENT
GROUP BY STU_DEPT) B
WHERE A.STU_DEPT = B.STU_DEPT
AND A.STU_HEIGHT > B.AVG_HEIGHT;

--학생테이블에서 신장이 큰 상위 5명의 학번,이름,신장을 검색하라
SELECT STU_NO , STU_NAME , STU_HEIGHT FROM (SELECT STU_NO , STU_NAME , STU_HEIGHT
FROM STUDENT
WHERE STU_HEIGHT IS NOT NULL
ORDER BY STU_HEIGHT DESC)
WHERE ROWNUM  <= 5;

CREATE SYNONYM S FOR STUDENT;
SELECT * FROM S;
DROP SYNONYM S;