업무에 바로 쓰는 SQL(삼성멀티캠퍼스) 4일차

-- 4일차 교육
SELECT * FROM PLAYER_T;

-- 1. 서동명 선수와 같은 팀 소속인 선수들만 이름, 키 조회하기
SELECT player_name, height
FROM PLAYER_T
WHERE team_id = (SELECT team_id FROM PLAYER_T WHERE player_name ='서동명');
--> 결과 : team_id = 'K01'
-- 결과에서 서동명 제외하는 법
SELECT player_name, height
FROM PLAYER_T
WHERE team_id = (SELECT team_id FROM PLAYER_T WHERE player_name ='서동명');
AND player_name <> '서동명';

-- 2. GK포지션선수들 평균키보다 키가 큰 선수들만 이름, 키 조회하기
-- 대부분 개발자의 실수 쿼리문은 다음과 같다.
SELECT avg(height)
FROM PLAYER_T
GROUP BY position
HAVING position='GK'; -- 결과는 나왔으나, 불필요한 그룹핑 작업을 하게 되었음
-- 단순화하는 방법은 WHERE 절로 구현하는 방법이다.
-- 다른 방법으로도 추출 가능하다.
SELECT AVG(height)
FROM PLAYER_T
WHERE position='GK';

SELECT player_name, height
FROM PLAYER_T
WHERE height > (SELECT AVG(height) FROM PLAYER_T WHERE position ='GK');
-- 결과 : 40 개의 행이 선택되었습니다. / 서브쿼리 결과는 ? 186.547619

-- 3. 평균키가 180보다 작은 팀만 팀id 조회하기
-- 팀별 데이터를 비교하기 때문에 팀으로 GROUP BY 연산을 떠올린다.
-- WHERE 절에서는 그룹함수(AVG, SUM ... 함수 직접 사용 불가 So, HAVING절 이용)
SELECT team_id, AVG(height)
FROM PLAYER_T
GROUP BY team_id
HAVING AVG(height) < 180;
-- 결과 : K02, K06, K07, K08, K09, K10

-- 4. 평균키가 K03팀보다 작은 팀만 팀id 조회하기(교재 p.206)
SELECT team_id, AVG(height)
FROM PLAYER_T
GROUP BY team_id
HAVING AVG(height) < (SELECT AVG(height) FROM PLAYER_T WHERE team_id='K03');
-- 결과 : K02, K06, K07, K08, K09, K10 / 서브쿼리 결과는 ? 179.91

-- 교재 p.209~p.212 다중행 서브쿼리(★★★★★)
-- 5. 박동우와 담당포지션이 같은 선두들만 이름, 키 조회하기
SELECT player_name, height, position
FROM PLAYER_T
WHERE position = (SELECT position FROM PLAYER_T WHERE player_name='박동우');
-- 결과 : ORA-01427: single-row subquery returns more than one row
--> 서브쿼리 결과가 2개 이상일 경우 WHERE절 등호(=) 불가
--> IN, =ANY, =SOME 비교연산자로 대체할 것

-- =ANY 사용
SELECT player_name, height, position
FROM PLAYER_T
WHERE position =ANY (SELECT position FROM PLAYER_T WHERE player_name='박동우');
-- =SOME 사용
SELECT player_name, height, position
FROM PLAYER_T
WHERE position =SOME (SELECT position FROM PLAYER_T WHERE player_name='박동우');
-- IN 사용
SELECT player_name, height, position
FROM PLAYER_T
WHERE position IN (SELECT position FROM PLAYER_T WHERE player_name='박동우');

-- 결과에서 박동우 제외하는 방법 : WHERE절에 AND player_name <> '박동우' 추가

-- 6. emp 테이블 사원중에서
-- 30번 부서에 소속된 모든 사원들보다
-- 급여를 더 많이 받는 사원들만 이름, 급여조회하기
SELECT ename, sal
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE deptno=30);
-- 서브쿼리 결과 : 1600, 1250, 1250, 2850, 1500, 950 이므로 부등호 연산 불가
-- 30번 부서에 소속된 모든 사원들보다 --> 모든 사원들 급여중
-- 최대 급여를 받는 사원을 기준으로 처리하면 된다. MAX() 함수 사용
SELECT ename, sal
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno=30);
-- 대체가능한 연산자는 ALL 이 있다. >ALL, <ALL
-- 모든 사원 보다 많을려면, 모든 사원 보다 작을려면 ALL 연산자를 사용하여 비교

-- 7. emp 테이블 사원중에서
-- 30번 부서에 소속된 모든 사원들보다
-- 급여를 더 적게 받는 사원들만 이름, 급여조회
SELECT ename, sal
FROM emp
WHERE sal < (SELECT MIN(sal) FROM emp WHERE deptno=30);

SELECT ename, sal
FROM emp
WHERE sal < ALL(SELECT MIN(sal) FROM emp WHERE deptno=30);

-- ALL연산자, ANY연산자도 있다.
-- ANY연산자는

~ 부서에 있는 사원들 중에서 1명의 ~

