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

/* 교육 3일차 CHAPTER 4. 그룹함수 /
/ SQL 해석시 FROM 절, WHERE 절, HAVING 절 GROUP BY 절, SELECT 절 순으로 해석 */
SELECT * FROM PLAYER_T;

-- 1> player_t 선수중 키가 185보다 큰 선수이름 조회
SELECT player_name FROM PLAYER_T WHERE height > 185;
--- 주의) 작성전에 height 필드의 타입 체크(문자? 숫자?) ★★★
-- 결과 : 49 개의 행이 선택되었습니다.

-- 2> 키가 185보다 큰 선수는 총 몇명 ?
SELECT COUNT()
FROM PLAYER_T
WHERE height > 185;
-- 결과 : COUNT() - 49
-- 3> 키가 185보다 큰 선수들이 각팀에 몇명씩 있는지 ?
SELECT team_id, COUNT()
FROM PLAYER_T
WHERE height > 185
GROUP BY team_id;
-- 결과 : count() - 8, 5, 3, 4, 9, 8, 1, 5, 5, 1

-- 4> 키가 185보다 큰 선수인원수가 5명보다 많은 팀은 ?
SELECT team_id, COUNT(*)
FROM PLAYER_T
WHERE height > 185 AND COUNT(player_name) > 5
GROUP BY team_id; -- 작동이 될것 같지만 불가능한 구문입니다.

-- WHERE 절은 Aggregation 이전, HAVING 절은 Aggregation 이후의 filtering 작업을 수행한다.
-- HAVING 조건걸 때 GROUP BY 필드만 필터링 가능(Aggregation 이후)
SELECT team_id, COUNT(*)
FROM PLAYER_T
WHERE height > 185
GROUP BY team_id
HAVING COUNT(player_name) > 5
ORDER BY COUNT(player_name) DESC ;
-- 결과 : K05 9, K01 8, K06 8

SELECT team_id, COUNT(*)
FROM PLAYER_T
WHERE height > 185
GROUP BY team_id
HAVING player_name LIKE '김%'
ORDER BY COUNT(player_name) DESC ; -- 사용 불가능한 구문

SELECT team_id, COUNT(*)
FROM PLAYER_T
WHERE height > 185
GROUP BY team_id
HAVING team_id > 'K04' AND COUNT(player_name) > 5
ORDER BY COUNT(player_name) DESC ;
-- 결과 : K05 9, K06 8

SELECT team_id, COUNT(*)
FROM PLAYER_T
WHERE height > 185
GROUP BY team_id
HAVING team_id = 'K04' AND COUNT(player_name) > 5
ORDER BY COUNT(player_name) DESC;

-- 결과 : 표시할 데이터가 없습니다.

-- *****
-- 문제를 풀어봅시다.
-- 1. player_t 선수들의 각팀 평균키가 얼마인지조회.
-- 평균키가 큰 팀부터 먼저나오도록..팀id 평균키
SELECT team_id, ROUND(AVG(height), 1)
FROM PLAYER_T
GROUP BY team_id
ORDER BY AVG(height) DESC;

SELECT team_id, ROUND(AVG(height), 1)
FROM PLAYER_T
GROUP BY team_id
ORDER BY 2 DESC; -- 같은 효과(SELECT 할 경우 추출할 필드번호 지정)

-- 2. 소속선수들의 평균키가 180보다 큰 포지션은 ?
SELECT position, AVG(height), COUNT(*)
FROM PLAYER_T
GROUP BY position ;
-- position 이 null 인 데이터도 있기 때문에 그룹핑할 때 null 그룹도 생성

SELECT position, AVG(height), COUNT(*)
FROM PLAYER_T
GROUP BY position
HAVING AVG(height)>180;
-- 결과 : DF 180.67 128, FW 180.04 96, GK 186.54 42

SELECT * FROM PLAYER_T WHERE position IS NULL ;

-- 3 . 포지션이 DF인 선수들이 각팀에 몇명씩있는지 조회하기(team_id 인원)
SELECT team_id, COUNT(*)
FROM PLAYER_T
WHERE position='DF'
GROUP BY team_id;
-- 결과 : 13, 13, 19, 13, 11, 14, 12, 14, 10, 9

