SQL 기존 테이블 삭제, 새 테이블 생성 관련 (실습) (@sc.sql)

sc. sql 으로 저장 할 것

emp, dept, student, professor, course, score 테이블을 생성 하고 스크립트 파일 만들어서 실행 하면 됨

sqlplus 에서 @sc.sql 으로 실행

그리고 복구하려면 @school.sql 을 실행하면 됨

그리고 제약조건 데이터 저장해서 사용하면 됨 
( ucon.sql, ref.sql 스크립트로 저장해서 사용한다. 제약조건1 pk,fk 교안에 있음)

ㅡㅡㅡㅡ

SQL

 

-- [1] 기존 테이블 삭제 (휴지통 이동)
DROP TABLE score;
DROP TABLE course;
DROP TABLE professor;
DROP TABLE student;
DROP TABLE dept;
DROP TABLE emp;

-- [2] 화면 출력 최적화 설정
SET LINESIZE 200;
SET PAGESIZE 100;
COL ENAME FORMAT A10;
COL DNAME FORMAT A15;
COL PNAME FORMAT A10;
COL SNAME FORMAT A10;
COL CNAME FORMAT A20;
COL TABLE_NAME FORMAT A20;

-- [3] 6개 테이블 생성 (교안 이미지 기준 컬럼명 적용)

-- 1. emp (사원)
CREATE TABLE emp (
    eno      NUMBER(4) PRIMARY KEY,
    ename    VARCHAR2(10),
    sex      VARCHAR2(4),
    job      VARCHAR2(10),
    mgr      NUMBER(4),
    hdate    DATE,
    sal      NUMBER(10),
    comm     NUMBER(10),
    dno      NUMBER(2)
);

-- 2. dept (부서)
CREATE TABLE dept (
    dno      NUMBER(2) PRIMARY KEY,
    dname    VARCHAR2(14),
    loc      VARCHAR2(13)
);

-- 3. student (학생)
CREATE TABLE student (
    sno      NUMBER(10) PRIMARY KEY,
    sname    VARCHAR2(10),
    sex      VARCHAR2(4),
    syear    NUMBER(1),
    major    VARCHAR2(20),
    avr      NUMBER(3,2)
);

-- 4. professor (교수)
CREATE TABLE professor (
    pno      NUMBER(4) PRIMARY KEY,
    pname    VARCHAR2(10),
    section  VARCHAR2(20),
    orders   VARCHAR2(10),
    hiredate DATE
);

-- 5. course (과목)
CREATE TABLE course (
    cno      VARCHAR2(10) PRIMARY KEY,
    cname    VARCHAR2(20),
    st_num   NUMBER(2),
    pno      NUMBER(4)
);

-- 6. score (기말고사점수)
CREATE TABLE score (
    sno      NUMBER(10),
    cno      VARCHAR2(10),
    result   NUMBER(3),
    CONSTRAINT PK_SCORE PRIMARY KEY (sno, cno)
);

