오라클 11g와 함께하는 SQL과 PL/SQL 5장 연습문제 정답 정리

★연습문제1

1-1. 표의 논리적 스키마를 참고하여 상품관리 테이블을 생성하시오.

칼럼명	영문명	데이터형	크기	NN	키	참조테이블
상품코드	Product_Code	문자형	10	NN	PK
상품명	Product_Name	문자형	20	NN
규격	Standard	문자형	20
단위	Unit	문자형	10
단가	Unit_Price	숫자형	7	NN
재고수량	Left_Qty	숫자형	5	NN
생산처	Company	문자형	20
상품이미지명	ImageName	문자형	20
상품정보	Info	문잦형	50
상세소개	Detail_Info	문자형	255

CREATE TABLE EC_Product (
Product_Code VARCHAR2(10),
Product_Name VARCHAR2(20) NOT NULL,
Standard     VARCHAR2(20),
Unit         VARCHAR2(10),
Unit_Price   NUMBER(7)    NOT NULL,
Left_Qty     NUMBER(5)    NOT NULL,
Company      VARCHAR2(20),
ImageName    VARCHAR2(20),
Info         VARCHAR2(50),
Detail_Info  VARCHAR2(255),
CONSTRAINT EC_Product_pk PRIMARY KEY(Product_Code));

---

1-2. 표의 논리적 스키마를 참고하여 회원관리 테이블을 생성하시오.

칼럼명	영문명	데이터형	크기	NN	키	참조테이블
회원 ID	UserID	문자형	10	NN	PK
회원비밀번호	Passwd	문자형	10	NN
회원명	Name	문자형	10	NN
주민등록번호	Regist_No	문자형	14	NN	UK
eMail 주소	Email	문자형	20
전화번호	Telephone	문자형	13	NN
주소	Address	문자형	40
구매실적	Buycash	숫자형	9	기본값 0
가입일자	Timestamp	날짜형		기본값 SYSDATE

CREATE TABLE EC_Member (
UserID    VARCHAR(10),
Passwd    VARCHAR(10)   NOT NULL,
Name      VARCHAR(10)   NOT NULL,
Regist_No VARCHAR(14)   CONSTRAINT EC_Member_uk UNIQUE NOT NULL,
Email     VARCHAR(20),
Telephone VARCHAR(13)   NOT NULL,
Address   VARCHAR(40),
Buycash   NUMBER(9)     DEFAULT 0,
Timestamp DATE          DEFAULT SYSDATE,
CONSTRAINT EC_Member_pk PRIMARY KEY(UserID));

---

1-3. 표의 논리적 스키마를 참고하여 장바구니 테이블을 생성하시오.

칼럼명	영문명	데이터형	크기	NN	키	참조테이블
주문번호	Order_No	문자형	10	NN	PK
주문자 ID	Order_ID	문자형	10	NN	FK1	회원관리
상품코드	Product_Code	문자형	10	NN	FK2	상품관리
주문수량	Order_Qty	숫자형	3	NN
주문일자	Order_Date	날짜형		기본값 SYSDATE

CREATE TABLE EC_Basket(
Order_No     VARCHAR(10),
Order_ID     VARCHAR(10) NOT NULL,
Product_Code VARCHAR(10) NOT NULL,
Order_Qty    NUMBER(3)   NOT NULL,
Order_Date   DATE        DEFAULT SYSDATE,
CONSTRAINT EC_Basket_pk  PRIMARY KEY(Order_No),
CONSTRAINT EC_Basket_fk1 FOREIGN KEY(Order_ID)     REFERENCES EC_Member(UserId),
CONSTRAINT EC_Basket_fk2 FOREIGN KEY(Product_Code) REFERENCES EC_Product(Product_Code));

---

1-4. 표의 논리적 스키마를 참고하여 주문처리 테이블을 생성하시오.

칼럼명	영문명	데이터형	크기	NN	키	참조테이블
주문번호	Order_No	문자형	10	NN	PK
주문자ID	Order_ID	문자형	10	NN
상품코드	Product_Code	문자형	10	NN
주문수량	Order_Qty	숫자형	3	NN
결제방법	Csel	문자형	10
결제금액	CMoney	숫자형	9
결제일자	Cdate	날짜형
배달일자	Mdate	날짜형
구분	Gubun	문자형	10

CREATE TABLE EC_Order(
Order_No     VARCHAR2(10),
Order_ID     VARCHAR2(10) NOT NULL,
Product_Code VARCHAR2(10) NOT NULL,
Order_Qty    NUMBER(3)    NOT NULL,
Csel         VARCHAR2(10),
Cmoney       NUMBER(9),
Cdate        DATE,
Mdate        DATE,
Gubun        VARCHAR2(10),
CONSTRAINT EC_Order_pk PRIMARY KEY(Order_No));

---

2. 사용자가 생성한 테이블명을 모두 출력하시오.

SELECT * FROM TAB;

---

3. 장바구니(EC_Basket) 테이블의 구조를 출력하시오

DESC EC_Basket

---

4. 상품관리(EC_Product) 테이블의 상세소개(Detail_info) 칼럼을 삭제하시오.

ALTER TABLE EC_Product DROP COLUMN Detail_info;

---

5. 상품관리(EC_Product) 테이블의 상품정보(Info) 칼럼을 40자로 줄이시오.

ALTER TABLE EC_Product MODIFY(INFO VARCHAR2(40));

---

