DBeaver 설치 및 실습
-- DATAset 전체 읽기
SELECT *
FROM hr.hr_employee_attrition hea
;
-- 특정 column 만 일기
SELECT EmployeeNumber ,Attrition ,Department ,JobRole ,JobLevel
FROM hr.hr_employee_attrition hea
;
-- Row 수 확인 : 1470Row
SELECT count(*) HeadCount
FROM hr.hr_employee_attrition hea
;
-- 직원 수 확인 : 1470명
SELECT count(DISTINCT EmployeeNumber) HeadCount
FROM hr.hr_employee_attrition hea
;
/ GROUP BY /
-- Department , JobRole , JobLevel 별 재직 인원수를 구하자
SELECT Department , JobRole , JobLevel , count(*) hc
FROM hr.hr_employee_attrition hea
WHERE Attrition = 'No'
GROUP BY Department , JobRole , JobLevel
;
-- 퀴즈 1 : Department , JobRole , Gender 별로 재직인원수 , Age의 최대/최소/평균값
SELECT Department , JobRole , Gender
, count(*) HC , max(Age) max_age , min(Age) min_age , avg(Age) avg_age
FROM hr.hr_employee_attrition hea
WHERE Attrition = 'No'
GROUP BY Department , JobRole , Gender
;
-- 퀴즈 2 : Department, JobLevel 별로 아래값을 산출
-- 재직인원수 , TotalWorkingYears의 평균 , YearsAtCompany의 평균 , MonthlyIncome 최대,최소값
SELECT Department , JobLevel
,count(*) HC
, round(avg(TotalWorkingYears),1) avg_total_working_years
, round(avg(YearsAtCompany),1) avg_years_at_company
, max(MonthlyIncome) max_income
, min(MonthlyIncome) min_income
FROM hr.hr_employee_attrition hea
WHERE Attrition = 'No'
GROUP BY Department , JobLevel
ORDER BY avg_years_at_company DESC
;
-- Alias 별명 다른 이름
-- Department : 1 , JobLevel : 2
SELECT Department , JobLevel
,count(*) HC
, round(avg(TotalWorkingYears),1) avg_total_working_years
, round(avg(YearsAtCompany),1) avg_years_at_company
, max(MonthlyIncome) max_income
, min(MonthlyIncome) min_income
FROM hr.hr_employee_attrition hea
WHERE Attrition = 'No'
GROUP BY 1,2
ORDER BY 6 DESC
;
SELECT Department , JobRole , JobLevel
, count(*)hc
FROM hr.hr_employee_attrition hea
WHERE Attrition = 'No'
GROUP BY Department , JobRole , JobLevel
ORDER BY Department , JobRole , JobLevel
;
-- 데이터 셋에 중복값이 없음을 확인
SELECT
(SELECT count(*)
FROM hr.hr_employee_attrition hea)
=
(SELECT count(DISTINCT EmployeeNumber) hc
FROM hr.hr_employee_attrition hea) check_duplicated
;
SELECT count()
FROM (
SELECT DISTINCT
FROM hr.hr_employee_attrition hea
)hea2
-- 특정 팀에 인원수 파악
SELECT count(*)
FROM hr.hr_employee_attrition hea
WHERE Department = 'Sales'
AND Attrition = 'No'
;
SELECT count(*)
FROM hr.hr_employee_attrition hea
WHERE 1=1
AND Department = 'Sales'
;
-- 퀴즈 : 최소 1년이상 재직 / 나이 30살 이하 / level 2 이하 / HR팀 제외
SELECT count(*)
FROM hr.hr_employee_attrition hea
WHERE 1=1
AND YearsAtCompany > 1
AND Attrition = 'No'
AND Age <= 30
AND JobLevel < 3
AND Department != 'Human Resources'
SELECT 3 IN (0,1,2,3);
SELECT NOT (3 IN (0,1,2,3));
SELECT 10 BETWEEN 1 AND 10;
SELECT 5=5 AND 4=5;
SELECT 5=5 OR 4=5;
SELECT 'Apple' LIKE 'A%';
SELECT 'Apple' LIKE 'A%e';
SELECT 'Apple' LIKE 'A%p';
-- 최소 5년이상 / 나이 30~45 / level 3,4 / Sales 혹은 R&D 소속
SELECT count(*)
FROM hr.hr_employee_attrition hea
WHERE 1=1
AND YearsAtCompany >= 5
AND Attrition = 'No'
AND Age BETWEEN 30 AND 45
AND JobLevel IN (3,4 )
AND Department IN ('Sales', 'Research & Development')
;
-- 퀴즈 : Department , joblevel , Gender 별 인원수 , 평균 연령 표기
-- 평균 연령으로 내림차순 , 소수점 0
-- HC, avg_age 컬렴명 명명
SELECT Department , JobLevel , Gender
,count(*) HC , round(avg(Age),0) Avg_age
FROM hr.hr_employee_attrition hea
WHERE 1=1
AND YearsAtCompany >= 5
AND Attrition = 'No'
AND Age BETWEEN 30 AND 45
AND JobLevel IN (3,4 )
AND Department IN ('Sales', 'Research & Development')
GROUP BY 1,2,3
ORDER BY Avg_age DESC
;
-- 과제
-- Ql 재직연수 5년이상 / 입사 이후 동일 Role에서 지속근무 / 최근 1년 이내 승진이력 X (동일 Level에서 1년이상 근무)
-- 대상인원의 EmployeeNumber 리스트를 구하기 위한 쿼리를 작성
SELECT EmployeeNumber
FROM hr.hr_employee_attrition hea
WHERE 1=1
AND Attrition = 'NO'
AND YearsAtCompany >= 5
AND YearsInCurrentRole = YearsAtCompany
AND YearsSinceLastPromotion > 1
;
-- Ql에서 구한 결과값에 대하여 Department , JobRole , JobLevel 별로 대상 인원수
-- JobLevel 기준으로 내림차순
SELECT Department , JobRole , JobLevel , count(*) HC
FROM hr.hr_employee_attrition hea
WHERE 1=1
AND Attrition = 'NO'
AND YearsAtCompany >= 5
AND YearsInCurrentRole = YearsAtCompany
AND YearsSinceLastPromotion > 1
GROUP BY 1,2,3
ORDER BY JobLevel DESC
;
