[SQL] 그룹 함수, 조인

그룹함수

-- 05_Group_Func.sql
-- 그룹 함수 : 데이터 전체 집계 함수들(총액, 평균, 최고, 최저액, 총개수)
-- 결과 특징) 1건이 나옴
-- 사용법)    SUM(컬럼명), AVG(컬럼명), MAX(컬럼명), MIN(컬럼명), COUNT(컬럼명)
-- 예제 1) 사원들의 급여(SALARY) 총액, 평균액, 최고액, 최소액 출력하기
SELECT SUM(SALARY) AS 총액
      ,ROUND(AVG(SALARY)) AS 평균
      ,MAX(SALARY) AS 최고액
      ,MIN(SALARY) AS 최저액
FROM EMPLOYEE;

-- 예제1) 최근에 입사한(HIREDATE) 사원과(EMPLOYEE) 가장 오래전에 입사한 사원의 입사일 출력하기
-- 힌트) 위의 집계함수들중 2개 사용해야함
-- 최근 입사일 : MAX(입사일)
-- 오래전 입사일 : MIN(입사일)
SELECT MAX(HIREDATE) AS 최근
      ,MIN(HIREDATE) AS 오래전
FROM EMPLOYEE;

-- 예제2) 사원들의(EMPLOYEE) 상여금(COMMISSION) 총액 출력하기
-- 참고) COMMISSISON(상여금) 컬럼에는 NULL 있음 : 하지만 집계함수는 NULL 자동으로 제외하고 계산함
SELECT SUM(COMMISSION)
FROM EMPLOYEE;

-- 예제3) 사원들의(EMPLOYEE) 총인원을 출력하세요
-- 사용법) COUNT(컬럼명[*])
SELECT COUNT(*) AS 사원수
FROM EMPLOYEE;

-- 예제4) 상여금을(COMMISSION) 받는 사원의 수 계산하기
-- COMMISSION 컬럼 NULL 있음(상여금 못받음)
-- 집계함수는 자동으로 NULL 제외하고 계산
SELECT COUNT(COMMISSION) AS 사원수
FROM EMPLOYEE;

-- 예제5) NOT NULL 인 데이터만 계산하기
SELECT COUNT(COMMISSION) AS 사원수
FROM EMPLOYEE
WHERE COMMISSION IS NOT NULL;

-- 예제6) 직위의(JOB) 종류가 몇개인지 출력하기
-- 사원 : EMPLOYEE
-- DISTINCT : 중복제거 키워드
-- 사용법) 중복제거 COUNT 함수 : COUNT(DISTINCT 컬럼명)
-- 컬럼별칭(*)   : 컬럼명 AS 별칭
-- 테이블별칭(*) : 테이블명 별칭 (사용: 별칭.컬럼명)
SELECT COUNT(DISTINCT EMP.JOB) AS 직업개수
FROM EMPLOYEE EMP;

-- 전체 집계 함수 특징 : 1) 1건이 결과로 나옴
--                     2) 일반 컬럼과 같이 사용할 수 없음 : 에러 발생
SELECT ENAME, MAX(SALARY)
FROM EMPLOYEE; -- 에러 (일반컬럼(ENAME), MAX(SALARY) 같이 사용 못함)

-- 2) 부분(컬럼별) 집계 하기 : GROUP BY (예약어)
-- 사용법) GROUP BY 컬럼명,컬럼명2...
-- 예제 7) 소속 부서별(DNO) 평균 급여를(SALARY) 부서번호와 함께 출력하세요
-- TRUNC() : 버림(==절삭)
SELECT DNO
     ,TRUNC(AVG(SALARY)) AS 평균급여
FROM EMPLOYEE
GROUP BY DNO; -- 부서번호 컬럼별 집계(부서번호 컬럼은 출력할 수 있음)

-- 주의점) 1) 그룹함수와 일반컬럼은 같이 사용할 수 없음.
--           (단, 컬럼별 집계에서 그 컬럼은 그룹함수와 같이 사용할 수 있음)
SELECT DNO, ENAME
     ,TRUNC(AVG(SALARY)) AS 평균급여
