210409

3) 복수컬럼 서브쿼리
- 서브쿼리의 실행결과가 여러개의 컬럼과 여러개의 행을 리턴해
주는 쿼리

 예1) 부서번호가 30번인 사원들의 급여와 커미션이 같은 사원들의
 이름과 급여,커미션을 조회하시오.
SELECT ENAME, SAL, COMM FROM EMP
WHERE (SAL, COMM) IN(SELECT SAL, COMM FROM EMP WHERE DEPTNO=30);

 4) TOP-N절

  # ROWID,ROWNUM
   - 오라클에서 테이블을 생성하면 자동으로 생성되는 컬럼
   - ROWID : ROW의 고유번호(중간에 ROW를 삭제해도 변하지 않음)
   - ROWNUM : 행의 인덱스번호(중간에 ROW를 삭제하면 변함)

  예1) EMP테이블에서 급여가 가장 낮은 5명의 사원번호,이름,급여를 조회하시오.
SELECT*FROM
(
	SELECT EMPNO, ENAME, SAL
	FROM EMP
	ORDER BY SAL ASC
)
WHERE ROWNUM<=5;

  Q1) 입사일이 가장 빠른 사원 3명의 사원번호,이름,입사일 조회해 보세요.
SELECT*FROM
(
	SELECT EMPNO, ENAME, HIREDATE
	FROM EMP
	ORDER BY HIREDATE
) WHERE ROWNUM<4;

  Q2) 부서별 월급총액이 가장 많은 순서대로 2위까지 해당하는
  부서와 월급총액을 구하세요.
SELECT*
FROM
(
	SELECT DEPTNO, SUM(SAL) SUMSAL
	FROM EMP
	GROUP BY DEPTNO
	ORDER BY SUMSAL DESC;
) WHERE ROWNUM<3;

  Q3) EMP테이블에서 인원수가 가장 많은 JOB과 인원수를
      조회해 보세요.(ROWNUM사용)
SELECT*FROM
(
	SELECT JOB, COUNT(*) 인원수
	FROM EMP
	GROUP BY JOB
	ORDER BY 인원수 DESC
) WHERE ROWNUM=1;

-- 참고
SELECT JOB, MAX(인원수)
FROM
(
	SELECT JOB, COUNT(*) 인원수
	FROM EMP
	GROUP BY JOB
	ORDER BY 인원수 DESC
);

 5) 스칼라서브쿼리
   - SELECT 절에 오는 서브쿼리
   - 조회되는 데이터의 양이 적을 경우는 JOIN보다 수행속도가
     빠르다.
   - 스칼라서브쿼리는 결과값이 행이 하나 열이 하나인 경우만
     가능하다.

  예1) 사원번호,사원이름,부서번호,부서이름을 조회하시오.
SELECT EMPNO, ENAME, DEPTNO, (SELECT DNAME FROM DEPT WHERE DEPTNO=E.DEPTNO)
FROM EMP E;

  Q1) 사원번호,이름,직업,부서번호,근무지(LOC)을 조회해 보세요.
SELECT EMPNO, ENAME, JOB, DEPTNO, (SELECT LOC FROM DEPT WHERE E.DEPTNO=DEPTNO)
FROM EMP E;

(6) 조인 (***)
- 하나의 테이블로는 원하는 컬럼정보를 얻어올 수 없을때 관련된 테이블을 논리적으로 결합하여 컬럼정보를 얻어오는 방법

   1) EQUI 조인 
     - 가장 일반적인 조인. WHERE절에 공통컬럼들이 동등연산자(=)에 의해 비교되어 결합되는 조인

 형식)
 SELECT 컬럼명1,컬럼명2,..
 FROM 테이블명1,테이블명2
 WHERE 조인조건;

예1) 사원번호,이름,급여,부서번호,부서명을 조회하시오. 
SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO;

예2) 30번 부서 사원들의 사원번호,이름,부서명,직업을 조회하시오.
SELECT EMPNO, ENAME, DNAME, JOB
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=30;

Q1) 부서명이 'ACCOUNTING'에 근무하는 사원들의 이름,급여,입사일,부서명,부서위치를 조회하시오.
SELECT ENAME, SAL, HIREDATE, DNAME, LOD
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND D.DNAME='ACCOUNTING';

Q2) 전체 사원의 평균급여보다 많은 사원의 사원번호,이름,급여,부서명 조회하기
SELECT EMPNO, ENAME, SAL, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND SAL>(SELECT AVG(SAL) FROM EMP);

 예3) 
 -- 학생테이블(학생번호,이름,전화번호)
 DROP TABLE STUDENT;
 CREATE TABLE STUDENT
 (
	SNUM NUMBER(5) PRIMARY KEY, -- 기본키(중복X,NULL값X)
	NAME VARCHAR2(10),
	PHONE VARCHAR2(20)
 );

 INSERT INTO STUDENT VALUES(100,'홍길동','0101111234');
 INSERT INTO STUDENT VALUES(101,'이영희','0102220987');
 INSERT INTO STUDENT VALUES(102,'김철수','0103338888');
 COMMIT; -- DB에 최종적으로 저장하기

 -- 성적테이블(성적번호,학생번호,과목명,점수)
 DROP TABLE GRADE;
