231123 3일차(오라클)

--*1122 복습

--부서번호가 30번인 사원정보

: WHERE 절

--부서번호 10,20,30번인 사원정보

(IN 연산자)

--부서번호가 10번이 아닌 사원정보(2가지)

: 부서번호 뒤에 !=, <>

--부서번호가 10,20,30번이 아닌 사원정보

: NOT

--급여의 최댓값은?

:MAX

--급여가 4000~5000인 사원정보

:BETWEEN A AND B 연산자

(WHERE 열이름 BETWEEN 최솟값 AND 최댓값)

--이름이 'A'로 시작하는 사원들

: LIKE연산자와 와일드 카드 'A%'

--이름 끝에서 두번째 글자가 'A'인 사원

: LIKE '%A_'

--COMM의 값이 NULL인 사원정보

: IS NULL

--COMM의 값이 NULL이 아닌 사원 정보

: IS NOT NULL

--NVL() COMM이 NULL이면 0으로 처리해서 ENAME,COMM을 출력

: NULL 함수>NVL(COMM,0)

--NVL() COMM이 NULL이면 0으로 처리해서 ENAME,COMM A COMM을 포함한 연봉

: NVL(COMM,0)/ SAL*12+NVL(COMM,0)

--NVL2()을 사용하여 COMM이 NULL이면 0으로 처리해서 ENAME,COMM을 출력

: NVL2(COMM,COMM,0)

--이름의 중간에 소문자 ***MI**가 있는 사원의 정보

/ ENAME을 소문자 OR 대문자로 바꾸고 출력

WHERE LOWER(ENAME) LIKE '%_mi_%'

--1+1=?

: FROM DUAL 사용

--990723-2824014 14자리에서 뒤쪽 부분 '2824014' 만 출력, DUAL사용

: SUBSTR(문자열 데이터, 시작 위치)

--'990723-2824014'앞쪽 부분 출력

: SUBSTR(문자열 데이터, 시작 위치, 추출길이)

 

 

*1123 수업

※다중행 함수 : 하나의 열에 출력 결과를 담는 함수
※GROUP BY절 : 결과값을 원하는 열로 묶어 출력
 >
※HAVING절 : GROUP BY 절에 조건을 줄 때 사용
SELECT DEPTNO, JOB, AVG(SAL)을 할때 평균 값은 1개 행으로만 나옴
DEPTNO, JOB을 GROUP BY 해줘서 출력

--부서별 평균 급여 출력
SELECT AVG(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO;

--부서 번호 및 직책별 평균 급여로 정렬(ORDER BY)
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;

--부서 번호별 평균 추가 수당을 출력, 빈칸 채우라
부서번호 DEPTNO 평균 추가 수당 AVG(COMM), 빈칸채우기 NVL(COMM,0)
NVL을 먼저하고, AVG를 해야한다. COMM의 빈칸을 먼저 채울건지 정하고, 평균낸다.
SELECT DEPTNO,AVG(NVL(COMM,0))
FROM EMP
GROUP BY DEPTNO;

--GROUP BY, HAVING 절 비교
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>2000
ORDER BY DEPTNO;


SELECT DEPTNO, AVG(SAL)
FROM EMP
WHERE SAL>1000
GROUP BY DEPTNO
HAVING AVG(SAL)>2000
ORDER BY DEPTNO;

*실행순서 2(테이블)-> 3(조건) -> 4(그룹) -> 5(그룹에 조건) -> 1(선택) ->6(정렬)

--WHERE절과 HAVING절을 모두 사용한 경우
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
WHERE SAL>2000
GROUP BY DEPTNO, JOB
HAVING AVG(SAL)BETWEEN 2000 AND 3000
ORDER BY DEPTNO, JOB;

--HAVIG절을 사용하여 EMP 테이블의 부서별 직책의 급여가 500 이상인 사원들의 부서 번호, 직책, 부서별 직책의 평균 급여가 출력되도록 SQL문의 빈칸을 채워 보시오. 
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL)>=500
ORDER BY DEPTNO, JOB;

※그룹화와 관련된 여러 함수
*ROLLUP: 중간 합계 보여주는 것
--기존 GROUP BY절만 사용한 그룹화
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;

--ROLLUP 함수를 적용한 그룹화> 소그룹간의 합계를 계산하는 함수
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

*CUBE : 항목들 간의 다차원적인 소계를 계산
>부서와 상관없이 직책별
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

--DEPTNO를 먼저 그룹화한 후 ROLLUP함수에 JOB 지정하기
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB);
--JOB를 먼저 그룹화한 후 ROLLUP함수에 DEPTNO 지정하기
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY JOB, ROLLUP(DEPTNO);