FROM EMPLOYEE
GROUP BY DNO; -- 에러 : GROUP BY 대상 컬럼이 아니므로(ENAME) 그룹함수와 같이 출력할 수 없음

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

-- GROUP BY 사용시 조건을 추가 : HAVING 키워드 (그룹함수에만 사용함)
-- 사용법) HAVING 그룹함수(컬럼명) > 값
-- 예제9) 부서번호별(DNO) 최고급여가(SALARY) 3000 이상인 부서의 번호(DNO)와
--       최고급여금액 구하기
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) 직급별(JOB) 총액 구하기
--       2) MANAGER 인 값 제외(조건1) : WHERE
--       3) 급여(SALARY) 총액(SUM)이 5000 이상이 사원(조건2) : HAVING

SELECT JOB, SUM(SALARY)
FROM EMPLOYEE
GROUP BY  JOB
HAVING JOB != 'MANAGER' AND SUM(SALARY) >= 5000;

--성능 더 빠름
SELECT JOB, SUM(SALARY)
FROM EMPLOYEE
WHERE JOB NOT LIKE '%MANAGER%'
GROUP BY JOB
HAVING SUM(SALARY) >= 5000;

 

연습문제

-- 05_Group_Func_Exam.sql
-- 연습문제
-- 연습문제 1) 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하세요.
--  컬럼의 별칭은 : 최고액, 최저액, 총액, 평균급여
-- 단 실수가 나오면 반올림 해주세요(ROUND)
-- 사원 : EMPLOYEE

SELECT MAX(SALARY) AS 최고액,
       MIN(SALARY) AS 최저액,
       SUM(SALARY) AS 총액,
       ROUND(SUM(SALARY)) AS 평균급여
FROM EMPLOYEE;

-- 연습문제 2) 각 담당 업무(직위)(JOB) 유형별로 급여(SALARY) 최고액, 최저액, 
--             총액 및 평균액을 출력하세요.
--  컬럼의 별칭은 : 최고액, 최저액, 총액, 평균급여
-- 단 실수가 나오면 반올림 해주세요(ROUND)
-- 사원 : EMPLOYEE

SELECT JOB,
       MAX(SALARY) AS 최고액,
       MIN(SALARY) AS 최저액,
       SUM(SALARY) AS 총액,
       ROUND(SUM(SALARY)) AS 평균급여
FROM EMPLOYEE
GROUP BY JOB;

-- 연습문제 3) 담당업무별로(JOB) count(*) 함수를 이용하여  
--            사원 수를 출력하세요.
-- 사원 : EMPLOYEE

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

-- 연습문제 4) 관리자(MANAGER) 수를 출력하세요 
-- (MANAGER 컬럼의 데이터 개수를 나열하세요.)
-- 사원 : EMPLOYEE

SELECT COUNT(MANAGER)
FROM EMPLOYEE;

-- 연습문제 5) 급여(SALARY) 최고액, 급여 최저액의 차액을 출력하세요.
-- 컬럼의 별칭은 "차액"으로 표기하세요
-- 사원 : EMPLOYEE

SELECT MAX(SALARY), MIN(SALARY),
MAX(SALARY) - MIN(SALARY) AS 차액
FROM EMPLOYEE;

-- 연습문제 6) 직급별(JOB) 사원의 최저 급여를(SALARY) 출력하세요, 
-- 단, 관리자를(MANAGER) 알 수 없는 사원 및 최저 급여가 2000 미만인 그룹은 제외시키고,
-- 급여에 대한 내림차순으로 정렬해서 출력하세요

SELECT JOB, MIN(SALARY)
FROM EMPLOYEE
WHERE MANAGER IS NOT NULL
GROUP BY JOB
HAVING MIN(SALARY) >= 2000
ORDER BY MIN(SALARY) DESC;

-- 연습문제 7) 각 부서에 대해(부서번호별로)(DNO) 부서번호(DNO), 사원수(COUNT), 
--       부서 내의 모든 사원의 평균 급여를(ROUND(AVG(),2)) 출력하세요
--      (부서별 사원수, 평균급여 출력)
-- 컬럼별칭을 사용해서 부서번호, 사원수,  평균급여를 출력하세요.
-- 평균급여는 소수점 2째자리에서 반올림하세요
SELECT DNO AS 부서번호
      ,COUNT(*) AS 사원수
      ,ROUND(AVG(SALARY),2) AS 평균급여