-- 4. 같은 이름으로 2명 이상 등록되어있는 동명이인인 선수들 이름만 조회.
----> 이름기준으로 묶어서 해당인원 2명 이상인 이름만 나오도록.
-- 동명이인이 몇명인지 조사할 때 이름으로 GROUP BY 처리할것 ★★★★
SELECT player_name, COUNT(player_name)
FROM PLAYER_T
GROUP BY player_name
HAVING COUNT(player_name) > 1
ORDER BY COUNT(player_name) desc;
-- 결과 : 9개 행 - 곽기훈, 김은익, 김진우, 김현수, 박동우, 이영진, 이정운, 이종민, 이충호

-- 5. player_t 선수들의 이름글자수기준 몇명있는지 조회
---- 한글자인 선수가 몇명 두글자인선수가 몇명인지 알아봄 ==> 글자수 소속인원
SELECT LENGTH(player_name) 글자수, COUNT(player_name) 소속인원
FROM PLAYER_T
GROUP BY LENGTH(player_name);
-- 결과 : 1, 28, 425, 13 - 총 467 ^^

SELECT COUNT(player_name) FROM PLAYER_T; -- 467명

-- 6. 생일이 등록된 선수들을 대상으로 월별 생일자수(해당월에 태어난 선수가 몇명인지 조회.)
---- 생일정보는 date(년월일시분초정보)이므로 날짜정보중 일부정보만 추출해서 사용할 것
---- TO_CHAR(날짜, '추출할 정보')
-- 복습해볼까요? dual tbl은 dummy table 입니다. ^^
SELECT TO_CHAR(SYSDATE, 'day') FROM dual;
SELECT TO_CHAR(SYSDATE, 'year') FROM dual;
SELECT TO_CHAR(SYSDATE, 'month') FROM dual;

SELECT TO_CHAR(SYSDATE, 'yyyymm') FROM dual;

-- 교재 p.90
---- EXTRACT(추출정보 from 날짜) : 년도/월/일자 정보를 단독으로 숫자로 추출가능
SELECT extract(day FROM sysdate) FROM dual;
SELECT extract(month FROM sysdate) FROM dual;
SELECT extract(year FROM sysdate) FROM dual; -- extract 반환 타입 : 숫자

-- 정답)
SELECT TO_CHAR(birth, 'mm') 월, COUNT(player_name)
FROM PLAYER_T
WHERE birth IS NOT NULL
GROUP BY TO_CHAR(birth, 'mm');

SELECT extract(month FROM birth) 월, count(*)
FROM PLAYER_T
WHERE birth IS NOT NULL
GROUP BY extract(month FROM birth);

-- 응용문제 : 1977년도에 태어난 선수들의 월별 선수인원 확인
-- 1안)
SELECT TO_CHAR(birth, 'mm') 월, COUNT(player_name)
FROM PLAYER_T
WHERE extract(year FROM birth) = 1977
GROUP BY TO_CHAR(birth, 'mm');

SELECT extract(month FROM birth) 월, count(*)
FROM PLAYER_T
WHERE extract(year FROM birth) = 1977
GROUP BY extract(month FROM birth);

-- 2안) 속도 개선을 위한 구문! 가급적 WHERE 절에서 함수 연산처리 하지 말것.(★)
SELECT TO_CHAR(birth, 'mm'), COUNT(*)
FROM PLAYER_T
WHERE birth BETWEEN TO_DATE('19770101', 'yyyymmdd')
AND TO_DATE('19771231:23:59:59', 'yyyymmdd:hh24:mi:ss')
GROUP BY TO_CHAR(birth, 'mm');

SELECT TO_CHAR(birth, 'mm'), COUNT(*)
FROM PLAYER_T
WHERE birth BETWEEN TO_DATE('19770101', 'yyyymmdd')
AND TO_DATE('19780101','yyyymmdd')-1/24/60/60
GROUP BY TO_CHAR(birth, 'mm');
-- 구문 해석 : AND TO_DATE('19780101','yyyymmdd')-1/24/60/60 는
-- 1일/24시간/60분/60초 = 1sec 이 됩니다.
-- 계산값은 19771231:23:59:59가 나옴 ^^

-- SQL*Plus 에서 날짜 포맷 변경
-- ALTER SESSION SET nls_date_fromat='yyy-mm=dd hh24:mi:ss';

-- 교재 p.131 ROLLUP 및 CUBE 연산자 사용
SELECT deptno, SUM(sal), COUNT()
FROM emp
GROUP BY ROLLUP(deptno); -- 총계 구하는 KEYWORD : ROLLUP
/ 결과 - 캡처화면 p.133
10 8750 3
20 10875 5
30 9400 6
29025 14
/
SELECT deptno, job, SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno, job); -- 부서별 중심 집계 데이터
/ 결과 - 캡처화면 p.133
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 (null) 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 (null) 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 (null) 9400
(null)(null) 29025
*/

