201102

EX

부서테이블에서 상위부서가 null인 부서에 속한 직원 수를 조회하시오 (최종적으로나타내줘야할 메인쿼리, 중간에나와야하는게 서브쿼리)

문제를 보면 서브쿼리랑 메인쿼리에서 처리해야하는일을 구별해야한다.

SELECT  COUNT(*) AS 직원수
 FROM   EMPLOYEES
 WHERE  DEPARTMENT_ID = ( 상위부서가 NULL인부서)

서브쿼리 - 상위브서가 NULL인 부서코드

SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE PARENT_ID IS NULL

결합시켜보자

SELECT  COUNT(*) AS 직원수
 FROM   EMPLOYEES
 WHERE  DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE PARENT_ID IS NULL)

IS NOT NULL  하면 반대의 경우가 나올까? NO

DEPARTMENT_ID 는 하난데 서브쿼리는 다중행이 나오니까

다중행연산자를 써야함 IN ANY SOME 등...

SELECT  COUNT(*) AS 직원수
 FROM   EMPLOYEES
 WHERE  DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE PARENT_ID IS NOT NULL)
                        


                        

 

EX

사원테이블에서 자기가 속한 부서의 평균급여보다 많은 급여를 받는 사원수를 부서별로 조회하시오.

과별 평균급여가 나올텐데.. 그중 자기가 속한 부서의 평균급여와 비교해야함

SELECT  A.DEPARTMENT_ID 부서번호
        COUNT(*) 사원수
FROM    EMPLOYEES A
WHERE   A.SALARY >= (자신의 부서의 평균급여)
GROUP BY A.DEPARTMENT_ID
ORDER BY 1;

부서도 같아야하고 샐러리도 조건보다 커야하므로 섭쿼리가 저기있으면 안됨

SELECT  A.DEPARTMENT_ID 부서번호,
        COUNT(*) 사원수
FROM    EMPLOYEES A,
        (SELECT ROUND(AVG(SALARY)) SAVG, 
                DEPARTMENT_ID DID
        FROM EMPLOYEES
        GROUP BY DEPARTMENT_ID) B
WHERE   A.SALARY>=B.SAVG
        AND A.DEPARTMENT_ID=B.DID
GROUP BY A.DEPARTMENT_ID
ORDER BY 1;

 

EX

직책변동테이블(JOB_HISTORY)테이블의 자료와 사원테이블에 있는 부서코드가 동일한 사원정보를 조회하시오

Alias는 사원번호 , 사원명, 사원명 , 부서코드, 부서명, 직무코드

SELECT  EMPLOYEE_ID 사원번호 , 
        (사원테이블과 직무테이블에서 사원번호가동일한)사원명, 
        DEPARTMENT_ID 부서코드, 
        (사원테이블과 직무테이블에서 부서번호가 동일한) 부서명, 
        JOB_ID 직무코드
FROM    JOB_HISTORY

괄호가 두개.. 섭쿼리가 두개가 있단말

SELECT  EMPLOYEE_ID 사원번호 , 
        (SELECT B.EMP_NAME
        FROM    EMPLOYEES B
        WHERE   B.EMPLOYEE_ID = A.EMPLOYEE_ID) 사원명, 
        DEPARTMENT_ID 부서코드, 
        (SELECT DEPARTMENT_NAME
        FROM    EMPLOYEES C, DEPARTMENTS D
        WHERE   C.DEPARTMENT_ID = D.DEPARTMENT_ID
        AND     C.DEPARTMENT_ID = A.DEPARTMENT_ID) 부서명, 
        JOB_ID 직무코드
FROM    JOB_HISTORY A; 

오류뜬다.한 사람이 두개의 부서명을 가지고있는경우가 있기때문에

SELECT  DISTINCT A.EMPLOYEE_ID 사원번호 , 
        (SELECT B.EMP_NAME
        FROM    EMPLOYEES B
        WHERE   B.EMPLOYEE_ID = A.EMPLOYEE_ID) 사원명, 
       
        C.DEPARTMENT_ID 사원부서코드, 
        B.DEPARTMENT_NAME 부서명,
        A.JOB_ID 직무코드
FROM    JOB_HISTORY A, DEPARTMENTS B, EMPLOYEES C
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
    AND A.DEPARTMENT_ID=C.DEPARTMENT_ID
ORDER BY   1; 

 

조인이나 서브쿼리를 없애려고 집합연산자 (UNION등)을 많이 쓰는편임

EX

상위부서( PARENT_ID) 가 90번에 속한 모든 부서의 부서별 평균급여를 구하시오

상위부서는 직원테이블에 존재하지않는다. DEPARTMENT에 가야잇음

SELECT  ROUND(AVG(SALARY)) 평균급여, 
        A.DEPARTMENT_ID 부서코드,
        B.DN 부서명
FROM    EMPLOYEES A, 
        (SELECT DEPARTMENT_ID DI, DEPARTMENT_NAME DN
        FROM DEPARTMENTS 
        WHERE PARENT_ID=90) B
WHERE   B.DI=A.DEPARTMENT_ID
GROUP BY A.DEPARTMENT_ID, B.DN;

서브쿼리 안써도 가능

SELECT  ROUND(AVG(A.SALARY)) 평균급여,
        B.DEPARTMENT_NAME 부서명,
        A.DEPARTMENT_ID 부서코드
FROM    EMPLOYEES A, 
        DEPARTMENTS B
WHERE   B.DEPARTMENT_ID=A.DEPARTMENT_ID
AND B.PARENT_ID=90
GROUP BY A.DEPARTMENT_ID,B.DEPARTMENT_NAME ;