FROM EMPLOYEE
GROUP BY DNO;

-- 연습문제 8 사원테이블을(EMPLOYEE) 이용하여 
-- 부서번호(DNO), 평균급여(AVG_SAL), 최고급여(MAX_SAL), 최저급여(MIN_SAL), 사원수를(CNT) 출력하세요
-- 단 평균 급여를 출력할 때 소수점을 제외하고 각 부서번호별로 출력하세요
SELECT DNO AS 부서번호
      ,TRUNC(AVG(SALARY)) AS AVG_SAL
      ,MAX(SALARY) AS MAX_SAL
      ,MIN(SALARY) AS MIN_SAL
      ,COUNT(*) AS CNT
FROM EMPLOYEE
GROUP BY DNO;

-- 연습문제 9 같은 직책에(JOB) 종사하는 사원이 3명 이상인 직책과 인원수를 출력하세요
-- 문제해석) 직위별(JOB) 사원수를 구하고, 그 사원수가 3명이상인 사람만 출력하세요
SELECT JOB
      ,COUNT(*)
FROM EMPLOYEE
GROUP BY JOB
HAVING COUNT(*) >= 3;

-- 연습문제 10 사원들의(EMPLOYEE) 입사 연도를(HIREDATE) 기준으로(입사연도별로) 
-- 부서별로(DNO) 몇 명이 입사했는지 출력하세요
-- 힌트) 입사년도 : TO_CHAR(HIREDATE, 'YYYY')

SELECT TO_CHAR(HIREDATE, 'YYYY'), DNO
      ,COUNT(*) AS CNT
FROM EMPLOYEE
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DNO;

 

조인

-- 06_Join.sql
-- 조인(*****)
-- 예제1) 사원번호가(ENO) 7788 인 사원의 부서명은(DNAME) 뭘까요?
-- 사원 : EMPLOYEE
-- 부서 : DEPARTMENT
-- 1) 사원테이블에서 ENO=7788 인 사람의 부서번호(DNO) 를 알아낸뒤 (1번)
SELECT DNO FROM EMPLOYEE
WHERE ENO = 7788; -- DNO = 20

-- 2) 부서테이블에서 그 부서번호에 해당하는 부서명을 출력하면됨     (2번)
SELECT * FROM DEPARTMENT
WHERE DNO = 20;
-- 결과 : 조회 성능이 저하됨( SQL 문 1번 실행하는 것이 가장 좋음 )
--       1) 코딩(SQL) : 해석(시간 소요)
--       2) 네트웍을 통해서 : 접속툴 <-> DB서버간 통신(시간 소요)

-- 2) 조인을 사용함 : 위의 2번조회를 1번 조회로 수정할 수 있음(성능 증가)
-- 특징) 테이블 여러개를 공통컬럼을 이용해서 연결할 수 있음( 권장 : 4개 이내 )
-- 예제1) 사원번호가(ENO) 7788 인 사원의 부서명은(DNAME) 뭘까요?
-- 사용법) SELECT 별칭1.컬럼명, 별칭2.컬럼명
--        FROM 테이블1 별칭1
--            ,테이블2 별칭2
--        WHERE 별칭1.공통컬럼 = 별칭2.공통컬럼
SELECT EMP.*, DEP.*
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO; -- 공통컬럼으로 연결함 (2개의 테이블)

-- 조인 의미
-- 사원 테이블 
SELECT * FROM EMPLOYEE
ORDER BY DNO;

-- 부서 테이블
SELECT * FROM DEPARTMENT
ORDER BY DNO;

-- 예제 1) 사원번호(ENO) 가 7499 또는 7900 인 사원들에 소속된 부서정보를 모두 출력하세요
-- 부서정보 : 부서번호, 부서명, 부서위치
-- 힌트) 조인 및 조건 사용
-- 힌트) 조인조건) EMP.DNO = DEP.DNO
--      추가조건) EMP.ENO IN (7499, 7900)
SELECT EMP.*, DEP.*
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
AND   EMP.ENO IN (7499, 7900);

