GROUP BY, HAVING

Q. 각 직급별 총사원수, 보너스를 받는 사람수, 급여합, 평균급여, 최저급여, 최고급여 (정렬 = 직급 오름차순)

SELECT
    job_code           AS "직급",
    COUNT(*)           AS "사원수",
    COUNT(bonus)       AS "보너스 받는 사람",
    SUM(salary)        AS "급여합",
    round(AVG(salary)) AS "급여평균",
    MIN(salary)        AS "최저급여",
    MAX(salary)        AS "최고급여"
FROM
    employee
GROUP BY
    job_code
ORDER BY
    job_code;

Q. 부서별 보너스를 받는 사원이 없는 부서의 부서코드

SELECT
    dept_code,
    COUNT(bonus)
FROM
    employee
GROUP BY
    dept_code
HAVING
    COUNT(bonus) = 0;