오라클[sql] 연습문제 (고객, 상품, 주문)

연습문제 (고객, 상품, 주문)

 

테이블 생성

 

▶ 고객테이블

CREATE TABLE gogek(

  id VARCHAR2(8),

  name VARCHAR2(15) NOT NULL,

  tel VARCHAR2(13),

  

  CONSTRAINT pk_gogek_id PRIMARY KEY (id),

  CONSTRAINT uq_gogek_tel UNIQUE (tel)

);

 

▶ 상품 테이블

CREATE TABLE sangpum(

  no VARCHAR(6),

  title VARCHAR(50) NOT NULL,

  detail VARCHAR(100),

  count NUMBER(3) NOT NULL,

  price NUMBER(10),

  

  CONSTRAINT pk_sangpum_no PRIMARY KEY (no)

);

 

▶ 주문 테이블

CREATE TABLE jumun(

  no NUMBER(4),

  gogek VARCHAR2(8),

  sangpum VARCHAR(6),

  count NUMBER(3),

  jumunil DATE,

  

  CONSTRAINT pk_jumun_no PRIMARY KEY (no),

  CONSTRAINT fk_jumun_gogek FOREIGN KEY (gogek)

  REFERENCES gogek(id),

  CONSTRAINT fk_jumun_sangpum FOREIGN KEY (sangpum)

  REFERENCES sangpum(no)

);

 

에러발생  이유  분석

 

INSERT INTO gogek ( id, name, tel ) VALUES('babo1234','홍길동', '031-333-3333' );

INSERT INTO gogek ( id, name, tel ) VALUES('babo1111','김길동', '031-111-1111' );

INSERT INTO gogek ( id, name, tel ) VALUES('babo2222','홍길동', '031-222-2222' );

INSERT INTO gogek ( id, name, tel ) VALUES('babo9999','박길동', '031-333-3333' );
-- 에러 : tel > unique 

INSERT INTO gogek ( id, name, tel ) VALUES('babo1234','맹길동', '031-999-9999' );

-- 에러 : id 중복

INSERT INTO gogek ( id, name, tel ) VALUES('babo12345','이길동', '031-333-1234' );

-- 에러 : id 크기

INSERT INTO gogek ( id, name, tel ) VALUES('123','최길동', '031-999-9999' );

INSERT INTO gogek ( id, name ) VALUES('babo','봉길동' );

INSERT INTO gogek ( id, tel ) VALUES('babo1988','031-888-8888' );

-- 에러 : name > NOT NULL

 

INSERT INTO sangpum ( no, title, count,price, detail ) VALUES ('A00001','이쁜바지', 30, 1000, '싸고이뻐서 금방 다 팔

릴 바지입니다' );

INSERT INTO sangpum ( no, title, count,price, detail ) VALUES ('A00002','그냥바지', 20, 500, '그냥이뻐고 편안한 바지입

니다' );

INSERT INTO sangpum ( no, title, count )VALUES ('B00009','윗도리', 20 );

INSERT INTO sangpum ( no, title, count,price ) VALUES ('Z0001','비싼코트', 3, 20000, '겁나 비싼코트입니다' );

-- 에러 : 4개의 데이터 > 5개 입력 : 값의 수가 많음

INSERT INTO sangpum VALUES ('Z00001','비싼코트2', 5, 20000, '겁나 비싼 코트입니다' );

-- 에러 : 컬럼순서대로 입력안함

INSERT INTO sangpum VALUES ('Z00001','비싼코트3','겁나 비싼 코트입니다', 5, 20000);

 

 

INSERT INTO jumun(no, gogek, sangpum,count, jumunil) VALUES ( 1000, 'babo1234', 'A00001', 1, '2016/09/09' );

INSERT INTO jumun(no, gogek, sangpum,count, jumunil) VALUES ( 1001, 'babo1233', 'Z00001', 1, '2016/09/09' );

-- 에러 : 'babo1233' 부모키가 없음

INSERT INTO jumun(no, gogek, sangpum,count, jumunil) VALUES ( 1002, 'Babo1234', 'A00002', 2, '2016/09/10' );

-- 에러 : 'Babo1234' 부모키가 없음, 대소문자 구분함

INSERT INTO jumun(no, gogek, sangpum, count,jumunil) VALUES ( 1003, 'babo1234', 'B00001', 1, '2016/09/11' );

-- 에러 : 'B00001' 부모키가 없음

INSERT INTO jumun(no, gogek, sangpum,count, jumunil) VALUES ( 1004, 'babo', 'Z00001', 1, '2016/10/11' );

INSERT INTO jumun(no, gogek, sangpum,count, jumunil) VALUES ( 1005, 'babo1234', 'B00009', 1, '2016/11/11' );

INSERT INTO jumun(no, gogek, sangpum,count, jumunil) VALUES ( 1006, 'babo1234', 'B00009', 1, '2016/12/12' );

 

 

문제풀기

 

1. 전화번호가 없는 고객은 누구인지 고객명을 검색하세요

SELECT name FROM gogek

WHERE tel IS NULL;

 

2. 홍씨 성을 가진 고객들의 정보를 검색하세요

SELECT * FROM gogek

WHERE name like '홍%';

 

3. babo2222 고객명을 박길동으로 변경하세요

SELECT * FROM gogek WHERE id = 'babo2222';

UPDATE gogek

SET name = '박길동'

WHERE id = 'babo2222';

 

4. 아이디 123 고객의 아이디를 babo123 으로 변경하세요

SELECT * FROM gogek WHERE id = '123';

UPDATE gogek

SET id = 'babo123'

WHERE id = '123';

 

5. 봉길동씨의 전화번호 02-555-5555를 추가하세요

SELECT * FROM gogek WHERE name = '봉길동';

UPDATE gogek

SET tel = '02-555-5555'

WHERE name='봉길동';

 

6. 상품번호 B00009 에 가격 1200원 그리고 “겁나 쌕시한 옷입니다”라는 상품설명을 추가하세요

SELECT * FROM sangpum WHERE no = 'B00009';

UPDATE sangpum

SET price = 1200, detail = '겁나 쌕시한 옷입니다'

WHERE no = 'B00009';

 

7. 이쁜바지 상품이 10개가 팔렸습니다.

SELECT * FROM sangpum WHERE title ='이쁜바지';

UPDATE sangpum

SET count = count-10

WHERE title = '이쁜바지';

 

8. Z00001 상품이 3개가 더 입고 되었습니다.

SELECT * FROM sangpum WHERE no ='Z00001';

UPDATE sangpum

SET count = count+3

WHERE no ='Z00001';

 

9. 상품 가격이 1000원을 넘는 상품들을 검색하세요

SELECT *

FROM sangpum

WHERE price >= 1000;

 

10. 11월 이후에 주문한 상품 정보를 검색하세요

SELECT *

FROM jumun

WHERE  jumunil > '2016/11/30';

 

11. babo 고객이 주문한 상품을 A00002로 변경하고 수량을 2개로 수정하세요

SELECT * FROM jumun WHERE gogek = 'babo';

UPDATE jumun

SET sangpum = 'A00002', count = 2

WHERE gogek = 'babo';

 

12. babo1234 고객이 11월에 주문한 상품을 검색하세요

SELECT * FROM jumun WHERE gogek = 'babo1234'

AND jumunil BETWEEN '16/11/01' AND '16/11/30';

 

13. B00009 상품을 주문한 고객 아이디를 검색하세요

SELECT DISTINCT gogek FROM jumun WHERE sangpum = 'B00009';