GPT 먼데이와 함께하는 SQL 1일차 #2

들어가기전, 먼데이 단장님께서 오프닝 멘트 시작하시겠다. 다들 집중

드가자!!!!!!!!!

 오늘은 Self Join과 CASE WHEN,  EXISTS를 배워보도록 하자.

 

예시

 

SELF JOIN부터 알아보자. JOIN을 알다시피 테이블을 하나로 합치는 것

SELF JOIN이란 자기 자신을 자기에게 붙이는것..! 

ex) customer 테이블의 데이터를 다시 customer 테이블에 붙인다! 

WHY??? 이게 왜 필요한지 물어봤다.

오늘도 창의적으로 포문을 여는 먼선생.!

SELECT 
	e.name AS employee_name, m.name AS manager_name
    FROM employees e
    JOIN employees m
    ON e.mangers_id = m.id;

먼선생의 유머 수준을 60%로 낮춰야겠다.

재밌는 교수는 없다는 소리가 괜히 나오는게 아니다.

---

바로 실전 문제 가보자!

문제 1.

직원과 매니저 이름을 같이 출력하라. (Self Join 구조 익히기)

Employee(employeeId, name, salary, managerId)
→ managerId가 employeeId를 참조하고 있음

# 직원과 매니저 이름을 같이 출력하라. (Self Join 구조 익히기)

# Employee(employeeId, name, salary, managerId)
# → managerId가 employeeId를 참조하고 있음

SELECT e.name, m.name
	FROM employees e
	JOIN employees m
    ON e.employeeId = m.managerId

괴라는 나물..SQL 어렵다..

e.employeeId = m.mangerId가 아니라 e.managerId = m.employeeId! (직원 -> 매니저 찾아야하므로)

문제 2.

각 직원이 자기 매니저보다 연봉이 높으면 ‘Higher’, 낮으면 ‘Lower’라고 표시하라.

컬럼: employee_name, manager_name, 비교결과

# 각 직원이 자기 매니저보다 연봉이 높으면 ‘Higher’, 낮으면 ‘Lower’라고 표시하라.
# 컬럼: employee_name, manager_name, 비교결과

SELECT e.name AS employee_name, m.name AS manager_name, e.salary,
CASE 
	WHEN e.salary > m.salary THEN 'Higher'
    ELSE 'Lower'
    END AS '비교결과'
FROM employees e
JOIN employees m
ON e.managerId= m.employeeId;

 

문제 3.

매니저가 없는 직원은 'No Manager'로 표기하라.

CASE WHEN을 IS NULL 조건과 함께 써봐

참고) 테이블은 아래와 같음.

# 매니저가 없는 직원은 ‘No Manager’로 표시
# employeeId/managerId/name/salary/department

SELECT e.name AS employee_name,
CASE
	WHEN m.name IS NULL THEN 'No Manager'
    ELSE m.name
    END AS "매니저"
FROM employees e
JOIN employees m
ON e.managerId = m.employeeId;

 

문제 4.

직원이 자기 매니저보다 1.1배 이상 높은 연봉이면 ‘Fast Climber’,
동일하면 ‘Peer’, 아니면 ‘Underpaid’ 라고 표시

# 직원이 자기 매니저보다 1.1배 이상 높은 연봉이면 ‘Fast Climber’,
# 동일하면 ‘Peer’, 아니면 ‘Underpaid’ 라고 표시
# columns - 'employeeId/name/salary/managerId'

SELECT name,
CASE 
	WHEN e.salary > ( 1.1 * m.salary) THEN "Fast Climber"
    WHEN e.salary = m.salary THEN "Peer"
    ELSE "Underpaid"
	END AS "연봉 상황"
FROM employees e
INNER JOIN employees m
ON e.managerId = m.employeeId;

궁금한 점

어떻게 e.salary m.salary를 각각 직원 연봉과 매니저 연봉으로 구분할 수 있는가?

-> 먼교수님 말씀... 

요새 GPT는 노트북 카메라로 얼굴 보면서 대답하나보다. 괴물처럼 생겨서 화는 나지 않는다.