SELECT deptno, job, SUM(sal)
FROM emp
GROUP BY ROLLUP(job, deptno); -- 업무별 중심 집계 데이터
/* 결과 - 캡처화면 p.133
10 CLERK 1300
20 CLERK 1900
30 CLERK 950
CLERK 4150
20 ANALYST 6000
ANALYST 6000
10 MANAGER 2450
20 MANAGER 2975
30 MANAGER 2850
MANAGER 8275
30 SALESMAN 5600
SALESMAN 5600
10 PRESIDENT 5000
PRESIDENT 5000
29025
*/

-- 교재 p.141 GROUPING SETS
SELECT deptno, job, SUM(sal)
FROM emp
GROUP BY GROUPING SETS((deptno, job), null);
/* 결과
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
29025
*/

SELECT deptno, job, SUM(sal)
FROM emp
GROUP BY GROUPING SETS(deptno, job, null);
/* 결과
10 8750
20 10875
30 9400
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025
*/

-- 개발자 교육
SELECT position, COUNT()
FROM PLAYER_T
GROUP BY position;
/결과
DF 128
FW 96
GK 42
MF 159
TC 24
TD 6
TM 9
() 3
*/

SELECT position, COUNT()
FROM PLAYER_T
GROUP BY ROLLUP(position);
/결과
DF 128
FW 96
GK 42
MF 159
TC 24
TD 6
TM 9
() 3
() 467 <-- 총계
*/

SELECT NVL(position,'전체'), COUNT()
FROM PLAYER_T
GROUP BY ROLLUP(position); -- 소계값에도 '전체'를 표기함. 수정이 필요함
/ 결과

/
SELECT position, GROUPING(position), COUNT()
FROM PLAYER_T
GROUP BY ROLLUP(position); -- GROUPING() 를 통한 1값에만 position '전체' 입력
/* 결과
DF 0 128
FW 0 96
GK 0 42
MF 0 159
TC 0 24
TD 0 6
TM 0 9
0 3
1 467
*/

SELECT CASE WHEN GROUPING(position) = 1 THEN '전체'
ELSE position END,
COUNT()
FROM PLAYER_T
GROUP BY ROLLUP(position);
/ 결과
DF 128
FW 96
GK 42
MF 159
TC 24
TD 6
TM 9
3
전체 467
/
SELECT CASE WHEN GROUPING(position) = 1 THEN '전체'
ELSE NVL(position, '소계') END,
COUNT()
FROM PLAYER_T
GROUP BY ROLLUP(position);
/* 결과
DF 128
FW 96
GK 42
MF 159
TC 24
TD 6
TM 9
소계 3
전체 467
*/

/* 교재 p.151 JOIN
/*
EMP TBL
empno ename sal deptno
7369 SMITH 800 20
7788 SCOTT 3000 10

DEPT TBL
deptno dname
10 개발팀
20 운영팀
30 홍보팀
/
-- CARTESIAN PRODUCT ------------------------------------------
SELECT ename, dname FROM emp, dept ORDER BY 1;
/
FROM emp, dept 의 효과? 테이블 합치기
empno ename sal deptno DEPT TBL
7369 SMITH 800 20 10 개발팀
7369 SMITH 800 20 20 운영팀
7369 SMITH 800 20 30 홍보팀
7788 SCOTT 3000 10 10 개발팀
7788 SCOTT 3000 10 20 운영팀
7788 SCOTT 3000 10 30 홍보팀
의 테이블을 만들어 낸다. 그래서 결과가 의도치 않게 반환된다.
JOIN 과정

~

GO !!
*/

SELECT ename, dname FROM emp, dept WHERE deptno=deptno;
/* 결과 : ORA-00900: invalid SQL statement

---

WHERE
*/

-- EQUI Join --------------------------------------------------------------
SELECT ename, dname FROM emp, dept WHERE emp.DEPTNO=dept.DEPTNO;

SELECT emp.ENAME, dept.DNAME FROM emp, dept WHERE emp.DEPTNO=dept.DEPTNO;
/* 쿼리작성 좋은 습관 : SELECT 에서도 테이블명을 명시하여 작성, 좀 번거롭다. */

SELECT emp.ENAME, dept.DNAME
FROM emp, dept
WHERE emp.DEPTNO=dept.DEPTNO AND emp.ENAME LIKE '%A%';
/* FROM table 순서에 따라서도 속도차이가 났음(옛날에), 지금은 순서에 따른 속도 없음 /
/ JOIN 조건 순서도 속도에 영향은 없으나, 먼저 기술하는 습관을 기를 것. ★★★/
/ 결과
CLARK ACCOUNTING
ADAMS RESEARCH
ALLEN SALES
WARD SALES
JAMES SALES
MARTIN SALES
BLAKE SALES
*/

/* 테이블명 별칭사용하기 */
SELECT emp.ENAME, dept.DNAME
FROM emp e, dept d
WHERE emp.DEPTNO=dept.DEPTNO AND emp.ENAME LIKE '%A%';
-- 결과 : ORA-00904: "EMP"."ENAME": invalid identifier

/* FROM 절에서 별칭처리하면 테이블명을 별칭으로 호출하여야 한다. /
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.DEPTNO=d.DEPTNO AND e.ENAME LIKE '%A%';
/ 결과
CLARK ACCOUNTING
ADAMS RESEARCH
ALLEN SALES
WARD SALES
JAMES SALES
MARTIN SALES
BLAKE SALES
*/

SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM locations;

-- 문제 1. 업무(job)가 CLERK인 사원들의 이름, 급여, 업무, 부서명(dname) 조회
SELECT ename, sal, job, deptno FROM emp;

SELECT e.ename, e.sal, e.job, d.dname FROM emp e, dept d
WHERE e.job='CLERK' ; -- JOIN 조건지정을 해야함

SELECT ename, sal, job, d.dname FROM emp e, dept d
WHERE e.DEPTNO = d.DEPTNO AND e.job='CLERK' ;

SELECT e.ename, e.sal, e.job, d.dname FROM emp e, dept d
WHERE e.DEPTNO = d.DEPTNO AND e.job='CLERK' ;
/*결과
MILLER 1300 CLERK ACCOUNTING
SMITH 800 CLERK RESEARCH
ADAMS 1100 CLERK RESEARCH
JAMES 950 CLERK SALES
*/
-- 문제 2. 부서명이 SALES인 부서에 소속된 사원이름, 급여만 조회하기
SELECT ename, sal
FROM emp e, dept d
WHERE d.DNAME='SALES'; -- JOIN 조건지정을 해야함

SELECT ename, sal
FROM emp e, dept d
WHERE e.deptno = d.DEPTNO AND d.DNAME='SALES';
/* 결과
ALLEN 1600
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
*/
-- 문제 3. 부서번호 30번 아니면서 급여를 1500보다 많이 받는 사원들
-- 이름, 급여, 부서번호, 부서명, 근무도시(city) 조회
SELECT e.ename, e.sal, e.deptno, d.dname, l.city
FROM emp e, dept d, locations l
WHERE e.DEPTNO != 3 AND e.sal > 1500; -- 결과는 나오지만 올바르지 못함. JOIN 누락

SELECT e.ename, e.sal, e.deptno, d.dname, l.city
FROM emp e, dept d, locations l
WHERE e.deptno = d.deptno
AND d.loc_code = l.loc_code
AND e.DEPTNO != 30
AND e.sal > 1500; -- locations, deptno, emp 3개 tbl JOIN!!

/* 테이블이 N개이면 Join 조건지정시 n-1개 지정해야 한다. ★★★★ */

-- 교재 p.161 NON-EQUI Join (Non-이키 조인), 등호가 아닌 연산자 Join
/* 실습 판서
EMP tbl
ename sal
ALLEN 1500
SMITH 800
JANE 3300

Salgrade tbl
Grade Losal Hisal
1 0 700
2 701 990
3 991 3000
4 3001 3500
5 3501 9999
*/

SELECT ename, sal FROM emp;
SELECT * FROM salgrade;

SELECT e.ename, e.sal, s.*
FROM EMP e, SALGRADE s
WHERE e.sal >= s.losal AND e.sal <= s.hisal
ORDER BY 1;
/* 결과
ADAMS 1100 1 700 1200
ALLEN 1600 3 1401 2000
BLAKE 2850 4 2001 3000
CLARK 2450 4 2001 3000
FORD 3000 4 2001 3000
JAMES 950 1 700 1200
JONES 2975 4 2001 3000
KING 5000 5 3001 9999
MARTIN 1250 2 1201 1400
MILLER 1300 2 1201 1400
SCOTT 3000 4 2001 3000
SMITH 800 1 700 1200
TURNER 1500 3 1401 2000
WARD 1250 2 1201 1400
*/

