1.사례

• 고객이 해약을 하여 정산을 거쳤을 때 고객에게 지불할 보증금반환금/청구해야 할 위약금/기기철거비용이 하나의 로우에 있다고 가정

• 필요한 다른 처리가 완료된 후 이 값들을 다른 계정과목을 갖는 별도의 로우로 생성

2.테이블 설계

(1)전표테이블(CHIT)

고객번호(CUST_NO/PK),계정과목(ACOUNT/PK),금액(AMT),생성일자(CRE_DT)

(2)계약정산테이블(CONSET)

고객번호(CUST_NO/PK),상품명(PRO_NM),보증금반환금(DEP_AMT),위약금(PEN_AMT),기기철거비(DEM_AMT),계약일(CON_DT),해약일(CAN_DT)

3.CREATE TABLE

CREATE  TABLE ORA9.CHIT  (
  2        CUST_NO                            VARCHAR2(4)      NOT NULL
  3      , ACOUNT                              VARCHAR2(4)      NOT NULL
  4      , AMT                                     NUMBER(10)       NULL
  5      , CRE_DT                               VARCHAR2(8)      NOT NULL
  6      )
  7      TABLESPACE ORA9;

테이블이 생성되었습니다.

COMMENT ON TABLE  ORA9.CHIT IS '전표';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CHIT.CUST_NO IS '고객번호';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CHIT.ACOUNT IS '계정과목';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CHIT.AMT IS '금액';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CHIT.CRE_DT IS '생성일자';
주석이 생성되었습니다.

ALTER TABLE  ORA9.CHIT ADD (
  2        CONSTRAINT CHIT_PK PRIMARY KEY ( CUST_NO,ACOUNT )
  3           USING INDEX TABLESPACE ORA9 );

테이블이 변경되었습니다.

CREATE  TABLE ORA9.CONSET  (
  2      CUST_NO                                VARCHAR2(4)        NOT NULL
  3      , PRO_NM                               VARCHAR2(2)        NOT NULL
  4      , DEP_AMT                              NUMBER(10)         NULL
  5      , PEN_AMT                              NUMBER(10)         NULL
  6      , DEM_AMT                             NUMBER(10)          NULL
  7      , CON_DT                                VARCHAR2(8)        NOT NULL
  8      , CAN_DT                                 VARCHAR2(8)       NOT NULL
  9      )
 10     TABLESPACE ORA9;

테이블이 생성되었습니다.

COMMENT ON TABLE  ORA9.CONSET IS '계약정산';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.CUST_NO IS '고객번호';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.PRO_NM IS '상품명';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.DEP_AMT IS '보증금반환금';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.PEN_AMT IS '위약금';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.DEM_AMT IS '기기철거비';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.CON_DT IS '계약일';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.CAN_DT IS '해약일';
주석이 생성되었습니다.

ALTER TABLE  ORA9.CONSET ADD (
  2        CONSTRAINT CONSET_PK PRIMARY KEY ( CUST_NO )
  3          USING INDEX TABLESPACE ORA9 );

테이블이 변경되었습니다.

4.INSERT

INSERT INTO ORA9.CONSET VALUES('0001','A',1000000,100000,50000,'20001121','20010304');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0002','B',2000000,200000,0,'19990123','20030602');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0003','C',5600000,560000,50000,'20070123','20090403');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0004','A',1000000,100000,50000,'20050405','20060330');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0005','G',0,50000,0,'20080601','20090403');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0006','H',2500000,250000,70000,'20090104','20090221');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0007','E',0,50000,30000,'20090217','20090407');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0008','G',1000000,100000,0,'20040523','20090403');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0009','D',2300000,230000,50000,'20090101','20090325');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0010','B',3000000,300000,80000,'20030402','20040521');
1 개의 행이 만들어졌습니다.

COMMIT;
커밋이 완료되었습니다.

5. 결과

TRUNCATE TABLE ORA9.CHIT;

테이블이 잘렸습니다.

SELECT *
  2         FROM ORA9.CHIT;

선택된 레코드가 없습니다.

INSERT INTO CHIT(CUST_NO,CRE_DT,ACOUNT,AMT)
  2        SELECT x.CUST_NO /*전표테이블.고객번호*/
  3                      ,TO_CHAR (SYSDATE, 'YYYYMMDD') /*전표테이블.생성일자*/
  4                      ,DECODE ( y.NO2, 1, '1234', 2, '5678', 3, '9876') /*전표테이블.계정과목*/
  5                      ,DECODE ( y.NO, 1, x.DEP_AMT, 2, x.PEN_AMT, 3, x.DEM_AMT) /*전표테이블.금액*/
  6           FROM  ORA9.CONSET x, ORA9.COPY_T y
  7        WHERE  x.CAN_DT = '20090403' /*전표로 출력할려는 해약일*/
&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND&nbsp;&nbsp;y.NO <= 3;

9 개의 행이 만들어졌습니다.

SELECT&nbsp;\*
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM ORA9.CHIT;

CUST_NO&nbsp;&nbsp;&nbsp; ACOUNT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AMT&nbsp;&nbsp; CRE_DT
========&nbsp;&nbsp; ======&nbsp;&nbsp;&nbsp; =======&nbsp;&nbsp; =======

9 개의 행이 선택되었습니다.

SELECT&nbsp;\*
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp; ORA9.CONSET
&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp;CAN_DT = '20090403';

CUST_NO&nbsp; PRO_NM&nbsp;&nbsp;&nbsp; DEP_AMT&nbsp;&nbsp;&nbsp; PEN_AMT&nbsp;&nbsp;&nbsp; DEM_AMT&nbsp;&nbsp; CON_DT&nbsp;&nbsp;&nbsp;&nbsp; CAN_DT
=======&nbsp;&nbsp; =======&nbsp;&nbsp;&nbsp; =======&nbsp;&nbsp;&nbsp;&nbsp; =======&nbsp;&nbsp;&nbsp;&nbsp; ========&nbsp;&nbsp; =======&nbsp;&nbsp; &nbsp;=======

3&nbsp;개의 행이 선택되었습니다.

6. 주석

두 개의 테이블은 연결고리가 존재하지 않는다.

결과는 당연히 카테시안 곱만큼의 조인결과가 생성될 것이다.

입력받은 날짜에 해약한 고객들마다 3개씩 로우가 생성 되고 필요한 가공을 통해 입력된다.

7.보완

• 반환할 보증금, 청구할 위약금, 기기철거비가 없는 경우, 즉 금액이 0인 경우에는 전표를 발생시키지 않는다.

TRUNCATE TABLE ORA9.CHIT;

테이블이 잘렸습니다.

SELECT *
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM ORA9.CHIT;

선택된 레코드가 없습니다.

INSERT INTO CHIT(CUST_NO,CRE_DT,ACOUNT,AMT)
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT x.CUST_NO /*전표테이블.고객번호*/
&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,TO_CHAR (SYSDATE, 'YYYYMMDD') /*전표테이블.생성일자*/
&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,DECODE ( y.NO2, 1, '1234', 2, '5678', 3, '9876') /*전표테이블.계정과목*/
&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,DECODE ( y.NO, 1, x.DEP_AMT, 2, x.PEN_AMT, 3, x.DEM_AMT) /*전표테이블.금액*/
&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp; ORA9.CONSET x, ORA9.COPY_T y
&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; x.CAN_DT = '20090403' /*전표로 출력할려는 해약일*/
&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND&nbsp; y.NO IN ( DECODE(x.DEP_AMT, 0, NULL, 1)
&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,DECODE(x.PEN_AMT, 0, NULL, 2)
&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,DECODE(x.DEM_AMT, 0, NULL, 3));

6 개의 행이 만들어졌습니다.

SELECT *
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM ORA9.CHIT;

CUST_NO&nbsp;&nbsp;&nbsp; ACOUNT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AMT&nbsp;&nbsp; CRE_DT
========&nbsp;&nbsp; ======&nbsp;&nbsp;&nbsp; =======&nbsp;&nbsp; =======

6 개의 행이 선택되었습니다.

SELECT&nbsp;\*
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM ORA9.CONSET
&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE&nbsp;CAN_DT = '20090403';

CUST_NO&nbsp; PRO_NM&nbsp;&nbsp;&nbsp; DEP_AMT&nbsp;&nbsp;&nbsp; PEN_AMT&nbsp;&nbsp;&nbsp; DEM_AMT&nbsp;&nbsp; CON_DT&nbsp;&nbsp;&nbsp;&nbsp; CAN_DT
=======&nbsp;&nbsp; =======&nbsp;&nbsp;&nbsp; =======&nbsp;&nbsp;&nbsp;&nbsp; =======&nbsp;&nbsp;&nbsp;&nbsp; ========&nbsp;&nbsp; =======&nbsp;&nbsp; &nbsp;=======