라도 크거나 작을 경우 사용
-- ALL연산자는

~ 부서에 있는 모든 사원들의 ~

라도 크거나 작을 경우 사용
-- 생각하기 힘들면 MIN(), MAX() 함수를 쓰면 된다. ^^;;;

-- 교재 p.211 예제
-- 포지션이 FW인 선수 중 최소 한명보다 키가 크다면 해당 선수의 이름, 팀id, 키 정보를 조회
-- 1안) MIN() 함수 사용
SELECT player_name, team_id, height
FROM PLAYER_T
WHERE position<>'FW'
AND height > (SELECT MIN(height) FROM player_t WHERE position='FW');
-- 결과 : 75개 행이 선택되었습니다.
-- 2안) > ANY 연산자 사용
SELECT player_name, team_id, height
FROM PLAYER_T
WHERE position <> 'FW'
AND height > ANY(SELECT height FROM player_t WHERE position='FW');
-- 결과 : 75개 행이 선택되었습니다.

-- 교재 p.212 예제
-- 포지션이 FW인 모든 선수보다 키가 더 큰 선수들의 이름, 팀id, 키 정보를 조회
-- 1안) MAX() 함수 사용
SELECT player_name, team_id, height
FROM PLAYER_T
WHERE position <> 'FW'
AND height > (SELECT MAX(height) FROM PLAYER_T WHERE position='FW');
-- 결과 : 권정혁 K01 195, 서동명 K01 196

SELECT player_name, team_id, height
FROM PLAYER_T
WHERE position <> 'FW'
AND height > ALL(SELECT height FROM PLAYER_T WHERE position='FW');
-- 결과 : 권정혁 K01 195, 서동명 K01 196

/* 암기사항(★★★★★)

1. < ANY는 최대값보다 작음을 나타내고

2. ANY는 최소값보다 크다는 것을 나타내고

3. =ANY는 IN과 동일하다

4. < ALL은 최소값보다 작음을 나타내고

5. ALL은 최대값보다 크다는 것을 나타낸다. */

-- Question
-- emp 사원 중 10번 부서에서 CLERK 업무담당 OR
-- 20번 부서에서 MANAGER 업무담당 OR
-- 30번 부서에서 TA OR 40번 부서에서 DA 업무를 수행하는
-- 사원이름, 급여, 부서번호, 업무 조회
SELECT ename, sal, deptno, job
FROM emp
WHERE (deptno=10 AND job='CLERK')
OR (deptno=20 AND job='MANAGER')
OR (deptno=30 AND job='TA')
OR (deptno=40 AND job='DA');
/결과 :
JONES 2975 20 MANAGER
MILLER 1300 10 CLERK */
-- 윗 조건이 너무 많아서 SQL이 무한히 길어질 수 있다. 단순화를 고려하면 ?
SELECT ename, sal, deptno, job
FROM emp
WHERE deptno IN(10,20,30,40) AND job IN ('CLERK','MANAGER','TA','DA');
/결과 :
SMITH 800 20 CLERK
JONES 2975 20 MANAGER
BLAKE 2850 30 MANAGER
CLARK 2450 10 MANAGER
ADAMS 1100 20 CLERK
JAMES 950 30 CLERK
MILLER 1300 10 CLERK */

-- 단순화하려고 했는데, 결과가 다르게 나옵니다. ㅠ.ㅠ

-- 개발자 코드를 검토해보겠습니다.
SELECT ename, sal, deptno, job
FROM emp
WHERE deptno||job IN('10CLERK','20MANAGER','30TA','40DA');
/*결과 :
JONES 2975 20 MANAGER
MILLER 1300 10 CLERK */

-- 개발자 코드의 문제는 ? WHERE절의 || 연산자를 사용하기 때문에 속도 저하를 초래
-- SQL 작성시 최대한 WHERE 절에서는 연산을 처리하지 않도록 할 것
-- 그럼, 최적의 대안은 ? 문장도 줄이고, 속도도 고려하는 문장은? 교재 P.214
-- 이를 Pairwise 쿼리라고 합니다. ^^

SELECT ename, sal, deptno, job
FROM emp
WHERE (deptno, job) IN ((10, 'CLERK'),(20,'MANAGER'),(30,'TA'),(40,'DA'));

-- 교재 p.214 다중 열 서브쿼리 예제1(Non-Pairwise 서브쿼리)
SELECT player_name, team_id, height, position
FROM PLAYER_T
WHERE position IN (SELECT position
FROM PLAYER_T
WHERE player_name IN ('샤샤','이운재'))
AND height IN (SELECT height
FROM PLAYER_T
WHERE player_name IN ('샤샤','이운재'));

-- 위 SQL 문은 다음 쿼리문을 실행한 것과 같다.
SELECT player_name, team_id, hieght, position
FROM PLAYER_T
WHERE position IN ('FW','GK')
AND height IN (190,182);