6. 주문처리(EC_Order) 테이블의 구분(Gubun) 칼럼을 20자로 늘리시오.

ALTER TABLE EC_Order MODIFY(Gubun VARCHAR2(20));

---

7. 주문처리(EC_Order) 테이블의 상품코드(Product_Code) 칼럼을 상품관리(EC_Product 테이블을 참조하여 외부 키를 추가하시오.

ALTER TABLE EC_Order
ADD CONSTRAINT EC_Order_fk
    FOREIGN KEY(Product_Code) REFERENCES EC_Product(Product_Code);

---

8. 주문처리(EC_Order) 테이블에 지정된 제약조건을 출력하시오.

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME ='EC_ORDER';

---

9. 주문처리(EC_Order) 테이블에 지정한 제약조건명, 칼럼명, 복합칼럼(POSITION) 유무를 출력하시오.

SELECT CONSTRAINT_NAME,COLUMN_NAME,POSITION
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME ='EC_ORDER';

---

★연습문제2

1. 표의 논리적 스키마를 참고하여 자유게시판(Board) 테이블을 생성하시오.

칼럼명	영문명	데이터형	크기	NN	키
게시물 번호	B_Id	숫자형	5	NN	PK
등록자	B_Name	문자형	20	NN
비밀번호	B_Pwd	문자형	20	NN
이메일 주소	B_Email	문자형	20	NN
제목	B_Title	문자형	80	NN
내용	B_Content	문자형	2000	NN
등록일자	B_Date	날짜형		기본값 SYSDATE
조회수	B_Hit	숫자형	5	기본값 0
IP 주소	B_Ip	문자형	15

 

CREATE TABLE Board(
B_Id      NUMBER(5),
B_Name    VARCHAR2(20)   NOT NULL,
B_Pwd     VARCHAR2(20)   NOT NULL,
B_Email   VARCHAR2(20)   NOT NULL,
B_Title   VARCHAR2(80)   NOT NULL,
B_Content VARCHAR2(2000) NOT NULL,
B_Date    DATE           DEFAULT SYSDATE,
B_Hit     NUMBER(5)      DEFAULT 0,
B_IP      VARCHAR2(15),
CONSTRAINT Board_pk PRIMARY KEY(B_Id));

---

2. 표를 참고하여 답변형 게시판에 필요한 칼럼을 Board 테이블에 추가하시오.

칼럼명	영문명	데이터형	크기	NN	키
동일 게시물 참조번호	B_Ref	숫자형	5	기본값 0
동일 게시물 단계번호	B_Step	숫자형	5	기본값 0
동일 게시물 위치번호	B_Order	숫자형	5	기본값 0

ALTER TABLE Board
ADD(B_Ref  NUMBER(5) DEFAULT 0,
    B_Step NUMBER(5) DEFAULT 0, 
    B_Order NUMBER(5) DEFAULT 0);

---

3. Board 테이블의 제목(B_Title) 칼럼 길이를 100자로 늘리시오

ALTER TABLE Board
MODIFY(B_TiTLE VARCHAR2(100));

---

4. Board 테이블의 비밀번호(B_Pwd) 칼럼을 NULL로 수정하시오.

ALTER TABLE Board
MODIFY(B_Pwd VARCHAR2(20) NULL);

---

5. Board 테이블의 IP 주소(b_ip) 칼럼을 삭제하시오

ALTER TABLE Board
DROP COLUMN B_Ip;

---

6. Board 테이블의 구조를 확인하시오

DESC Board

---

7. 회원관리(EC_Member) 테이블의 회원 ID(UserID) 칼럼에 영소문자(a부터 z까지)로 제한하시오. 체크 제한조건이 동작하는지 다음 INSERT문을 실행해 보시오.

INSERT INTO EC_Member (UserID, Passwd, Name, Regist_No, Telephone) VALUES ('srlee','1234','이소라','821001-2******','010-1234-1234');

INSERT INTO EC_Member (UserID, Passwd, Name, Regist_No, Telephone) VALUES ('20park','1234','박연수','810604-1******','010-2345-2345');

ALTER TABLE EC_MEMBER
ADD(CONSTRAINT Member_CK CHECK(UserID BETWEEN 'a' AND 'z'));
INSERT INTO EC_Member(UserId,Passwd,Name,Regist_No,Telephone)
VALUES('srlee','1234','이소라','821001-2******','010-1234-1234');
INSERT INTO EC_Member(UserId,Passwd,Name,Regist_No,Telephone)
VALUES('20park','1234','박연수','810604-1******','010-2345-2345');

---

8. Board 테이블의 동일게시물번호(B_Step) 칼럼명을 "B_Level"로 변경하시오

ALTER TABLE Board
RENAME COLUMN B_Step TO B_Level;

---

9. 주문처리(EC_Order) 테이블의 기본 키를 삭제하시오

ALTER TABLE EC_Order DROP PRIMARY KEY;

---

10. Board 테이블의 B_Email 칼럼에 유일성(unique) 제약조건을 추가하시오.

ALTER TABLE Board
MODIFY(B_Email VARCHAR2(20) CONSTRAINT BOARD_uk UNIQUE);

---

11. Board 테이블을 Free_Board 테이블명으로 변경하시오.

RENAME Board TO FREE_Board;

---

12. 장바구니(EC_Basket) 테이블을 삭제하시오.

DROP TABLE EC_Basket;

---

13. 휴지통의 임시 테이블들을 삭제하시오.

PURGE RECYCLEBIN;