테이블 생성 과 데이터입력

• 테스트용 테이블 생성 및 관련 데이터 입력 SQL
  • employees : 사원 테이블
  • sales  : 판매 테이블
  • customers : 고객 테이블
  • orders  : 주문 테이블
  • students  : 학생 테이블

---

• `employees` 사원 테이블 생성

CREATE TABLE employees (
    employee_id   NUMBER PRIMARY KEY,
    department_id NUMBER,
    salary        NUMBER,
    hire_date     DATE
);

-- 샘플 데이터 입력 
INSERT INTO employees VALUES (1, 10, 4800, TO_DATE('2006-03-15', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (2, 10, 5200, TO_DATE('2007-06-01', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (3, 20, 6000, TO_DATE('2005-08-20', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (4, 20, 5500, TO_DATE('2004-11-10', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (5, 30, 7000, TO_DATE('2006-12-25', 'YYYY-MM-DD'));
commit;

• `sales` 판매 테이블 생성

CREATE TABLE sales (
    sale_id          NUMBER PRIMARY KEY,
    sale_date        DATE,
    product_category VARCHAR2(50),
    sale_amount      NUMBER
);

INSERT INTO sales VALUES (1, TO_DATE('2023-01-15', 'YYYY-MM-DD'), '전자제품', 300000);
INSERT INTO sales VALUES (2, TO_DATE('2023-02-10', 'YYYY-MM-DD'), '전자제품', 400000);
INSERT INTO sales VALUES (3, TO_DATE('2023-04-05', 'YYYY-MM-DD'), '가전',     500000);
INSERT INTO sales VALUES (4, TO_DATE('2023-07-20', 'YYYY-MM-DD'), '가전',     600000);
INSERT INTO sales VALUES (5, TO_DATE('2023-10-03', 'YYYY-MM-DD'), '도서',     200000);
INSERT INTO sales VALUES (6, TO_DATE('2023-10-15', 'YYYY-MM-DD'), '도서',     850000);
commit;

• `students` 학생 테이블 생성

CREATE TABLE students (
    student_id NUMBER PRIMARY KEY,
    department VARCHAR2(100),
    score      NUMBER
);

::<source lang=sql>
INSERT INTO students VALUES (1, '컴퓨터공학과', 90);
INSERT INTO students VALUES (2, '컴퓨터공학과', 85);
INSERT INTO students VALUES (3, '전자공학과',   88);
INSERT INTO students VALUES (4, '전자공학과',   82);
INSERT INTO students VALUES (5, '심리학과',     78); -- 제외됨 (80 미만)
INSERT INTO students VALUES (6, '경영학과',     95);
INSERT INTO students VALUES (7, '경영학과',     90);
commit;

• `customers` 고객, `orders` 주문 테이블 생성

CREATE TABLE customers (
    customer_id   NUMBER PRIMARY KEY,
    customer_name VARCHAR2(100)
);

CREATE TABLE orders (
    order_id     NUMBER PRIMARY KEY,
    customer_id  NUMBER,
    order_amount NUMBER,
    order_date   DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 고객
INSERT INTO customers VALUES (1, '홍길동');
INSERT INTO customers VALUES (2, '김영희');
INSERT INTO customers VALUES (3, '이철수');

-- 주문
INSERT INTO orders VALUES (101, 1, 2000000, TO_DATE('2022-02-15', 'YYYY-MM-DD'));
INSERT INTO orders VALUES (102, 1, 3500000, TO_DATE('2022-06-10', 'YYYY-MM-DD'));
INSERT INTO orders VALUES (103, 2, 1500000, TO_DATE('2022-07-20', 'YYYY-MM-DD'));
INSERT INTO orders VALUES (104, 2, 2000000, TO_DATE('2022-09-12', 'YYYY-MM-DD'));
INSERT INTO orders VALUES (105, 3, 500000,  TO_DATE('2022-03-01', 'YYYY-MM-DD'));
commit;