-- 교재 p.215 다중 열 서브쿼리 예제2(Pairwise 서브쿼리)
SELECT player_name, team_id, height, position
FROM PLAYER_T
WHERE (position, height) IN (SELECT position, height
FROM PLAYER_T
WHERE player_name IN ('샤샤', '이운재'));
-- 위 SQL 문은 다음 쿼리문을 실행한 것과 같다.
SELECT player_name, team_id, height, position
FROM PLAYER_T
WHERE (position, height) IN (('FW', 190), ('GK', 182));

-- 교재 p.218~221 TOP-N 서브쿼리 & FROM 절 서브쿼리(인라인 뷰)
SELECT rowid, ename, sal FROM emp; -- DB2, Oracle 에서는 RID(18자리),
-- rowid : DB서버 디스크에 ROW가 저장된 위치정보, 개발자들이 종종 씀

SELECT ROWNUM, ename, sal FROM emp WHERE deptno = 10;
-- rownum : Row 찾아간 순서기준, 일련번호값 부여함(번호표라고 생각해야 함)

-- 1000만개 자료중에 1개 자료만 보자.
SELECT ROWNUM, ename, sal FROM emp WHERE ROWNUM = 1;
-- 그럼 5개만 봐볼까?
SELECT ROWNUM, ename, sal FROM emp WHERE ROWNUM = 5;
-- rownum 값은 미리 만들어 있지 않음. DBMS가 DATA를 가져올 시점에 부여한다.
-- 시스템은 5번째 번호표가 누가 받을지 모르기 때문에
-- 특정 번호표를 예측해서 가져올 수는 없음.
-- 결론, rownum은 첫번째 번호표를 부여받은 데이터만 가져올 수 있으며,
-- 2번째 이상의 번호표는 누가 받을지 모르기 때문에 콕 찍어서 가져올 수 없음
-- 따라서, 부등호(<, >, >=, <=)를 써서 한정지어 데이터를 가져와야 함

-- 결과 : 표시할 데이터가 없습니다. ㅠ.ㅠ 어떻게 하지?
SELECT ROWNUM, ename, sal FROM emp WHERE ROWNUM <= 5;
-- 결과 나온다

~

SELECT ROWNUM, ename, sal FROM emp WHERE ROWNUM > 5;
---- 결과 : 표시할 데이터가 없습니다.

-- (★★)
-- 질의 : emp 사원중에서 급여를 많이 받는 사원들 기준 5명의 이름, 급여만 조회
-- 오답
SELECT ROWNUM, ename, sal FROM emp WHERE ROWNUM <=5 ORDER BY sal DESC;
-- 오답에 대한 해석
-- 전체 데이터를 기준으로 급여가 많은 5명을 가져오려고 했으나,
-- 의도치 않게 시스템은 맨처음 만나는 데이터부터 5개까지만 가져와서
-- 5개 데이터에서 order by 구문으로 정렬하기 때문에 원치 않는 값이 나온다.(★★)
SELECT ROWNUM, ename, sal
FROM emp ------------ (1)
WHERE ROWNUM <=5 ---- (2)
ORDER BY sal DESC;--- (3)

/* 결과
4 JONES 2975
2 ALLEN 1600
3 WARD 1250
5 MARTIN 1250
1 SMITH 800 */

-- 정답
SELECT ROWNUM, ename, sal
FROM (SELECT ename, sal from emp ORDER BY sal desc) -- 메모리상에 테이블 형성
WHERE ROWNUM < 6;
/* 결과
1 KING 5000
2 SCOTT 3000
3 FORD 3000
4 JONES 2975
5 BLAKE 2850
*/
-- 서브쿼리결과를 FROM 절로 사용할 경우 별칭 가능
SELECT ROWNUM, ename, sal
FROM (SELECT ename, sal from emp ORDER BY sal desc) A -- 별칭 A
WHERE ROWNUM < 6;
-- FROM 절에 기술한 서브쿼리는 마치 뷰와 같은 역할을 한다.
-- FROM 절에 사용된 서브쿼리를 '인라인(inline) 뷰' 라고 한다.

-- 문제 1. player_t 선수들 각팀 평균몸무게 조회(team_id, 평균몸무게)

SELECT * FROM PLAYER_T; -- 몸무게 필드 확인

SELECT team_id, AVG(weight)
FROM PLAYER_T
GROUP BY team_id;

-- 문제 2. 1번 결과를 팀명(team_name) 평균몸무게 형태로 나오도록 조회
SELECT team_id, AVG(weight)
FROM PLAYER_T
GROUP BY team_id;

SELECT P.TEAM_ID, T.TEAM_NAME, AVG(P.WEIGHT)
FROM PLAYER_T P, TEAM_T T
WHERE P.TEAM_ID = T.TEAM_ID
GROUP BY T.TEAM_NAME;
-- 결과 : ORA-00979: not a GROUP BY expression
-- SELECT 로 가져올 컬럼은 꼭 GROUP BY 컬럼과 일치해야 함