-- 연습 1) 조인하고 아래 조건을 추가하세요
--     1-1) 사원번호가(ENO) 7500 ~ 7700 사이에 있는 사원들의 소속된 부서정보를 출력하되
--      조건(범위) : BETWEEN A AND B
--     1-2) 부서이름이 SALES 인 부서만 출력하세요
-- 대상 : EMPLOYEE(사원), DEPARTMENT(부서)
SELECT EMP.*, DEP.*
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO -- 공통컬럼 연결(이퀄(equal(=))조인
AND   EMP.ENO BETWEEN 7500 AND 7700
AND   DEP.DNAME = 'SALES';

-- 특수한 조인
-- 1) 범위 조인 : BETWEEN A AND B ( A ~ B 사이의 값 )
-- 단점 : 성능 대폭 하락
-- 급여등급 테이블 : SALGRADE
-- 가장 낮은 등급  : LOSAL 컬럼
-- 가장 높은 등급  : HISAL 컬럼
SELECT ENAME, SALARY, GRADE
FROM  EMPLOYEE EMP
     ,SALGRADE SAL
WHERE EMP.SALARY BETWEEN SAL.LOSAL AND SAL.HISAL;

-- 2) 아우터 조인(OUTER JOIN)(**) : 
-- 이퀄조인(=) : 두 테이블에 공통컬럼에 NULL 값이 있으면 NULL 값을 제외하여 연결됨
-- MANAGER 컬럼 : 관리자 사원번호
-- 용도 : NULL 을 포함한 데이터도 화면에 표시하고 싶을 때 사용함
-- 사용법 : = 조인을 하되 NULL 값이 있는 쪽에 (+) 붙이면 됨
SELECT EMP.ENAME
      ,MAN.ENAME AS MANAGER
FROM EMPLOYEE EMP
    ,EMPLOYEE MAN
WHERE EMP.MANAGER = MAN.MANAGER(+);

 

조인 연습문제

-- 06_Join_Exam.sql
-- 조인 연습문제
-- 1) = 조인을 이용해서 SCOTT 사원의(ENAME)(조건) 부서번호와(DNO) 
--    부서이름을(DNAME) 출력하시오.
-- 대상 : EMPLOYEE(사원) EMP, DEPARTMENT(부서) DEP
-- 공통컬럼 : EMP.DNO = DEP.DNO
-- 사용법) SELECT 컬럼명, ...
--        FROM EMPLOYEE EMP, DEPARTMENT DEP
--        WHERE EMP.DNO = DEP.DNO
SELECT EMP.DNO, DEP.DNAME
FROM EMPLOYEE EMP, DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
AND EMP.ENAME = 'SCOTT';

-- 2) 모든 사원의 사원이름과(ENAME) 그 사원이 소속된 부서이름(DNAME)과 
--   지역명(LOC)을 출력하시오
-- 사원테이블 : EMPLOYEE
-- 부서테이블 : DEPARTMENT 
-- 조인 : 공통컬럼 : 1) 이름이 똑같은 컬럼 + 자료형도 똑같은 컬럼
-- 부서:DNO(부서번호) , 사원:DNO(부서번호)
SELECT EMP.ENAME, DEP.DNAME, DEP.LOC
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO; -- (공통컬럼 = 연결)

-- 3) 10번 부서에(DNO) 속하는(조건) 사원(번호)에(ENO)(사원) 대해 직급과(JOB)(사원) 
--      지역명(LOC)(부서)을 출력하시오. 
-- 사원테이블 : EMPLOYEE
-- 부서테이블 : DEPARTMENT
-- 공통컬럼 : DNO(부서번호)
SELECT EMP.ENAME ,EMP.JOB, DEP.LOC
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
AND   EMP.DNO = 10; -- 조건

-- 4) 커미션을(COMMISSION) 받는 모든사원의 이름(ENAME), 
--      부서이름(DNAME), 지역명(LOC)을 출력하시오.

SELECT EMP.ENAME, DEP.DNAME,LOC
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
AND COMMISSION IS NOT NULL;

