오라클 DB 설계 및 활용 7일째 2번 내용

2부 시간입니다. 이제는 서브 쿼리에 대해서 배워볼까요

-- SUB QUERY : 질의 속에 질의가 있는 형태( 안쪽의 결과를 바깥쪽에서 참조)

-- 채진실과 직급이 같은 직원 출력
SELECT SAWON_JIK FROM SAWON_WHERE SAWON_NAME='채진실';
SELECT * FROM SAWON WHERE SAWON_JIK='사원';

SELECT * FROM SAWON WHERE SAWON_JIK=(SELECT SAWON_JIK FROM SAWON WHERE SAWON_NAME='채진실')

WHERE 문에다 SELECT 에 넣어 줄 수가 있습니다. 이것이 바로 SUB QUERY 입니다.

-- 직급이 *대리* 중 가장 먼저 입사한 직원은
SELECT * FROM SAWON WHERE SAWON_JIK='대리' AND SAWON_IBSAIL=(SELECT MIN(SAWON_IBSAIL) FROM SAWON WHERE SAWON_JIK='대리');

-- 인천에서 근무하는 직원 출력
SELECT * FROM SAWON WHERE BUSER_NUM=(SELECT BUSER_NO FROM BUSER WHERE BUSER_LOC = '인천');

-- 인천에서 근무하지 않는 직원 출력

SELECT * FROM SAWON WHERE BUSER_NUM IN(SELECT BUSER_NO FROM BUSER WHERE NOT BUSER_LOC='인천');
(여러 데이타를 받을시에 IN 데이타를 사용한다.)

-- 담당 직원이 이미라와 같은 고객 자료 출력
SELECT GOGEK_NO, GOGEK_NAME, GOGEK_TEL FROM GOGEK WHERE GOGEK_DAMSANO=(SELECT SAWON_NO FROM SAWON WHERE SAWON_NAME='이미라');

-- 고객인 장도리를 담당하는 사원이 담당하는 고객 자료 출력
SELECT GOGEK_DAMSANO 담당사원번호, GOGEK_NO, GOGEK_NAME, GOGEK_TEL FROM GOGEK WHERE GOGEK_DAMSANO=(SELECT GOGEK_DAMSANO FROM GOGEK WHERE GOGEK_NAME='강나루');

-- 고객 중 차일포와 나이가 같은 자료 출력
SELECT * FROM GOGEK WHERE SUBSTR(GOGEK_JUMIN, 1,2)=(SELECT SUBSTR(GOGEK_JUMIN, 1,2) FROM GOGEK WHERE GOGEK_NAME='차일포');

CREATE TABLE PUM1 (BUN NUMBER, PUMMOK VARCHAR2(20));

INSERT INTO PUM1 VALUES(1, '귤')
INSERT INTO PUM1 VALUES(2, '바나나')
INSERT INTO PUM1 VALUES(3, '한라봉')

CREATE TABLE PUM2(NUM NUMBER, SANGPUM VARCHAR2(20));

INSERT INTO PUM2 VALUES(10, '수박');
INSERT INTO PUM2 VALUES(20, '참외');
INSERT INTO PUM2 VALUES(30, '토마토');
INSERT INTO PUM2 VALUES(40, '딸기');

SELECT * FROM PUM2;

COMMIT;

SELECT BUN AS 번호, PUMMOK AS 품목 FROM PUM1 UNION SELECT NUM, SANGPUM FROM PUM2;

AS 별명을 앞 쪽에 써 주어야 한다.

CREATE TABLE MSA1 AS SELECT SAWON_NO, SAWON_NAME, SAWON_PAY FROM SAWON WHERE SAWON_NO <= 10;
CREATE TABLE MSA2 AS SELECT SAWON_NO, SAWON_NAME, SAWON_PAY FROM SAWON WHERE SAWON_NO <= 10 AND SAWON_JIK = '사원';

INSERT INTO MSA1 VALUES(100, ' 소나타', 4500);
INSERT INTO MSA2 VALUES(200, ' 이슬비', 4550);

SELECT * FROM MSA1;
SELECT * FROM MSA2;

