work 05

-- 문제1) EMPLOYEES 테이블에서 부서별로 인원수,평균 급여,급여의 합,최소 급여,최대 급여를 포함하는 
-- EMP_DEPTNO 테이블을 생성하라.
CREATE TABLE emp_deptno(deptno, e_count, e_avg, e_sum, e_min, e_max)
AS
SELECT department_id, COUNT(*), ROUND(AVG(salary), 1), SUM(salary), 
    MIN(salary), MAX(salary)
FROM employees
GROUP BY department_id;

DROP TABLE emp_deptno;

SELECT * FROM emp_deptno;

CREATE TABLE emp_deptno(deptno, e_count, e_avg, e_sum, e_min, e_max)
AS
SELECT department_id, COUNT(*), ROUND(AVG(salary), 1), SUM(salary), 
    MIN(salary), MAX(salary)
FROM employees
GROUP BY department_id;

-- 문제2) EMP_DEPTNO 테이블에 ETC COLUMN을 추가하라. 
-- 단 자료형은 VARCHAR2(50) 사용하라.
ALTER TABLE emp_deptno
ADD
(ETC VARCHAR2(50));

-- 문제3) EMP_DEPTNO 테이블에 ETC COLUMN을 수정하라. 
-- 자료 형은 VARCHAR2(15)로 하라.

ALTER TABLE emp_deptno
MODIFY
(etc VARCHAR2(15));

-- 문제4) EMP_DEPTNO 테이블에 있는 ETC 을 삭제하고 확인하라.
ALTER TABLE emp_deptno
DROP
(etc);

-- 문제5) 이전에 생성한 EMP_DEPTNO 테이블의 이름을 EMP_DEPT로 변경하라.

RENAME emp_deptno TO emp_dept;

-- 문제6) EMP_DEPT 테이블을 삭제하라.

DROP TABLE emp_dept;

-- 문제7) EMPLOYEES 테이블을 EMP 테이블을 생성하고 복제하도록 하라.
-- (데이터 포함)

CREATE TABLE EMP("사원번호", "이름", "월급", "부서번호", "부서명", "부서월급순위")
AS
SELECT e.employee_id, e.last_name, e.salary,
    d.department_id, d.department_name,
    COUNT(*)OVER(PARTITION BY d.department_id ORDER BY e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id;

SELECT * FROM emp;

-- 문제8) EMP 테이블에 row를 추가해 봅니다.
-- 다만, 반드시 데이터를 기입을 안해도 되면, NULL로 설정하도록 한다.
INSERT INTO emp
VALUES(1001, '홍길동', 15000, 10, '개발부', 1);

-- 문제9) EMPLOYEES 테이블에서 EMPNO,ENAME,SAL,HIREDATE의 COLUMN만 선택하여 
-- EMP_10 테이블을 생성(데이터 미포함)한 후 10번 부서만 선택하여 
-- 이에 대응하는 값을 EMP_10테이블에 입력하라.

CREATE TABLE EMP_10(EMPNO, ENAME, SAL, HIREDATE)
AS
SELECT employee_id, first_name, salary, hire_date
FROM employees
WHERE 1 = 2 

SELECT * FROM EMP_10;

INSERT INTO EMP_10(EMPNO, ENAME, SAL, HIREDATE)
SELECT employee_id, first_name, salary, hire_date
FROM employees
WHERE department_id = 50;

/*
CREATE TABLE EMP_10(EMPNO, ENAME, SAL, HIREDATE)
AS
SELECT employee_id, first_name, salary, hire_date
FROM employees
WHERE department_id = 50;
*/

/*
문제10) 다음은 무결성 제약 조건이 위배된 쿼리이다.
무엇을 위배했는지 알아보도록 합시다.
INSERT INTO EMPLOYEES
VALUES ((100), NULL, 'YOON', 'dbs', NULL, SYSDATE, 'ST_MAN', NULL, NULL, NULL, NULL);	

INSERT INTO EMPLOYEES
VALUES (98, NULL, 'YOON', ('dbs'), NULL, SYSDATE, 'ST_MAN', NULL, NULL, NULL, 100);

INSERT INTO EMPLOYEES
VALUES (98, NULL, 'YOON', 'dbs2', NULL, SYSDATE, 'ST_MAN', NULL, NULL, NULL, (101));
*/

-- TABLE을 작성하라.
-- TEAM : 팀 아이디, 지역, 팀명, 개설 날짜, 전화번호, 홈페이지
-- EXERD ER-WIN -> 이미지 저장, 모델링 파일 저장
CREATE TABLE TEAM(
    TEAM_ID NUMBER(8) CONSTRAINT PK_TEAM_ID PRIMARY KEY,    
    RESION_NAME VARCHAR2(10),   -- 지역
    TEAM_NAME VARCHAR2(50) NOT NULL, -- 팀명
    START_DATE DATE,    -- 개설 날짜
    TEL VARCHAR2(20),   -- 전화번호 
    HOMEPAGE VARCHAR2(20) -- 홈페이지
);
-- PLAYER : 선수번호, 선수명, 등록일, 포지션, 키, 팀 아이디
CREATE TABLE PLAYER(
    PLAYER_ID NUMBER CONSTRAINT PK_PLAYER PRIMARY KEY,
    PLAYER_NAME VARCHAR2(20),
    JOIN_DATE DATE,
    POSITION VARCHAR2(20),
    HEIGHT NUMBER(5, 1),
    TEAM_ID NUMBER(8)
);

