Oracle_sql그룹함수

--예제 2) 최근에 입사한 (HIREDATE) 사원과 (EMPLOYEE)
-- 가장 오래전에 입사한 사원의입사일 출력하기
-- => 최근에 입사 : 입사일이 최고값(MAX)
-- => 가장 오래전에 입사 : 입사일이 최저값(MIN)

SELECT MAX(HIREDATE) AS 최근
,MIN(HIREDATE) AS 오래전
FROM EMPLOYEE;
--(참고) 자동으로 NULL값은 제외하고 계산
--예제 3) 사원들의(EMPLOYEE) 상여금 (COMMISSION) 총액 출력하기
-- 사원 : EMPLOYEE

SELECT SUM(COMMISSION)
FROM EMPLOYEE;

-- 예제3) 사원들의(EMPLOYEE) 총인원을 출력하세요
-- 사용법) COUNT(컬럼명), COUNT(*)

SELECT COUNT(*) FROM EMPLOYEE;

--예제 4) 상여금을(COMMISSION) 못받는 사원수 구하기

SELECT COUNT(*) FROM EMPLOYEE
WHERE COMMISSION IS NULL;

--간단연습 상여금을 받는 사원수 구하기
SELECT COUNT(*) FROM EMPLOYEE
WHERE COMMISSION IS NOT NULL;

-- 예제 5) 그룹함수 DISTINCT(중복제거) 예악어 사용
-- 직위의 종류가 몇개인지 출력하기

SELECT COUNT(DISTINCT JOB) AS 직업개수
FROM EMPLOYEE;

--2) 부분 데이터 집계하는 그룸함수 : GROUP BY
-- 예제 7) 소속 부서별(DNO) 평균 급여를(SALARY) 부서번호와 함계 출력
-- 평균 : 버림(절삭)
--특징 : 1) 그룹핑컬럼 : 그룹(같은 데이터를 의미를한다)
-- 2) 같은 데이터끼리 뽑내서 그 개수가 몇개인가? COUNT
-- 같은 데이터끼리 뽑아내서 그 합계가 얼마인가? SUM
-- 3) 그룹핑 컬럼은 SELECT 에서 그룹함수와 사용가능

SELECT DNO, TRUNC (AVG(SALARY)) AS평균급여
FROM EMPLOYEE
GROUP BY DNO;

--간단연습 : 직위별 평균 급여를 출력하세요

SELECT JOB, TRUNC (AVG(SALARY))
FROM EMPLOYEE
GROUP BY JOB;

-- 예제 8) 부서번호별(DNO), 직위별(JOB) 데이터 건수(COUNT)
-- 및 급여(SALARY) 총액(SUM) 구하기
-- 사원 : EMPLOYEE
-- 힌트 : GROUP BY DNO, JOB

SELECT DNO, JOB, COUNT(*), SUM(SALARY)
FROM EMPLOYEE;
GROUP BY DNO, JOB;

-- 3) 부분데이터 집계에서 조건절2 사용하기 : HAVING (그룹함수만 사용가능)
-- 예제 9) 부서번호별(DNO) 최고급여가(SALARY) 3000 이상인 부서의 번호(DNO)와
-- 최고급여금액 구하기
-- 1) 부서번호별(DNO) 최고급여 : ?
-- 2) 최고급여를 각각 비교해서 3000 이상인 값만 화면 출력
-- HAVING : 그룹함수에서만 사용가능
SELECT DNO, MAX(SALARY)
FROM EMPLOYEE
GROUP BY DNO
HAVING MAX(SALARY) >= 3000;

-- 연습 1) 사원테이블에서(EMPLOYEE) JOB(직위)이 1) MANAGER 인
-- 값을 제외하고 3) 급여(SALARY) 총액(SUM)이 5000 이상인
-- 2) 직급별(JOB) 총액 구하기
-- 힌트 )
-- 1) MANAGER 인 값 제외(조건1) : WHERE 컬럼 NOT LIKE '%문자%'
-- 2) 직급별(JOB) 급여 총액 구하기
-- 3) 급여(SALARY) 총액(SUM)이 5000 이상이 사원(조건2) : HAVING

SELECT JOB FROM EMPLOYEE
WHERE JOB NOT LIKE '%MANAGER%'
GROUP BY JOB
HAVING SUM(SALARY) >= 5000;

Oracle_sql_Join

