Database_09_오라클 데이터베이스 sqldeveloper SQL 자격 검증 시험 대비 실습 환경 설정

- SCOTT 계정으로 접속합니다.
    - '+' 접속 클릭 > 새접속- Name : K리그
    - 사용자 정보     
        - 사용자 이름 : scott     
        - 비밀번호 : tiger     
        - 롤 : 기본값- 세부 정보     
        - 호스트 이름 : localhost     
        - 포트 : 1521     
        - SID : xe> 접속

 - 수업에 사용할 예제 데이터 생성
    - 인사관리 데이터베이스 > 테이블 선택- 아무런 데이터가 없습니다.

'K리그' > 'cre_scottdata.sql'
=====
rem******************
rem  DROP TABLES
DROP TABLE schedule CASCADE CONSTRAINTS PURGE;
DROP TABLE team CASCADE CONSTRAINTS PURGE;
DROP TABLE player CASCADE CONSTRAINTS PURGE;
DROP TABLE stadium CASCADE CONSTRAINTS PURGE;

rem******************
rem  create TABLES
CREATE TABLE STADIUM (
STADIUM_ID    CHAR(3) NOT NULL,
STADIUM_NAME  VARCHAR2(40) NOT NULL,
HOMETEAM_ID   CHAR(3),
SEAT_COUNT    NUMBER,
ADDRESS       VARCHAR2(60),
DDD           VARCHAR2(3),
TEL           VARCHAR2(10),
CONSTRAINT STADIUM_PK PRIMARY KEY (STADIUM_ID)
);
CREATE TABLE TEAM (
TEAM_ID     CHAR(3) NOT NULL,
REGION_NAME VARCHAR2(8) NOT NULL,
TEAM_NAME   VARCHAR2(40) NOT NULL,
E_TEAM_NAME VARCHAR2(50),
ORIG_YYYY   CHAR(4),
STADIUM_ID  CHAR(3) NOT NULL,
ZIP_CODE1   CHAR(3),
ZIP_CODE2   CHAR(3),
ADDRESS     VARCHAR2(80),
DDD         VARCHAR2(3),
TEL         VARCHAR2(10),
FAX         VARCHAR2(10),
HOMEPAGE    VARCHAR2(50),
OWNER       VARCHAR2(10),
CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID),
CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID)
);
CREATE TABLE SCHEDULE (
STADIUM_ID   CHAR(3) NOT NULL,
SCHE_DATE    CHAR(8) NOT NULL,
GUBUN        CHAR(1) NOT NULL,
HOMETEAM_ID  CHAR(3) NOT NULL,
AWAYTEAM_ID  CHAR(3) NOT NULL,
HOME_SCORE   NUMBER(2),
AWAY_SCORE   NUMBER(2),
CONSTRAINT SCHEDULE_PK PRIMARY KEY (STADIUM_ID, SCHE_DATE),
CONSTRAINT SCHEDULE_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID)
);
CREATE TABLE  PLAYER (
PLAYER_ID     CHAR(7) NOT NULL,
PLAYER_NAME   VARCHAR2(20) NOT NULL,
TEAM_ID       CHAR(3) NOT NULL,
E_PLAYER_NAME VARCHAR2(40),
NICKNAME      VARCHAR2(30),
JOIN_YYYY     CHAR(4),
POSITION      VARCHAR2(10),
BACK_NO       NUMBER(2),
NATION        VARCHAR2(20),
BIRTH_DATE    DATE,
SOLAR         CHAR(1),
HEIGHT        NUMBER(3),
WEIGHT        NUMBER(3),
CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),
CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)
);
rem  insert STADIUM 20 data
INSERT INTO stadium VALUES ('D03','전주월드컵경기장','K05',28000,'전북 전주시 덕진구 반월동 763-1','063','273-1763');
...
INSERT INTO stadium VALUES ('F05','안양경기장','',20000,'경기도 안양시','031','');
commit;