-- [4] 마무리 및 확인
COMMIT;
SELECT table_name FROM user_tables WHERE table_name IN ('EMP', 'DEPT', 'STUDENT', 'PROFESS​

 

화면 출력 및 형식 설정 SET LINESIZE 200 -- 한 줄에 표시할 글자 수를 200자까지 늘림(결과가 옆으로 깨지는 것을 방지) SET PAGESIZE 100 -- 한 페이지에 출력할 행 수를 100개로 설정(제목이 자주 반복되는 것 방지)  COL ENAME FORMAT A10 -- ENAME(사원명) 칸의 너비를 영문 10자 크기로 고정 COL DNAME FORMAT A15 -- DNAME(부서명) 칸의 너비를 영문 15자 크기로 고정  COL PNAME FORMAT A10 -- PNAME(교수명) 칸의 너비를 영문 10자 크기로 고정  COL SNAME FORMAT A10 -- SNAME(학생명) 칸의 너비를 영문 10자 크기로 고정 COL CNAME FORMAT A20 -- CNAME(과목명) 칸의 너비를 영문 20자 크기로 고정 COL TABLE_NAME FORMAT A20 -- 조회 시 테이블 이름 칸의 너비를 영문 20자 크기로 고정  기존 테이블 삭제(초기화)  DROP TABLE score; -- 성적 테이블 삭제 DROP TABLE course; -- 과목 테이블 삭제  DROP TABLE professor; -- 교수 테이블 삭제 DROP TABLE student; -- 학생 테이블 삭제 DROP TABLE dept; -- 부서 테이블 삭제 DROP TABLE emp; -- 사원 테이블 삭제 1. 사원 테이블 생성 CREATE TABLE emp (     eno      NUMBER(4)    PRIMARY KEY, -- 사원번호 (숫자 4자리, 기본키: 중복불가/NULL불가)     ename    VARCHAR2(10),             -- 사원명 (가변길이 문자열 10바이트)     sex      VARCHAR2(4),              -- 성별 (가변길이 문자열 4바이트)     job      VARCHAR2(10),             -- 직업/직무 (가변길이 문자열 10바이트)     mgr      NUMBER(4),                -- 상사의 사원번호 (숫자 4자리)     hdate    DATE,                     -- 입사일 (날짜 형식)     sal      NUMBER(10),               -- 급여 (숫자 10자리)     comm     NUMBER(10),               -- 커미션/보너스 (숫자 10자리)     dno      NUMBER(2)                 -- 부서번호 (숫자 2자리) ); 2. 부서 테이블 생성 CREATE TABLE dept (     dno      NUMBER(2)    PRIMARY KEY, -- 부서번호 (숫자 2자리, 기본키)     dname    VARCHAR2(14),             -- 부서명 (가변길이 문자열 14바이트)     loc      VARCHAR2(13)              -- 지역/위치 (가변길이 문자열 13바이트) ); 3. 학생 테이블 생성 CREATE TABLE student (     sno      NUMBER(10)   PRIMARY KEY, -- 학번 (숫자 10자리, 기본키)     sname    VARCHAR2(10),             -- 학생명 (가변길이 문자열 10바이트)     sex      VARCHAR2(4),              -- 성별 (가변길이 문자열 4바이트)     syear    NUMBER(1),                -- 학년 (숫자 1자리)     major    VARCHAR2(20),             -- 전공 (가변길이 문자열 20바이트)     avr      NUMBER(3,2)               -- 평점 (전체 3자리 중 소수점 2자리까지 표시) ); 4. 교수 테이블 생성 CREATE TABLE professor (     pno      NUMBER(4)    PRIMARY KEY, -- 교수번호 (숫자 4자리, 기본키)     pname    VARCHAR2(10),             -- 교수명 (가변길이 문자열 10바이트)     section  VARCHAR2(20),             -- 소속학과/부문 (가변길이 문자열 20바이트)     orders   VARCHAR2(10),             -- 직위 (가변길이 문자열 10바이트)     hiredate DATE                      -- 임용일 (날짜 형식) ); 5. 과목 테이블 생성 CREATE TABLE course (     cno      VARCHAR2(10) PRIMARY KEY, -- 과목번호 (가변길이 문자열 10바이트, 기본키)     cname    VARCHAR2(20),             -- 과목명 (가변길이 문자열 20바이트)     st_num   NUMBER(2),                -- 이수학점수 (숫자 2자리)     pno      NUMBER(4)                 -- 담당 교수번호 (숫자 4자리) ); 6. 성적 테이블 생성 (복합키 구조) CREATE TABLE score (     sno      NUMBER(10),               -- 학번 (숫자 10자리)     cno      VARCHAR2(10),             -- 과목번호 (가변길이 문자열 10바이트)     result   NUMBER(3),                -- 점수 (숫자 3자리)     CONSTRAINT PK_SCORE PRIMARY KEY (sno, cno) -- 기본키 설정 (학번+과목번호를 묶어서 식별자로 사용) );

 

단일키 vs 복합키 비교 정리

구분	대상 테이블	기본키(PK) 구성	이유 (데이터가 겹치면 안 되는 기준)
단일키	사원, 부서, 학생, 교수	번호 1개 (학번, 사번 등)	사람이나 부서는 번호 하나만 있으면 세상에 딱 한 명뿐임을 증명할 수 있음.
복합키	성적(SCORE)	학번 + 과목번호	한 학생이 여러 과목을 볼 수 있고, 한 과목을 여러 학생이 보므로, '누가 무슨 과목을 봤는지' 세트로 묶어야 데이터가 안 겹침.

 

테이블 컬럼 크기 설정 이유 정리

구분	컬럼명	데이터 타입	크기 설정 이유 (추정 및 표준)
성명	ename, sname, pname	VARCHAR2(10)	한글 약 3글자(9바이트) 내외를 수용하기 위한 크기. 이름이 길면 잘릴 위험이 있음. * 한 글자에 3바이트
성별	sex	VARCHAR2(4)	'남', '여' 한 글자(3바이트) 또는 'MALE', 'FEM' 등의 약어를 넣기에 적합함 * 한 글자에 3바이트
부서/과목명	dname, cname	VARCHAR2(14~20)	'경영지원팀', '데이터베이스실습' 등 5~6글자 이상의 한글 명칭을 수용하기 위함.
식별 번호	eno, pno, dno	NUMBER(2~4)	사원/교수/부서 번호는 고정된 짧은 숫자로 관리하여 메모리 효율을 높임.
학번	sno	NUMBER(10)	'2024010001'과 같이 연도(4) + 학과(3) + 순번(3) 형태의 긴 숫자를 담기 위함.
전공/학과	major, section	VARCHAR2(20)	'컴퓨터공학과' 등 긴 명칭(한글 6자=18바이트)을 안전하게 저장하기 위한 설정.
학점/성적	avr, result	NUMBER(3,2) / (3)	avr: 4.50(3자리 중 소수점 뒤에 2자리 고정), result: 100점 만점(3자리)에 최적화 * avr: 4.50 에서 - 앞의 숫자 3: 소수점 앞 뒤 다 합쳐서 숫자를 딱 3개 쓰겠다는 의미 - 뒤의 숫자 2: 그 3개 중에서 2개는 소수점 뒤에 쓰겠다는 의미   * 소수점 뒤의 2자리 까지 쓰는 이유는 정확히 기록하기 위해서
날짜	hdate, hiredate	DATE	연/월/일/시/분/초를 고정 7바이트로 저장하는 표준 타입.