SELECT P.TEAM_ID, T.TEAM_NAME, AVG(P.WEIGHT)
FROM PLAYER_T P, TEAM_T T
WHERE P.TEAM_ID = T.TEAM_ID
GROUP BY P.TEAM_ID, T.TEAM_NAME;
/* 결과
K01 현대호랑이 73.15555555555555555555555555555555555556
K02 삼성블루윙즈 72.08888888888888888888888888888888888889
K03 스틸러스 73.42222222222222222222222222222222222222
K04 SK 74.22222222222222222222222222222222222222
K05 현대모터스 73.62222222222222222222222222222222222222
K06 아이콘스 73.88888888888888888888888888888888888889
K07 드래곤즈 71.73913043478260869565217391304347826087
K08 일화천마 72.46341463414634146341463414634146341463
K09 LG치타스 71.68181818181818181818181818181818181818
K10 시티즌 70.93939393939393939393939393939393939394
*/

SELECT P.TEAM_ID, T.TEAM_NAME, AVG(P.WEIGHT)
FROM PLAYER_T P, TEAM_T T
WHERE P.TEAM_ID = T.TEAM_ID
GROUP BY P.TEAM_ID, T.TEAM_NAME;
-- 시스템 처리과정 ? WHERE 절 JOIN 으로 데이터수만큼(70000만개이면 70000개 연산)
-- 연산하였으나, GROUP BY 로 인해 모든 데이터를 연산하였으나 불필요한 연산이 되어버렸음
-- 왜냐? WHERE 절을 통해 7만개 연산 후 GROUP BY 로 10개 밖에 결과를 안가져오니까..
-- 이 경우 개선방법은 ? GROUP BY로 10개 데이터를 가져온 후 10개 데이터에 대해서
-- JOIN 하도록 하자.

SELECT team_id, AVG(weight)
FROM PLAYER_T
GROUP BY team_id;

SELECT P.TEAM_ID, T.TEAM_NAME, P.*
FROM (SELECT team_id, AVG(weight)
FROM PLAYER_T
GROUP BY team_id) P, team_t T
WHERE P.team_id = T.team_id;
/* 결과
K01 현대호랑이 K01 73.15555555555555555555555555555555555556
K02 삼성블루윙즈 K02 72.08888888888888888888888888888888888889
K03 스틸러스 K03 73.42222222222222222222222222222222222222
K04 SK K04 74.22222222222222222222222222222222222222
K05 현대모터스 K05 73.62222222222222222222222222222222222222
K06 아이콘스 K06 73.88888888888888888888888888888888888889
K07 드래곤즈 K07 71.73913043478260869565217391304347826087
K08 일화천마 K08 72.46341463414634146341463414634146341463
K09 LG치타스 K09 71.68181818181818181818181818181818181818
K10 시티즌 K10 70.93939393939393939393939393939393939394
/
SELECT P.TEAM_ID, T.TEAM_NAME, P.AVG(weight)
FROM (SELECT team_id, AVG(weight)
FROM PLAYER_T
GROUP BY team_id) P, team_t T
WHERE P.team_id = T.team_id;
/ 결과 : ORA-00904: "V"."AVG": invalid identifier */
-- 해결방법 ? AVG(weight)을 별칭처리하여 별칭을 가져옴

SELECT P.TEAM_ID, T.TEAM_NAME, P.A1 평균키
FROM (SELECT team_id, AVG(weight) A1
FROM PLAYER_T
GROUP BY team_id) P, team_t T
WHERE P.team_id = T.team_id;
/* 결과
K01 현대호랑이 73.15555555555555555555555555555555555556
K02 삼성블루윙즈 72.08888888888888888888888888888888888889
K03 스틸러스 73.42222222222222222222222222222222222222
K04 SK 74.22222222222222222222222222222222222222
K05 현대모터스 73.62222222222222222222222222222222222222
K06 아이콘스 73.88888888888888888888888888888888888889
K07 드래곤즈 71.73913043478260869565217391304347826087
K08 일화천마 72.46341463414634146341463414634146341463
K09 LG치타스 71.68181818181818181818181818181818181818
K10 시티즌 70.93939393939393939393939393939393939394
*/

-- 문제 1.
-- emp 사원 중 10번 부서 평균급여보다 급여 많이 받는 사원들(이름, 급여, 부서번호) 조회
SELECT * FROM emp;

SELECT ename, sal, deptno
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 10);
/*결과
JONES 2975 20
SCOTT 3000 20
KING 5000 10
FORD 3000 20
*/

-- 문제 2.
-- emp 사원 중 본인이 소속된 부서 평균 급여보다 급여 많이 받는 사원이름, 급여, 부서번호 조회
-- 결과는 급여순 desc 정렬
SELECT ename, sal, deptno
FROM emp
WHERE sal =;

-- 1. 각 부서들의 평균
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno;

-- 2. 본인이 소속된 부서들의 평균
SELECT E.deptno, T.G_AVG
FROM (SELECT deptno, AVG(sal) G_AVG
FROM emp
GROUP BY deptno) T, emp E
WHERE T.deptno = E.deptno;
-- 결과 : 14개 행이 선택되었습니다.

