//그룹함수
COUNT 행의개수
SUM 열의 합
AVG 열의평균
MIN 열의최소
MAX열의 최대
//최근에 입사한사원, 가장 오래전에 입사한사원의 입사일출력
SELECT MAX(hiredate), MIN(hiredate) FROM employee;
//커미션총액
SELECT SUM(commission) as "커미션총액" FROM employee;
//전체사원의수
SELECT COUNT(*) AS "사원의수" FROM employee;
//커미션받는 사원의수 구하기 //널값은 빠짐
SELECT COUNT(commission) as "커미션받는사원의수" FROM employee;
//직업종류의 개수 출력
SELECT COUNT(DISTINCT job) as "직업종류의 개수" FROM employee;
//ORDER BY 컬럼기준으로 정렬, GROUP BY //디폴트셋팅은 내림차순
SELECT dno AS "부서번호", AVG(salary) AS "급여평균" FROM employee GROUP BY dno;
SELECT dno, job, COUNT(*), SUM(salary) FROM employee GROUP BY dno, job;
//부서별 급여총액이 3000이상인 부서의 번호와 부서별 급여총액 구하기
SELECT dno, MAX(salary) FROM employee GROUP BY dno HAVING MAX(salary) >= 3000;
//manager를 제외하고 급여총액이 5000이상인 직급별 총액 구하기 //명령어순서중요
SELECT job, COUNT(*), SUM(salary)
FROM employee
WHERE job NOT LIKE '%MANAGER%' //매니저를 제외하고.....
GROUP BY job
HAVING SUM(salary)>=5000
ORDER BY SUM(salary);
//부서별 평균 최고급여 구하기 //함수안에 함수 //
SELECT MAX(AVG(salary)) FROM employee GROUP BY dno;