10/17 22-2 쿼리

서브쿼리

 

다른 하나의 SQL문장안에 기술된 SELECT문장을 말한다.(SELECT문 안의 SELECT문)

조건을 먼저 살펴보면

-서브쿼리는 괄호로 묶어야 함 

-서브쿼리만을 단독 실행 시 실행이 가능해야 한다

-두 종류의 연산자가 서브쿼리에 사용된다. 

 =  ,  >  ,   >=  ,  <  ,  <=  ,  <>  ,  !=  (단일행 연산자)  =>  레코드 결과 값이 1개나옴

 혹은 IN, NOT IN(복수행 연산자)등    => 결과값이 여러 개 나옴

- 서브쿼리는 연산자의 오른쪽에 기술 되어야 한다

-단일행 서브 쿼리에는 단일행 연산자를 다중행 서브쿼리에는 복수행 연산자를 사용한다

-서브쿼리는  SELECT절,FROM절 WHERE절등에 위치할 수 있다.  웬만한 절에는 어디서나 다 사용 가능

 

역시 설명보단 실전이지!!!

1. 최소연봉을 이 얼마인지 알기 위한 코드

SELECT MIN(SAL) FROM EMP;

2. ++ 나는 최소연봉 받는 사람의 이름이나 그런 정보를 알고 싶다!!! 

 

연습연습연습!!

문제)부서별 최고연봉으르 받는 사람들의 이름과 직무를 출력하라

1. 먼저  부서별 최고연봉을 구하는 로직을 작성해 놓고!  (그룹함수 잘 사용해야 함!)

SELECT  DEPTNO  MAX(SAL)  FROM EMP GROUP BY DEPTNO     ==> 값이 단일값이 아닌 표로 나온다!!

 

2 .틀을 만들어 놓아

SELECT ENAME, JOB  FROM EMP 

WHERE   여기에 어떤 조건을 해야 하는지 의문스럽군...

 

3. 1번과 2번 둘을 조합해!!

SELECT ENAME, JOB  FROM EMP

WHERE  ( DEPTNO,SAL )  IN (SELECT  DEPTNO  MAX(SAL)  FROM EMP GROUP BY DEPTNO   ) ;

  ==WHERE절을 풀어서 보면

WHERE DEPTNO = 30 AND SAL = 2850 OR DEPTNO = 20 AND SAL=3000 OR DEPTNO =10 AND SAL =5000;
WHERE (DEPTNO,SAL)IN ((30,2850),(20,3000),(10,5000));  이런 해설임!!

 

 

문제2)  최소연봉/최대연봉을 받는 사람의 이름과 연봉,직무를 출력하라, 연봉이 낮은 사람이 먼저 출력..

1. 최소연봉 받는 사람의 정보 구하기

SELECT ENAME, SAL , JOB FROM EMP

WHERE SAL = (SELECT MIN(SAL) FROM EMP );

2. 최대연봉 받는 사람의 정보 구하기

SELECT ENAME,SAL,JOB FROM EMP

WHERE SAL = (SELECT MAX(SAL) FORM EMP);

3. 둘을 합쳐!

SELECT ENAME,SAL,JOB FROM EMP

WHERE SAL IN((SELECT MIN(SAL) FROM EMP ),(SELECT MAX(SAL) FORM EMP))

ORDER BY SAL;

 

문제3-1) 부서배치가 안된 직원 구하기

걍 부서가 없는직원 구하는거.. 

SELECT LAST_NAME ||' '|| FIRST_NAME, SALARY,HIRE_DATE

FROM EMPLOYEES

WHERE DEPARTMENT_ID IS NULL;

문제 3-2) 인원충원이 안된 모든 부서의 코서코드명 출력

====>이거 서브쿼리로 바꾸기

1. 대략적인 감을 찾기위해 하드코딩 

SELECT DNAME

FROM DEPT

WHERE DEPTNO NOT IN(10,20,30);

2. 참고해서 코딩

SELECT DNAME

FROM DEPT

WHERE DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM EMP) 

  => 어떤개념??? EMP 사람들 정보가 있는 테이블에서 DEPTNO 부서코드 가 부여된 것들을 다 빼면 NULL값만 남겠지

그게 인원이 없는 부서!! 와우( DISTINCT 부서는 중복이 가능하니까 간편하게 하려고 중복제거한거)

 

문제4-1) 연봉이 평균 이하인 직원들의 정보 출력

SELECT ENAME, SAL,DEPTNO,HIREDATE

FROM EMP

WHERE SAL <= (SELECT AVG(SAL) FROM EMP);

문제4-2) 부서별 최고연봉을 받는 사람들의 이름과 직책 , 입사일 정보 출력

SELECT ENAME,SAL,DEPTNO,HIREDATE

FROM EMP