-- 5) = 조인과 Like 검색(와일드카드(%))를 사용하여 
--     이름에 A가 포함된 모든 사원의 이름과(ENAME)
--    부서명을(DNAME) 출력하시오.

SELECT EMP.ENAME, DEP.DNAME
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
AND EMP.ENAME LIKE '%A%';

-- 6) NEW YORK 에 근무하는(LOC) 모든 사원의 이름(ENAME), 
--     업무(JOB), 부서번호(DNO) 
--     및 부서명을(DNAME) 출력하시오.
-- 사원테이블 : EMPLOYEE
-- 부서테이블 : DEPARTMENT

SELECT EMP.ENAME, EMP.JOB, EMP.DNO
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
AND DEP.LOC = 'NEW YORK';

-- 고급 응용 문제
-- 7) 각 부서에 대해 부서번호(DNO)별, 부서명(DNAME)별, 위치(LOC)별로
--   사원수(COUNT), 부서 내의 모든 사원의 평균 급여(AVG)를 출력하세요
--   컬럼별칭을 사용해서 부서번호, 부서명, 위치, 사원수 출력하세요
-- 평균급여는 소수점 2째자리에서 반올림하세요.
-- 사원테이블 : EMPLOYEE
-- 부서테이블 : DEPARTMENT
-- 힌트 : 그룹함수 사용, GROUP BY 사용

SELECT EMP.DNO AS 부서번호
     , DEP.DNAME AS 부서명
     , DEP.LOC AS 위치
     , COUNT(*) AS 사원수
     , ROUND(AVG(SALARY),1) AS 평균급여
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
GROUP BY EMP.DNO, DEP.DNAME, DEP.LOC;

-- 8) 각 부서명(DNAME)별, 급여별(SALARY) 사원수(COUNT)를 출력하세요
--   단, 부서명, 급여, 사원수의 별칭을 써서 출력하세요
-- 사원테이블 : EMPLOYEE
-- 부서테이블 : DEPARTMENT

SELECT DNAME AS 부서명
     , SALARY AS 급여
     , COUNT(*) AS 사원수
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
GROUP BY DEP.DNAME, EMP.SALARY;

-- 9) 각 부서명(DNAME)별, 담당업무별(JOB) 
--   급여(SALARY) 총액에서 5000 이상인 결과만 출력하세요
-- 사원테이블 : EMPLOYEE
-- 부서테이블 : DEPARTMENT
-- 힌트) HAVING 사용

SELECT EMP.JOB, DEP.DNAME, SUM(EMP.SALARY)
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
GROUP BY DEP.DNAME, EMP.JOB
HAVING SUM(EMP.SALARY) >= 5000;


-- 10) 10 급여가(SALARY) 2000 초과인 사원들의 
--  부서번호(DNO), 부서명(DNAME), 사원번호(ENO), 사원명(ENAME), 급여를 출력하세요

SELECT DEP.DNO, DEP.DNAME, EMP.ENO, EMP.ENAME, EMP.SALARY
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
AND EMP.SALARY > 2000;

-- 11) 각 부서별(DNO) 평균 급여(SALARY), 최대급여, 최소급여, 사원수를 출력해 보세요

SELECT EMP.DNO, TRUNC(AVG(EMP.SALARY)) AS 평균급여, MAX(EMP.SALARY) AS 최대급여, MIN(EMP.SALARY) AS 최소급여, COUNT(*) AS 사원수
FROM EMPLOYEE EMP
    ,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
GROUP BY EMP.DNO;

-- 12) 부서번호로 right outer join 하여 모든 부서 정보와 사원정보를 출력하세요
-- 단 부서번호, 사원명으로 오름차순 정렬하세요 

-- 오라클 조인
SELECT EMP.*, DEP.*
FROM EMPLOYEE EMP
   ,DEPARTMENT DEP
WHERE EMP.DNO(+) = DEP.DNO
ORDER BY EMP.DNO, EMP.ENAME;

SELECT EMP.*, DEP.*
FROM EMPLOYEE EMP RIGHT OUTER JOIN DEPARTMENT DEP ON(EMP.DNO = DEP.DNO)
ORDER BY DEP.DNO ASC, EMP.ENAME ASC;