CREATE FUNCTION getNthHighestSalary(N INT)
RETURNS INT
BEGIN
RETURN (
SELECT CASE WHEN COUNT(sub.salary) < N THEN NULL
ELSE MIN(sub.salary)
END
FROM(
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT N ) sub
);
END
if 함수
CREATE FUNCTION getNthHighestSalary(N INT)
RETURNS INT
BEGIN
RETURN (
SELECT IF( COUNT(sub.salary) < N , NULL , MIN(sub.salary))
FROM(
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT N ) sub
);
END
- LIMIT OFFSET 풀이
CREATE FUNCTION getNthHighestSalary(N INT)
RETURNS INT
BEGIN
SET N = N-1;
RETURN (
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET N
);
END
SELECT Department
,Employee
,Salary
FROM (
SELECT de.name AS Department
, em.name AS Employee
, em.salary AS Salary
, DENSE_RANK() OVER (PARTITION BY departmentID ORDER BY salary DESC) drank
FROM employee em
INNER JOIN department de ON de.id = em.departmentID) rank
WHERE rank.drank <= 3
SELECT DISTINCT l1.num AS ConsecutiveNums
FROM logs l1
INNER JOIN logs l2 ON L1.id = l2.id - 1
INNER JOIN logs l3 ON l2.id = l3.id - 1
WHERE l1.num = l2.num
AND l1.num = l3.num
(2)
SELECT DISTINCT l1.num AS ConsecutiveNums
FROM logs l1
INNER JOIN logs l2 ON L1.id + 1 = l2.id
INNER JOIN logs l3 ON l1.id + 2 = l3.id
WHERE l1.num = l2.num
AND l1.num = l3.num
- MS SQL 윈도우 함수 풀이
SELECT DISTINCT co.num AS ConsecutiveNums
FROM(
SELECT num
, LEAD(num) OVER (ORDER BY id) AS lead
, LEAD(num, 2) OVER (ORDER BY id) AS lead2
FROM Logs) co
WHERE co.num = co.lead
AND co.num = co.lead2
[2] 자녀의 생년월일이 2012년 1월 1일 ~ 2015년 12월 31일 경우만 대상에 포함
[3] 사내부부이면서 [1],[2] 조건에 만족하는 경우 자녀의 아버지 “직원번호” 기준으로 데이터 추출하고, 어머니는 리스트에서 제거할 것, 이 때 사내부부 여부는 Y로 표시, 배우자성명은 어머니 기준으로 표시
[4] 쌍생아의 경우 2명 모두 지원이 아님, 따라서 쌍생아 중 1명만 포함 시킬 것
[5] 쌍생아 1명을 가져오는 조건은 이름의 오름차순의 상위자로 추출할 것
풀이과정
: WITH문으로 자녀정보를 담은 테이블과, 사내부부 테이블을 따로 만들어
각 테이블을 EMP_C에 조인해서 최종 쿼리를 만드는 방법으로 생각.
CHILD 테이블
- FAM_C 테이블에서 2012년 1월 1일 ~ 2015년 12월 31일 사이에 태어난 자녀(A27) 정보만 담은 테이블
- 쌍생아의 경우 1명만 포함, 1명의 기준은 쌍생아 이름의 오름차순 상위자가 자녀이름에 들어감
SELECT ch.emp_no emp_no
, ch.fam_nm fam_nm
, ch.rel_type_cd
, ch.gender_cd gender_cd
, ch.birth_ymd birth_ymd
, tw.emp_no twin_emp_no
, tw.twin twin_nm
FROM (SELECT *
FROM FAM_C
WHERE rel_type_cd = 'A27'
AND birth_ymd BETWEEN '20120101' AND '20151231')ch
LEFT JOIN
(SELECT FAM1.EMP_NO AS emp_no
, FAM1.FAM_NM AS twin
FROM FAM_C FAM1
INNER JOIN FAM_C FAM2
ON FAM1.EMP_NO = FAM2.EMP_NO
AND FAM1.FAM_NM <> FAM2.FAM_NM
AND FAM1.REL_TYPE_CD = FAM2.REL_TYPE_CD
AND FAM1.REL_TYPE_CD = 'A27'
AND FAM1.BIRTH_YMD = FAM2.BIRTH_YMD) tw
ON ch.emp_no = tw.emp_no
WHERE ch.fam_nm <> tw.twin
AND fam_nm < tw.twin
OR tw.twin IS NULL;
쿼리결과
COUPLE 테이블
- FAM_REL_C 테이블과 EMP_C테이블로 현재 재직중인 사내부부 중 남편의 정보만 담은 테이블
- 배우자의 성명 컬럼추가
SELECT hus.emp_no AS h_no
, hus.emp_nm AS h_nm
, e2.emp_nm AS w_nm
FROM
(SELECT e1.emp_no AS emp_no
, e1.emp_nm AS emp_nm
, frel.emp_rel_no AS emp_rel_no
FROM fam_rel_c frel
LEFT JOIN emp_c e1 ON e1.emp_no = frel.emp_no
WHERE SYSDATE BETWEEN frel.sta_ymd AND frel.end_ymd
AND SYSDATE BETWEEN e1.hire_ymd AND e1.retire_ymd
AND rel_type_cd = 'A18') hus
LEFT JOIN emp_c e2 ON e2.emp_no = hus.emp_rel_no;
쿼리 결과
최종 쿼리
- 사내부부여부, 쌍생아 여부 CASE 조건문 이용
- 자녀 나이는 현재날짜 기준 만나이로 계산
- '현재 재직중' WHERE 조건문 이용
SELECT em.emp_no AS 직원번호
, em.emp_nm AS 직원성명
, em.gender_cd AS 직원성별
, (CASE WHEN co.h_no IS NULL THEN 'N' ELSE 'Y' END) AS 사내부부여부
, co.w_nm AS 배우자성명
, ch.fam_nm AS 자녀성명
, ch.gender_cd AS 자녀성별
, ch.birth_ymd AS 자녀생년월일
, FLOOR((TO_CHAR(SYSDATE, 'YYYYMMDD') - ch.birth_ymd)/10000) AS 자녀나이
,(CASE WHEN ch.twin_nm IS NULL THEN 'N' ELSE 'Y' END) AS 쌍생아여부
FROM emp_c em
INNER JOIN child ch ON em.emp_no = ch.emp_no
LEFT JOIN couple co ON em.emp_no = co.h_no
WHERE SYSDATE BETWEEN em.hire_ymd AND em.retire_ymd
ORDER BY em.emp_no;
WITH문 포함
WITH couple AS(
SELECT hus.emp_no AS h_no
, hus.emp_nm AS h_nm
, e2.emp_nm AS w_nm
FROM
(SELECT e1.emp_no AS emp_no
, e1.emp_nm AS emp_nm
, frel.emp_rel_no AS emp_rel_no
FROM fam_rel_c frel
LEFT JOIN emp_c e1 ON e1.emp_no = frel.emp_no
WHERE SYSDATE BETWEEN frel.sta_ymd AND frel.end_ymd
AND SYSDATE BETWEEN e1.hire_ymd AND e1.retire_ymd
AND rel_type_cd = 'A18') hus
LEFT JOIN emp_c e2 ON e2.emp_no = hus.emp_rel_no)
, child AS (
SELECT ch.emp_no emp_no
, ch.fam_nm fam_nm
, ch.rel_type_cd
, ch.gender_cd gender_cd
, ch.birth_ymd birth_ymd
, tw.emp_no twin_emp_no
, tw.twin twin_nm
FROM (SELECT *
FROM FAM_C
WHERE rel_type_cd = 'A27'
AND birth_ymd BETWEEN '20120101' AND '20151231')ch
LEFT JOIN
(SELECT FAM1.EMP_NO AS emp_no
, FAM1.FAM_NM AS twin
FROM FAM_C FAM1
INNER JOIN FAM_C FAM2
ON FAM1.EMP_NO = FAM2.EMP_NO
AND FAM1.FAM_NM <> FAM2.FAM_NM
AND FAM1.REL_TYPE_CD = FAM2.REL_TYPE_CD
AND FAM1.REL_TYPE_CD = 'A27'
AND FAM1.BIRTH_YMD = FAM2.BIRTH_YMD) tw
ON ch.emp_no = tw.emp_no
WHERE ch.fam_nm <> tw.twin
AND fam_nm < tw.twin
OR tw.twin IS NULL
)
SELECT em.emp_no AS 직원번호
, em.emp_nm AS 직원성명
, em.gender_cd AS 직원성별
, (CASE WHEN co.h_no IS NULL THEN 'N' ELSE 'Y' END) AS 사내부부여부
, co.w_nm AS 배우자성명
, ch.fam_nm AS 자녀성명
, ch.gender_cd AS 자녀성별
, ch.birth_ymd AS 자녀생년월일
, FLOOR((TO_CHAR(SYSDATE, 'YYYYMMDD') - ch.birth_ymd)/10000) AS 자녀나이
,(CASE WHEN ch.twin_nm IS NULL THEN 'N' ELSE 'Y' END) AS 쌍생아여부
FROM emp_c em
INNER JOIN child ch ON em.emp_no = ch.emp_no
LEFT JOIN couple co ON em.emp_no = co.h_no
WHERE SYSDATE BETWEEN em.hire_ymd AND em.retire_ymd
ORDER BY em.emp_no;
쿼리결과
<과제2> LISTAGG를 활용하여 직원번호가 10001483 인 자녀의 성명이 한줄로 추출 되도록 SQL작성
SELECT EMP_NO AS 직원번호,
LISTAGG(FAM_NM, ',') WITHIN GROUP(ORDER BY FAM_NM) AS 자녀성명
FROM FAM_C
WHERE EMP_NO = '10001483'
AND REL_TYPE_CD = 'A27'
GROUP BY EMP_NO;
내가 잘하고 있나 스스로 의심이 들었지만 4주가 지난 뒤 이걸 다시 보니 지금의 난 정말로 4가지를 다 하고 있다.
1. SQL로 간단한 데이터 추출
나는 캠프를 듣기 전에는 SQL툴을 한번도 써본 적이 없는데 지금은 예전에 하던 R, 파이썬 보다 훨씬 편하고 익숙해졌다.
분석까지는 모르겠지만 적어도 질의문을 보고 쿼리를 어떻게 짤 지 고민하고 문법을 작성하는 걸 한달 내내 하다보니 재미도 있고 자신감도 붙었다. (지금 실전반들으면서 다시 자신감하락중이긴 하다...😂)
적어도 이력서에 SQL을 다룰 줄 안다고 쓸 수 있게 된 건 확실하다.
2. 해커랭크, 리트코드 등 코딩 플랫폼에 익숙해짐
강의 실습으로 해커랭크, 리트코드,solvesql 코딩플랫폼을 이용한다. 뿐만 아니라 입문반 수업에서 원하는 사람들은 신청받아 스터디를 구성해줬는데 스터디를 하면서 해커랭크에 있는 대부분의 문제는 거의 다 푼 것 같다. 그 기록은 이 블로그에 'SQL문제풀이' 카테고리에도 있다. 이런 코딩플랫폼에 익숙해짐으로써 나중에 SQL 코딩테스트에 대비할 수 있다고 한다.
3. SQL을 실무에 활용할 수 있음
아직 취준중이라 실무에서 사용하고 있다고 말하기 어렵지만 배운 분석 내용을 바탕으로 프로젝트를 하려고 하고 있다. 그리고 입문반 3주정도 지났을 때 부터 다른 곳에서 직무부트캠프를 추가로 들었는데 실무자가 알려주는 분석을 위한 SQL문법 내용 중 90%이상은 이미 데이터리안 입문반수업으로 알고 있는 문법이었다.
4. 남이 작성한 SQL쿼리를 읽고 이해/수정 할 수 있음
SQL스터디를 하면서 다른 사람의 쿼리를 읽을 수 있었고 특히 해커랭크 문제 풀 때 도저히 안 풀리면 풀이를 검색하거나 Discussion부분을 보면서 남이 작성한 쿼리를 자주 봤는데 내가 모르는 문법, 함수가 나와도 당황하지 않고, 구체적으로는 몰라도 전체적인 흐름, 왜 이사람이 이렇게 쿼리를 작성했는지는 알 수 있게 되었다.
(당황하지 않고 천천히 쿼리를 뜯어보려고 하는 습관은 데 정말로 데이터리안의 강의력이 뛰어난 덕분이라고 생각한다.)
추가로 캠프를 들으면서 비슷한 직무를 준비하는 사람들을 만날 수 있어서 좋았다.
나는 지금 데이터리안에서 다른 수강생분들과 SQL스터디, 기초 통계학 스터디를 같이 하고 있다.
그리고 캠프 수강생은 월간세미나가 무료인데 데이터리안 블로그+ 유튜브+ 세미나까지 양질의 콘텐츠를 제공받을 수 있는 점이 정말 좋다.
또한 슬랙으로 언제든지 질문을 올릴 수 있는 게 편리하다. 나는 사실 구글링으로 대부분 해결하는 편이라 질문답변 채널을 잘 이용하지는 않았지만 그래도 언제든 편하게 질문할 수 있는 창구가 있냐 없냐는 다르다고 생각한다.
심지어 이 슬랙은 캠프가 종료되어도 이용할 수 있다. 혼자서 공부할 때는 질문할 곳이 막막한데 슬랙에서 질문하거나 프로젝트 피드백도 받을 수 있다.
아쉬웠던 점
나는 '데이터분석'을 경험하고 싶었는데 아무래도 입문반이다 보니까 SQL문법 위주의 수업이었다. RFM분석, 매출분석은 3,4주차 세션이 거의 끝나갈 때쯤 배웠어서 아쉬움이 있다.
입문반을 듣고 SQL에는 익숙해졌지만 막상 프로젝트를 하고 포트폴리오 작성을 하는데는 여전히 막막하다.
다른 분들한테도 물어보니 아직 다들 막막하다 하는 걸 보니 나만 그렇게 느끼는 것 아닌 것 같다.
반면에 실전반은 언어보다 분석위주라고 해서 기대 중이다.
내가 처음 등록할 때 고민했던 부분은 인프런에 데이터리안 강의가 있는데 굳이 데이터분석 캠프를 들어야 하는 이유를 제대로 파악하기 어려웠다. 이번에 캠프가 전부 VOD로 바껴서 더욱이 나와 비슷한 고민을 하는 사람이 있을 것 같다.
만일 패키지를 들을 지, 실전반만 들을 지 고민 중이라면,
금전적으로 여유가 있다면 패키지를 추천한다.
캠프를 들으면 매주 규칙적으로 공부할 수 있고 진도, 공부분량 등 다 정해주고 그에 따른 적절한 콘텐츠도 큐레이팅 해주기 때문에 캠프로 듣는 것이 확실히 편하고 꾸준히 공부할 수 있다. 특히나 나같이 인강만으로는 규칙적으로 공부하기 어려운 사람이라면 말이다.
그러나 혼자서 인강도 계획적으로 잘 듣고, 문제도 추가로 찾아서 풀 수 있는 사람이라면
인프런에 있는 데이터리안 초급, 중급 강의로 SQL 언어를 독학하고 실전반 캠프를 들으며 분석을 배우고 프로젝트를 하는 것도 좋지 않을까 생각이 든다.
마지막으로 데이터리안이 어떤 곳인지 아직 잘 모르겠다면 데이터리안 블로그 글을 읽는 걸 강추한다.
좋은 글들이 많고 나도 블로그 글을 읽으면서 캠프에 대한 신뢰성(?)이 생겨 등록할 수 있었다. 뿐만 아니라 유튜브도 있으니 충분히 찾아보고 고민해보시길 바란다.