*EMP 테이블을 이용하여 부서번호,평균 급여, 최고 급여, 최저 급여, 사원수를 출력
--단 평균 급여를 출력할 때 소수점을 제외하고 각 부서번호별로 출력
>TRUNC 정수와 가까운 수 출력
SELECT DEPTNO, TRUNC(AVG(SAL)),MAX(SAL), MIN(SAL),COUNT(*)
FROM EMP
GROUP BY DEPTNO;

--같은 직책에 종사하는 사원이 3명 이상인 직책과 인원수 출력
SELECT JOB, COUNT(*)
FROM EMP
GROUP BY JOB
HAVING COUNT(*)>=3;

--사원들의 입사 연도를 기준으로 부서별로 몇 명이 입사했는지 출력
TO_ CHAR : 날짜, 숫자 등의 값을 문자열로 변환하는 함수
>날짜 포맷 변경(YYYY-MM-DD)
SELECT TO_CHAR(HIREDATE, 'YYYY') AS HIRE_YEAR, DEPTNO, COUNT(*)
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO; 

--추가 수당(COMM)을 받는 사원 수와 받지 않는 사원 수를 출력    
SELECT NVL2(COMM,'O','X') AS EXIST_COMM, COUNT(*) 
FROM EMP
GROUP BY NVL2(COMM,'O','X');

--각 부서의 입사 연도별 사원 수, 최고 급여, 급여 합, 평균 급여를 출력하고 각부서별 소계와 총계를 출력
SELECT DEPTNO,TO_CHAR(HIREDATE,'YYYY') AS HIRE_YEAR, COUNT(*),MAX(SAL),SUM(SAL),AVG(SAL)
FROM EMP
GROUP BY DEPTNO,ROLLUP(HIREDATE);


※JOIN 여러 테이블을 하나의 테이블처럼 사용하는 조인

--FROM절에 여러 테이블 선언하기 >EMP,DEPT
SELECT * 
FROM EMP, DEPT
ORDER BY EMPNO;

--EMP테이블의 DEPTNO와 DEPT 테이블의 DEPTNO를 합쳐서 맞는 정보를 찾는다.
SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

SELECT ENAME, JOB, DNAME, LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;


--테이블 별칭 설정
SELECT E.ENAME, E.JOB, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO;


--EMP 테이블 별칭을 E로, DEPT 테이블 별칭은 D로 하여 EMP테이블의 사원 번호와 DEPT 테이블 부서 이름이 출력되도록 다음 SQL문 코드를 채워 보세요
SELECT E.EMPNO, D.DNAME
FROM EMP E, DEPT D;


※조인 종류
*등가 조인 조인을 사용한다는 것은 대부분 등가 조인
등가조인 EQUI JOIN/ 내부조인 INNER JOIN 단순조인 SIMPLE JOIN
*비등가 조인 NON=EQUI JOIN

--WHERE절에 추가로 조건식 넣어 출력
SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL>=3000;

SELECT E.JOB, D.LOC
FROM EMP E, DEPT D 
WHERE E.DEPTNO= D.DEPTNO
AND E.ENAME='SMITH';

--EMP 테이블 별칭을 E로, DEPT 테이블 병칭을 D로 하여 다음과 같이 등가 조인을 했을 때 급여가 2500 이하이고 사원 번호가 9999 이하인 사원의 정보가 출력되도록 다음 SQL문 코드를 채워 보세요.

SELECT *FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL<=2500 AND E.EMPNO <=9999;

 

*비등가 조인

SELECT *FROM EMP;
SELECT *FROM SALGRADE;
SELECT *FROM EMP, SALGRADE;

SELECT ENAME, SAL FROM EMP;

*EMP과 SALGRADE 다른 테이블 조인 비교

--급여 범위를 지정하는 조건식으로 조인
-등호사용
SELECT * FROM EMP E, SALGRADE S
WHERE S.LOSAL<=SAL AND S.HISAL>=SAL;

-BETWEEN A AND B 연산자 사용
SELECT * FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

--등호사용
SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP E , SALGRADE S
WHERE S.LOSAL<=SAL AND S.HISAL>=SAL;

--BETWEEN A AND B 연산자 사용

:EMP 테이블과 SALGRADE 테이블 조인을 손쉽게 처리할 수 있음
SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP E , SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

--SMITH의 급여액과 급여 등급은?
SELECT E.ENAME,E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.ENAME= 'SMITH'
AND E.SAL BETWEEN S.LOSAL AND S.HISAL;


--자체조인 : 같은 테이블을 쓰되, 별칭을 다르게 해서 새로운 테이블을 만든 것 처럼 한다.

--테이블 만들기
CREATE TABLE EMPNEW AS SELECT * FROM EMP; 
SELECT *FROM EMPNEW;


--SMITH의 매니저 이름은?
SELECT E.EMPNO,E.ENAME,E.MGR, EN.ENAME AS MGR_NAME
FROM EMP E, EMPNEW EN 
WHERE E.MGR=EN.EMPNO
AND E.ENAME='SMITH';