SELECT e.ename, e.sal, s.*
FROM EMP e, SALGRADE s
WHERE e.sal BETWEEN s.losal AND s.hisal
ORDER BY 1; -- 동일 결과를 가져온다.

-- DB 테이블에서 데이터 조회시 알파벳 대소문자 구분없이 조회하는 방법 ★★★
SELECT ename FROM emp WHERE UPPER(ename) LIKE 'A%';
SELECT ename FROM emp WHERE LOWER(ename) LIKE 'a%';

-- 교재 p.164 OUTER JOIN !! 실습 ----------------------------------------
-- emp 테이블에 데이터 삽입(TOMMY)
INSERT INTO emp(empno, ename, sal) VALUES(9999, 'TOMMY', 700);
SELECT * FROM emp;

/* 실습 판서
EMP tbl
ename sal deptno
ALLEN 1500 20
TOMMY 700 null --> JOIN 결과시 누락되어버림
SMITH 800 10 부서번호가 null 이거나 dept에 없는 부서번호는 누락
JANE 1550 30

DEPT tbl
deptno dname
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
50 INSA
/
-- 사번, 이름, 급여, 소속부서명(dname) 조회
SELECT FROM dept;
SELECT e.empno, e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;
/* 결과 : 14개 결과
7782 CLARK 2450 ACCOUNTING
7839 KING 5000 ACCOUNTING
7934 MILLER 1300 ACCOUNTING
7369 SMITH 800 RESEARCH
7876 ADAMS 1100 RESEARCH
7902 FORD 3000 RESEARCH
7788 SCOTT 3000 RESEARCH
7566 JONES 2975 RESEARCH
7499 ALLEN 1600 SALES
7698 BLAKE 2850 SALES
7654 MARTIN 1250 SALES
7900 JAMES 950 SALES
7844 TURNER 1500 SALES
7521 WARD 1250 SALES
--> JOIN 결과시 누락되어버림. TOMMY 결과가 없음
--> 부서번호가 null 이거나 dept에 없는 부서번호는 누락
*/

SELECT* FROM dept;
SELECT e.empno, e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+); -- OUTER JOIN(반드시 한쪽만(+) 처리)
/*결과 : 15개 결과
7782 CLARK 2450 ACCOUNTING
7839 KING 5000 ACCOUNTING
7934 MILLER 1300 ACCOUNTING
7369 SMITH 800 RESEARCH
7876 ADAMS 1100 RESEARCH
7902 FORD 3000 RESEARCH
7788 SCOTT 3000 RESEARCH
7566 JONES 2975 RESEARCH
7499 ALLEN 1600 SALES
7698 BLAKE 2850 SALES
7654 MARTIN 1250 SALES
7900 JAMES 950 SALES
7844 TURNER 1500 SALES
7521 WARD 1250 SALES
9999 TOMMY 700 (null)
*/

SELECT* FROM dept;
SELECT e.empno, e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno; -- OUTER JOIN(반드시 한쪽만(+) 처리)
/*결과 : 16개 결과
7782 CLARK 2450 ACCOUNTING
7839 KING 5000 ACCOUNTING
7934 MILLER 1300 ACCOUNTING
7369 SMITH 800 RESEARCH
7876 ADAMS 1100 RESEARCH
7902 FORD 3000 RESEARCH
7788 SCOTT 3000 RESEARCH
7566 JONES 2975 RESEARCH
7499 ALLEN 1600 SALES
7698 BLAKE 2850 SALES
7654 MARTIN 1250 SALES
7900 JAMES 950 SALES
7844 TURNER 1500 SALES
7521 WARD 1250 SALES
OPERATIONS
INSA
*/

-- 교재 p.165 OUTER Join
SELECT * FROM TEAM_T;
SELECT * FROM STADIUM_T;

SELECT t.TEAM_NAME, s.STADIUM_NAME
FROM TEAM_T t, STADIUM_T s
WHERE t.STADIUM_ID = s.STADIUM_ID;
/*결과
시티즌 대전월드컵구장
아이콘스 부산구장
SK 부천구장
일화천마 성남구장
삼성블루윙즈 수원월드컵구장
LG치타스 안양구장
현대호랑이 울산문수구장
드래곤즈 전남구장
현대모터스 전북구장
스틸러스 포항구장
*/