MERGE INTO MSA1 A USING MSA2 B ON(A.SAWON_NO=B.SAWON_NO)
WHEN MATCHED THEN UPDATE SET A.SAWON_NAME=B.SAWON_NAME,
A.SAWON_PAY=B.SAWON_PAY WHEN NOT MATCHED THEN INSERT VALUES(B.SAWON_NO,B.SAWON_NAME,B.SAWON_PAY);
SELECT SAWON_JIK FROM SAWON WHERE SAWON_NAME='채진실';

SELECT * FROM SAWON WHERE SAWON_JIK='사원';

SELECT * FROM SAWON WHERE SAWON_JIK=(SELECT SAWON_JIK FROM SAWON WHERE SAWON_NAME='채진실');

-- 직급이 *대리* 중 가장 먼저 입사한 직원은
SELECT * FROM SAWON WHERE SAWON_JIK='대리' AND SAWON_IBSAIL=(SELECT MIN(SAWON_IBSAIL) FROM SAWON WHERE SAWON_JIK='대리');

SELECT * FROM SAWON WHERE BUSER_NUM=(SELECT BUSER_NO FROM BUSER WHERE BUSER_LOC ='인천');

-- 인천에서 근무하는 직원 출력
SELECT * FROM SAWON WHERE BUSER_NUM=(SELECT BUSER_NO FROM BUSER WHERE BUSER_LOC = '인천');

-- 인천에서 근무하지 않는 직원 출력

SELECT * FROM SAWON WHERE BUSER_NUM IN(SELECT BUSER_NO FROM BUSER WHERE NOT BUSER_LOC='인천');
(여러 데이타를 받을시에 IN 데이타를 사용한다.)

-- 담당 직원이 강나루와 같은 고객 자료 출력

SELECT GOGEK_NO, GOGEK_NAME, GOGEK_TEL FROM GOGEK;

SELECT GOGEK_DAMSANO 담당사원번호, GOGEK_NO, GOGEK_NAME, GOGEK_TEL FROM GOGEK WHERE GOGEK_DAMSANO=(SELECT GOGEK_DAMSANO FROM GOGEK WHERE GOGEK_NAME='강나루');

SELECT * FROM GOGEK WHERE SUBSTR(GOGEK_JUMIN, 1,2)=(SELECT SUBSTR(GOGEK_JUMIN, 1,2) FROM GOGEK WHERE GOGEK_NAME='차일포');

SAWON, BUSER, GOGEK 테이블을 사용한다.

문1) 2000년 이후에 입사한 남자 중 급여를 가장 많이 받는 직원은?

SELECT SAWON_NAME FROM SAWON WHERE TO_CHAR(SAWON_IBSAIL, 'YYYY')>=2000
AND SAWON_PAY=(SELECT MAX(SAWON_PAY) FROM SAWON WHERE SAWON_SEX='남' AND TO_CHAR(SAWON_IBSAIL, 'YYYY')>=2000) AND SAWON_SEX='남'SELECT MAX(SAWON_PAY) FROM SAWON WHERE SAWON_SEX='남';


문2) 평균급여보다 급여를 많이 받는 직원은?

SELECT SAWON_NAME, SAWON_PAY FROM SAWON WHERE SAWON_PAY > (SELECT AVG(SAWON_PAY) FROM SAWON)


문3) '한국남' 직원의 입사 이후에 입사한 직원은?

SELECT SAWON_NAME, SAWON_IBSAIL FROM SAWON WHERE SAWON_IBSAIL >
(SELECT SAWON_IBSAIL FROM SAWON WHERE SAWON_NAME = '한국남')

문4) 직급이 과장인 사람 중에서 급여를 가장 많이 받는 사람은?


SELECT SAWON_NAME, SAWON_JIK, SAWON_PAY FROM SAWON WHERE SAWON_JIK = '과장' AND
SAWON_PAY = (SELECT MAX(SAWON_PAY) FROM SAWON WHERE SAWON_JIK = '과장')


문5) 2000 ~ 2005 사이에 입사한 총무부,영업부,전산부 직원 중 급여가 가장 적은 사람은?

(직급이 NULL인 자료는 작업에서 제외)

