SQL 문법 다지(10)

employees 테이블

id	department_id	name
1	101	르탄이
2	102	배캠이
3	103	구구이
4	101	이션이

 

departments 테이블

id	name
101	인사팀
102	마케팅팀
103	기술팀

 

위와 같은 테이블이 주어졌을 때, 다음 문제들을 풀어보세요.

문제

1. 현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!
2. 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!
3. '기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!
4. 부서별로 직원 수를 계산하는 쿼리를 작성해주세요!
5. 직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!
6. '마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

문제 풀이

데이터베이스와 테이블 생성하기

첫 번째 테이블

CREATE DATABASE step10;
USE step10;
CREATE TABLE employees(
    id INT PRIMARY KEY
    , department_id INT
    , name VARCHAR(50)
);
INSERT INTO employees 
(id, department_id, name) 
VALUES
(1, 101, '르탄이')
, (2, 102, '배캠이')
, (3, 103, '구구이')
, (4, 101, '이션이')
;

두 번째 테이블

CREATE TABLE departments(
    id INT PRIMARY KEY
    , name VARCHAR(50)
);
INSERT INTO departments 
(id, name) 
VALUES
(101, '인사팀')
, (102, '마케팅팀')
, (103, '기술팀')
;

1번

현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!

 

 

정답 :

SELECT DISTINCT
    COUNT(name) "총 부서 수"
FROM departments
;

 

 

정답 확인 :

2번

모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!

 

서로 다른 테이블을 조회하려면 LEFT JOIN을 이용한다. 직원 테이블을 기준으로 합쳐주도록 하겠다.

 

 

정답 :

SELECT e.id
    , e. department_id
    , e.name
    , d.name as department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id
;

 

 

정답 확인 :

3번

'기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

 

 

LEFT JOIN으로 합친 뒤 WHERE 절로 필터링해준다. 직원 테이블을 기준으로 합쳐주도록 하겠다.

 

 

정답 :

SELECT e.id
    , e. department_id
    , e.name
    , d.name as department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id
WHERE d.name = '기술팀'
;

 

 

정답 확인 :

4번

부서별로 직원 수를 계산하는 쿼리를 작성해주세요!

 

 

위 문제들과 마찬가지로 LEFT JOIN으로 합친 뒤, COUNT()를 계산하고 GROUP BY를 이용해 부서별 직원 수를 구해준다.

 

 

정답 :

SELECT
    emp_dpt.department_name
    , COUNT(*) AS "부서별 직원 수"
FROM
(
SELECT e.id
    , e. department_id
    , e.name
    , d.name as department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id
) emp_dpt
GROUP BY emp_dpt.department_name
;

 

 

정답 확인 :

5번

직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!

 

 

부서를 기준으로 LEFT JOIN을 해주고 직원 이름이 NULL인 행을 찾아본다.

 

 

정답:

SELECT d.name as department_name
FROM departments d
LEFT JOIN employees e
ON d.id = e.department_id
WHERE e.id IS NULL
;

 

 

정답 확인:

6번

'마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

 

 

LEFT JOIN한 테이블에서 부서명(department_name)이 마케팅팀인 직원(name)의 이름 출력한다.

 

 

정답:

SELECT emp_dpt.name AS "마케팅팀 직원"
FROM
(
SELECT e.*
    , d.name as department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id
) emp_dpt
WHERE emp_dpt.department_name = '마케팅팀'

 

 

정답 확인: