-- AVG(SALARY) AS 평균급여 -- , MAX(SALARY) AS 최대 -- , MIN(SALARY) AS 최소
-- 사원테이블의 소득구간별 대상자수와 평균급여 최대, 최소 연봉을 구하시오
SELECT salary , CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END commission_pct , (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END))*12 as 연봉 FROM employees;
-----------------------------------------------------------일부채워본거임 미완성 SELECT CASE WHEN (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END))*12 BETWEEN 0 AND 70000 THEN '저소득(7만이하)' WHEN (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END))*12 > 70000 AND (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END))*12 <= 200000 THEN '중위소득(7만초과 20만이하)' WHEN (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END))*12 > 200000 AND (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END))*12 <=250000 THEN '고소득(20만초과 ~ 25만이하)' WHEN (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END))*12 > 250000 THEN '초과소득' END AS 소득구간
, ROUND(AVG(salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END))*12) AS 평균급여 , MAX(salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END))*12 AS 최대연봉 , MIN(salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38 WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44 WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 WHEN commission_pct is NULL then 0.35 ELSE commission_pct END))*12 AS 최소연봉 FROM employees;
---다양한 조건 조합해서 사용자 정의 함수를 만들어볼수있음. /*단일 조건에 사용시 1. CASE문 >>>함수가 아닌 표현식의 일종. 하나, 그이상의 연산자또는 함수를 조합해서 조건을 만드는 것임. 넓은 범위에 활용가능. CASE WHEN 특정 컬럼의 조건과 일치 THEN 처리결과 [입력 값, 컬럼 ] ELSE 다른결과 END
2. DECODE (조건컬럼, 조건1, 반환결과1, 조건2, 반환결과2) 사용방법은 간단하지만 동등비교에서만 사용가능한 함수, 오라클에서만 사용가능. */ SELECT department_id , DECODE(department_id, 100, 1, 0) as CHK1 , CASE WHEN department_id = 100 THEN 1 ELSE 0 END CHK22 FROM employees ;
/* 다중조건에 사용시 1. CASE문 CASE WHEN 특정 컬럼의 조건과 일치 THEN 처리결과[입력값, 컬럼] <---조건1 WHEN 특정 컬럼의 조건과 일치 THEN 처리결과[입력값, 컬럼] <---조건2 WHEN 특정 컬럼의 조건과 일치 THEN 처리결과[입력값, 컬럼] <---조건3 ELSE 다른결과 END
2. DECODE(조건컬럼, 조건1, 반환결과1, 조건2, 반환결과2) */ SELECT department_id , DECODE(department_id, 90, 'A그룹', 60, 'B그룹', 100, 'C그룹', 'D그룹') as CHK1 , CASE WHEN department_id = 90 THEN 'A그룹' WHEN department_id = 60 THEN 'B그룹' WHEN department_id = 100 THEN 'C그룹' ELSE 'D그룹' END CHK22 FROM employees ; /*하나의 값에 1대1 대응하는 조건인경우 디코드랑 케이스를 동시에 사용가능, 오히려 케이스를 작성하는것보다 디코드가 효율적이어 보인다. 이번엔 부서의 번호가 구간별로 잡힌다면 디코드를 사용할수있을것인가.?*/
--디코드는 1대1로 TRUE / FALSE에 대한 반환값을 확인할수있기때문에 범위조건으로 해결하려면 CASE문 사용해야함. --해당하는 하나의 값에 대응되는 조건에 대해서는 디코드와 케이스를 동시에 사용할수있다 SELECT department_id , DECODE(department_id, 90, 'A그룹', 60, 'B그룹', 100, 'C그룹', 'D그룹') as CHK1 ,CASE WHEN department_id >= 90 THEN 'A그룹' WHEN department_id < 90 THEN 'B그룹' ---NULL값은 숫자로 인식하지 못하기때문에 예외처리를 해줘야함. ELSE '대기발령' END CHK22 FROM employees;
--CASE구문의 확장성을 경험하기 위한 문제 /* 연습문제 사원의 입사일 ~ 2020년 1월 1일까지의 근속개월수를 구해 근속년수로 산출하고 근속년수가 15년을 초과하면 기본급여에 보너스 10,000을 10년초과 ~ 15년이하는 보너스 7,000DMF 10년 이하는 5,000의 보너스를 지급하는 CASE문을 작성해보자. */ SELECT hire_date AS 입사일 ,round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date), 2) 근속개월 , round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date) /12 ,2) 근속년수 , salary as 급여
,CASE WHEN round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date) /12 ,2) > 15 THEN salary + 10000 when round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date) /12 ,2) > 10 and round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date) /12 ,2) <= 15 THEN salary + 7000 when round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date) /12 ,2) <= 10 THEN salary + 5000 end as 보너스금액 FROM employees;
--50을 더하면 날짜가 제대로 더해지지 않기때문에 날짜형식으로 바꾼후 숫자를 더해준다. 중요!! SELECT TO_DATE('20200501', 'YYYYMMDD') - 50 FROM dual;
SELECT TO_DATE('20200501', 'YYYYMMDD') - TO_DATE('20200101', 'YYYYMMDD') FROM dual;
--아래와같이 출력하면 20200501 날짜만 보이게 되어있음 (TO_DATE 날짜타입이기 때문) SELECT TO_DATE('20200501123959', 'YYYYMMDD HH:MI:SS') FROM dual;
--시분초를 같이 보여지게 하기 위해서는 CHAR 사용 1을 더하면 5/1에서 5/2일로 바뀜 SELECT TO_CHAR(TO_DATE('20200501123959', 'YYYYMMDD HH:MI:SS')+1 , 'YYYYMMDD HH:MI:SS') FROM dual;
--시분초를 같이 보여지게 하기 위해서는 CHAR 사용 시간을 더해주기 위해서는 1/24 해서 더한다. (24시간중 1시간) SELECT TO_CHAR(TO_DATE('20200501123959', 'YYYYMMDD HH:MI:SS')+8/24 , 'YYYYMMDD HH:MI:SS') FROM dual;
--달수 차이 SELECT months_between(TO_DATE('20200501', 'YYYYMMDD'), TO_DATE('20000501', 'YYYYMMDD')) FROM dual;
--5/1에서 19달 지난 날짜는? SELECT add_months(TO_DATE('20200501', 'YYYYMMDD'), 19) FROM dual;
--**날짜함수의 종류 /* 1. 월수 계산 MONTHS_BETWEEN 2. 월 덧셈 ADD_MONTHS 3. 돌아오는 요일 NEXT_DAY 4. 마지막 날 LAST_DAY 5. 날짜 반올림 ROUND 6. 날짜 버림 TRUNC */
SELECT MONTHS_BETWEEN(TO_DATE('20210331', 'YYYYMMDD'), TO_DATE('20210131','YYYYMMDD')) "3월과 1월의 개월차" , ADD_MONTHS(TO_DATE('20210131', 'YYYYMMDD'),5) "5개월후" , LAST_DAY(TO_DATE('20210101', 'YYYYMMDD')) "1월의 마지막 날" --애러남 , NEXT_DAY(TO_DATE('20210131', 'YYYYMMDD'), 1) "1월31일 이후 돌아오는 일요일" FROM DUAL;
>> alias 부분의 내용이 길어서 에러가 나는거고, "돌아오는일요일"로 바꾸면 출력됨! (컬럼명은 30byte 이하여야 한다고 함. ) UTF-8인코딩기준
-- 1. TO_NUMBER 문자를 숫자로 SELECT '123' + 100 FROM dual; --문자타입과 숫자타입을 지정해서 계산해도 오랔르이 알아서 숫자로 변환해서 연산해줌. SELECT TO_NUMBER('123') + 100 --TO NUMBER 사용해서 정확하게 문자타입 연산하는걸 추천, 위와같이 했을때 연산이 되지않는경우도 종종 있기때문임. FROM dual;
-- 2. NVL NULL 값이 존재하는 것을 원하는 값으로 변경 SELECT salary, nvl(commission_pct,0) , salary + commission_pct ----연산하는값중에 null이 있을때 결과는 무조건 null을 반환하게 되어있음. >>값이null임 , salary + nvl(commission_pct,0) ----이런값들 연산하기위해서는 null값을 특정값으로 치환해줘야함. null을0으로치환했다. >>제대로값나옴 FROM employees ;
-- 추가)) 2. NVL NULL 값이 존재하는 것을 원하는 값으로 변경--- 문자'a'로변경 SELECT salary, nvl(TO_CHAR(commission_pct),'a') AS NULL값A로치환 /*null값을 a로바꾸려고하는데, 이때commission_pct의 데이터타입이 이미 숫자로 선언되어있기때문에 문자인 a로 바꾼다고하면 타입이 위배된다. 이럴때 옵션 없이 to_char 이용해서 commission_pct의 형태자체를 숫자로 바꾸면, NVL을 사용했을때 문자로 바꾸는 명령이 가능해짐. */ , salary + commission_pct , salary + nvl(commission_pct,0) FROM employees ;
SELECT TO_CHAR(TO_DATE('2020/12/8') , 'DDD') AS 올해몇일째 ,TO_CHAR(TO_DATE('2020/12/8') , 'DD') AS 이번달몇일째 ,TO_CHAR(TO_DATE('2020/12/8') , 'D') AS 이번주몇일째 ,TO_CHAR(TO_DATE('2020/12/8') , 'Q') AS 몇분기 FROM dual;
어려웠던 강의 4번째자리구해보라고 했는데 이건 답이없어서 맞는지모르겠다; 네번째자리수가 없는애들은 값 표시안하게 하고싶은데 이거나중에 더배워서 고쳐봐야할듯 ㅠ_ㅠ 헝
--문자열함수 알아보기 / 문자열을 다양한 함수를 이용해서 다뤄보기 /* 1. 대소문자 함수 알아보기 LOWER, UPER, INITCAP 2. 특정문자열 추출 SUBSTR 3. 문자열 길이 확인 LENGTH 4. 문자열의 위치 확인 INSTR 5. 문자열 변경하기 REPLACE 6. 특정문자로 자리수 맞추기
*/
--문자열 함수 응용문제 -- 1. 대소문자 함수들이 어떻게 쓰이는지?
--퍼스트네임에 소문자 E가들어가는 행 출력 SELECT * FROM employees WHERE first_name LIKE '%e%' ;
SELECT * FROM employees WHERE first_name||' '||last_name LIKE '%e%' --파생컬럼을 만들어서 데이터 필터링. 따라서 데이터 출력은 원본에서 출력이 된다. ; /* SELECT부터 작성해서 문장시작하지만, FROM 부터 실행해서 WHERE로 필터링 한 후에 나중에 SELECT로 넘어가서 실행된다. */
/* 대소문자 구분없이 출력*/ -(대/소문자) 하나로 치환해서 검색을 했을때 원하는 결과를 수월하게 얻을수 있다. SELECT * FROM employees WHERE upper(first_name||' '||last_name) LIKE '%E%' ;
SELECT first_name||' '||last_name as NAME ,instr(first_name||' '||last_name, 'a') as 소문자a의위치 ,lower(first_name||' '||last_name) as 소문자이름 FROM employees WHERE instr(lower(first_name||' '||last_name),'a') in (1, 5, 6, 7) --대소문자 구분없이 a가 1,5,6,7번째 존재하는 애들만 표시 ;
-- 2. 문자열을 자르는 함수들이 어떻게 쓰이는지?
SELECT phone_number , substr(phone_number, 1, 3) as col1 -- , substr(phone_number, 5, 3) as col2 , substr(phone_number, 9, 4) as col3 , length(phone_number) as 자리수
-- , substr(phone_number, 5, length(phone_number)) as ph2 --폰넘버 5번째부터 자릿수끝까지 표시 , instr(substr(phone_number, 5, length(phone_number)), '.') as 점의위치 --폰넘버 5번째부터 자릿수끝까지인 데이터에서 .의 위치를 표시해줌 , substr(substr(phone_number, 5, length(phone_number)), 1, instr(substr(phone_number, 5, length(phone_number)), '.')-1) as 두번째 --자른번호에서 첫번째자리부터 .위치 전(-1)까지 표시해줘라. , substr( substr(phone_number, 5, length(phone_number)) , instr(substr(phone_number, 5, length(phone_number)), '.')+1 , length( substr(phone_number, 5, length(phone_number))) ) as 세번째 ----아래꺼는 마지막 네번째자리 혼자서 구해본거... 잘모르겠음..
--문자열 함수 종류와 기능 /* 1. 대소문자 함수 알아보기 LOWER, UPER, INITCAP 2. 특정문자열 추출 SUBSTR 3. 문자열 길이 확인 LENGTH 4. 문자열의 위치 확인 INSTR 5. 문자열 변경하기 REPLACE 6. 특정문자로 자리수 맞추기
*/
--어느 위치에와도 이 함수를 다양한 구조로 활용할수있음. SELECT 번지주소, LENGTH(번지주소) AS 주소자리수 FROM 지역별설치현황 WHERE LENGTH(번지주소) > 22 ;
-- 1. 대소문자 함수 알아보기 -- LOWER, UPER, INITCAP SELECT last_name||' '||first_name as "name" , lower(last_name||' '||first_name) as "name_1" , upper(last_name||' '||first_name) as "name_2" , initcap(lower(last_name||' '||first_name)) as "name_3" FROM employees; --대소문자 구분하는게 가독성에 좋음
SELECT 번지주소, instr(번지주소, '동') as 동찾기 FROM 지역별설치현황;
--특정위치 키워드 찾아서 바꾸기 SELECT 번지주소, replace(번지주소, ' ', '**') as 키워드바꾸기 FROM 지역별설치현황;
--글자수 잘라보기 /주소에서 서울시만 빼서 표시 SELECT 번지주소, substr(번지주소, 1, 5) as 서울시 , substr(번지주소, 11, 7) as 서울시 FROM 지역별설치현황;
--lpad 채울 숫자가 바이트임. 한글은2바이트, 영어는1바이트 SELECT 시도명 , lpad(시도명, 20, '★') as "lpad확인" , rpad(시도명, 20, '★') as "rpad확인" FROM 지역별설치현황;
/*수치형 함수의 종류와 기능 설명 ㅈ함수들이 다 엑셀에 동일하게 존재한다 테이블에잇는 값이 아니라 한 숫자의 결과에대해서 결과값을 반환해주는 식의 sql을 작성해볼것임. 최소한의 문장은 select와 from으로 작성되어야 한다고 했음. */
-- 1. ABS(입력값) 절대 값 산출 SELECT ABS (-1) AS COL1 FROM ; --이렇게 입력하면 결과가 나오지 않는다. 테이블 명을 입력 해줘야함. SELECT ABS (-1) AS COL1 FROM DUAL ; --테이블을 사용하지않고 수치형에대한 결과를 연산해보려고 해도, 최소한의 구조는 잡아주어야 하기때문에 테이블은 DUAL로 입력. --DUMMY TABLE이라고 해서 임시적인 변수를 사용해서 자리를 맞춰준다고 가볍게 생각.
-- 2. ROUND(입력 값, 자리수지정) 반올림 SELECT ROUND (123.4567) AS COL1 --엑셀에서는 반올림 안할경우 옵션을 0으로 줘야했지만, SQL에서는 굳이 조건을 주지 않아도 넘어간다.(123.4567,0) , ROUND (123.4567, -1) AS COL2 , ROUND (123.4567, 1) AS COL3 FROM DUAL;
SELECT salary , salary/150 , sqrt(power(round(salary/150), 2)) as rd1 FROM employees ;
-- 3. MOD (입력 값, 나눌 수) SELECT MOD (10, 3) AS COL1 FROM DUAL ;
--4. SQRT (입력 값) -제곱근 SELECT SQRT( 2 ) AS COL1 FROM DUAL ;
-- 5. POWER(입력 값, 제곱 수)/????? 값이 8나오는데 이게맞나 SELECT POWER (2, 3) AS COL1 FROM DUAL ;
-- 6. CEIL(입력 값) SELECT CEIL (123.4567) AS COL1 --양의자리에서 반올림 , FLOOR (123.4567) AS COL2 -- 1의자리 이후 소수점 다 버림. FROM DUAL ; -- 7. FLOOR( 입력 값)
-- 8. TRUNC(입력값, 자리수) --올림을 하지 않음. 정해진 자리 이후를 전부다 절삭하는것임. SELECT TRUNC(123.4567) AS COL1 , TRUNC(123.4567, 2) AS COL2 , TRUNC(123.4567, -2) AS COL3 FROM DUAL;
덤벨로우 양팔로 했음 빈바 발에 올려놓고 엄지발가락 들린상태로 발 닿고 아치형되게 발쥐나는느낌이 맞음. 등근육신전? 시키고 올라와야한다햇음 상체가 내려가는게 아니고 팔만 앞으로 뻗어서 등 늘리는거임 사키로 시선 정면 보고 횟수 늘어날수록 발바닥에 신경쓰면서 그리고 다리 구부리고 팔 내릴때 수직으로 힘 받는 방향인거 인지하고 운동 하기
플레이트 두개 놓고 발바닥놓고 원레그런지 사키로씩인가 들고 했는데 10번씩에서 7번씩으로 횟수 줄였음 너무힘들다 뒤에 벤치에 올려놓는 다리에는 힘들어가는게 아님 스쿼트할때처럼 앞으로 내려가는게아니고 고관절 접히는쪽으로 대각선 안쪽방향으류 접어야함
벤치 앉아서 발끝붙히고 발뒤꿈치 들어서 고관절 접은 상태에서 머리에 팔놓고 뒤로제끼는거 했음 위로 올라갈때 가슴열리면 안된다는거 그대로 팔꿈치 닫은거 유지한상태로. 더 뒤로 젖히는 동작 할때는 등에 자극+배에 자극이 더 옴
마지막 스쿼트할때 다리 넓게 벌리고 끝까지 앉을 것. 무릎 앞으로 더 구부려도 된다햇음 내려갈때 엉덩이 빼는게아니고 고관절 잡아주고 접어준다는느낌인것 뒤로빠지지말고 배를 앞으로 잡아준다는 느낌 잊지말기!! 허리세우기 내전근에 힘들어오는 느낌 뭔지 기억 ⭐️⭐️발바닥쓰는게 너무너무 중요해!!!!