SELECT SAWON_NAME, SAWON_PAY FROM SAWON, BUSER WHERE BUSER_NO = BUSER_NUM AND SAWON_PAY =
(SELECT MIN(SAWON_PAY) FROM SAWON WHERE BUSER_NAME IN('총무부','영업부','전산부')
AND TO_CHAR(SAWON_IBSAIL, 'YYYY') >= 2000 AND TO_CHAR(SAWON_IBSAIL, 'YYYY') < 2005)



문6) 이미라, 이순신과 직급이 같은 사람은 누구인가? (직급이 NULL인 경우 임시직으로 표현)

SELECT SAWON_NAME, NVL(SAWON_JIK, '임시직') FROM SAWON
WHERE SAWON_JIK IN((SELECT SAWON_JIK FROM SAWON WHERE SAWON_NAME = '이미라'),
(SELECT SAWON_JIK FROM SAWON WHERE SAWON_NAME = '이순신'))

문7) 과장 중에서 최대급여, 최소급여를 받는 사람은?

SELECT SAWON_NAME, SAWON_PAY, SAWON_JIK FROM SAWON
WHERE SAWON_JIK = '과장' AND SAWON_PAY =
(SELECT MAX(SAWON_PAY) FROM SAWON WHERE SAWON_JIK='과장')
OR SAWON_PAY = (SELECT MIN(SAWON_PAY) FROM SAWON WHERE SAWON_JIK='과장');

문8) 20번 부서의 최소급여보다 많은 사람은?

SELECT BUSER_NUM, SAWON_NAME, SAWON_PAY FROM SAWON
WHERE SAWON_PAY < (SELECT MIN(SAWON_PAY) FROM SAWON WHERE BUSER_NUM = 20)


문9) 30번 부서의 평균급여보다 급여가 많은 '대리' 는 몇명인가?

SELECT BUSER_NUM, SAWON_NAME, SAWON_PAY, SAWON_JIK FROM SAWON
WHERE SAWON_JIK = '대리' AND SAWON_PAY > (SELECT AVG(SAWON_PAY) FROM SAWON WHERE BUSER_NUM = 30 AND SAWON_JIK = '대리')


문10) 고객을 확보하고 있는 직원들의 이름, 직급, 부서명을 입사일 별로 출력하라.

SELECT SAWON_IBSAIL, SAWON_NAME, SAWON_JIK, BUSER_NAME FROM SAWON, BUSER WHERE BUSER_NUM = BUSER_NO(+) AND
SAWON_NAME IN(SELECT SAWON_NAME FROM SAWON, GOGEK WHERE GOGEK_DAMSANO = SAWON_NO) ORDER BY SAWON_IBSAIL

SELECT * FROM SAWON



문11) 이순신과 같은 부서에 근무하는 직원과 해당 직원이 관리하는 고객 출력

(고객은 나이가 30 이하면 '청년', 50 이하면 '중년', 그 외는 '노년'으로 표시하고,
고객 연장자 부터 출력)

출력 ==> 직원명 부서명 부서전화 직급 고객명 고객전화 고객구분
한송이 총무부 123-1111 사원 백송이 333-3333 청년

SELECT SAWON_NAME 직원명, BUSER_NAME 부서명, BUSER_TEL 부서전화, SAWON_JIK 직급, GOGEK_NAME 고객명,
GOGEK_TEL 고객전화, CASE WHEN (TO_CHAR(SYSDATE, 'YYYY') - (SUBSTR(GOGEK_JUMIN, 1,2) + 1900)) <= 30 THEN
'청년' WHEN (TO_CHAR(SYSDATE, 'YYYY') - (SUBSTR(GOGEK_JUMIN, 1,2) + 1900)) <= 50 THEN
'중년' ELSE '노년' END 고객구분 FROM SAWON, GOGEK, BUSER WHERE SAWON_NO = GOGEK_DAMSANO AND BUSER_NUM = BUSER_NO AND
BUSER_NUM = (SELECT BUSER_NUM FROM SAWON WHERE SAWON_NAME='이순신')

SELECT * FROM BUSER

정도가 되겠네요. 오츠까레 사마데시따~ -_- 틀린 부분 지적 바랍니다. ^^

그리고 더효율적인 방법도 있으니 그거 올려주셔도 좋고요.^^

일본어 돌격~~~~^^