-- 3. 본인이 소속된 부서들의 평균보다 급여 많이 받는 사원이름, 급여, 부서번호 조회
SELECT E.ename, E.sal, E.deptno
FROM (SELECT deptno, AVG(sal) G_AVG
FROM emp
GROUP BY deptno) T, emp E
WHERE T.deptno = E.deptno AND E.sal>T.G_AVG
ORDER BY E.sal desc;
/* 결과
KING 5000 10
FORD 3000 20
SCOTT 3000 20
JONES 2975 20
BLAKE 2850 30
ALLEN 1600 30
*/

-- 다른 방법 모색해볼까요?
-- 문제 1번에 대한 정답을 변형해서 문제 2번을 해결해 봅시다.
-- 10번 부서 급여평균이 아닌 '본인이 소속된 부서들의 평균 급여'로 변경할 수 있을까?
-- 변수를 받아와서 Loop 을 돌리듯이 자동으로 처리되었으면 좋겠어....

-- 교재 p.216 상호연관 서브쿼리
-- 1번 정답 SQL문에 상호연관 서브쿼리 개념을 적용해보자.
SELECT ename, sal, deptno
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 10)
ORDER BY sal DESC;
-- 변형 SQL
SELECT ename, sal, deptno
FROM emp a
WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = a.deptno)
ORDER BY sal DESC;

-- 상호연관 서브쿼리 부가설명
-- 상호연관 서브쿼리는 메인쿼리의 테이블 필드값을 서브쿼리에서 사용하고자 할 경우
-- 메인쿼리의 테이블을 서브쿼리에서 불러오기 위해 메인쿼리의 테이블을 별칭처리하고
-- 서브쿼리에서 별칭.필드값으로 데이터를 가져와서 처리될 수 있도록 한다.
-- 따라서, 10번 부서 급여평균 즉 특정 부서 급여평균의 서브쿼리 작성문을 변형하여
-- 본인이 소속된 부서들의 값을 서브쿼리 작성시 특정 부서값에 매칭하기 위해
-- 본인이 소속된 부서들의 값이 있는 메인쿼리의 테이블.필드를 가져오기 위해서
-- 메인쿼리 테이블을 별칭 처리하고, 별칭.필드 형식으로 호출하여 사용한다.

-- 그럼 왜? 직접 emp.필드로 서브쿼리에서 호출하지? 꼭 별칭을 써야 하는가 ?
-- 비교
SELECT AVG(sal) FROM emp WHERE deptno = emp.DEPTNO; -- 의 결과가
SELECT AVG(sal) FROM emp; -- 의 결과와 같다. 전체 부서 평균값 : 2073.21428
-- 따라서 꼭 별칭으로 테이블 네이밍 후 서브쿼리에서 메인 쿼리의 테이블의 값들을
-- 호출해야 한다.
-- 상호연관 서브쿼리 개념에서만 FROM 절의 테이블을 별칭 처리하는데 의미있다.

/*
★ 서브쿼리 사용가능 위치
select (○) --- 서브쿼리 결과값(컬럼, ROW) 수가 1개일 경우만 가능
from (○)
where (○)
group by (x)
having (○)
order by (○) --- 서브쿼리 결과값(컬럼, ROW) 수가 1개일 경우만 가능
*/

SELECT ename, sal, deptno, (SELECT SUM(sal) FROM emp) 결과 FROM emp;
-- 결과 :
SELECT ename, sal, deptno, (SELECT SUM(sal), AVG(sal) FROM emp) 결과 FROM emp;
-- 결과 : ORA-00913: too many values
SELECT ename, sal, deptno, (SELECT SUM(sal) FROM emp GROUP BY deptno) 결과 FROM emp;
-- 결과 : ORA-01427: single-row subquery returns more than one row

SELECT ename, sal, deptno, (SELECT SUM(sal) FROM emp WHERE deptno =30) 결과 FROM emp;
-- 상호연관 서브쿼리
SELECT ename, sal, deptno, (SELECT SUM(sal) FROM emp WHERE deptno = E.deptno) 결과 FROM emp E;

-- 상호연관 서브쿼리(교재 p.217) 예제
-- 본인 소속팀 평균키 보다 키가 큰 선수들의 이름, tea_id, 키를 조회
SELECT player_name, team_id, height
FROM PLAYER_T P
WHERE height > (SELECT AVG(height) FROM PLAYER_T WHERE team_id=P.team_id);
-- 상호연관 서브쿼리에서 P.team_id 개수 만큼 연산처리를 반복하지 않는다.
-- 동일한 P.team_id의 값이 메모리상에 생성되어 있으면 연산처리 없이 결과값을
-- 바로 리턴한다. 따라서 연산처리가 의외로 속도가 빠르다.
-- 삽입된 P.team_id의 계산 결과값이 메모리 상에 없을 경우에만 연산하여 메모리에 올린다.

SELECT ename, sal, deptno, (SELECT COUNT(*) FROM emp WHERE sal > 4000) 결과
FROM emp;

