데베 과제2

CREATE TABLE member
(   member_id      VARCHAR(100)           NOT NULL,
    member_name    VARCHAR(100)    NOT NULL,
    member_pn      integer          NULL,
    member_rank     VARCHAR(100)     NULL,
    PRIMARY KEY (member_id)                  );
    
CREATE TABLE item
(   item_code     integer           NOT NULL,
    item_name   VARCHAR(100)      NOT NULL,
    sellection    VARCHAR(10)         NULL,
    item_location   integer       NULL,
	PRIMARY KEY (item_code)                  );
    
CREATE TABLE sell
(   member_id      VARCHAR(100)           NOT NULL,
    item_code     integer            NOT NULL,
    is_reorder    VARCHAR(10)      NOT NULL,
    price          integer          NOT NULL,
    PRIMARY KEY (member_id, item_code)                  );
    
member_id, item_code, is_reorder, price 
    
INSERT INTO 	member (member_id, member_name, member_pn, member_rank)
     VALUES 	(Ruddy12, '박지성', 00050012083,'gold');
INSERT INTO 	member (member_id, member_name, member_pn, member_rank)
     VALUES 	(Chris23, '김연아', 00060012083,'silver');
INSERT INTO 	member (member_id, member_name, member_pn, member_rank)
     VALUES 	(Summer34, '손연재', 00040012083,'bronze');
INSERT INTO 	member (member_id, member_name, member_pn, member_rank)
     VALUES 	(Try56, '추신수', 00080012083,'gold');
COMMIT;

INSERT INTO 	item (item_code, item_name,  sellection, item_location)
     VALUES 	(101, '	바삭한 감자칩', '식품', 244);
INSERT INTO 	item (item_code, item_name,  sellection, item_location)
     VALUES 	(201, '	최강 접착 테이프', '문구류', 349);
INSERT INTO 	item (item_code, item_name,  sellection, item_location)
     VALUES 	(301, '수건', '욕실용품', 211);
INSERT INTO 	item (item_code, item_name,  sellection, item_location)
     VALUES 	(401, '	단백 만점 닭가슴살', '식품', 543);
INSERT INTO 	item (item_code, item_name,  sellection, item_location)
     VALUES 	(501, '상큼 오이', '식품', 567);
INSERT INTO 	item (item_code, item_name,  sellection, item_location)
     VALUES 	(601, '향기로운 샴푸', '욕실용품', 232);
INSERT INTO 	item (item_code, item_name,  sellection, item_location)
     VALUES 	(701, '	슬리퍼', '생활용품', 892);
COMMIT;

INSERT INTO 	sell (member_id, item_code, is_reorder, price )
     VALUES 	(Ruddy12, 301, True, 19000);
INSERT INTO 	member (member_id, member_name, member_pn, member_rank)
     VALUES 	(Chris23, 301, False, 34000);
INSERT INTO 	member (member_id, member_name, member_pn, member_rank)
     VALUES 	(Summer34, 401, True, 3900);
INSERT INTO 	member (member_id, member_name, member_pn, member_rank)
     VALUES 	(Try56, 201, False, 2500);
COMMIT;

SELECT *
  FROM 	item
 WHERE 	item_name = '단백 만점 닭가슴살'
 
SELECT *
 FROM item
 WHERE item_name LIKE '향기%'; -- '잠실'로 시작하는 단어를 조회

 
UPDATE member
SET item_location = 854
WHERE item_location = 244
COMMIT;


DELETE FROM sell
WHERE item_code = 401;