쌤풀이

SELECT  ROUND(AVG(SALARY)) 평균급여, 
        A.DEPARTMENT_ID 부서코드
FROM    EMPLOYEES A
WHERE   A.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID DI
        FROM DEPARTMENTS 
        WHERE PARENT_ID=90)
GROUP BY A.DEPARTMENT_ID;

 

EX

서브쿼리를 이용한 UPDATE하거나 INSERT하는 경우 

1. 서브쿼리를 이용한 테이블생성

(1) 테이블생성: 별도의 테이블을 생성하고 자료를 복사할 수 있음

기본키와 외래키는 복사되지 않음

*사용형식

CREATE TABLE 테이블명
AS
SELECT문;  <<이게 서브쿼리

 

EX 

PROD테이블을 GOODS테이블로 복사하시오

CREATE TABLE GOODS
AS
SELECT *
FROM PROD;

기본키나 외래키는 복사되지않았따.

만약 조건을 부여했따면??

CREATE TABLE GOODS
AS
    SELECT *
    FROM PROD
    WHERE PROD_PRICE>=100000;

조건에 맞는 35개 행만으로 GOODS테이블이 만들어졌다.

이건 테스트용으로 테이블을 쓰고자할때 (찐으로 변경하고자하는 테이블은 너무위험해서 연습용)

똑같이복사해서 다른테이블로만들어두고 그걸로처리하는경우

 

 

*재고수불테이블을만들어볼거이

컬럼명	데이터타입	제약사항
REMAIN_YEAR	CHAR(4)	PK
REMAIN_PROD	VARCHAR2(10)	PK/FK
REMAIN_J_00   (기초재고)	NUMBER(5)	DEFAULT 0
REMAIN_I        (입고수량)	NUMBER(5)	DEFAULT 0
REMAIN_O      (출고수량)	NUMBER(5)	DEFAULT 0
REMAIN_J_99   (기말재고)	NUMBER(5)	DEFAULT 0
REMAIN_DATE  (조사한 날짜)	DATE	DEFAULT SYSDATE

 

CREATE TABLE REMAIN(
REMAIN_YEAR 	CHAR(4),
REMAIN_PROD	    VARCHAR2(10),
REMAIN_J_00	    NUMBER(5)	DEFAULT 0,
REMAIN_I 	    NUMBER(5)	DEFAULT 0,
REMAIN_O   	    NUMBER(5)	DEFAULT 0,
REMAIN_J_99 	NUMBER(5)	DEFAULT 0,
REMAIN_DATE 	DATE	    DEFAULT SYSDATE,

CONSTRAINT PK_REMAIN PRIMARY KEY(REMAIN_YEAR,REMAIN_PROD),
CONSTRAINT FK_REMAIN FOREIGN KEY(REMAIN_PROD)
    REFERENCES PROD(PROD_ID));

REMAIN 에

PROD 테이블의 PROD_ID,, 넣어야함

서브쿼리를 이용한 INSERT문!

*사용형식

INSERT INTO 테이블명[(컬럼명)]
서브쿼리;  <<유일하게 서브쿼리를 괄호로 안넣어주는데가 여기임

VALUES 키워드 생략

서브쿼리 기술시 '( )' 생략

 

EX

재고수불테이블(REMAIN)에 상품테이블의 상품코드를 모두 입력하시오

년도는 '2005'이다.

INSERT INTO REMAIN(REMAIN_YEAR, REMAIN_PROD)
    SELECT '2005',PROD_ID
    FROM PROD;

일괄로 입력할때 주로 사용되어짐.

---

주소테이블 싹 날리고 주소를 넣어줄거임

엑셀로 다운받아서 한꺼번에 오라클에 넣을수잇음 (공공데이터 쓰는경우 써먹을 수 있다.)

---

갑자기 다른거..

서브쿼리를 이용한 UPDATE문

값을 채워주려고 한다.

EX

*사용형식

UPDATE 테이블명 [별칭]     <<이때 업데이트문의 테이블명은 프롬절과 같아서,
SET 컬럼명 | (컬럼명,컬럼명,...)=(서브쿼리)      <<이 서브쿼리에서 써먹을수있음
[WHERE 조건];

SET다음에 오는 (컬럼명,컬럼명.,,,) 으로 여러개의 컬럼값을 동시변경하려고 할떄는

서브쿼리의 SERECT절에 오는 컬럼의 갯수와 완전히 일치해야함

 

EX

재고수불테이블의 기초재고(REMAIN_J_00)컬럼에

상품테이블의 적정재고(PROD_PROPERSTOCK) 값으로 변경하고

날짜를 2005-01-01로 수정하시오

>>이렇게 됐으면 당연히 기말재고도 변경해야함

즉 3개의 컬럼을 변경해야함

REMAIN_J_99=REMAIN_J_00+REMAIN_I-REMAIN_O

이기 때문에

UPDATE  테이블명 [별칭]
SET     컬럼명 | (컬럼명,컬럼명,...)=(서브쿼리)
[WHERE 조건];

UPDATE  REMAIN 
SET     (REMAIN_J_00, REMAIN_J_99, REMAIN_DATE)=
        (SELECT PROD_PROPERSTOCK,PROD_PROPERSTOCK,'20050101'
        FROM PROD WHERE PROD_ID=REMAIN_PROD);

74개행이 업데이트되었다.

AND REMAIN_YEAR='2005' 이걸 뒤에 더 써주라는데...효율ㅇ성?때문에... 무튼...