-- SELECT [Columns]
-- FROM [Schema]. [Table]

-- Dataset 전체 읽기

SELECT *
FROM hr.hr_employee_attrition hea
;

-- SELECT Col 1, Col 2, Col 3, Col4
-- FROM hr.hr_employee_attrition

SELECT EmployeeNumber, Attrition, Department, JobRole, JobLevel
FROM hr.hr employee attrition hea

df[['Col_1', 'Col_2', 'Col_3', 'Col_4']]

-- SELECT COUNT ()
-- FROM hr.hr_employee_ attrition

SELECT count (*) AS count_row
FROM hr.hr_employee_attrition hea
;

SELECT count (*) AS count row
FROM hr.hr_employee_attrition hea
;

SELECT count (Department) AS count row
FROM hr.hr_employee_attrition hea
;

-- SELECT COUNT(DISTINCT[Col])

SELECT count (DISTINCT Department) AS count row
15 FROM hr.hr_employee_attrition hea

-- Dataset 전체 읽기
SELECT *
FROM HR.HR_Employee_Attrition hea 
;

-- 특정 column 만 읽기
SELECT EmployeeNumber , Attrition , Department , JobRole , JobLevel 
FROM  HR.HR_Employee_Attrition hea 
;

-- ROW 수 확인 : 1,470row
SELECT count(*) Headcount -- Headcount를 알리아스(별칭)로 지정한 것임.
FROM HR.HR_Employee_Attrition hea 
;

-- 직원 수 확인 : 1,470ea
SELECT COUNT(DISTINCT EmployeeNumber)
FROM HR.HR_Employee_Attrition hea
;

SELECT [Col 1], [Col 2], [Col 3], AGG([Col 4])
FROM [Source Table]
WHERE [Conditions]
GROUP BY [Col 1], [Col. 2], [Col 3]
ORDER BY [Col 1], [Col 2], [Col 3]

ORDER BY [Col 1], [Col 2], [Col 3]
ORDER BY [Col 2], [Col 3], [Col 1]
ORDER BY [Col 1] DESC, [Col 2], [Col 3] DESC

SELECT Department , JobRole, JobLevel  -- 2
			,count(*) AS hc
FROM hr.hr_employee_attrition hea  -- 1
WHERE Attrition = 'No' -- 3
GROUP BY Department, JobRole, JobLevel  -- 4
ORDER BY Department, JobRole, JobLevel  -- 5

SELECT Department , JobRole, JobLevel 
			,count(*) AS hc
FROM hr.hr_employee_attrition hea 
WHERE Attrition = 'No'
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3

/*GROUP BY*/
-- Department, JobRole, JobLevel 별 재직 인원수를 구해보자
SELECT Department, JobRole, JobLevel, COUNT(*) AS HC -- 2
FROM HR.HR_Employee_Attrition hea -- 1
WHERE Attrition = 'No'  -- 3 
GROUP BY Department, JobRole, JobLevel 
;
-- Python의 Pandas 같은 수식: df.GROUPby(['Department', 'JobRole', 'JobLevel']).countn()

-- 다양한 집계 함수 
-- Department, JobRole, Gender 별 제품인원수, Age의 최대/ 최소/ 평균값
SELECT Department, JobRole, Gender
	, COUNT(*) AS hc
	, MAX(Age) AS max_age
	, MIN(Age) AS min_age
	, AVG(Age) AS avg_age
FROM HR.HR_Employee_Attrition hea 
WHERE Attrition = 'No'
GROUP BY Department, JobRole, Gender 
;

-- Department, JobLevel 별로 아래의 계산값을 산출해보자.
-- 재직인원수, TotalWorkingYears 평균값, YearAtCompany 평균값, MonthlyIncome 최대값, MonthlyIncome 최소값
SELECT Department, JobLevel
	, COUNT(*) AS `재직인원수`
	, ROUND(AVG(TotalWorkingYears),1) AS `TotalWorkingYears 평균값`
	, ROUND(AVG(YearsAtCompany),1) AS `YearAtCompany 평균값`
	, MAX(MonthlyIncome) AS `MonthlyIncome 최대값`
	, MIN(MonthlyIncome) AS `MonthlyIncome 최소값`
