[MySQL] 27. 연습 문제: GROUP BY/HAVING 절 활용하기

[MySQL] 27. 연습 문제: GROUP BY/HAVING 절 활용하기

1. 문제

2. 샘플 데이터

3. 문제 풀이

---

 

1. 문제

-- 문제 1.
-- 각 부서별 평균 급여 계산하기 (소수점 제거)

-- 문제 2.
-- 평균 급여가 50000000 금액 이상인 부서 찾기

-- 문제 3.
-- 각 부서에서 가장 높은 급여를 받는 직원의 급여 출력하기

-- 문제 4.
-- 특정 부서에서 근무하는 직원 수가 3명 이상인 부서만 출력해주세요.

-- 문제 5.
-- 각 부서별 평균 급여와 직원 수를 출력해주세요.

 

 

---

 

2. 샘플 데이터

CREATE TABLE tb_employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO tb_employees (name, department, salary) VALUES
('John Doe', 'Sales', 48000000),
('Jane Smith', 'Sales', 55000000),
('Alice Johnson', 'Marketing', 50000000),
('Chris Lee', 'Marketing', 45000000),
('Bob Brown', 'HR', 35000000),
('Patricia Pink', 'HR', 40000000),
('Michael White', 'Engineering', 75000000),
('Anna Black', 'Engineering', 65000000),
('Linda Green', 'Engineering', 72000000),
('James Red', 'Engineering', 68000000),
('Larry Blue', 'Sales', 52000000),
('Jessica Purple', 'Sales', 51000000),
('Amber Yelow', 'Marketing', 47000000),
('Peter Orange', 'HR', 43000000),
('Lisa Teal', 'HR', 39000000);

 

 

---

 

3. 문제 풀이

 

(1) 각 부서별 평균 급여 계산하기 (소수점 제거)

 

*round(): 괄호 안의 수에서 소수점을 제거해줌.

-- 문제 1.
-- 각 부서별 평균 급여 계산하기 (소수점 제거)
select department,round(avg(salary))
from tb_employees
group by department;

 

 

(2) 평균 급여가 50,000,000 이하인 부서를 찾아보기

 

*AVG(): 괄호 안의 레코드의 평균을 구해줌

-- 문제 2.
-- 평균 급여가 50000000 금액 이상인 부서 찾기
select department,avg(salary)
from tb_employees
group by department
having avg(salary)>50000000;

 

 

(3) 각 부서에서 가장 높은 급여를 받는 직원의 급여 출력하기

 

*min()/max(): 괄호 안 컬럼의 최소/최대값을 찾아줌

-- 문제 3.
-- 각 부서에서 가장 높은 급여를 받는 직원의 급여 출력하기
select department, max(salary),min(salary)
from tb_employees
group by department;

 

(4) 특정 부서에서 근무하는 직원 수가 3명 이상인 부서만 출력하기

 

*count(): 괄호 안의 컬럼에 해당하는 수를 출력(=갯수 출력)

-- 문제 4.
-- 특정 부서에서 근무하는 직원 수가 3명 이상인 부서만 출력해주세요.
select department, count(department)
from tb_employees
group by department
having count(department)>=3;

 

(5) 각 부서별 평균 급여와 직원 수를 출력해주세요.

-- 문제 5.
-- 각 부서별 평균 급여와 직원 수를 출력해주세요.
select department, round(avg(salary)),count(department)
from tb_employees
group by department;

---

*전체 코드

use mysb3;

CREATE TABLE tb_employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO tb_employees (name, department, salary) VALUES
('John Doe', 'Sales', 48000000),
('Jane Smith', 'Sales', 55000000),
('Alice Johnson', 'Marketing', 50000000),
('Chris Lee', 'Marketing', 45000000),
('Bob Brown', 'HR', 35000000),
('Patricia Pink', 'HR', 40000000),
('Michael White', 'Engineering', 75000000),
('Anna Black', 'Engineering', 65000000),
('Linda Green', 'Engineering', 72000000),
('James Red', 'Engineering', 68000000),
('Larry Blue', 'Sales', 52000000),
('Jessica Purple', 'Sales', 51000000),
('Amber Yelow', 'Marketing', 47000000),
('Peter Orange', 'HR', 43000000),
('Lisa Teal', 'HR', 39000000);

-- 문제 1.
-- 각 부서별 평균 급여 계산하기 (소수점 제거)
select department,round(avg(salary))
from tb_employees
group by department;

-- 문제 2.
-- 평균 급여가 50000000 금액 이상인 부서 찾기
select department,avg(salary)
from tb_employees
group by department
having avg(salary)>50000000;

-- 문제 3.
-- 각 부서에서 가장 높은 급여를 받는 직원의 급여 출력하기
select department, max(salary),min(salary)
from tb_employees
group by department;

-- 문제 4.
-- 특정 부서에서 근무하는 직원 수가 3명 이상인 부서만 출력해주세요.
select department, count(department)
from tb_employees
group by department
having count(department)>=3;

-- 문제 5.
-- 각 부서별 평균 급여와 직원 수를 출력해주세요.
select department, round(avg(salary)),count(department)
from tb_employees
group by department;