SQL활용 2022/03/18(Oracle)

-- 회원 테이블 생성
CREATE TABLE MEMBER1(
    USERID VARCHAR2(30)PRIMARY KEY,
    USERPW VARCHAR2(200),
    USERNAME VARCHAR2(15),
    USERAGE NUMBER(3),
    USERDATE DATE    
);

-- 컬럼중에서 이름의 길이를 15 => 20
ALTER TABLE MEMBER1 MODIFY USERNAME VARCHAR2(20);

-- 주소 컬럼 추가하기
ALTER TABLE MEMBER1 ADD USERADDR VARCHAR2(100);

-- 물품번호(숫자), 물품명(문자), 가격(숫자), 수량(숫자) 등록일
CREATE TABLE ITEM1(
    ITEMNO NUMBER,
    ITEMNAME VARCHAR2(30),
    ITEMPRICE NUMBER,
    ITEMQTY NUMBER,
    ITEMDATE DATE,
    PRIMARY KEY(ITEMNO)    
);

-- 물품명의 길이 30 => 50
ALTER TABLE ITEM1 MODIFY ITEMNAME VARCHAR2(50);

-- 주문 ORDER1 (주문번호, 주문수량, 주문일자, 물품번호, 주문자ID)
CREATE TABLE ORDER1(
    ORDNO NUMBER PRIMARY KEY,
    ORDCNT NUMBER,
    ORDDATE DATE,
    ORDITEM NUMBER,
    ORDID VARCHAR2(30),
    --외래키 연결
    FOREIGN KEY(ORDITEM) REFERENCES ITEM1(ITEMNO),
    FOREIGN KEY(ORDID) REFERENCES MEMBER1(USERID)
);

CREATE TABLE ITEM2
(
  ITEMNO    NUMBER       NOT NULL,
  ITEMNAME  VARCHAR2(50),
  ITEMPRICE NUMBER      ,
  ITEMQTY   NUMBER      ,
  ITEMDATE  DATE        ,
  ORDNO     NUMBER       NOT NULL,
  CONSTRAINT PK_ITEM2 PRIMARY KEY (ITEMNO)
);

CREATE TABLE MEMBER2
(
  USERID   VARCHAR2(30)  NOT NULL,
  USERPW   VARCHAR2(200),
  USERNAME VARCHAR2(50) ,
  USERAGE  NUMBER(3)    ,
  USERDATE DATE         ,
  CONSTRAINT PK_MEMBER2 PRIMARY KEY (USERID)
);

CREATE TABLE ORDER2
(
  ORDNO   NUMBER       NOT NULL,
  ORDCNT  NUMBER      ,
  ORDDATE DATE        ,
  ITEMNO  NUMBER       NOT NULL,
  USERID  VARCHAR2(30) NOT NULL,
  CONSTRAINT PK_ORDER2 PRIMARY KEY (ORDNO)
);

ALTER TABLE ORDER2
  ADD CONSTRAINT FK_ITEM2_TO_ORDER2
    FOREIGN KEY (ITEMNO)
    REFERENCES ITEM2 (ITEMNO);

ALTER TABLE ORDER2
  ADD CONSTRAINT FK_MEMBER2_TO_ORDER2
    FOREIGN KEY (USERID)
    REFERENCES MEMBER2 (USERID);

DROP TABLE ITEM2 CASCADE CONSTRAINTS;
DROP TABLE MEMBER2 CASCADE CONSTRAINTS;
DROP TABLE ORDER2 CASCADE CONSTRAINTS; 

-- 강의실 테이블 생성
CREATE TABLE CLASSROOM1
(
  CLANO   VARCHAR2(20)  NOT NULL,
  CLAADDR VARCHAR2(100),
  CONSTRAINT PK_CLASSROOM1 PRIMARY KEY (CLANO)
);

-- 설명 추가
COMMENT ON TABLE CLASSROOM1 IS '강의실';
COMMENT ON COLUMN CLASSROOM1.CLANO IS '강의실번호';
COMMENT ON COLUMN CLASSROOM1.CLAADDR IS '강의실 위치';