CREATE TABLE GRADE
(
	GNUM NUMBER(5) PRIMARY KEY,
	SNUM NUMBER(5) REFERENCES STUDENT(SNUM), -- 참조키설정
	SUBJECT VARCHAR2(10),
	SCORE NUMBER(3)
);

INSERT INTO GRADE VALUES(1,100,'국어',100);
INSERT INTO GRADE VALUES(2,100,'영어',90);
INSERT INTO GRADE VALUES(3,101,'국어',80);
COMMIT;

    Q1) 모든 학생번호,이름,과목명,점수를 조회해 보세요.
SELECT S.NUM, S.NAME, G.SUBJECT, G.SCORE
FROM STUDENT S, GRADE G
WHERE S.SNUM=G.SNUM;

    Q2) 100 번 학생의 이름,전화번호,과목명,점수를 조회해 보세요.
SELECT S.NAME, S.PHONE, G.SUBJECT, G.SCORE
FROM STUDENT S, GRADE G
WHERE S.SNUM=G.SNUM AND S.SNUM=100;

   2) NON-EQUI 조인
    - 공통컬럼이 존재하지 않는 경우에도 조인이 가능하다.
   
   예1) 사원번호,이름,급여,급여등급(GRADE)를 조회하시오.
SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL>=S.LOSAL AND E.SAL<=S.HISAL;

   예2) 사원번호, 이름, 부서명, 급여, 급여등급(GRADE)를 조회하시오. (3개 테이블 조인하기)
SELECT EMPNO, ENAME, DNAME, SAL, GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO=D.DEPTNO AND E.SAL>=S.LOSAL AND E.SAL<=S.HISAL;

      -- 학점정보 테이블
  DROP TABLE CREDITS;
  CREATE TABLE CREDITS
  (	
	CREDIT VARCHAR2(10) PRIMARY KEY,
	MINSCORE NUMBER(3),
	MAXSCORE NUMBER(3)
  );
  INSERT INTO CREDITS VALUES('A',90,100);
  INSERT INTO CREDITS VALUES('B',80,89);
  INSERT INTO CREDITS VALUES('C',70,79);
  INSERT INTO CREDITS VALUES('D',60,69);
  INSERT INTO CREDITS VALUES('F',0,59);
  COMMIT;

  - 학생번호,이름,과목명,점수,학점을 조회해 보세요.
SELECT S.SUM, S.NAME, G.SUBJECT, G.SCORE, C.CREDIT
FROM STUDENT S, GRADE G, CREDITS C
WHERE S.SNUM=G.SNUM AND G.SCORE BETWEEN C.MINSCORE AND C.MAXSCORE;

   3) CROSS조인
    - 2개이상의 테이블에서 조인될때 조인조건에 의해 결합이 발생되지 않는 경우.
      (실수로 조인결합조건을 안 쓴 경우가 대부분 또는 테스트용데이터를 한번에 많이 만들고자 할때 사용되는 경우가 있음)
SELECT E.EMPNO, ENAME, DNAME
FROM EMP E, DEPT D;

   4) SELF 조인
   - 참조해야 하는 컬럼이 자신의 테이블의 다른 컬럼인 경우 사용되는 조인

   예) 모든 사원의 사원번호,사원이름,매니져번호,매니져이름을 조회하시오.
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR=E2.EMPNO;

   Q1) EMP테이블에서 사원번호,이름,매니져번호,매니져이름,매니져직업을 조회하세요.
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.ENAME, E2.JOB
FROM EMP E1, EMP E2
WHERE E1.MGR=E2.EMPNO;

   5) OUTER 조인
- 한쪽 테이블에는 해당하는 데이터가 존재하는데 다른쪽 테이블에는 데이터가 존재하지 않는 경우 모든 데이터를 추출하는 조인방법
 
예1) 모든 부서번호와 부서명,근무사원이름을 조회하시오.
SELECT D.DEPTNO, D.DNAME, E.ENAME
FROM EMP E, DEPT D
WHERE E.DEPTNO(+)=D.DEPTNO;

   Q1) 모든 부서번호,부서명,사원이름,급여를 조회하시오.(OUTER조인)
SELECT D.DEPTNO, D.DNAME, ENAME, SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO(+)=D.DEPTNO
ORDER BY D.DEPTNO ASC;

   Q2) STUDENT테이블과 GRADE테이블을 조인해서 학생번호,이름,과목,점수를 출력해 보세요.(OUTER조인)
SELECT S.SNUM, S.NAME, G.SUBJECT, G.SCORE
FROM STUDENT S, GRADE G
WHERE S.SNUM=G.SNUM(+);