SELECT ename, sal, deptno, (SELECT COUNT(*) FROM emp WHERE sal > E.sal) "급여가 본인급여보다 높은 사람"
FROM emp E
ORDER BY "급여가 본인급여보다 높은 사람";

-- 문제 1. 업무가 CLERK인 사원들의 이름, 급여, 부서번호, 부서명 조회.
-- 결과는 급여많은 사원부터 나오도록 정렬
---- 방법1> emp, DEPT테이블 조인해서 처리
SELECT e.ENAME, e.SAL, e.DEPTNO,d.DNAME
FROM EMP e, DEPT d
WHERE e.DEPTNO = d.DEPTNO AND job = 'CLERK'
ORDER BY e.sal DESC ;
/*결과
MILLER 1300 10 ACCOUNTING
ADAMS 1100 20 RESEARCH
JAMES 950 30 SALES
SMITH 800 20 RESEARCH
*/

---- 방법2> 부서명 조회하는 select절 select문사용
SELECT ename, sal, deptno, (SELECT dname FROM DEPT WHERE deptno = E.deptno)
FROM EMP E
WHERE job = 'CLERK'
ORDER BY e.sal DESC;
/*결과
MILLER 1300 10 ACCOUNTING
ADAMS 1100 20 RESEARCH
JAMES 950 30 SALES
SMITH 800 20 RESEARCH
*/

-- 문제 1. 에 대한 결론 ?
-- 방법 1의 경우 여러 컬럼을 조회하고자 할 경우 JOIN 을 사용하고
-- 방법 2의 경우 1개 컬럼을 조회하여 가지고 올 경우 상호연관 서브쿼리를 사용
-- 속도 차이는 상황에 따라 다르다.
-- 대용량DB 처리시 방법1을 주로 사용하며, 소규모DB 처리시 방법2를 사용
-- *****

-- 문제2. ename에 A문자가 포함된 사원들의 사번, 이름, 멘토번호(mgr), 본인멘토이름 조회
---- 방법1> from 절에서 emp 테이블을 2번 사용하는 self 조인방법으로
---- 사번 = mgr(멘토번호) 이다. !! self join 사용
SELECT * FROM emp;

SELECT 사원.EMPNO, 사원.ENAME, 사원.MGR, 멘토.ENAME 멘토이름
FROM emp 사원, emp 멘토
WHERE (사원.MGR = 멘토.EMPNO) AND 사원.ENAME LIKE '%A%'
ORDER BY 사원.ENAME;
/*결과
7876 ADAMS 7788 SCOTT
7499 ALLEN 7698 BLAKE
7698 BLAKE 7839 KING
7782 CLARK 7839 KING
7900 JAMES 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE */

---- 방법 2> 멘토이름조회하는 select절 서브쿼리 사용
select empno, ename, mgr ,(SELECT ENAME FROM EMP
WHERE empno = 사원.mgr) 멘토이름
from emp 사원
where ename LIKE '%A%'
order by 사원.ename ;
/*결과
7876 ADAMS 7788 SCOTT
7499 ALLEN 7698 BLAKE
7698 BLAKE 7839 KING
7782 CLARK 7839 KING
7900 JAMES 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE */

-- 문제3. 본인 멘토사원보다 급여를 더 많이 받는 사원들만 이름, 급여조회
---- 방법1> emp테이블 self 조인
SELECT 사원.ENAME, 사원.SAL
FROM emp 사원, emp 멘토
WHERE 사원.MGR = 멘토.EMPNO AND 사원.sal> 멘토.sal;
/*결과
SCOTT 3000
FORD 3000 */

---- 방법2> where 절 서브쿼리 사용
SELECT ename, sal
FROM emp A
WHERE sal > (SELECT sal FROM emp WHERE empno = A.MGR);
/*결과
SCOTT 3000
FORD 3000 */

SELECT ename, sal, TRUNC((SELECT AVG(sal) FROM emp)) FROM emp a;

-- 교재 p.226 EXISTS 연산자
-- 1. dept 테이블 부서중 소속부서원이 존재하는 부서들만 부서번호, 부서명 조회하기
---- 1안)
SELECT deptno, dname
FROM dept
WHERE deptno IN (SELECT deptno FROM emp);
-- 비효율적인 SQL 구문이다.
-- IN(1, 2, 3, 5, 6, 7, ..... n개) : IN() 연사자의 n개의 제한은 1000개까지만 허용

----- 2안)
SELECT deptno, dname
FROM dept D
WHERE deptno IN (SELECT deptno WHERE WHERE deptno = D.DEPTNO);
-- 왜 2안은 괜찮은거지 ? IN() 연산자의 자료를 제한하기 위해서 2안을 권장

----- 3안) EXISTS 연산! 2안보다 연산속도가 빠르다.
-- EXISTS 연산자 사용시
-- 서브쿼리 행의 값이 발견된 경우 서브쿼리에서 더 이상 검색하지 않는다. vs. IN
-- 서브 쿼리 행의 값이 발견되지 않은 경우 서브쿼리에서 검색을 계속한다.
SELECT deptno, dname
FROM dept D
WHERE EXISTS (SELECT deptno WHERE WHERE deptno = D.DEPTNO);