*외부조인 : 조인 기준 열의 어느 한쪽이 NULL 이어도 강제로 출력하는 방식
왼쪽 외부 조인: WHERE TABLE1.COL1=TABLE2.COL1(+)
오른족 외부 조인: WHERE TABLE1.COL1(+)=TABLE2.COL1


--왼쪽 외부 조인 사용하기>보이지 않았던 7839 KING이 보인다
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR=E2.EMPNO(+)
ORDER BY E1.EMPNO;

--오른쪽 외부 조인 사용하기>보이지 않았던 14~21이 보인다
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR(+)=E2.EMPNO
ORDER BY E1.EMPNO;

-급여가 2000 초과인 사원들의 부서정보, 사원정보를 출력
SELECT E.DEPTNO, D.DNAME,E.EMPNO,E.ENAME,E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL>2000
ORDER BY E.DEPTNO;

--각 부서별 평균 급여, 최대 급여, 최소 급여, 사원수 출력
SELECT E.DEPTNO, D.DNAME, TRUNC(AVG(E.SAL)),MAX(E.SAL),MIN(SAL), COUNT(*)
FROM EMP E, DEPT D
WHERE E. DEPTNO= D.DEPTNO
GROUP BY E.DEPTNO, D.DNAME;

--모든 부서 정보와 사원 정보를 부서번호, 사원 이름 순으로 정렬하여 출력
SELECT E.DEPTNO,D.DNAME,E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
ORDER BY E.DEPTNO, E.ENAME;

*서브쿼리 : 쿼리 안에 있는 쿼리>괄호로 사용한다.

--* JONES의 급여보다 높은 급여를 받는 사원 정보 출력 과정

--JONES 월급 출력
SELECT ENAME, SAL
FROM EMP
WHERE ENAME LIKE'JONES';

--급여 2975보다 높은 사원 정보 출력
SELECT ENAME, SAL FROM EMP
WHERE SAL>2975;


--서브쿼리로 JONES의 급여보다 높은 급여를 받는 사원 정보 출력
SELECT ENAME, SAL FROM EMP
WHERE SAL>(SELECT SAL FROM EMP
            WHERE ENAME LIKE'JONES');


--*DALLAS에 근무하는 사원의 이름은?
--DALLAS에 근무하는 사원의 DEPTNO 구하기

SELECT*FROM DEPT; >DEPT에서 LOC를 알게된다
SELECT*FROM DEPT WHERE LOC='DALLAS'> DALLAS의 DEPTNO=20인 걸 알게된다.
SELECT ENAME FROM EMP WHERE DEPTNO=20; >EMP테이블에서 DEPTNO가 20인 사람의 이름을 셀렉트한다.

--서브쿼리 사용
SELECT ENAME
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='DALLAS');


--서브쿼리를 사용하여 EMP 테이블의 사원 정보 중에서 사원 이름이 ALLEN인 사원의 추가 수당 보다 많은 추가 수당을 받는 사원 정보를 구하도록 다음 코드를 채워 보시오.
SELECT * FROM EMP
WHERE COMM>(SELECT COMM FROM EMP
            WHERE ENAME LIKE'ALLEN');


--DALLAS에 근무하는 사원의 이름과 부서명은?
SELECT E.ENAME, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO =D.DEPTNO
AND D.LOC='DALLAS';
--서브쿼리로 하는 것은 조인으로도 할 수 있다.(테이블이 클 경우 서브쿼리로 하는게 좋다)

※단일행 서브쿼리 :실행 결과가 하나인 서브쿼리/단일행 연산자를 사용하여 비교
*서브쿼리의 결과 값이 날짜형인 경우
--SCOTT의 입사날짜보다 빠른 사원의 이름
--SCOTT의 입사날짜
SELECT HIREDATE
FROM EMP
WHERE ENAME='SCOTT';
--위를 서브쿼리로 사용하여 출력
SELECT ENAME FROM EMP
WHERE HIREDATE<(SELECT HIREDATE
FROM EMP
WHERE ENAME='SCOTT');

--*평균월급보다 많이 받는 사원의 이름?
--평균 월급
SELECT AVG(SAL) FROM EMP;
--더 많이 받는 사원
SELECT ENAME FROM EMP
WHERE SAL>(SELECT AVG(SAL) FROM EMP);


--서브쿼리를 사용하여 EMP 테이블에서 전체 사원의 평균 급여보다 작거나 같은 급여를 받고 있는  20번 부서의 사원 및 부서의 정보를 구하도록 코드를 채워 보세요

SELECT * FROM EMP 
WHERE SAL<=(SELECT AVG(SAL) FROM EMP)
AND DEPTNO = 20;

--실행 결과가 여러 개인 경우에는 등호가 안되고 IN을 사용한다.
SELECT ENAME,DEPTNO,SAL FROM EMP
WHERE SAL IN(SELECT MAX(SAL) FROM EMP
             GROUP BY DEPTNO);