testUser Query

-- 1. 테이블 생성
CREATE TABLE employees(
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR(25) UNIQUE,
    hire_date DATE,
    salary NUMBER(8, 2),
    department_id NUMBER(4)
);

-- 2. 열(Column) 추가
ALTER TABLE employees
ADD phone_number VARCHAR2(20);

-- 3. 제약조건(Constraint) 추가
ALTER TABLE employees
ADD CONSTRAINT check_salary
CHECK (salary > 0);

-- 4. 열(Column) 수정
ALTER TABLE employees
MODIFY phone_number VARCHAR(30);

-- 5. 제약조건(Constraint) 삭제
ALTER TABLE employees
DROP CONSTRAINT check_salary;

-- 6. 테이블 조회
SELECT *
FROM EMPLOYEES;

-- 7. 열(Column) 추가
ALTER TABLE employees
ADD job_id VARCHAR2(10);

-- 8. 열 위치 변경(*)

-- 9. 예제 Data 입력
INSERT INTO employees
    (employee_id, first_name, last_name, email, phone_number,
     hire_date, salary, department_id, job_id)
     VALUES (1, 'John', 'Doe', 'john.doe@example.com', '555-1234',
             TO_DATE('20220101', 'YYYYMMDD'), 4000.00, 10, 'SA_REP');
                          
-- 10. 예제 Data 추가 입력
--INSERT INTO employees
--    (employee_id, first_name, last_name, email, phone_number,
--     hire_date, salary, department_id, job_id)
--    VALUES
--        (2, 'Jane', 'Doe', 'jane.doe@example.com', '555-5678',
--        TO_DATE('20220115', 'YYYYMMDD'), 5000.00, 10, 'SA_REP');
--        (3, 'Bob', 'Smith', 'bob.smith@example.com', '555-9012',
--        TO_DATE('20220130', 'YYYYMMDD'), 6000.00, 10, 'SA_REP');
--        (4, 'Mary', 'Johnson', 'mary.johnson@example.com', '555-3456',
--        TO_DATE('20220201', 'YYYYMMDD'), 7000.00, 20, 'SA_REP');
--        (5, 'Mike', 'Brown', 'mike.brown@example.com', '555-7890',
--        TO_DATE('20220215', 'YYYYMMDD'), 8000.00, 20, 'SA_REP');

-- 11. Join 연산을 위한 추가 테이블 생성
CREATE TABLE departments(
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR(3) NOT NULL,
    manager_id NUMBER(10),
    loacation_id NUMBER(4)
);

-- 12. EMPLOYEES TABLE의 department_id 수정
-- department_id = 10, 20, 30
UPDATE employees
SET department_id = 20
WHERE employee_id = 2;

UPDATE employees
SET department_id = 20
WHERE employee_id = 3;

UPDATE EMPLOYEES
SET DEPARTMENT_ID = 30
WHERE EMPLOYEE_ID = 4;

UPDATE EMPLOYEES
SET DEPARTMENT_ID = 30
WHERE EMPLOYEE_ID = 5;

-- 13. DEPARTMENT_NAME DATA-TYPE 크기 변경
ALTER TABLE DEPARTMENTS
MODIFY DEPARTMENT_NAME VARCHAR2(30);


-- 14. DEPARTMENTS 테이블에 데이터 추가
INSERT INTO DEPARTMENTS
(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOACATION_ID)
VALUES (10, 'Sales', 123, 1000);

INSERT INTO DEPARTMENTS
(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOACATION_ID)
VALUES (20, 'Marketing', 234, 2000);

INSERT INTO DEPARTMENTS
(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOACATION_ID)
VALUES (30, 'Human Resources', 345, 2000);

-- 15. DEPARTMENTS 테이블의 LOACATION_ID(ADDITION ERROR) 수정
ALTER TABLE DEPARTMENTS
RENAME COLUMN LOACATION_ID TO LOCATION_ID;

-- 16. EMPLOYEES 와 DEPARTMENTS 테이블 INNER JOIN 수행
SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- 17. EQUI JOIN과 INNER JOIN의 차이점 확인(+NATURAL JOIN)
-- EQUI JOIN(모든 열 출력, FK열은 중복 출력)
SELECT *
FROM EMPLOYEES E
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- INNER JOIN
SELECT *
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- NATURAL JOIN
SELECT *
FROM EMPLOYEES
NATURAL JOIN DEPARTMENTS;

-- 19. OUTER JOIN
-- LEFT OUTER JOIN

--SELECT *
--FROM EMPLOYEES
--LEFT OUTER JOIN DEPARTMENTS
--ON  EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

SELECT *
FROM EMPLOYEES E
LEFT OUTER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

SELECT *
FROM DEPARTMENTS D
RIGHT OUTER JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID;

COMMIT;