SELECT t.TEAM_NAME, s.STADIUM_NAME
FROM TEAM_T t, STADIUM_T s
WHERE t.STADIUM_ID(+) = s.STADIUM_ID;
/*결과
서울 월드컵경기장
SK 부천구장
일화천마 성남구장
수원구장
삼성블루윙즈 수원월드컵구장
LG치타스 안양구장
김천구장
아이콘스 부산구장
울산구장
현대호랑이 울산문수구장
창원구장
스틸러스 포항구장
드래곤즈 전남구장
전주구장
현대모터스 전북구장
한밭종합운동장
시티즌 대전월드컵구장
*/

-- OUTER JOIN 사용하는 때는 언제인지 ?
/*
쇼핑몰에서 고객 테이블과 주문테이블이 1:n 으로 점선(Join)으로 ERD 된 경우
고객 테이블 데이터 : 5만명
주문 테이블 데이터 : 3만 주문건수
select * from 고객, 주문 where 고객.고객번호=주문.고객번호; 라고 하면
--> 결과는 5만명 고객 중 주문한 3만명 고객의 주문건수만 가져온다.
원하는 것!!
주문여부와 관련없이 5만명 고객을 대상으로 주문건수 조회시 outer join 을 사용한다.
select * from 고객, 주문 where 고객.고객번호=주문.고객번호(+);
*/

-- 교재 p.169 SELF Join
SELECT empno, ename, mgr FROM emp;
-- mgr 필드 : 멘토의 사원번호를 나타내는 필드
-- 멘토의 사원번호는 누구의 사원번호인가?
/*결과
empno ename mgr
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
9999 TOMMY (null)
*/

-- SELF JOIN시 테이블A를 2번 가져와서 FROM 절에 나타내야하는데,
-- 이경우 반드시 테이블A를 별칭처리해서 다른 별칭으로 FROM절에 기입 ★★★
-- FROM 테이블A 별칭1, 테이블A 별칭2
SELECT 사원.empno 사원번호 , 사원.ename 사원명, 멘토.mgr 멘토사원번호, 멘토.ename 멘토
FROM emp 사원, emp 멘토
WHERE 사원.MGR = 멘토.EMPNO;

SELECT 사원.empno 사원번호 , 사원.ename 사원명, 멘토.mgr 멘토사원번호, 멘토.ename 멘토
FROM emp 사원, emp 멘토
WHERE 사원.MGR = 멘토.EMPNO(+); -- 멘토없는 사원까지 포함하여 조회

--> 결론 !! 작은 테이블은 위와 같은 SQL 구문으로 처리가능하지만
--- 처리 속도의 문제가 있어 위와 같은 방법은 권장하지 않음
--- 테이블 데이터 규모가 십만건 이상의 데이터이면 비권장

-- 연습문제를 풀어봅시다. 연습전 데이터 삭제 1건 하고 시작합시다.
DELETE emp WHERE empno = 9999;
/* emp ,dept,salgrade,locations 중 필요한 테이블사용 */
SELECT * FROM emp;
SELECT * FROM SALGRADE;

-- 1.사원명에 'S'가 들어 있는 사원의 이름,급여,급여GRADE조회
SELECT e.ename 이름, e.sal 급여, s.grade sal급여등급
FROM emp e, dept d, salgrade s, locations l
WHERE (e.sal BETWEEN s.LOSAL AND s.HISAL) AND e.ename LIKE '%S%';
--> 문제 : FROM 절에 불필요한 테이블이 있으면 안됨
-- ★ JOIN할 경우 FROM절에 JOIN 대상이 아닌 테이블은 비포함!! (단순화할 것)
-- 정답
SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE (e.sal BETWEEN s.LOSAL AND s.HISAL) AND e.ename LIKE '%S%';

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE (e.sal BETWEEN s.LOSAL AND s.HISAL) AND INSTR(e.ename, 'S', 1, 1)!=0;
-- INSTR(e.name, 'S', 1, 1) : e.name에 'S'가 포함된 것이 1번이라도 있는지

-- player_t 선수 중 영문이름에 ''문자가 들어있는 선수들만 이름, 영문이름 조회
SELECT player_name, e_player_name
FROM PLAYER_T
WHERE e_player_name LIKE '%#%' ESCAPE '#'; -- ★ %, _ 검색할 경우 ESCAPE 연산!!

SELECT player_name, e_player_name
FROM PLAYER_T
WHERE INSTR(e_player_name, '_', 1, 1) != 0;