WHERE (SAL,DEPTNO) IN ( SELECT MAX(SAL), DEPTNO FROM EMP GROUP BY DEPTNO )

                                                               =>부서별이니 부서랑 연봉정보 필요하고 그룹함수가 쓰였으니 같이  나머지도 같이 그룹화 해줘야지 에러가 안나겠지?!

문제 4-3) 4-2번에서 부서명도 같이 출력

SELECT ENAME,SAL,DEPTNO,HIREDATE, DNAME   =>부서명은 DEPT 테이블에 있으니까 JOIN!!

FROM EMP E JOIN DEPT D ON D.DEPTNO = E.DEPTNO

WHERE (SAL,DEPTNO) IN ( SELECT MAX(SAL), DEPTNO FROM EMP GROUP BY DEPTNO )  

 

TOP 쿼리/구간쿼리

랭킹함수같은 느낌? 얻어진 결과를 위에서 순서대로 몇 개만 가져오려고 할 때 쓰는 것

하기전에 알아야 할 명령어  ROWNUM 이건 데이터가 입력된 순서 혹은 서브쿼리에 의해 생성된 테이블에 레코드가 생성된 순서대로 내부적으로 번호가 부여되는데 그 번호를 저장한 컬림 이름임! 이건 내갸 만든게 아니고 오라클 내부에서 원래 있던 컬럼임TOP 쿼리 구조는 그냥 외우는게 마음 편함! TOP쿼리에서 파생된게 구간쿼리인데 개인적으로 구간쿼리 형식 하나 외워놓는게 더 편한 것 같다.

 

우선 탑쿼리 (연봉이 높은순으로 위에서 3명)

SELECT T.*    ->모든걸 꺼내온다고는 하지만 이미 서브쿼리에서 ENAME , SAL 밖에 지정되어있지않아서 두 개만 출력 됨FROM  (SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC) T    ->서브쿼리 자체를 별칭 함!WHERE ROWNUM <= 3 ;

 

구간쿼리

SELECT *
FROM (SELECT T.*,  ROWNUM RN FROM

(SELECT * FROM EMP ORDER BY SAL)T )  => 서브쿼리로 원하는 순서로 나열해 놓고
WHERE RN BETWEEN 5 AND 8;         => 원하는 구간을 가져 옴. 연봉이 5~8 구간 사이인 사람 

 

쪼개서 보여드리리

 

SELECT T.* , ROWNUM     
FROM (SELECT * FROM EMP ORDER BY hiredate DESC)T;   => 연봉기준으로 나열한거 요게 밑에 있는 쿼리안으로 들어간거임!

SELECT *
FROM (SELECT T.*,ROWNUM RN FROM
(SELECT * FROM EMP ORDER BY hiredate DESC)T)
WHERE RN BETWEEN 1 AND 3;                                         =>1~3 구간을 똑 잘라 온거

 

문제5-1)EMP테이블에서 부서 코드가 30인 직원중에 연봉 높은 순서로 위에서부터 3명을 출력하여라
       출력시 컬럼은 이름,연봉,직책,부서코드를 출력하여라.    

SELECT * 

FROM(SELCT T.* ROWNUM RN FROM

(SELECT ENAME,SAL,JOB,DEPTNO FROM EMP WHERE DEPTNO = 30 OERDER BY SAL DESC)T) 

WHERE RN BETWEEN 1 AND 3;

 

문제 5-2) 5-1에서 부서명 추가

SELECT *

FROM(SELCT T.*, ROWNUM RN FROM

(SELECT ENAME,SAL,JOB,DEPTNO ,DNAME

 FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO  WHERE DEPTNO = 30 OERDER BY SAL DESC)T) 

WHERE RN BETWEEN 1 AND 3;

문제 5-3) 모든사람 정렬 6~10번째인 사람들 출력 후 입사일,부서명 추가

 

 

최종보스

HR 계정에서 이름,연봉,입사일,부서명 ,도시명 출력! 입사일 기준으로 위에서 5명만

SELECT *

FROM(SELECT T.* , ROWNUM RN FROM

(SELECT FIRST_NAME,SALARY,HIRE_DATE,DEPARTMENT_NAME,CITY

FROM EMPLOYEES E JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID 

JOIN LOCALTIONS L ON L.LOCATION_ID = D.LOCATION_ID

ORDER BY HIRE_DATE DESC)T)

WHERE RN BETWEEN 1 AND 5;

역시

SELECT*FROM(SELECT T.*, ROWNUM RN FROM(  여기만 조건에 맞게 SELCT절 FROM절 JOIN ORDER BY 바꿔 )T) WHERE RN BETWEEN 11 AND 20

이 틀만 외우면 무적임 !!! 

 

++ 보너스 문제

역시 HR계정에서 사원들 연봉순으로 나열후 11~20까지만 출력

그리고 이름(LAST_NAME || FIRST_NAME), 연봉, 부서명 , 도시명 출력하라