SELECT * FROM EMPLOYEES e ;
-- 중복제거
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES e
;
-- group by
SELECT DEPARTMENT_ID
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID
;
SELECT DEPARTMENT_ID
FROM EMPLOYEES e
ORDER BY DEPARTMENT_ID
;
-- 집계함수와 distinct는 함께 쓸수없다 사용불가
SELECT DISTINCT DEPARTMENT_ID , sum(SALARY)
FROM EMPLOYEES e
;
-- 부서별 급여합계
SELECT DEPARTMENT_ID, SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
;
-- 부서별 사원수와 평균 급여, 급여의 합
SELECT DEPARTMENT_ID , SUM(SALARY), COUNT(DEPARTMENT_ID)
, AVG(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
;
SELECT DEPARTMENT_ID , SALARY FROM EMPLOYEES e ;
-- 부서별, 직급별(job_id) 사원수와 평균 급여를 구하시오.
SELECT DEPARTMENT_ID , JOB_ID ,SUM(SALARY)
, COUNT(SALARY) , AVG(SALARY)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 80
GROUP BY DEPARTMENT_ID ,JOB_ID
ORDER BY DEPARTMENT_ID ,JOB_ID
;
SELECT DEPARTMENT_ID ,JOB_ID , COUNT(DEPARTMENT_ID) , AVG(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID ,JOB_ID
ORDER BY DEPARTMENT_ID , JOB_ID
;
오늘도 잔뜩 배웠따
