2021.07.09 수업일지

- ZOODB REVIEW

- 정규화가 잘되어있는 테이블은 보안성이 높다.

-- Q9. 이주성 사육사가 담당하는 동물이름 검색 (3개의 테이블 조인)

SELECT ZK.ZK_NAME, AN.AN_NAME
FROM AM JOIN ZK
ON AM.AM_ZKCODE = ZK.ZK_CODE
JOIN AN
ON AM.AM_ANCODE = AN.AN_CODE
WHERE ZK.ZK_CODE = 1;

 

-- Q10. 꼬끼리를 담당하는 사육사.

SELECT AN_NAME, ZK_NAME
FROM AM JOIN ZK
ON AM_ZKCODE = ZK_CODE
JOIN AN
ON AM_ANCODE = AN_CODE
WHERE AN_NAME = '꼬끼리';

 

-- Q11. 사육사별 담당 동물이름 검색 ( 사육사 이름으로 오름정렬)

SELECT ZK_NAME, AN_NAME
FROM AM JOIN ZK
ON AM_ZKCODE = ZK_CODE
JOIN AN
ON AM_ANCODE = AN_CODE
ORDER BY ZK_NAME ASC;

 

-- Q12. 먹이 중 과일의 단가는 20% 증가, 개수는 20% 감소해서 검색(서브뭐리)

SELECT AF_NAME 먹이, AF_COST*1.2 단가, AF_QTY*0.8 개수
FROM AF
WHERE AF_FCCODE=(SELECT FC_CODE FROM FC WHERE FC_NAME='과일');

--WHERE X = Y;(일대일)
--WHERE X IN(Y);(하나여도 되고 하나 이상도 됨)
--WHERE X OR(Y);(모두다 만족)

 

-- Q13. 이름에 '신'이 포함된 사용사의 담당동물의 개체수를 100% 증가하시오(UPDATE)
-- 조인을 하여 VIEW를 만들어 UPDATE, INSERT 수행하지 않는다.
-- UPDATE, INSERT, DELETE는 물리적인 테이블만 가능하다. 논리적 X

- 나)

SELECT * FROM AN;
UPDATE animal SET AN_QTY = AN_QTY*2
WHERE AN_CODE IN(
SELECT AN_CODE
FROM AM JOIN ZK
ON AM_ZKCODE = ZK_CODE
JOIN AN
ON AM_ANCODE = AN_CODE
WHERE ZK_NAME LIKE '%신%'
);

 

- 선생님)

UPDATE animal SET AN_QTY = AN_QTY * 2
WHERE AN_CODE IN(
SELECT AM_ANCODE FROM AM
WHERE AM_ZKCODE =
(SELECT ZK_CODE FROM ZK
WHERE ZK_NAME LIKE '%신%')
);

 

-- Q14. 이주성 사육사가 퇴사하고(삭제), 김주성 사육사가 입사하여(삽입)
-- 담당하던 동물을 인수 받는다(수정)
-- UPDATE TABLE NAME SET 컬럼명1=수정1, 컬럼명2=수정2...
-- 위와 같이 가능하다.
-- 1. 삽입 2. 수정 3. 삭제 순으로 한다.
-- ZK기본키를 AM외래키 참조하고 있기 때문에 삭제를 먼저할 수는 없다.
-- 1. 삽입

INSERT INTO zoo_keeper VALUES(5, '김주성', DEFAULT);
SELECT * FROM ZK;

 

-- 2. 수정

UPDATE animal_manager SET AM_ZKCODE=(SELECT ZK_CODE FROM ZK WHERE ZK_NAME='김주성')
WHERE AM_ZKCODE=(SELECT ZK_CODE FROM ZK WHERE ZK_NAME='이주성');
SELECT * FROM AM;

 

-- 3. 삭제

DELETE FROM ZK WHERE ZK_NAME='이주성';

 

-- 4. 확인

SELECT * FROM ZK;
SELECT * FROM AM;

 

-- Q15. 절지류 거미를 동물원에 새로 들어고 담당사육사를 이방원으로 해보자.
-- 해보기

 

-- Q16. 먹이공급시간, 하루 먹이 공급테이블
-- 해보기

 

- BACK TO ICIA TABLE

- VIEW(뷰) : 논리적인 가상의 테이블, 주로 SELECT용 사용

- 검색성 용이, 보안성

SELECT * FROM EMP;
DELETE FROM EMP WHERE JOB IS NULL;

 

- 뷰생성

CREATE OR REPLACE VIEW SAL_MAX_VIEW
AS
SELECT DEPTNO, MAX(SAL) SAL
FROM EMP
GROUP BY DEPTNO;

SELECT * FROM SAL_MAX_VIEW;

 

-- Q1. 위 뷰 활용 부서별 최대 급여 받는 사원이름, 부서명, 최대급여 검색

SELECT EMP.ENAME, DEPT.DEPTNO, M.SAL
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
JOIN SAL_MAX_VIEW M
ON M.SAL=EMP.SAL AND M.DEPTNO=EMP.DEPTNO;

 

 

- 시퀀스, NEXTVAL, CURRVAL

CREATE TABLE SEQ_TEST(
    NO NUMBER PRIMARY KEY,
    NAME NVARCHAR2(10) NOT NULL
);

SELECT * FROM SEQ_TEST;

INSERT INTO SEQ_TEST(NO,NAME) VALUES(1, 'AAA');
INSERT INTO SEQ_TEST(NO,NAME) VALUES(2, 'BBB');
SELECT MAX(NO)+1 FROM SEQ_TEST; -- MAX는 FULL SCAN
-- 임계영역이 보장되지 않는다. 사용자 제한을 하지 않는다. 중복이 발생할 수 있다.
-- 귀찮더라도 시퀀스를 만드는게 좋다.
CREATE SEQUENCE SEQ_NO; --옵션 생략시 1부터 N까지 증가 N은 지정 X
SELECT SEQ_NO.NEXTVAL FROM DUAL;
SELECT SEQ_NO.CURRVAL FROM DUAL;
INSERT INTO SEQ_TEST(NO,NAME) VALUES(SEQ_NO.NEXTVAL, 'CCC'); --임계영역보장, 중복 X

 

- [NEW!] eXERD

- 프로젝트 생성은 이클립스랑 유사하다.
- eXERD 파일 생성도 이클립스 패키지 생성이랑 유사하다.

- 도메인은 변경해서 사용해도 좋다. 

-테이블은 해당 버튼을 클릭해서 생성한다.

- 마우스 오른쪽 클릭하면 위와 같이 나타내고 컬럼을 생성할 수 있다. 단축키 ctrl + enter

테이블의 보고싶은 목록을 설정할 수 있다.

기본키 설정할 수 있다.

비식별 부모 먼저 선택하고 자식

식별도 부모 선택 후 자식 선택

- 비식별로 하고 싶으니 비식별로 설정한다.
- 식별로 하면 반드시 주문해야할 수도 있기 때문이다.

이와 같이 만들어본다.