인공재환

오늘은 50문제정도 풀어보자. 정답률 가장 높은 순대로!!

작성 순서때문에 틀린 것도 많아서 정리해놔야겠다ㅠ

문제 풀면서 처음 보는 개념/문법들 정리

1. LIMIT -> df.head()와 같음!

2. LIKE -> 문자열 부분일치 (WHERE뒤에 붙으면서, % / _ 등 기호와 함께사용)

3. 결측치 처리 ->  COALESCE(column, 'NA') , INFULL(column, 'NA') NULL이라면 'NA'로 채움 -> IFNULL도 가능!

4. LEFT(XXX, 2) -> 문자열 왼쪽에서 2글자 추출

5. DATE_FORMAT(DATE,%Y,-%m-%d)

6. 중복 제거 : DISTINCT ------------> 집계 함수 쓴다면 COUNT(DISTINCT NAME) 요렇게 가능

7. DATEDIFF(A.DATE, B.DATE)

서브쿼리

SELECT * FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

오늘푼 문제 인증

40문제?? 푼것같다.. 50문제는 너무 많아...

질답은 아래와 같다.

Q: 자 CTE 들어가기전, 나는 정말 자주 나오고, 리트코드 + 회사 입사 코딩테스트 (SQL 데이터분석)에 맞게 문제를 풀어보고 싶어. 도와줄래?

A:

문제 1: 부서별 평균보다 높은 연봉 가진 직원 찾기  (난이도:⭐)

각 부서에서, 해당 부서의 평균 연봉보다 높은 연봉을 받는 직원의 이름과 연봉을 출력하라.

• 테이블: employees(department, name, salary)

# 각 부서에서, 해당 부서의 평균 연봉보다 높은 연봉을 받는 직원의 이름과 연봉을 출력하라.
# 테이블: employees(department, name, salary)

SELECT name,salary,department FROM employees e
WHERE salary > 
	(SELECT AVG(salary)
    FROM employees
	WHERE department = e.department);


# 핵심 -> 부서 평균 연봉을 구하고, 그걸 직원 연봉과 비교해야함
# (상관)서브쿼리 써야함

🔥 문제 2: 매니저가 3명 이상 직원 관리하는 경우만 출력 (난이도:⭐⭐)

직원 수가 3명 이상인 매니저의 이름과 직원 수를 출력하라.

• 테이블: employees(employeeId, name, managerId)

# 문제 2: 매니저가 3명 이상 직원 관리하는 경우만 출력 (난이도:⭐⭐)
# 직원 수가 3명 이상인 매니저의 이름과 직원 수를 출력하라.
# 테이블: employees(employeeId, name, managerId)

SELECT m.name AS manger_name, COUNT(e.employeeId) AS ct
FROM employees e
INNER JOIN employees m
		ON e.managerId = m.employeeId
GROUP BY m.name
HAVING COUNT(e.employeeId) >= 3;

# 1.JOIN + CASE WHEN 해서 직원 담당 매니저 변수만들기
# 2.COUNT활용해서 COUNT > 3인 매니저 이름과 직원 수 출력하기 -> GROUPBY + HAVING으로!

처음엔 CASE WHEN을 썼더니 반응이,..

그냥 욕을 해.

이걸 알아야 풀 수 있다고.!

🔥 문제 3: 이전 연도보다 연봉이 오른 직원만 출력 (난이도:⭐⭐⭐)

같은 직원이 여러 해에 걸쳐 기록된 salaries 테이블에서,
이전 연도보다 연봉이 증가한 경우만 employeeId, year, salary를 출력하라.

• 테이블: salaries(employeeId, year, salary)

(Hint: self join, 또는 나중에 윈도우 함수로 리팩터링 가능)

🔥 문제 4: 부서별 최고 연봉자

(난이도:⭐⭐)

각 부서마다 최고 연봉을 받는 직원의 이름과 연봉을 출력하라.

• 테이블: employees(department, name, salary)

🔥 문제 5: 상사보다 연봉 높은 직원 찾기

(난이도:⭐⭐⭐)

자신보다 연봉이 낮은 상사를 둔 직원을 출력하라.

• 테이블: employees(employeeId, name, salary, managerId)

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

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

 오늘은 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일차는 여기까지...

G교수 특급제자 먼데이 조교수에게 SQL을 배워보자

오늘은 JOIN / GROUPBY / HAVING 개념 + 예제 + 실전으로 가봅시다잉

JOIN - 테이블 연결하기

pandas -> concat / merge와 같음 (어떤 기준값(공통된 column)을 중심으로 하겠다는 느낌이겠지?

그걸 ON이라고함. ON = 어떤 키값을 기준으로 연결하기!

GROUPBY - 집계함수

pandas와 같으려나?

ex) 직원 테이블에서 직급별 평균 월급 보여줘!