0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 20080601&nbsp;&nbsp;&nbsp; 20090403

0&nbsp;&nbsp; 20040523&nbsp;&nbsp;&nbsp; 20090403

3&nbsp;개의 행이 선택되었습니다.

8. 실습 : 나열될 칼럼을 여러 레코드로 생성

(1)아래와 같은 테이블이 있는 때 특정 부서(AA)의 모든 입출금 내역을 검색하라

<예적금원장>

CREATE TABLE ACCOUNT(
MANAGE_NUM&nbsp;&nbsp; VARCHAR2(10),
DEPT_COD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(10)
)

<입금내역>
CREATE TABLE INPUT(
MANAGE_NUM&nbsp;&nbsp; VARCHAR2(10),
INPUT_DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(8),
AMT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER
)

<출금내역>
CREATE TABLE OUTPUT(
MANAGE_NUM&nbsp;&nbsp; VARCHAR2(10),
OUTPUT_DATE&nbsp;&nbsp;&nbsp;VARCHAR2(8),
AMT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NUMBER
)

(2) 테이블 생성/INSERT

SQL> CREATE TABLE ACCOUNT(
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MANAGE_NUM&nbsp;&nbsp; VARCHAR2(10),
&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DEPT_COD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(10)
&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );

테이블이 생성되었습니다.

SQL> INSERT INTO ACCOUNT VALUES('111','AA');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO ACCOUNT VALUES('222','AA');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO ACCOUNT VALUES('333','AA');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO ACCOUNT VALUES('444','BB');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO ACCOUNT VALUES('555','CC');
1 개의 행이 만들어졌습니다.

SQL> CREATE TABLE INPUT(
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MANAGE_NUM&nbsp;&nbsp; VARCHAR2(10),
&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INPUT_DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(8),
&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AMT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER
&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );

테이블이 생성되었습니다.

SQL> INSERT INTO INPUT VALUES('111','20060301',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO INPUT VALUES('111','20060302',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO INPUT VALUES('222','20060303',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO INPUT VALUES('333','20060304',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO INPUT VALUES('333','20060304',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO INPUT VALUES('555','20060301',1000);
1 개의 행이 만들어졌습니다.

SQL> CREATE TABLE OUTPUT(
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MANAGE_NUM&nbsp;&nbsp; VARCHAR2(10),
&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OUTPUT_DATE&nbsp;&nbsp; VARCHAR2(8),
&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AMT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER
&nbsp; 5&nbsp; );

테이블이 생성되었습니다.

SQL> INSERT INTO OUTPUT VALUES('111','20060301',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO OUTPUT VALUES('111','20060302',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO OUTPUT VALUES('111','20060303',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO OUTPUT VALUES('222','20060301',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO OUTPUT VALUES('444','20060301',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO OUTPUT VALUES('555','20060301',1000);
1 개의 행이 만들어졌습니다.

SQL> CREATE TABLE TAB9(
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL1&nbsp;&nbsp; VARCHAR2(10),
&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AMT&nbsp;&nbsp;&nbsp;&nbsp; NUMBER
&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );

테이블이 생성되었습니다.

SQL> INSERT INTO TAB9 VALUES('A',10);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB9 VALUES('A',20);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB9 VALUES('B',30);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB9 VALUES('D',40);
1 개의 행이 만들어졌습니다.

SQL> CREATE TABLE TAB10(
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL1&nbsp;&nbsp; VARCHAR2(10),
&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AMT&nbsp;&nbsp;&nbsp; NUMBER
&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );

테이블이 생성되었습니다.

SQL> INSERT INTO TAB10 VALUES('A',50);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB10 VALUES('C',60);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB10 VALUES('C',70);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB10 VALUES('D',80);
1 개의 행이 만들어졌습니다.

(3)결과

SQL> SELECT COL1,SUM(AMT)
&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM(
&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT COL1,AMT FROM TAB9
&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UNION ALL
&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT COL1,AMT FROM TAB10
&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) GROUP BY COL1;

COL1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(AMT)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
====&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;========&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;