[SQL] 연습 문제 모음1

🤔 갑자기?

회사에서 HR(인사) 시스템을 개발하다 보니 복잡한 쿼리를 자주 사용할 일이 많습니다. 업무에 잘 적응하기 위해 SQL 문제를 풀어보려고 합니다.

 

🔒 문제

employees (직원 정보)
emp_id	name	department	hire_date	salary	termination_date
1	Kim	Sales	2020-03-15	5000000	NULL
2	Lee	IT	2018-07-20	6000000	NULL
3	Park	HR	2019-05-10	4500000	NULL
4	Choi	IT	2021-09-30	5500000	NULL
5	Jung	Sales	2017-12-01	7000000	NULL
6	Yoon	HR	2016-08-23	4000000	NULL
7	Cho	Sales	2022-05-12	4800000	NULL
8	Han	IT	2019-11-01	6200000	NULL
9	Oh	HR	2020-10-20	5100000	NULL
10	Kim	Sales	2018-02-14	5800000	NULL
테이블 예시 설명
emp_id: 직원 ID (기본 키)
name: 직원 이름
department: 소속 부서
hire_date: 입사일
salary: 급여
termination_date: 퇴사일 (퇴사하지 않은 경우 NULL)


문제 1:
부서별로 "퇴사한 직원"의 수를 계산하고, 퇴사한 직원이 1명 이상인 부서만 조회하세요.
퇴사일(termination_date)이 NULL이 아닌 경우를 퇴사한 직원으로 간주합니다.

문제 2:
각 부서에서 '입사일이 가장 빠른 직원'과 '입사일이 가장 늦은 직원'을 조회하세요.
각 부서별로 입사일이 가장 빠르고 늦은 직원의 이름, 입사일을 조회합니다.

문제 3:
직원별로 지난 6개월 동안 받은 급여의 총합을 계산하고, 급여 총합이 3,000,000원 이상인 직원만 조회하세요.

문제 4:
각 부서의 급여 총합과 평균 급여를 계산한 후, 급여 총합이 20,000,000원 이상인 부서만 조회하세요.
각 부서의 급여 총합과 평균 급여를 계산하고, 급여 총합이 20,000,000원 이상인 부서만 조회합니다.

문제 5:
각 직원의 퇴사일로부터 현재까지 근무 기간을 계산하고, 근무 기간이 5년 이상인 직원만 조회하세요.
근무 기간은 퇴사일과 현재 날짜를 기준으로 계산합니다. 퇴사하지 않은 직원은 현재 날짜를 기준으로 계산하세요.

 

🔓 내가 푼 답

1.

SELECT department, COUNT(*) AS terminated_employee_count
FROM employees
WHERE termination_date IS NOT NULL
GROUP BY department;

 

2. .

SELECT e.department, 
       (SELECT name FROM employees WHERE department = e.department ORDER BY hire_date LIMIT 1) AS first_hired_employee,
       (SELECT name FROM employees WHERE department = e.department ORDER BY hire_date DESC LIMIT 1) AS last_hired_employee
FROM employees e
GROUP BY e.department;

 

3.

SELECT name, 
       CASE 
           -- 6개월 이상 근무한 직원
           WHEN hire_date <= CURDATE() - INTERVAL 6 MONTH THEN salary * 6
           -- 6개월 미만 근무한 직원
           ELSE salary * (DATEDIFF(CURDATE(), hire_date) / 30)
       END AS total_salary
FROM employees
WHERE hire_date <= CURDATE()
HAVING total_salary >= 3000000;

 

4.

SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING SUM(salary) >= 20000000;

 

5.

SELECT emp_id,
       CASE 
           WHEN termination_date IS NULL THEN DATEDIFF(NOW(), hire_date)
           ELSE DATEDIFF(termination_date, hire_date)
       END AS work_day
FROM employees
HAVING work_day >= 365 * 5;

✅ 사용 문법 정리

CURDATE() - INTERVAL 6 MONTH

현재 날짜에서 6개월 전

. 기타 INTERVAL 사용 예시.

• INTERVAL 6 MONTH (6개월)                       
• INTERVAL 10 DAY (10일)
• INTERVAL 1 YEAR (1년)
• INTERVAL 5 HOUR (5시간)
• INTERVAL 30 MINUTE (30분)
• CURDATE() + INTERVAL 6 MONTH 6개월 후