ALTER TABLE PLAYER
ADD
CONSTRAINT FK_TEAM_ID FOREIGN KEY(TEAM_ID)
REFERENCES TEAM(TEAM_ID);

-- TEAM 두개만 등록한다.
INSERT INTO TEAM(TEAM_ID, RESION_NAME, TEAM_NAME, START_DATE, TEL, HOMEPAGE)
VALUES(10, '서울', '트윈즈', '02/10/23', '02-123-4567', 'http://www.twins.com');

INSERT INTO TEAM(TEAM_ID, RESION_NAME, TEAM_NAME, START_DATE, TEL, HOMEPAGE)
VALUES(11, '광주', '타이거즈', '92/06/27', '032-1123-2427', 'http://tigers.com');

SELECT * FROM team;

-- 두개의 TEAM에 선수를 각각 3명씩 등록해 본다. 
INSERT INTO player(PLAYER_ID, PLAYER_NAME, JOIN_DATE, POSITION, HEIGHT, TEAM_ID)
VALUES(1, '홍길동', SYSDATE, '투수', 172.1, 10);

INSERT INTO player(PLAYER_ID, PLAYER_NAME, JOIN_DATE, POSITION, HEIGHT, TEAM_ID)
VALUES(2, '일지매', SYSDATE, '2루수', 179.2, 10);

INSERT INTO player(PLAYER_ID, PLAYER_NAME, JOIN_DATE, POSITION, HEIGHT, TEAM_ID)
VALUES(3, '정수동', SYSDATE, '좌익수', 181.2, 11);

INSERT INTO player(PLAYER_ID, PLAYER_NAME, JOIN_DATE, POSITION, HEIGHT, TEAM_ID)
VALUES(4, '성춘향', SYSDATE, '지명타자', 165.3, 11);

INSERT INTO player(PLAYER_ID, PLAYER_NAME, JOIN_DATE, POSITION, HEIGHT, TEAM_ID)
VALUES(5, '임꺽정', SYSDATE, '유격수', 182.7, NULL);

SELECT * FROM team;
SELECT * FROM player;

SELECT t.team_id, p.player_name, t.team_name
FROM team t, player p
WHERE t.team_id = p.team_id
    AND p.player_name = '성춘향';
    
    
-- TABLE을 작성하라.
-- PRODUCT(상품) : 상품번호, 상품명, 상품가격, 상품설명
CREATE TABLE PRODUCT(
    PRODUCT_ID NUMBER CONSTRAINT PK_PRODUCT PRIMARY KEY,
    NAME VARCHAR2(50),
    PRICE NUMBER,
    PRODUCT_DESC VARCHAR2(500)
);

-- CONSUMER(소비자) : 소비자 ID, 이름, 나이
CREATE TABLE CONSUMER(
    USER_ID VARCHAR2(50) CONSTRAINT PK_USER PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL,
    AGE NUMBER
);

-- CART(장바구니) : 장바구니 번호, 소비자 ID, 상품번호, 수량
CREATE TABLE CART(
    CART_ID NUMBER CONSTRAINT PK_CART PRIMARY KEY,
    USER_ID VARCHAR2(50),   -- 외래키
    PRODUCT_ID NUMBER,      -- 외래키
    AMOUNT NUMBER
);

ALTER TABLE CART
ADD
CONSTRAINT FK_USER_ID FOREIGN KEY(USER_ID)
REFERENCES CONSUMER(USER_ID);

ALTER TABLE CART
ADD
CONSTRAINT FK_PRODUCT_ID FOREIGN KEY(PRODUCT_ID)
REFERENCES PRODUCT(PRODUCT_ID);

INSERT INTO PRODUCT(PRODUCT_ID, NAME, PRICE, PRODUCT_DESC)
VALUES(101, '새우깡', 1500, '새우깡에는 새우가 없습니다');

INSERT INTO PRODUCT(PRODUCT_ID, NAME, PRICE, PRODUCT_DESC)
VALUES(102, '포테이토칩', 2300, '감자로 만듭니다');

-- 소비자
INSERT INTO consumer(USER_ID, NAME, AGE)
VALUES('hgd', '홍길동', 24);

INSERT INTO consumer(USER_ID, NAME, AGE)
VALUES('ijm', '일지매', 22);

-- cart
INSERT INTO cart(CART_ID, USER_ID, PRODUCT_ID, AMOUNT)
VALUES(1, 'hgd', 102, 2);

SELECT * FROM cart;