-- Minstudio SQL Practice Dataset
-- Oracle / MySQL 호환용 더미 데이터 스크립트입니다.
-- 이 스크립트를 실행하여 테스트용 테이블과 데이터를 생성하세요.

-- 기존 테이블 삭제 (필요한 경우)
-- DROP TABLE ORDERS;
-- DROP TABLE EMPLOYEES;
-- DROP TABLE USERS;
-- DROP TABLE ACCOUNTS;

-- 1. USERS Table (고객 테이블)
CREATE TABLE USERS (
    USER_ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL,
    CITY VARCHAR2(50)
);

INSERT INTO USERS (USER_ID, NAME, CITY) VALUES (1, '홍길동', 'seoul');
INSERT INTO USERS (USER_ID, NAME, CITY) VALUES (2, '이순신', 'busan');
INSERT INTO USERS (USER_ID, NAME, CITY) VALUES (3, '알렉스', 'seoul');
INSERT INTO USERS (USER_ID, NAME, CITY) VALUES (4, '제시카', 'busan');

-- 2. ORDERS Table (주문 테이블)
CREATE TABLE ORDERS (
    ORDER_ID NUMBER PRIMARY KEY,
    USER_ID NUMBER,
    PRODUCT_NAME VARCHAR2(100),
    ORDER_DATE DATE,
    PRICE NUMBER
);

INSERT INTO ORDERS (ORDER_ID, USER_ID, PRODUCT_NAME, ORDER_DATE, PRICE) VALUES (99, 1, '키보드', TO_DATE('2026-05-01', 'YYYY-MM-DD'), 25000);
INSERT INTO ORDERS (ORDER_ID, USER_ID, PRODUCT_NAME, ORDER_DATE, PRICE) VALUES (100, 2, '마우스', TO_DATE('2026-05-02', 'YYYY-MM-DD'), 15000);
INSERT INTO ORDERS (ORDER_ID, USER_ID, PRODUCT_NAME, ORDER_DATE, PRICE) VALUES (101, 3, '모니터', TO_DATE('2026-05-10', 'YYYY-MM-DD'), 15400);
INSERT INTO ORDERS (ORDER_ID, USER_ID, PRODUCT_NAME, ORDER_DATE, PRICE) VALUES (102, 4, 'USB', TO_DATE('2026-05-15', 'YYYY-MM-DD'), 8200);

-- 3. EMPLOYEES Table (직원 테이블)
CREATE TABLE EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL,
    DEPT_ID NUMBER,
    ROLE VARCHAR2(50),
    SALARY NUMBER,
    MGR_ID NUMBER
);

INSERT INTO EMPLOYEES (EMP_ID, NAME, DEPT_ID, ROLE, SALARY, MGR_ID) VALUES (100, '마이클', 10, 'Manager', 8000, NULL);
INSERT INTO EMPLOYEES (EMP_ID, NAME, DEPT_ID, ROLE, SALARY, MGR_ID) VALUES (101, '알렉스', 20, 'Developer', 6000, 100);
INSERT INTO EMPLOYEES (EMP_ID, NAME, DEPT_ID, ROLE, SALARY, MGR_ID) VALUES (102, '제시카', 10, 'Designer', 5500, 100);
INSERT INTO EMPLOYEES (EMP_ID, NAME, DEPT_ID, ROLE, SALARY, MGR_ID) VALUES (103, '브라이언', 20, 'Designer', 6000, 101);
INSERT INTO EMPLOYEES (EMP_ID, NAME, DEPT_ID, ROLE, SALARY, MGR_ID) VALUES (104, '사라', 30, 'Developer', 4625, 100);

-- 4. ACCOUNTS Table (계좌 테이블 - 트랜잭션 실습용)
CREATE TABLE ACCOUNTS (
    ACCOUNT_ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL,
    BALANCE NUMBER
);

INSERT INTO ACCOUNTS (ACCOUNT_ID, NAME, BALANCE) VALUES (1, 'A', 100000);
INSERT INTO ACCOUNTS (ACCOUNT_ID, NAME, BALANCE) VALUES (2, 'B', 50000);

COMMIT;