FROM HR.HR_Employee_Attrition hea 
WHERE Attrition = 'No'
GROUP BY Department, JobLevel
ORDER BY `YearAtCompany 평균값` DESC 
;

-- Alias 별명 다른 이름 
SELECT Department, JobLevel
	, COUNT(*) AS `재직인원수`
	, ROUND(AVG(TotalWorkingYears),1) AS `TotalWorkingYears 평균값`
	, ROUND(AVG(YearsAtCompany),1) AS `YearAtCompany 평균값`
	, MAX(MonthlyIncome) AS `MonthlyIncome 최대값`
	, MIN(MonthlyIncome) AS `MonthlyIncome 최소값`
FROM HR.HR_Employee_Attrition hea 
WHERE Attrition = 'No'
GROUP BY 1, 2
ORDER BY 6 DESC 
;

/*비교 연산자*/
-- True : 1, False : 0
SELECT 1=1;
SELECT 1=2;
SELECT 5>3;
SELECT 2!=1;
SELECT 10>=5;

-- 데이터셋 중복값이 있는지 없는지 확인 값이 1면 TRUE
SELECT 
	(SELECT count(*)
	FROM HR.HR_Employee_Attrition hea 
	)
	=
	(SELECT COUNT(DISTINCT EmployeeNumber) AS 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'
;

-- 뒤의 Quiz 풀기 전에 Department 에 무슨 Distinct 값이 있는지 확인하기.
SELECT DISTINCT Department
FROM HR.HR_Employee_Attrition hea; 

-- Quiz
-- 최소 1년 이상 재직 / 나이 30살 이하 / level 2 이하 / HR 팀 제외 만족하는 인원수 파악 
SELECT COUNT(*) 
FROM HR.HR_Employee_Attrition hea 
WHERE 1=1
AND Department != 'Human Resources'
AND YearsAtCompany >= 1
AND Age <= 30
AND Attrition = 'No'
AND JobLevel <= 2
;

/* 논리 연산자 */
SELECT 3 IN (0,1,2,3,4);
SELECT NOT(2 IN (0,1,2,3,4));
SELECT 100 NOT IN (0,1,2,3,4);

SELECT 5=5 AND 100=5;
SELECT 5=5 OR 100=5;

SELECT 'apple' LIKE 'a%';
SELECT 'apple' LIKE 'a%e';
SELECT 'apple' LIKE 'A%l';

-- 최소 5년 이상 재직 / 나이 30~45 / level 3,4 / Sales 혹은 R&D 소속 일 것
SELECT COUNT(*) 
FROM HR.HR_Employee_Attrition hea 
WHERE 1=1
AND Attrition = 'No'
AND YearsAtCompany >= 5
AND Age BETWEEN 30 AND 45
AND JobLevel IN (3, 4)
AND Department IN ('Sales', 'Research & Development')
-- AND Department <> 'Human Resources'
;

-- Department, joblevel, gender 별 인원수, 평균 연령 표기
-- 평균 연령으로 내림차순 (Round 0)
-- HC, avg_age 컬럼명 명
-- 최소 5년 이상 재직 / 나이 30~45 / level 3,4 / Sales 혹은 R&D 소속 일 것
SELECT Department, JobLevel, Gender
		, COUNT(*) HC
		, AVG(Age) Avg_Age
FROM HR.HR_Employee_Attrition hea
WHERE 1=1
AND Attrition = 'No'
AND YearsAtCompany >= 5
AND Age BETWEEN 30 AND 45
AND JobLevel IN (3, 4)
AND Department IN ('Sales', 'Research & Development')
GROUP BY 1, 2, 3
ORDER BY 5 DESC 
;