결국, INNER JOIN , ON -> 부터 관계가 생기면서 (두 테이블 연결하면서) 인지함.

문제 5.

매니저 이름이 ‘Kim’인 사람만 필터해서, 그들과 연봉 비교 결과 출력하기

예: Kim보다 높으면 'Surpassed Kim', 낮으면 'Still Climbing' 등

# 매니저 이름이 ‘Kim’인 사람만 필터해서, 그들과 연봉 비교 결과 출력하기
# 예: Kim보다 높으면 'Surpassed Kim', 낮으면 'Still Climbing' 등
# table = 'employeeId','name','salary','managerId'

SELECT e.name AS employee_name,
	   e.salary AS employee_salary,
       m.name AS manager_name,
       m.salary AS manager_salary,
CASE
	WHEN e.salary > m.salary THEN "Surpassed Kim"
    ELSE 'Still Climbing'
	END AS "비교 결과"
FROM employees e
INNER JOIN employees m
ON e.managerId = m.employeeId
WHERE m.name = "Kim";

문제 6.

부서가 같은 직원들 중 매니저보다 연봉이 낮은 사람만 출력,
그리고 'Raise Suggested' 라는 컬럼 추가

# 부서가 같은 직원들 중 매니저보다 연봉이 낮은 사람만 출력하고 그 사람들에게 "Raise Suggested"라는 데이터 채우기, column명은 "평과 결과"

SELECT e.name AS employee_name,
	   e.salary AS employee_salary,
       m.name AS manager_name,
       m.salary AS manager_salary,
       CASE
       WHEN e.salary < m.salary THEN "Raise Suggested"
       END AS "평가 결과"
FROM employees e
INNER JOIN employees m
ON e.managerId = m.employeeId
WHERE (e.salary < m.salary) and (e.department = m.department);

문제 7.

각 매니저별로, 자기보다 연봉 높은 직원을 몇 명이나 두고 있는지 출력하라

Self Join + GROUP BY + COUNT + CASE WHEN 조합해야 함

# 각 매니저별로, 자기보다 연봉 높은 직원을 몇 명이나 두고 있는지 출력하라
# Self Join + GROUP BY + COUNT + CASE WHEN 조합해야 함

SELECT m.name AS manager_name,
	COUNT(CASE WHEN m.salary < e.salary THEN 1 END) AS "better than me"
FROM employees e
INNER JOIN employees m
		ON e.managerId = m.employeeId
GROUP BY m.name;

문제 8.

직원이 속한 부서에 ‘김 매니저’가 있으면 ‘Under Kim’,
없으면 ‘Other Manager’ 라고 표시하라.

Self Join + CASE WHEN + 부서 조건 조합

# 직원이 속한 부서에 ‘김 매니저’가 있으면 ‘Under Kim’, 없으면 ‘Other Manager’ 라고 표시하라.
# table = 'employeeId','name','salary','managerId','department'

SELECT department, e.name AS employee_name,
	CASE
    WHEN m.name = "Kim" THEN "Under Kim"
	ELSE "Other Manager"
    END AS "김 매니저 유무"
FROM employees e
INNER JOIN employees m
	    ON e.managerId = m.employeeId
WHERE e.department = m.department

문제 9.

각 직원이 '매니저와 연봉 차이'가 20% 이상이면 Alert,
아니면 Normal, 매니저 없으면 Unknown 출력

# 각 직원이 '매니저와 연봉 차이'가 20% 이상이면 Alert,
# 아니면 Normal, 매니저 없으면 Unknown 출력

SELECT e.name AS employee_name,
	   e.salary AS employee_salary,
       m.name AS manager_name,
	   m.salary AS manager_salary,
CASE
	WHEN e.managerId IS NULL THEN "Unknown"
    WHEN ABS(e.salary - m.salary) > (e.salary) * 0.8  THEN "Alert"
    ELSE "Normal"
    END AS "매니저 연봉 차이"
FROM employees e
LEFT JOIN employees m
		ON e.managerId = m.employeeId;

개빡세다 1일차는 여기까지...