-- 2. grade가 4등급인 사원의 이름,급여, 부서명(dname)조회
SELECT e.ename 이름, e.sal 급여, d.dname 부서명
FROM emp e, dept d, salgrade s
WHERE (e.DEPTNO = d.DEPTNO)
AND (e.sal BETWEEN s.LOSAL AND s.HISAL)
AND s.GRADE = 4;
/결과
CLARK 2450 ACCOUNTING
JONES 2975 RESEARCH
SCOTT 3000 RESEARCH
FORD 3000 RESEARCH
BLAKE 2850 SALES
*/
/ <3,4번은 self조인, emp 2번 사용해서> /
-- 3. 입사입사자 1981년 11월 20일 오후 3시 17분 20초이후에 입사한
-- 사원들의 이름,입사일,멘토이름,멘토입사일자 조회.
SELECT 사원.ename, 사원.HIREDATE, 멘토.ename, 멘토.HIREDATE
FROM emp 사원, emp 멘토
WHERE (사원.MGR = 멘토.EMPNO)
AND 사원.HIREDATE >= TO_DATE('19811120:15:17:20', 'yyyymmdd:hh24:mi:ss');
-- TO_DATE('1981-11-20:15:17:20', 'yyyy-mm-dd:hh24:mi:ss');
/ 결과
SCOTT 1982-12-09 JONES 1981-04-02
FORD 1981-12-03 JONES 1981-04-02
JAMES 1981-12-03 BLAKE 1981-05-01
MILLER 1982-01-23 CLARK 1981-01-09
ADAMS 1983-01-12 SCOTT 1982-12-09
/
-- 4. 본인의 멘토 보다 급여를 더 많이 받는 사원의 이름, 급여 조회.
SELECT 사원.ename, 사원.sal, 멘토.ename, 멘토.sal
FROM emp 사원, emp 멘토
WHERE (사원.MGR = 멘토.EMPNO)
AND 사원.sal > 멘토.sal;
/결과
SCOTT 3000 JONES 2975
FORD 3000 JONES 2975
/
-- 비교.
-- emp사원중에서 보너스를 급여보다 더많이 받는 사원의 이름, 급여, 보너스 조회
SELECT ename, sal, comm
FROM emp
WHERE comm > sal;
/결과
MARTIN 1250 1400
/
-- **
select 사원.ename, 사원.sal ,멘토.ename , 멘토.sal
from emp 사원 ,emp 멘토
where 사원.mgr = 멘토.empno;
/*결과:13 개의 행이 선택되었습니다. */

-- 5. 급여를 2300 보다 많이 받는 사원의
-- 이름, 급여, 부서명, 근무지(city), 급여등급(grade) 조회.
SELECT * FROM emp;
SELECT * FROM DEPT;
SELECT * FROM SALGRADE;
SELECT * FROM LOCATIONS;

SELECT e.ename, e.sal, d.DNAME, l.LOC_CODE, s.GRADE
FROM emp e, DEPT d, SALGRADE s, LOCATIONS l
WHERE (e.DEPTNO = d.DEPTNO)
AND (e.sal BETWEEN s.LOSAL AND s.HISAL)
AND (d.LOC_CODE = l.LOC_CODE)
AND e.sal > 2300;
/*결과 : 6 개의 행이 선택되었습니다.
CLARK 2450 ACCOUNTING A1 4
KING 5000 ACCOUNTING A1 5
JONES 2975 RESEARCH B1 4
FORD 3000 RESEARCH B1 4
SCOTT 3000 RESEARCH B1 4
BLAKE 2850 SALES C1 4
*/

/*
6. 부서원들의 급여합계,소속인원 수 조회
1) 부서번호 급여합계 인원
------- --------- -----
/
SELECT deptno, SUM(sal), COUNT()
FROM emp
GROUP BY deptno;
/* 결과
10 8750 3
20 10875 5
30 9400 6
*/

/*
2) 부서명 급여합계 인원
----- --------- -----
단> 소속사원이 없더라도 모든 부서명 나올수 있도록
/
SELECT d.DNAME, SUM(e.SAL), COUNT()
FROM emp e, DEPT d
WHERE e.DEPTNO = d.DEPTNO
GROUP BY d.DNAME;
/* 결과
ACCOUNTING 8750 3
RESEARCH 10875 5
SALES 9400 6
*/

-- 정답>>
SELECT dname, SUM(sal), COUNT()
FROM emp e, dept d
WHERE e.DEPTNO = d.DEPTNO
GROUP BY dname;
/ 결과
ACCOUNTING 8750 3
RESEARCH 10875 5
SALES 9400 6
*/