-- 14_join.sql
-- 왜? 1) 네트웍으로 SELECT 문을 전송함 : 명령실행시 (성능이 느려짐)
-- 2) 그래서 조인을 사용해서 SELECT 사용을 줄임
-- 예제1) 사원번호가(ENO) 7788 인 사원의 부서명은(DNAME) 뭘까요?
-- 사원 : EMPLOYEE
-- 부서 : DEPARTMENT
--
SELECT DNO FROM EMPLOYEE
WHERE ENO = 7788;
-- 부서명 없음 : 부서명은(DNAME) 부서테이블에 있음(DEPARTMENT)
-- 사원테이블에서 부서번호를 찾아서 부서테이블에서 다시 재조회 : 20(dno)
SELECT DNAME FROM DEPARTMENT
WHERE DNO = 20;

-- => SELECT(조회) 최소화할수록 성능이 좋아짐
-- 왜? 1) 네트웍으로 SELECT 문을 전송함 : 명령실행시

-- => 개선 : 조인사용(*)
-- 1) =(equal) 조인 : ** (성능 좋음)
-- 사용법 : SELECT 테이블1., 테이블2.
-- FROM 테이블1
-- ,테이블2
-- WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼;
SELECT EMPLOYEE., DEPARTMENT.*
FROM EMPLOYEE
,DEPARTMENT
WHERE EMPLOYEE.DNO = DEPARTMENT.DNO
AND EMPLOYEE.ENO = 7788;

--위에 있는 테이블=> 별명 : 효율적으로 코딩

-- 위의 있는 테이블 => 별명 : 효율적으로 코딩
SELECT EM., DE.
FROM EMPLOYEE EM
,DEPARTMENT DE
WHERE EM.DNO = DE.DNO
AND EM.ENO = 7788;

--간단 연습
-- 간단연습 ) 사원번호가 7499 인 사원들에 소속된 부서정보를 모두 출력하세요
-- 단, 테이블 별명을 이용해서 조인하세요

SELECT DE.*
FROM EMPLOYEE EM
,DEPARTMENT DE
WHERE EM.DNO = DE.DNO
AND EM.ENO = 7499;

-- 예제 2) 사원번호가 7499 또는 7900 인 사원들에 소속된 부서정보를 모두 출력하세요
-- 부서 : DEPARTMENT
-- 사원 : EMPLOYEE

SELECT EM., DE.
FROM EMPLOYEE EM
,DEPARTMENT DE
WHERE EM.DNO = DE.DNO
AND EM.ENO IN (7499, 7900);

-- 간단연습 2) 조인하고 아래 조건을 추가하세요
-- 사원번호가(테이블명.ENO) 7500 ~ 7700 사이에 있는 사원들의 소속된 부서정보를 출력하되
-- 조건(범위) : 테이블명.컬럼명 BETWEEN A AND B

SELECT EM., DE.
FROM EMPLOYEE EM, DEPARTMENT DE
WHERE EM.DNO = DE.DNO
AND EM. ENO BETWEEN 7500 AND 7700;

-- 2) 아우터 조인 :
-- 예제) MANAGER 를 공통컬럼으로 등호조인하되 NULL 값도 화면에 표시하세요
-- 아래 결과 : NULL 값은 조인되지 않음(화면에 안보임)
-- 사용법 : SELECT 테이블1.컬럼명, 테이블2.컬럼명
-- FROM 테이블1
-- , 테이블2
-- WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼(+)
-- (+) 컬럼 : NULL 값이 포함된 컬럼에 붙임

SELECT EM.ENAME, MA.ENAME
FROM EMPLOYEE EM, EMPLOYEE MA
WHERE EM.MANAGER = MA.MANAGER(+);

-- 기타 등등 :

-- SQL-99 표준 표기법) 조인 :
-- 오라클, MY-SQL 모든 제품에서 동작
-- 4-1) 오라클 조인 :

SELECT EM., DE.
FROM EMPLOYEE EM
,DEPARTMENT DE
WHERE EM.DNO = DE.DNO

-- 4-2 ) SQL-99 표준 표기법
SELECT EM., DE.
FROM EMPLOYEE EM
JOIN DEPARTMENT DE
ON (EM.DNO = DE.DNO)

--5-1 ) 오라클 조인 : 아우터 조인
SELECT EM.ENAME, MA.ENAME
FROM EMPLOYEE EM, EMPLOYEE MA
WHERE EM.MANAGER = MA.MANAGER(+);

--5-2 ) SQL-99 표준표기법

SELECT EM.ENAME, MA.ENAME
FROM EMPLOYEE EM
LEFT OUTER JOIN EMPLOYEE MA
WHERE EM.MANAGER = MA.MANAGER;