-- EXISTS() 연산은 서브쿼리에서 WHERE 절만 T/F 판단하기 때문에 SELECT 절에 임의값 사용 가능
SELECT deptno, dname
FROM dept D
WHERE EXISTS (SELECT 1 WHERE WHERE deptno = D.DEPTNO);

SELECT deptno, dname
FROM dept D
WHERE EXISTS (SELECT 7 WHERE WHERE deptno = D.DEPTNO);

SELECT deptno, dname
FROM dept D
WHERE EXISTS (SELECT "부서원존재함" WHERE WHERE deptno = D.DEPTNO);

-- 교재 P.226 EXISTS 연산자 예제
-- EMP 테이블에서 적어도 한 명의 사원으로부터 보고를 받을 수 있는 (부하 사원을 가지고 있는)
-- 사원의 사원번호, 이름, 업무, 입사일자, 급여를 조회

SELECT empno, ename, job, hiredate, sal, deptno
FROM emp e
WHERE EXISTS (SELECT 1 FROM emp WHERE e.empno = mgr)
ORDER BY empno;

-- 교재 P.228 NOT EXISTS 연산자 예제
특정 팀에서 전용 구장으로
SELECT COUNT(*) FROM emp
WHERE deptno = (SELECT deptno FROM dept;

-- 부서원이 있는 부서의 부서번호만 가져올까?
-- 부서원이 없는 부서의 부서번호만 가져올까?
-- 유사질의. 주문한 적 있는 고객 명단, 주문한 적 없는 고객 명단 추출하기

-- 교재 P.231 CHAPTER.8 분석함수
-- 전체 사원을 대상으로 가장 많은 급여를 기준으로 순위를 등수 필드에 입력
SELECT ename, sal, deptno, RANK() OVER(ORDER BY sal desc) 등수 FROM emp;
/* 결과
KING 5000 10 1
SCOTT 3000 20 2
FORD 3000 20 2
JONES 2975 20 4
BLAKE 2850 30 5
CLARK 2450 10 6
ALLEN 1600 30 7
TURNER 1500 30 8
MILLER 1300 10 9
WARD 1250 30 10
MARTIN 1250 30 10
ADAMS 1100 20 12
JAMES 950 30 13
SMITH 800 20 14
*/
-- 전체 사원을 대상으로 가장 많은 급여를 기준으로 순위를 등수1 필드, 부서별 급여순위를 등수 1
SELECT ename, sal, deptno, RANK() OVER(ORDER BY sal desc) 등수1,
RANK() OVER(PARTITION BY deptno ORDER BY sal desc) 등수2
FROM emp
ORDER BY deptno;

SELECT * FROM PLAYER_T;
-- 1. 등수를 구해서 알려주는 함수
-- rank vs. dense_rank
---- 1)
-- 100점이 10명이면 99점은 11등, 98점은 12등 ....
SELECT ename, sal, deptno, RANK() OVER(ORDER BY sal desc) 전체석차,
RANK() OVER(PARTITION BY deptno ORDER BY sal desc) 반석차
FROM emp
ORDER BY deptno;
-- 100점이 10명이이어도 99점은 2등, 98점은 3등 ....
SELECT ename, sal, deptno, DENSE_RANK() OVER(ORDER BY sal desc) 전체석차,
DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal desc) 반석차
FROM emp
ORDER BY deptno;

-- 2. 일련번호를 부여할 때
---- 1)
SELECT ROWNUM, ename, sal FROM emp ORDER BY sal DESC;

---- 2)
SELECT ROW_NUMBER() OVER(ORDER BY sal desc) 번호, ename, sal
FROM emp ORDER BY sal DESC;