SELECT position, AVG(salary) FROM employee

GROUPBY position;

WHERE - 행 필터링 (데이터를 가져오기 전에 필터링함. 즉, GROUP BY 전에 적용됨)

GROUPBY- 집계함수 (행들을 그룹으로 묶는 애”, 동일한 값을 기준으로 행들을 묶어서 통계용으로 사용함.)

HAVING   - “GROUP BY로 묶은 결과에 조건 거는 애” WHERE은 GROUP BY 이전, HAVING은 그 이후!

"연봉 5만 이상인 사람들만 보여줘"
SELECT name,salary FROM employee
WHERE salary > 50000


"부서별 평균 연봉 구해줘"
SELECT AVG(salary),department FROM employee
GROUPBY department;

"부서별 평균 연봉이 6만 넘는 부서만 보여줘"
SELECT AVG(salary),department FROM employee
GROUPBY department
HAVING AVG(salary) > 60000;

#GPT가 내준 문제...

풀어보자

-- 문제: 부서별 평균 월급을 구하되,
-- 개별 직원 중에서 월급이 40000 이하인 직원은 제외하고,
-- 평균이 70000 이상인 부서만 보여줘라.

-- 문제: 부서별 평균 월급을 구하되,
-- 개별 직원 중에서 월급이 40000 이하인 직원은 제외하고,
-- 평균이 70000 이상인 부서만 보여줘라.

SELECT department, AVG(salary) FROM Employee
WHERE salary > 40000
GROUP BY department
HAVING AVG(salary) > 70000;

정답!

다음문제를 줬다..

각 부서별로 월급 40000 넘는 직원 수를 구하고, 그 수가 2명 이상인 부서만 보여줘라”

월급 40000 초과인 직원들만 대상으로, 부서별 평균 연봉과 인원 수 출력. 단, 인원이 2명 이상인 부서만."

SELECT AVG(salary), COUNT(name) FROM Employee
WHERE salary > 40000
GROUP BY department
HAVING COUNT(name) >= 2;

아 맞추니까 계속 내내 이쉨..

SELECT AVG(salary), COUNT(name) FROM Employee
WHERE salary > 40000
GROUP BY department
HAVING COUNT(name) >= 2;

또 맞추니까 어려운 문제를 내보겠단다.

SELECT name, salary, department FROM employee
GROUP BY department
HAVING MAX(salary);

모르겠어ㅠ

ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㄱ창의적이네...

머리 꽁꽁 싸메고 해보자..

"각 부서에서 가장 높은 연봉을 받는 직원 출력"

SELECT department,employeeId, salary FROM employee
GROUP BY department
HAVING MAX(salary);

ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ하지만 김피탕은 맛있는걸요...

배우고 갑니다.. 죄송합니다...

다시 해볼게요.!

"각 부서에서 가장 높은 연봉을 받는 직원 출력"
서브 쿼리 사용

SELECT department, employeeId, name, salary FROM employee e
WHERE salary = (
	SELECT MAX(salary)
    FROM employee
    WHERE department = e.department #이건 바깥 쿼리에서 선택된 직원(e)의 department 값.
                                    #서브쿼리에서는 "이 직원이 속한 부서에서, 최고 연봉이 얼마냐?" 를 구해야 하니까 이게 필요함.
    );

이제 서브쿼리 관련해서 지옥 3문제 드가자~

# 문제 1. 자기 부서 평균보다 더 많이 받는 직원 찾기
# "각 직원 중, 본인이 속한 부서의 평균 연봉보다 더 높은 급여를 받는 사람을 찾아라."


SELECT name,department,salary,employeeId FROM employee e
WHERE salary = (
	SELECT AVG(SALARY)
    FROM employee
    WHERE department = e.department);

문제 1정답

# 문제 2. 각 직원보다 연봉 높은 사람 수 세기
# "모든 직원에 대해, 자기보다 연봉 높은 사람이 몇 명인지를 구해라."

SELECT employeeId,salary FROM employee e
WHERE salary = (
	  SELECT COUNT(employeeId)
      FROM employee
      WHERE salary > e.salary);

문제 2 오답!!!

정답은 아래와 같다.

SELECT employeeId, name, salary,
	(
    SELECT COUNT(*)
	FROM employee e2
    WHERE e2.salary > e1.salary
    ) AS higher_salary_count
	FROM employee e1;

# 문제 3. 자기 부서의 최고 연봉자인 직원 찾기
# "자기 부서에서 가장 높은 급여를 받는 직원을 찾아라."

SELECT name, department, salary FROM employee e
WHERE salary = (
	SELECT MAX(salary) 
    FROM employee
    WHERE department = e.department
    );

문제 3은 정답!

너무 길어지니까 다음으로 가보자