오라클 DB 설계 및 활용 8일째 1번 내용

--30번 부서의 최고 급여자 보다 급여를 많이 받는 사람은?

SELECT SAWON_NAME,SAWON_PAY FROM SAWON WHERE SAWON_PAY > ALL(SELECT SAWON_PAY FROM SAWON WHERE BUSER_NUM=30);--찾아진 결과에 대해 AND 연산을 한다...ALL

--30번 부서의 최저 급여자 보다 급여를 많이 받는 사람은?

SELECT SAWON_NAME,SAWON_PAY FROM SAWON WHERE SAWON_PAY > ANY(SELECT SAWON_PAY FROM SAWON WHERE BUSER_NUM=30);--찾아진 결과에 대해 OR 연산을 한다...ANY

--총무부에 근무하는 직원들이 관리하는 고객 출력

1) SUB QUERY
SELECT * FROM GOGEK WHERE GOGEK_DAMSANO IN(SELECT SAWON_NO FROM SAWON WHERE BUSER_NUM=(SELECT BUSER_NO FROM BUSER WHERE BUSER_NAME='총무부'));

2) JOIN --모두 다 INNER JOIN 이용...
SELECT GOGEK_NO,GOGEK_NAME, GOGEK_TEL FROM GOGEK INNER JOIN SAWON ON SAWON_NO=GOGEK_DAMSANO INNER JOIN BUSER ON BUSER_NUM=BUSER_NO WHERE BUSER_NAME='총무부';

--상관 서브 쿼리 : 안쪽 질의 에서 바깥쪽 질의를 참조하고, 다시 안쪽의 질의
--결과를 바깥쪽에서 참조하는 형태

연습1) 각 부서의 최대 급여자는?
SELECT * FROM SAWON A WHERE A.SAWON_PAY=(SELECT MAX(B.SAWON_PAY) FROM SAWON B WHERE A.BUSER_NUM=B.BUSER_NUM);

--SELF JOIN은 하나의 테이블에 별명을 두개 이상 붙인다...

연습2) 급여 순의 3위 이내의 자료 출력(내림차순)

SELECT A.SAWON_NAME ,A.SAWON_PAY FROM SAWON A WHERE 3>(SELECT COUNT(*) FROM SAWON B WHERE B.SAWON_PAY > A.SAWON_PAY) ORDER BY SAWON_PAY DESE;--ROWNUM으로 대체

--SUB QUERY 를 이용한 테이블 생성 및 추가, 수정, 삭제

CREATE TABLE SA1 AS SELECT *FROM SAWON;
DESC SA1;
SELECT * FROM SA1;

CREATE TABLE SA2 AS SELECT * FROM SAWON WHERE 1=0; --구조만....
SELECT * FROM SA2;

INSERT INTO SA2(SAWON_NO,SAWON_NAME,BUSER_NUM) SELECT SAWON_NO,SAWON_NAME, BUSER_NUM FROM SAWON WHERE SAWON_JIK='대리';
CREATE TABLE SA3 AS SELECT SAWON_NO AS BUN,SAWON_NAME AS IRUM, SAWON_PAY AS PAY FROM SAWON WHERE 1=0;
SELECT * FROM SA3
DESC AS3;

--복수 테이블에 보수 컬럼 추가하기

CREATE TABLE SA4 AS SELECT SAWON_NO,SAWON_NAME,SAWON_JIK FROM SAWON WHERE 1=0;
CREATE TABLE SA5 AS SELECT SAWON_NO,SAWON_NAME,SAWON_PAY, SAWON_SEX FROM SAWON WHERE 1=0;
INSERT ALL INTO SA4 VALUES(SAWON_NO,SAWON_NAME,SAWON_JIK) INTO SA5 VALUES(SAWON_NO,SAWON_NAME,SAWON_PAY,SAWON_SEX) SELECT SAWON_NO,SAWON_NAME,SAWON_JIK,SAWON_PAY, SAWON_SEX FROM SAWON WHERE BUSER_NUM=10;
SELECT * FROM SA4;
SELECT * FROM SA5;

--조건에 따른 복수 테이블에 복수 컬럼 추가

INSERT ALL WHEN SAWON_JIK='사원' THEN INTO SA4 VALUES(SAWON_NO,SAWON_NAME,SAWON_JIK) WHEN SAWON_SEX ='남' THEN INTO SA5 VALUES( SAWON_NO,SAWON_NAME,SAWON_PAY, SAWON_SEX) SELECT SAWON_NO,SAWON_NAME,SAWON_PAY, SAWON_SEX FROM SAWON WHERE BUSER_NUM IN(20,30);

--UPDATE 에서 서브 쿼리 사용

SELECT * FROM SA1;
UPDATE SA1 SET SAWON_JIK=(SELECT SAWON_JIK FROM SAWON WHERE SAWON_NAME='홍길동') WHERE SAWON_NO=2;

--DALETE 에서 서브 쿼리 사용

DELETE FROM SA1 WHERE SAWON_NO IN(SELECT DISTINCT GOGEK_DAMSANO FROM GOGEK);

--ROWNUM 을 이용하여 TOP-N을 할 수 있다.

ROWNUM : 행이 패치된 순서(행이 읽혀진 상태)
SELECT 추출시 원하는 갯수만큼 추출이 가능
연산자는 <,<= 만 사용 가능
MY SQL의 LIMIT와 동일

SELECT SAWON_NO,SAWON_NAME,SAWON_PAY FROM SAWON WHERE ROWNUM <= 5;

--급여 순위 3위 이내 자료 출력

SELECT ROWNUM ,SAWON_NAME,SAWON_PAY FROM(SELECT SAWON_NAME, SAWON_PAY FROM SAWON ORDER BY SAWON_PAY DESC) WHERE ROWNUM <= 3;

SELECT * FROM (SELECT SAWON_NAME, SAWON_PAY FROM SAWON ORDER BY SAWON_PAY DESC) WHERE ROWNUM <= 3;

--가장 최근에 입사한 직원 5위 이내 출력

SELECT ROWNUM, SAWON_NAME, SAWON_IBSAIL FROM (SELECT SAWON_NAME,SAWON_IBSAIL FROM SAWON ORDER BY SAWON_IBSAIL DESC) WHERE ROWNUM <= 5;