26일차//[Oracle] TABLE 생성 (종합 예제)

팀 테이블

-- TABLE을 작성하라.
-- TEAM : 팀 아이디, 지역, 팀명, 개설 날짜, 전화번호, 홈페이지
CREATE TABLE TEAM(
    TEAMID NUMBER(8) CONSTRAINT PK_TEAM_ID PRIMARY KEY,
    RESIONNAME VARCHAR2(10),
    TEAMNAME VARCHAR2(30) NOT NULL,
    STARTDATE DATE,
    TEL VARCHAR2(20),
    HOMEPAGE VARCHAR2(30)
);

-- PLAYER : 선수번호, 선수명, 등록일, 포지션, 키, 팀 아이디
CREATE TABLE PLAYER(
    PLAYERID NUMBER CONSTRAINT PK_PLAYER PRIMARY KEY,
    PLAYERNAME VARCHAR2(20),
    JOINDATE DATE,
    POSITION VARCHAR2(20),
    HEIGHT NUMBER(5, 1),
    TEAMID NUMBER(8)
);

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

-- TEAM 두개만 등록한다.
INSERT INTO TEAM(TEAMID, RESIONNAME, TEAMNAME, STARTDATE, TEL, HOMEPAGE)
VALUES(10, '서울', '베어즈', SYSDATE, '02-123-4567', 'http://www.bears.com');

INSERT INTO TEAM(TEAMID, RESIONNAME, TEAMNAME, STARTDATE, TEL, HOMEPAGE)
VALUES(20, '대구', '라이온즈', SYSDATE, '031-234-5678', 'http://www.lions.com');

-- 두개의 TEAM에 선수를 각각 3명씩 등록해 본다. 
INSERT INTO player(PLAYERID, PLAYERNAME, JOINDATE, POSITION, HEIGHT, TEAMID)
VALUES(100, '홍길동', '02/05/23', '투수', 181.1, 20);

INSERT INTO player(PLAYERID, PLAYERNAME, JOINDATE, POSITION, HEIGHT, TEAMID)
VALUES(101, '일지매', '04/07/16', '타자', 185.1, 10);

INSERT INTO player(PLAYERID, PLAYERNAME, JOINDATE, POSITION, HEIGHT, TEAMID)
VALUES(102, '성춘향', '05/09/12', '투수', 176.5, 20);

INSERT INTO player(PLAYERID, PLAYERNAME, JOINDATE, POSITION, HEIGHT, TEAMID)
VALUES(103, '정수동', '01/11/13', '투수', 179.3, 10);

-- 선수를 입력하면 그 선수의 팀 명과 전화번호, 홈페이지가 출력되도록 합니다.
SELECT p.playername, t.teamname, t.tel, t.homepage
FROM player p, team t
WHERE p.teamid = t.teamid
    AND p.playername = '성춘향';

온라인 마켓

--온라인 마켓 TABLE을 작성하라.

-- PRODUCT(상품) : 상품번호, 상품명, 상품가격, 상품설명
CREATE TABLE PRODUCT(
    PRODUCTID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50),
    PRICE NUMBER,
    PRODUCT_DESC VARCHAR2(500)
);

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

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

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

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

CREATE SEQUENCE PRODUCT_SEQ
INCREMENT BY 1
START WITH 1000;


-- 상품 테이블에 상품을 등록합니다(개수는 원하는 데로).
INSERT INTO PRODUCT(PRODUCTID, NAME, PRICE, PRODUCT_DESC)
VALUES(PRODUCT_SEQ.NEXTVAL, '새우깡', 1500, '맛있어요');

INSERT INTO PRODUCT(PRODUCTID, NAME, PRICE, PRODUCT_DESC)
VALUES(PRODUCT_SEQ.NEXTVAL, '포테이토칩', 2300, '짜요');

INSERT INTO PRODUCT(PRODUCTID, NAME, PRICE, PRODUCT_DESC)
VALUES(PRODUCT_SEQ.NEXTVAL, '커피', 2500, '끝내줘요');

-- 소비자를 등록합니다.
INSERT INTO CONSUMER(USERID, NAME, AGE)
VALUES('HGD', '홍길동', 24);

INSERT INTO CONSUMER(USERID, NAME, AGE)
VALUES('SCH', '성춘향', 16);


-- 소비자가 쇼핑한 상품을 추가합니다.
INSERT INTO CART(CARTID, USERID, PRODUCTID, AMOUNT)
VALUES(1, 'SCH', 1001, 2);

INSERT INTO CART(CARTID, USERID, PRODUCTID, AMOUNT)
VALUES(2, 'SCH', 1000, 1);

INSERT INTO CART(CARTID, USERID, PRODUCTID, AMOUNT)
VALUES(3, 'HGD', 1001, 2);

-- 쇼핑한 상품을 출력합니다.
SELECT *
FROM cart
WHERE userid = 'SCH';

SELECT c.userid, c.productid, p.name, p.price
FROM cart c, product p
WHERE c.productid = p.productid
    AND c.userid = 'SCH';