-- 3. 그룹함수 VS 분석함수
---- 1)
SELECT ename, sal, SUM(sal) FROM emp; -- 오류발생
-- 결과 : ORA-00937: not a single-group group function
---- 2) 서브쿼리를 사용한 경우
SELECT ename, sal, (SELECT SUM(sal) FROM emp) FROM emp;
---- 3) 분석함수를 사용한 경우 : 2번보다 빠르다.
SELECT ename, sal, SUM(sal) OVER(PARTITION BY deptno) 결과 FROM emp;
---- 4) 부서별로 급여합계를 계산해줘
SELECT ename, sal, deptno, SUM(sal) OVER(PARTITION BY deptno) 결과 FROM emp;
SELECT ename, sal, deptno, SUM(sal) OVER(PARTITION BY deptno ORDER BY sal desc) 결과 FROM emp; -- ????
---- 5-1) 단순 부서별 집계정보만 보여줌
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
---- 5-2) 개인별 정보 + 부서별 집계정보
SELECT deptno, SUM(sal) OVER(PARTITION BY deptno) 결과, ename, sal, hiredate FROM emp;
-- *****
-- 4. ROWS 옵션은 계산 범위를 지정함
----1)
----- 1줄 앞에 나오는 거랑 1줄 뒤에 나오는 거랑 더해서 찍어줘
SELECT ename, sal, SUM(sal) OVER (ORDER BY ename ROWS BETWEEN 1 preceding AND 1 following) 결과 FROM emp;
/결과
ADAMS 1100 2700
ALLEN 1600 5550
BLAKE 2850 6900
CLARK 2450 8300
FORD 3000 6400
JAMES 950 6925
JONES 2975 8925
KING 5000 9225
MARTIN 1250 7550
MILLER 1300 5550
SCOTT 3000 5100
SMITH 800 5300
TURNER 1500 3550
WARD 1250 2750
*/
----2)
SELECT ename, sal, SUM(sal) OVER (ORDER BY ename ROWS BETWEEN 1 preceding AND CURRENT ROW) 결과 FROM emp;
/결과
ADAMS 1100 1100 <-- 1100
ALLEN 1600 2700 <-- 1100 + 1600
BLAKE 2850 4450 <-- 1100 + 1600 + 2850
CLARK 2450 5300 <-- ....
FORD 3000 5450
JAMES 950 3950
JONES 2975 3925
KING 5000 7975
MARTIN 1250 6250
MILLER 1300 2550
SCOTT 3000 4300
SMITH 800 3800
TURNER 1500 2300
WARD 1250 2750
/
----3) 현재열을 기준으로 앞에 데이터를 모두 누적하여 합계
------ 기준일자 중심으로 이전일자 실적자료 누적하여 합계를 표시하시오
SELECT ename, sal, SUM(sal) OVER (ORDER BY ename ROWS unbounded preceding) 결과 FROM emp;
----4) 비교
SELECT ename, sal, SUM(sal) OVER (ORDER BY ename ROWS unbounded preceding) 결과1,
SUM(sal) OVER(ORDER BY ename) 결과2 FROM emp;
-- 결과2 : ename을 기준으로 오름차순하여 누적데이터를 표시함(기본)
--*****
-- 5. 현재 ROW기준 앞줄컬럼 / 뒷줄 컬럼 가져올때
-- LAG LEAD
SELECT ename, sal,
LAG(sal, 1) OVER (ORDER BY sal) 앞줄급여,
LAG(ename, 1) OVER (ORDER BY sal) 앞줄이름
FROM emp;

SELECT ename, sal,
LEAD(sal, 1) OVER (ORDER BY sal) 뒷줄급여,
LEAD(ename, 1) OVER (ORDER BY sal) 뒷줄이름
FROM emp;
--**
-- 문제 1> player_t 선수이름, 키, 키가 큰 선수부터 1등으로 등수부여해서 조회
-- 결과> 서동명 196 1
-- 권정혁 195 2
SELECT player_name, height, RANK() OVER(ORDER BY height DESC) 등수 FROM PLAYER_T;
-- 결과 : 내림차순으로 인한 정렬로 인하여 (null) 값의 선수가 1등을 차지하는 오류 발생
-- 조치 방법 : nulls last 키워드를 이용하라.
SELECT player_name, height, RANK() OVER(ORDER BY height DESC nulls LAST ) 등수 FROM PLAYER_T;

-- 의문점
SELECT ename, sal, SUM(sal) OVER (ORDER BY ename ROWS BETWEEN 1 preceding AND CURRENT ROW) 결과 FROM emp;
-- 이 SQL문 언제써 ?
/*
대출계좌 입금일자 금액 이자
100 2015-11-03 100만
100 2015-11-17 300만
100 2015-11-23 700만
*/
-- 위에 데이터 정보 가공할 경우 쓴다. ㅠ.ㅠ LAG, LEAD 함수 ^^;;

-- 문제2. 각 팀선수 중 키가 제일 큰 선수들 이름, 키, 팀id 조회하기(각 팀별 키 1등만 뽑아오기)
---- 1) 선수이름, 키, 팀id, 각팀에서 키 기준 등수부여하기
SELECT player_name, height, team_id,
RANK() OVER(PARTITION BY team_id ORDER BY height DESC nulls LAST, birth ASC ) 등수
FROM PLAYER_T;

SELECT player_name, height, team_id,
RANK() OVER(PARTITION BY team_id ORDER BY height DESC nulls LAST, birth ASC ) 등수
FROM PLAYER_T
WHERE 등수 = 1; -- ORA-00904: "등수": invalid identifie

SELECT player_name, height, team_id,
RANK() OVER(PARTITION BY team_id ORDER BY height DESC nulls LAST, birth ASC ) 등수
FROM PLAYER_T
WHERE RANK() OVER(PARTITION BY team_id ORDER BY height DESC nulls LAST, birth ASC ) = 1;
-- ORA-30483: window functions are not allowed here
-- 정답>> 놀랍다. ★★★★★★★
SELECT *
FROM (SELECT player_name, height, team_id,
RANK() OVER(PARTITION BY team_id ORDER BY height DESC nulls LAST, birth ASC ) 등수
FROM PLAYER_T)
WHERE 등수 = 1;