-- 수강테이블 생성
CREATE TABLE COURSE1
(
  CURNO   NUMBER NOT NULL,
  STDNO   NUMBER NOT NULL,
  SUBCODE NUMBER NOT NULL,
  CONSTRAINT PK_COURSE1 PRIMARY KEY (CURNO)
);

-- 설명추가
COMMENT ON TABLE COURSE1 IS '수강테이블';
COMMENT ON COLUMN COURSE1.CURNO IS '수강번호';
COMMENT ON COLUMN COURSE1.STDNO IS '학번';
COMMENT ON COLUMN COURSE1.SUBCODE IS '과목코드';

-- 교수테이블 생성
CREATE TABLE PROFESSOR1
(
  PRONO    NUMBER       NOT NULL,
  PRONAME  VARCHAR2(15),
  PROPHONE NUMBER      ,
  CONSTRAINT PK_PROFESSOR1 PRIMARY KEY (PRONO)
);

-- 설명추가
COMMENT ON TABLE PROFESSOR1 IS '교수테이블';
COMMENT ON COLUMN PROFESSOR1.PRONO IS '교수번호';
COMMENT ON COLUMN PROFESSOR1.PRONAME IS '교수이름';

-- 학생테이블 생성
CREATE TABLE STUDENT1
(
  STDNO    NUMBER       NOT NULL,
  STDNAME  VARCHAR2(15),
  STDPHONE NUMBER      ,
  CONSTRAINT PK_STUDENT1 PRIMARY KEY (STDNO)
);

-- 설명추가
COMMENT ON TABLE STUDENT1 IS '학생테이블';
COMMENT ON COLUMN STUDENT1.STDNO IS '학번';
COMMENT ON COLUMN STUDENT1.STDNAME IS '학생이름';

-- 교과목테이블 생성
CREATE TABLE SUBJECT1
(
  SUBCODE NUMBER       NOT NULL,
  SUBNAME VARCHAR2(30),
  PRONO   NUMBER       NOT NULL,
  CLANO   VARCHAR2(20) NOT NULL,
  CONSTRAINT PK_SUBJECT1 PRIMARY KEY (SUBCODE)
);

-- 설명추가
COMMENT ON TABLE SUBJECT1 IS '과목테이블';
COMMENT ON COLUMN SUBJECT1.SUBCODE IS '과목코드';
COMMENT ON COLUMN SUBJECT1.SUBNAME IS '과목명';
COMMENT ON COLUMN SUBJECT1.PRONO IS '교수번호';
COMMENT ON COLUMN SUBJECT1.CLANO IS '강의실번호';

-- 수강에 학생정보 외래키 설정
ALTER TABLE COURSE1
  ADD CONSTRAINT FK_STUDENT1_TO_COURSE1
    FOREIGN KEY (STDNO)
    REFERENCES STUDENT1 (STDNO);

-- 수강에 교과목 외래키 설정
ALTER TABLE COURSE1
  ADD CONSTRAINT FK_SUBJECT1_TO_COURSE1
    FOREIGN KEY (SUBCODE)
    REFERENCES SUBJECT1 (SUBCODE);

-- 교과목에 교수정보 외래키 설정
ALTER TABLE SUBJECT1
  ADD CONSTRAINT FK_PROFESSOR1_TO_SUBJECT1
    FOREIGN KEY (PRONO)
    REFERENCES PROFESSOR1 (PRONO);

-- 교과목에 강의실정보 외래키 설정
ALTER TABLE SUBJECT1
  ADD CONSTRAINT FK_CLASSROOM1_TO_SUBJECT1
    FOREIGN KEY (CLANO)
    REFERENCES CLASSROOM1 (CLANO);
    
          
-- 테이블 지우기
DROP TABLE CLASSROOM1 CASCADE CONSTRAINTS;
DROP TABLE COURSE1 CASCADE CONSTRAINTS;
DROP TABLE PROFESSOR1 CASCADE CONSTRAINTS;
DROP TABLE STUDENT1 CASCADE CONSTRAINTS;
DROP TABLE SUBJECT1 CASCADE CONSTRAINTS;