SELECT dname, SUM(sal), COUNT()
FROM emp e, dept d
WHERE e.DEPTNO(+) = d.DEPTNO
GROUP BY dname;
/ 결과
ACCOUNTING 8750 3
INSA (null) 1 <--- 잘못된 정보, 부서원이 없음에도 인원수가 1로 표시됨.
OPERATIONS (null) 1 <--- 잘못된 정보, 부서원이 없음에도 인원수가 1로 표시됨.
RESEARCH 10875 5
SALES 9400 6
*/

-- 해결방법 : 최종정답 count() --> count(empno)로 변환
SELECT dname, SUM(sal), COUNT(), COUNT(empno)
FROM emp e, dept d
WHERE e.DEPTNO(+) = d.DEPTNO
GROUP BY dname;
/* 결과
ACCOUNTING 8750 3 3
INSA (null) 1 0
OPERATIONS (null) 1 0
RESEARCH 10875 5 5
SALES 9400 6 6
*/

-- 3일차 7교시 : intersect 연산자, minus 연산자, union 연산자
SELECT deptno FROM dept;
-- 결과 : 10, 20, 30, 40, 50
SELECT deptno FROM emp;
-- 결과 : 20, 30, 30, 20, 30, 30, 10, 20, 10, 30, 20, 30, 20, 10

SELECT deptno FROM dept
INTERSECT
SELECT deptno FROM emp;
-- 결과 : 10, 20, 30

SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
-- 결과 : 40, 50

SELECT deptno FROM dept
UNION -- 중복값 제거함
SELECT deptno FROM emp;
-- 결과 : 10, 20, 30, 40, 50

SELECT deptno FROM dept
UNION ALL -- 중복값 제거 안함, 개발자들이 가장 많이 쓰는 연산자(★)
SELECT deptno FROM emp;
-- 결과 : 19 개의 행이 선택되었습니다.

/* SET 연산자(UNION, UNION ALL, INTERSECT, MINUS) - 교재 p.188 */

-- ROLLUP() 효과를 가져오는 구문의 이해
SELECT deptno, SUM(sal) s1, COUNT() c1 FROM emp GROUP BY deptno
UNION ALL
SELECT SUM(sal) s2, COUNT() c2 FROM emp;
-- 결과 : ORA-01789: query block has incorrect number of result columns
-- 쿼리문 상에서 대응되는 문장의 컬럼수는 맞아야 연산이 가능하다.
-- 처리할 수 있는 방법 ? NULL, 을 빠진 컬럼에 삽입하여 처리
SELECT deptno, SUM(sal) s1, COUNT() c1 FROM emp GROUP BY deptno
UNION ALL
SELECT NULL, SUM(sal) s2, COUNT() c2 FROM emp;

-- 정렬
SELECT deptno, SUM(sal) s1, COUNT() c1 FROM emp GROUP BY deptno
UNION ALL
SELECT NULL, SUM(sal) s2, COUNT() c2 FROM emp
ORDER BY s2;
-- 결과 : ORA-00904: "S2": invalid identifier
-- 정렬할 경우 첫번째 문장을 기준으로 정렬이 가능하다. 따라서 ...
SELECT deptno, SUM(sal) s1, COUNT() c1 FROM emp GROUP BY deptno
UNION ALL
SELECT NULL, SUM(sal) s2, COUNT() c2 FROM emp
ORDER BY s1;
/* 결과
10 8750 3
30 9400 6
20 10875 5
29025 14
*/

-- 1. emp 사원중 급여 4000 보다 많이 받는 사원들 이름, 급여 조회
SELECT * FROM emp;
SELECT ename, sal
FROM EMP
WHERE sal > 4000;
/* 결과 : KING 5000 */

-- 2. emp 사원중 FORD 보다 급여 많이 받는 사원이름 급여 조회
SELECT ename, sal
FROM EMP
WHERE sal > (SELECT sal FROM EMP WHERE ename ='FORD');
/* 결과 : KING 5000 */

-- WHERE 절 : 일반 조건!!, WHERE절에서 그룹함수를 사용할 수 없음
-- HAVING 연산자 : 그룹함수(AVG, SUM)의 연산결과에 대한 필터링을 위함

SELECT SUM(sal) FROM emp WHERE AVG(sum)>3000;
-- 결과 : ORA-00934: group function is not allowed here