-
005. 서브쿼리, 인라인뷰, 등수 함수, 그룹 함수 와 주의점SsY/Class 2023. 3. 29. 09:02728x90
2023.03.28 (화)
SCOTT 계정 실습...
- 어제 이어서..
마지막 문제 풀이
- 배운 내용 기준으로 아래 내용 구하기 ( + 주의 점들 잘 살펴보기 )
① 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일, 급여
더보기SELECT USER FROM DUAL; --==>> SCOTT ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; --==>> Session이(가) 변경되었습니다. --○ TBL_SAWON 테이블을 활용하여 다음과 같은 항목들을 조회한다. -- 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일 -- , 정년퇴직일, 근무일수, 남은일수, 급여, 보너스 -- 단, 현재나이는 한국나이 계산법에 따라 연산을 수행한다. (태어난 해에 한 살 념념) -- 또한, 정년 퇴직일은 해당 직원의 나이가 한국나이로 60세가 되는 해(연도)의 -- 그 직원의 입사 월, 일 로 연산을 수행한다. -- 그리고, 보너스는 근무일수에 따라 1000일 이상 2000일 미만인 사원은 -- 해당 사원의 원래 급여 기준 30%를 지급하고, -- 2000일 이상 근무한 사원은 -- 해당 사원의 원래 급여 기준 50%를 지급할 수 있도록 처리한다. SELECT * FROM TBL_SAWON; -- TBL_SAWON 테이블에 존재하는 사원들의 -- 입사일(HIREDATE)컬럼에서 월,일만 조회하기 SELECT SANAME, HIREDATE, TO_CHAR(HIREDATE,'MM-DD') "결과확인" FROM TBL_SAWON; SELECT SANAME, HIREDATE, TO_CHAR(HIREDATE,'MM') || TO_CHAR(HIREDATE,'DD')"결과확인" FROM TBL_SAWON; SELECT SANAME, HIREDATE, TO_CHAR(HIREDATE,'MM') || '-' || TO_CHAR(HIREDATE,'DD')"결과확인" FROM TBL_SAWON; ---------------------------------------------------------------------------------- -- 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일 -- , 정년퇴직일, 근무일수, 남은일수, 급여, 보너스 --① 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일, 급여 SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" -- 성별 , CASE WHEN THEN ELSE END "성별" FROM TBL_SAWON; ------------------------------------------------------------------------------- SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" -- 성별 , CASE WHEN 주민번호 7번째자리 1개가 '1' 또는 '3' THEN '남자' WHEN 주민번호 7번째자리 1개가 '2' 또는 '4' THEN '여자' ELSE '성별확인불가' END "성별" FROM TBL_SAWON; SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" -- 성별 , CASE WHEN SUBSTR(JUBUN,7,1) '1' 또는 '3' THEN '남자' WHEN SUBSTR(JUBUN,7,1) '2' 또는 '4' THEN '여자' ELSE '성별확인불가' END "성별" FROM TBL_SAWON; ------------------------------------------------------------------------------- SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" -- 성별 , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' ELSE '성별확인불가' END "성별" -- 현재나이 = 현재년도 - 태어난년도 + 1 (1900년대 생 / 2000년대 생) , CASE WHEN THEN ELSE END FROM TBL_SAWON; SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" -- 성별 , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' --// 문자형 WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' --// 문자형 ELSE '성별확인불가' --// 문자형 END "성별" -- 현재나이 = 현재년도 - 태어난년도 + 1 (1900년대 생 / 2000년대 생) , CASE WHEN 1900년대 생이라면 THEN 현재년도 - (주민번호 앞 두자리 + 1899) --// 숫자형 WHEN 2000년대 생이라면... THEN 현재년도 - (주민번호 앞 두자리 + 1999) --// 숫자형 ELSE '나이 확인 불가' --CHECK! --// 문자형 END "현재나이" FROM TBL_SAWON; --// 즉 CASE WHEN THEN ELSE END 구문의 경우 THEN, ELSE 의 데이터 타입을 통일해야한다. SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" -- 성별 , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' --// 문자형 WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' --// 문자형 ELSE '성별확인불가' --// 문자형 END "성별" -- 현재나이 = 현재년도 - 태어난년도 + 1 (1900년대 생 / 2000년대 생) , CASE WHEN 1900년대 생이라면 THEN 현재년도 - (주민번호 앞 두자리 + 1899) --// 숫자형 WHEN 2000년대 생이라면... THEN 현재년도 - (주민번호 앞 두자리 + 1999) --// 숫자형 ELSE -1 --CHECK! --// 숫자형 END "현재나이" FROM TBL_SAWON; SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" -- 성별 , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' ELSE '성별확인불가' END "성별" -- 현재나이 = 현재년도 - 태어난년도 + 1 (1900년대 생 / 2000년대 생) , CASE WHEN 주민번호 7번째자리 1개가 '1' 또는 '2' THEN 현재년도 - (주민번호 앞 두자리 + 1899) WHEN 주민번호 7번째자리 1개가 '3' 또는 '4' THEN 현재년도 - (주민번호 앞 두자리 + 1999) ELSE -1 END "현재나이" FROM TBL_SAWON; SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" -- 성별 , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' ELSE '성별확인불가' END "성별" -- 현재나이 = 현재년도 - 태어난년도 + 1 (1900년대 생 / 2000년대 생) , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899) --// 날짜 타입을 TO_DATE()로 쓰면 내부적으로 오류 날 가능성 높음 //T0_NUMBER(TO_CHAR(SYSDATE,'YYYY')) 도 가능 WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999) ELSE -1 END "현재나이" -- 입사일 --//날짜 세션과 상관없이 그대로 고정되게 보이게 하고 싶으면 TO_DATE(TO_CHAR(날짜형식데이터,'YYYY-MM-DD')) 와 같이 처리해두는 것이 좋다. , HIREDATE "입사일" -- 급여 , SAL "급여" FROM TBL_SAWON; --==>> /* 1001 박혜서 9910102234567 여성 25 2001-01-03 3000 1002 유서영 9902242234567 여성 25 2010-11-05 2000 1003 최이서 9403302234567 여성 30 1999-08-16 5000 1004 양연수 9910132234567 여성 25 2008-02-02 4000 1005 양수현 9907092234567 여성 25 2009-07-15 2000 1006 주수인 9604052234567 여성 28 2009-07-15 2000 1007 이기배 9806261234567 남성 26 2009-07-15 2000 1008 이준복 9607161234567 남성 28 2010-06-05 1000 1009 이하이 0204054234567 여성 22 2012-12-10 1000 1010 이이제 0503243234567 남성 19 2015-03-01 1500 1011 선동열 7505071234567 남성 49 1998-03-01 3000 1012 선우용녀 7102032234567 여성 53 1995-03-01 2000 1013 남주혁 0607083234567 남성 18 2015-03-01 4000 1014 남궁이 0512153234567 남성 19 2020-05-06 3000 1015 선우선 0102094234567 여성 23 2010-03-01 2000 1016 남진 7309231234567 남성 51 1996-03-01 4000 */
- 나머지 아래 내용은 기존 구한 데이터를 바탕으로 구하면 쉽게 구할 수 있다.
② 정년퇴직일, 근무일수, 남은일수, 보너스
해당 방법을 알아보자
※ 서브쿼리, 뷰, 인라인 뷰 의 개념
SELECT SANO "사원번호", SANAME "사원명", SAL"급여", SAL*12 "연봉", 연봉*2 "연봉두배" FROM TBL_SAWON; --==>> 에러 발생 --// ALIAS(별칭) 를 끌어다 쓰는 것은 불가능하다. /* ORA-00904: "연봉": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: 160행, 79열에서 오류 발생 */ -- 생략된 구문 SELECT TBL_SAWON.SANO FROM TBL_SAWON; SELECT T.사원번호 --// T.* 도 가능 // 이 T라는테이블에는 SANO 등의 컬럼은 존재하지않게 됨 FROM ( SELECT SANO "사원번호", SANAME "사원명", SAL"급여", SAL*12 "연봉" FROM TBL_SAWON ) T; --// 이런 모양의 테이블 "T"(내가 명명하는이름) (★뷰★) 로 보겠다 하는 것 --// 쿼리 문 안의 또다른 쿼리문의 형태의 서브쿼리가 있고, 그게 FROM, WHERE, SELECT 들어갈 수 있는데 FROM 에 들어가게 되면 인라인뷰라고 한다. --// 인라인 뷰(라고 하는 이유): 물리적인 형태로 하드디스크에 저장X, 가상으로 쿼리를 존재하게 보이게 하는 것(객체) --// 세미콜론 위치 중요 // 쿼리문 안의 또다른 쿼리문 : "서브 쿼리" SELECT T.사원번호, T.사원명, T.급여, T.연봉, T.연봉*2 "두배연봉" FROM ( SELECT SANO "사원번호", SANAME "사원명", SAL"급여", SAL*12 "연봉" FROM TBL_SAWON ) T;
더보기-- 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일 -- , 정년퇴직일, 근무일수, 남은일수, 급여, 보너스 SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일 -- 정년퇴직일 -- 정년퇴직년도 || '-' || 입사월일 -- ------------ -- → 해당 직원의 나이가 한국나이로 60세가 되는 해 -- 현재나이가... 58세... 2년 후 2023 → 2025 -- 현재나이가... 35세... 25년 후 2023 → 2048 -- ADD_MONTHS(SYSDATE, 남은년수 * 12) -- -------- -- → (60 - 현재나이) -- ADD_MONTHS(SYSDATE, (60 - 현재나이) * 12) -- TO_CHAR(ADD_MONTHS(SYSDATE, (60 - 현재나이) * 12), 'YYYY') → 연도만 추출 → 정년퇴직년도 -- TO_CHAR(입사일 ,'MM-DD') → 월일만 추출 → 정년퇴직월일 -- TO_CHAR(ADD_MONTHS(SYSDATE, (60 - 현재나이) * 12), 'YYYY') || '-' || TO_CHAR(입사일 ,'MM-DD') "정년퇴직일" , TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일 ,'MM-DD') "정년퇴직일" -- 근무일수 -- 근무일수 = 현재일 - 입사일 , TRUNC(SYSDATE - T.입사일) "근무일수" -- 남은일수 -- 남은일수 = 정년퇴직일 - 현재일 , TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일 ,'MM-DD'),'YYYY-MM-DD') - SYSDATE) "남은일수" --급여 , T.급여 -- 보너스 -- 근무일수가 1000일 이상 2000일 미만인 → 기존 급여의 30% -- 근무일수가 2000일 이상 → 기존 급여의 50% -- 나머지 → 0 --, CASE WHEN 근무일수 >= 2000 THEN 급여 * 0.5 -- WHEN 근무일수 >= 1000 THEN 급여 * 0.3 -- ELSE 0 -- END "보너스" , CASE WHEN TRUNC(SYSDATE - T.입사일) >= 2000 THEN T.급여 * 0.5 WHEN TRUNC(SYSDATE - T.입사일) >= 1000 THEN T.급여 * 0.3 ELSE 0 END "보너스" FROM ( SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" -- 성별 , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' ELSE '성별확인불가' END "성별" -- 현재나이 = 현재년도 - 태어난년도 + 1 (1900년대 생 / 2000년대 생) , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899) WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999) ELSE -1 END "현재나이" -- 입사일 , HIREDATE "입사일" -- 급여 , SAL "급여" FROM TBL_SAWON ) T;
--○ 최종 쿼리문 SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일 , TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일 ,'MM-DD') "정년퇴직일" , TRUNC(SYSDATE - T.입사일) "근무일수" , TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일 ,'MM-DD'),'YYYY-MM-DD') - SYSDATE) "남은일수" , T.급여 , CASE WHEN TRUNC(SYSDATE - T.입사일) >= 2000 THEN T.급여 * 0.5 WHEN TRUNC(SYSDATE - T.입사일) >= 1000 THEN T.급여 * 0.3 ELSE 0 END "보너스" FROM ( SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' ELSE '성별확인불가' END "성별" , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899) WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999) ELSE -1 END "현재나이" , HIREDATE "입사일" , SAL "급여" FROM TBL_SAWON ) T; -- 상기 내용에서.... -- 특정 근무일수의 사원을 확인해야 한다거나... -- 특정 보너스 금액을 바든 사원을 확인해야 한다거나... 하는 -- 추가 업무 요소가 발생될 수 있다. -- 이와 같은 경우... 해당 쿼리문을 처음부터 다시 구성해야 하는 번거로움을 -- 줄일 수 있도록 뷰(VIEW) 를 생성하여 저장해 둘 수 있다. CREATE OR REPLACE VIEW VIEW_TEST --// 기존에 있는 테이블명을 사용할 경우 에러나게 되는데(기존의 테이블과 연결된 다른 테이블들에서도 난리나게 되기 때문에) --// 뷰의 경우에는 기존 뷰를 제거하고 새로 만들러도 상관없기 때기 때문에 OR REPLACE 를 같이 사용(라이트하게 편하게 사용하게 하기 위해서 제공해주는 거기 때문) AS SELECT EMPNO, ENAME, JOB FROM TBL_EMP WHERE JOB = 'SALESMAN'; --==>> 에러발생 /* ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to perform a database operation without the necessary privileges. *Action: Ask your database administrator or designated security administrator to grant you the necessary privileges */ --> 권한 불충분 에러
- SYS 계정에서 SCOTT 계정에 CREATE VIEW 권한 부여
더보기SELECT USER FROM DUAL; --==>> SYS --○ SCOTT 계정에 VIEW 를 생성할 수 있는 권한(CREATE VIEW) 부여 GRANT CREATE VIEW TO SCOTT; --==>> Grant을(를) 성공했습니다.
-- SYS 로 부터 CREATE VIEW 권한을 부여받은 후 다시 실행 CREATE OR REPLACE VIEW VIEW_TEST AS SELECT EMPNO, ENAME, JOB FROM TBL_EMP WHERE JOB = 'SALESMAN'; --==>> View VIEW_TEST이(가) 생성되었습니다. SELECT * FROM VIEW_TEST; --// VIEW_TEST 해당 쿼리문이 돌아가게 되는것! SELECT * FROM ( SELECT EMPNO, ENAME, JOB FROM TBL_EMP WHERE JOB = 'SALESMAN' );
더보기CREATE OR REPLACE VIEW VIEW_SAWON AS SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일 , TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일 ,'MM-DD') "정년퇴직일" , TRUNC(SYSDATE - T.입사일) "근무일수" , TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일 ,'MM-DD'),'YYYY-MM-DD') - SYSDATE) "남은일수" , T.급여 , CASE WHEN TRUNC(SYSDATE - T.입사일) >= 2000 THEN T.급여 * 0.5 WHEN TRUNC(SYSDATE - T.입사일) >= 1000 THEN T.급여 * 0.3 ELSE 0 END "보너스" FROM ( SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호" , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' ELSE '성별확인불가' END "성별" , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899) WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999) ELSE -1 END "현재나이" , HIREDATE "입사일" , SAL "급여" FROM TBL_SAWON ) T; --==>> View VIEW_SAWON이(가) 생성되었습니다.
- VIEW 생성 이후 데이터 추가 하여 관찰
SELECT * FROM TBL_SAWON; --○ VIEW 생성 이후 데이터 변경 UPDATE TBL_SAWON SET HIREDATE=SYSDATE, SAL=100 WHERE SANO = 1001; --==>> 1 행 이(가) 업데이트되었습니다. --○ 확인 SELECT * FROM TBL_SAWON; --==>> 변경 확인 --○ 커밋 COMMIT; --==>> 커밋 완료. SELECT * FROM TBL_SAWON; -- 변경전 T : 1001 박혜서 9910102234567 2001-01-03 3000 -- 변경후 T : 1001 박혜서 9910102234567 2023-03-28 100 SELECT * FROM VIEW_SAWON; -- 변경전 V : 1001 박혜서 9910102234567 여성 25 2001-01-03 2058-01-03 ? ????? 3000 1500 -- 변경후 V : 1001 박혜서 9910102234567 여성 25 2023-03-28 2058-03-28 0 12783 100 0
--○ 서브쿼리를 활용하여 -- TBL_SAWON 테이블을 다음과 같이 조회할 수 있도록 한다. /* --------------------------------------------------- 사원명 성별 현재나이 급여 나이보너스 --------------------------------------------------- */ -- 단, 나이 보너스는 현재 나이가 40 세 이상이면 급여의 70% -- 30세 이상 40세 미만이면 급여의 50% -- 20세 이상 30세 미만이면 급여의 30%로 한다. -- 또한, 완성된 조회 구문을 기반으로 -- VIEW_SAWON2 라는 이름의 뷰(VIEW)를 생성한다.
더보기-- 내풀이 SELECT * FROM TBL_SAWON; SELECT SANAME "사원명" , CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여성' ELSE '판별불가' END"성별" , EXTRACT(YEAR FROM SYSDATE) - CASE WHEN SUBSTR(JUBUN,7,1) IN('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899 WHEN SUBSTR(JUBUN,7,1) IN('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999 ELSE -1 END "현재나이" , SAL "급여" FROM TBL_SAWON; SELECT T.사원명, T.성별 , T.현재나이, T.급여 , CASE WHEN T.현재나이 >= 40 THEN T.급여 * 0.7 WHEN T.현재나이 >= 30 THEN T.급여 * 0.5 WHEN T.현재나이 >= 20 THEN T.급여 * 0.3 ELSE 0 END "나이보너스" FROM ( SELECT SANAME "사원명" , CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여성' ELSE '판별불가' END"성별" , EXTRACT(YEAR FROM SYSDATE) - CASE WHEN SUBSTR(JUBUN,7,1) IN('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899 WHEN SUBSTR(JUBUN,7,1) IN('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999 ELSE -1 END "현재나이" , SAL "급여" FROM TBL_SAWON ) T; CREATE OR REPLACE VIEW VIEW_SAWON2 AS SELECT T.사원명, T.성별 , T.현재나이, T.급여 , CASE WHEN T.현재나이 >= 40 THEN T.급여 * 0.7 WHEN T.현재나이 >= 30 THEN T.급여 * 0.5 WHEN T.현재나이 >= 20 THEN T.급여 * 0.3 ELSE 0 END "나이보너스" FROM ( SELECT SANAME "사원명" , CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여성' ELSE '판별불가' END"성별" , EXTRACT(YEAR FROM SYSDATE) - CASE WHEN SUBSTR(JUBUN,7,1) IN('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899 WHEN SUBSTR(JUBUN,7,1) IN('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999 ELSE -1 END "현재나이" , SAL "급여" FROM TBL_SAWON ) T; --==>> View VIEW_SAWON2이(가) 생성되었습니다. SELECT * FROM VIEW_SAWON2;
SELECT SANAME "사원명" , CASE WHEN THEN ELSE END "성별" , CASE WHEN THEN ELSE END "현재나이" , SAL "급여" FROM TBL_SAWON; SELECT SANAME "사원명" , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' ELSE '성별확인불가' END "성별" , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899) WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999) ELSE 0 END "현재나이" , SAL "급여" FROM TBL_SAWON; SELECT T.* , CASE WHEN T.현재나이 >= 40 THEN T.급여 * 0.7 WHEN T.현재나이 >= 30 THEN T.급여 * 0.5 WHEN T.현재나이 >= 20 THEN T.급여 * 0.3 ELSE 0 END "나이보너스" FROM ( SELECT SANAME "사원명" , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' ELSE '성별확인불가' END "성별" , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899) WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999) ELSE 0 END "현재나이" , SAL "급여" FROM TBL_SAWON ) T; --==>> /* 박혜서 여성 25 100 30 유서영 여성 25 2000 600 최이서 여성 30 5000 2500 양연수 여성 25 4000 1200 양수현 여성 25 2000 600 주수인 여성 28 2000 600 이기배 남성 26 2000 600 이준복 남성 28 1000 300 이하이 여성 22 1000 300 이이제 남성 19 1500 0 선동열 남성 49 3000 2100 선우용녀 여성 53 2000 1400 남주혁 남성 18 4000 0 남궁이 남성 19 3000 0 선우선 여성 23 2000 600 남진 남성 51 4000 2800 */ CREATE OR REPLACE VIEW VIEW_SAWON2 AS SELECT T.* , CASE WHEN T.현재나이 >= 40 THEN T.급여 * 0.7 WHEN T.현재나이 >= 30 THEN T.급여 * 0.5 WHEN T.현재나이 >= 20 THEN T.급여 * 0.3 ELSE 0 END "나이보너스" FROM ( SELECT SANAME "사원명" , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성' ELSE '성별확인불가' END "성별" , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899) WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999) ELSE 0 END "현재나이" , SAL "급여" FROM TBL_SAWON ) T; --==>> View VIEW_SAWON2이(가) 생성되었습니다. --○ 생성된 뷰(VIEW_SAWON2) 조회 → 확인 SELECT * FROM VIEW_SAWON2;
- RANK()
- 등수(순위) 를 반환하는 함수
- RANK() OVER(ORDER 절(BY 오름/내림 차순))
--○ RANK() → 등수(순위)를 반환하는 함수 SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", SAL "급여" , RANK() OVER(ORDER BY SAL DESC) "전체급여순위" FROM EMP; --==>> /* 7839 KING 10 5000 1 7902 FORD 20 3000 2 7788 SCOTT 20 3000 2 7566 JONES 20 2975 4 7698 BLAKE 30 2850 5 7782 CLARK 10 2450 6 7499 ALLEN 30 1600 7 7844 TURNER 30 1500 8 7934 MILLER 10 1300 9 7521 WARD 30 1250 10 7654 MARTIN 30 1250 10 7876 ADAMS 20 1100 12 7900 JAMES 30 950 13 7369 SMITH 20 800 14 */ SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", SAL "급여" , RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "부서내급여순위" --// 파티션 : 논리적인 공간 분할 , RANK() OVER(ORDER BY SAL DESC) "전체급여순위" FROM EMP; --==>> /* 7839 KING 10 5000 1 1 7902 FORD 20 3000 1 2 7788 SCOTT 20 3000 1 2 7566 JONES 20 2975 3 4 7698 BLAKE 30 2850 1 5 7782 CLARK 10 2450 2 6 7499 ALLEN 30 1600 2 7 7844 TURNER 30 1500 3 8 7934 MILLER 10 1300 3 9 7521 WARD 30 1250 4 10 7654 MARTIN 30 1250 4 10 7876 ADAMS 20 1100 4 12 7900 JAMES 30 950 6 13 7369 SMITH 20 800 5 14 */ SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", SAL "급여" , RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "부서내급여순위" , RANK() OVER(ORDER BY SAL DESC) "전체급여순위" FROM EMP ORDER BY 3,4 DESC; --==>> /* 7839 KING 10 5000 1 1 7782 CLARK 10 2450 2 6 7934 MILLER 10 1300 3 9 7902 FORD 20 3000 1 2 7788 SCOTT 20 3000 1 2 7566 JONES 20 2975 3 4 7876 ADAMS 20 1100 4 12 7369 SMITH 20 800 5 14 7698 BLAKE 30 2850 1 5 7499 ALLEN 30 1600 2 7 7844 TURNER 30 1500 3 8 7654 MARTIN 30 1250 4 10 7521 WARD 30 1250 4 10 7900 JAMES 30 950 6 13 */
- DENSE_RANK()
- 서열을 반환하는 함수
- RANK 와는 달리, 공동 2등이 2명 있어도 다음은 3등으로 표현 됨
--○ DENSE_RANK() → 서열을 반환하는 함수 SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", SAL "급여" , DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "부서내급여순위" , DENSE_RANK() OVER(ORDER BY SAL DESC) "전체급여순위" FROM EMP ORDER BY 3,4 DESC; --==>> /* 7839 KING 10 5000 1 1 7782 CLARK 10 2450 2 5 7934 MILLER 10 1300 3 8 7902 FORD 20 3000 1 2 7788 SCOTT 20 3000 1 2 7566 JONES 20 2975 2 3 7876 ADAMS 20 1100 3 10 7369 SMITH 20 800 4 12 7698 BLAKE 30 2850 1 4 7499 ALLEN 30 1600 2 6 7844 TURNER 30 1500 3 7 7654 MARTIN 30 1250 4 9 7521 WARD 30 1250 4 9 7900 JAMES 30 950 5 11 */
--○ EMP 테이블의 사원 데이터를 -- 사원명, 부서번호, 연봉, 부서내연봉순위, 전체연봉순위 항목으로 조회한다. -- 단, 급여(SAL)는 매월 지급하며 수당(COMM)은 연 1회 지급한다. SELECT * FROM EMP; -- 1번) SELECT ENAME "사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0) "연봉" , RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL*12+NVL(COMM,0) DESC) "부서내연봉순위" , RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) " 전체연봉순위" FROM EMP ORDER BY 2, 3 DESC; --==>> /* 사원명 부서번호 연봉 부서내연봉순위 전체연봉순위 ---------- ---------- ---------- ---------- ---------- KING 10 60000 1 1 CLARK 10 29400 2 6 MILLER 10 15600 3 10 FORD 20 36000 1 2 SCOTT 20 36000 1 2 JONES 20 35700 3 4 ADAMS 20 13200 4 12 SMITH 20 9600 5 14 BLAKE 30 34200 1 5 ALLEN 30 19500 2 7 TURNER 30 18000 3 8 MARTIN 30 16400 4 9 WARD 30 15500 5 11 JAMES 30 11400 6 1 */ -- 2번) SELECT ENAME "사원명" , DEPTNO "부서번호" , SAL*12 + NVL(COMM,0) "연봉" FROM EMP; SELECT T.* , RANK() OVER(PARTITION BY T.부서번호 ORDER BY T.연봉 DESC) "부서내연봉순위" , RANK() OVER(ORDER BY T.연봉 DESC) "전체연봉순위" FROM ( SELECT ENAME "사원명" , DEPTNO "부서번호" , SAL*12 + NVL(COMM,0) "연봉" FROM EMP )T ORDER BY 2,3 DESC;
--○ EMP 테이블에서 전체연봉순위가 1등부터 5등까지만.. -- 사원명, 부서번호, 연봉, 전체연봉순위 항목으로 조회한다. SELECT 사원명, 부서번호, 연봉, 전체연봉순위 FROM EMP WHERE 전체연봉순위가 1등부터 5등; SELECT ENAME "사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0) "연봉" , RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위" FROM EMP WHERE RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) <= 5; --==>> 에러 발생 /* ORA-30483: window functions are not allowed here 30483. 00000 - "window functions are not allowed here" *Cause: Window functions are allowed only in the SELECT list of a query. And, window function cannot be an argument to another window or group function. *Action: 766행, 37열에서 오류 발생 */ -- ※ 위의 내용은 RANK() OVER() 함수를 WHERE 조건절에서 사용한 경우이며 -- 이 함수는 WHERE 조건절에서 사용할 수 없기 때문에 발생하는 에러이다. -- 이 경우, 우리는 INLINE VIEW 를 활용하여 해결해야 한다. SELECT T.* FROM ( SELECT ENAME "사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0) "연봉" , RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위" FROM EMP )T WHERE T.전체연봉순위 <=5 ; /* KING 10 60000 1 SCOTT 20 36000 2 FORD 20 36000 2 JONES 20 35700 4 BLAKE 30 34200 5 */
--○ EMP 테이블에서 각 부서별로 연봉 순위가 1등부터 2등까지만 조회한다. -- 사원번호, 사원명, 부서번호, 연봉, 부서내연봉등수, 전체연봉등수 항목을 조회한다. SELECT * FROM EMP; SELECT 사원번호, 사원명, 부서번호, 연봉, 부서내연봉등수, 전체연봉등수 FROM EMP WHERE 부서별로 연봉 순위가 1등부터 2등; SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호" , SAL*12 + NVL(COMM,0) "연봉" FROM EMP; SELECT T.* , DENSE_RANK() OVER(PARTITION BY T.부서번호 ORDER BY T.연봉 DESC) "부서내연봉등수" , RANK() OVER(ORDER BY T.연봉 DESC) "전체연봉등수" FROM ( SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호" , SAL*12 + NVL(COMM,0) "연봉" FROM EMP ) T ; SELECT S.* FROM ( SELECT T.* , DENSE_RANK() OVER(PARTITION BY T.부서번호 ORDER BY T.연봉 DESC) "부서내연봉등수" , RANK() OVER(ORDER BY T.연봉 DESC) "전체연봉등수" FROM ( SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호" , SAL*12 + NVL(COMM,0) "연봉" FROM EMP ) T ) S WHERE S.부서내연봉등수 <=2 ;
- 그룹함수
SUM() 합, AVG() 평균, COUNT() 카운트, MAX() 최대값, MIN() 최소값, VARIANCE() 분산, STDDEV() 표준편차
- 데이터 가운데 NULL 값이 존재하면, NULL 값을 제외하고 연산을 수행
(편리하나 주의점도 있으니 유의하고 사용할 것)
- SUM()
-- SUM() -- EMP 테이블을 대상으로 전체 사원들의 급여 총합을 조회한다. SELECT SAL FROM EMP; --==>> /* 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 */ SELECT SUM(SAL) FROM EMP; --==>> 29025 SELECT COMM FROM EMP; --==>> /* (null) 300 500 (null) 1400 (null) (null) (null) (null) 0 (null) (null) (null) (null) */ SELECT SUM(COMM) -- (null) + 300 + 500 + (null) + ... + (null) 이 아니라 FROM EMP; -- 300 + 500 + 1400 + 0 으로 계산 된 것 --==>> 2200
- COUNT()
- 행의 갯수를 카운팅하여 결과값 반환
※ (null) 값을 건너 뛰기 때문에 목적성을 갖고 있지 않다면 COUNT(*) 로 사용
-- COUNT() -- 행의 갯수를 카운팅하여 결과값 반환 SELECT COUNT(ENAME) FROM EMP; --==>> 14 SELECT COUNT(COMM) FROM EMP; --==>> 4 SELECT COUNT(*) FROM EMP; --==>> 14
- AVG()
- 평균 반환
※ NULL 값을 건너뜀으로 인해 오차가 발생할 수 있음. 주의!
-- AVG() -- 평균 반환 SELECT SUM(SAL) / COUNT(SAL) FROM EMP; --==>> 2073.214285714285714285714285714285714286 SELECT AVG(SAL) FROM EMP; --==>> 2073.214285714285714285714285714285714286 SELECT AVG(COMM) FROM EMP; --==>> 550 SELECT SUM(COMM) / COUNT(COMM) FROM EMP; --==>> 550 --// 커미션을 수령하는 사람들의 평균값임. SELECT SUM(COMM) / COUNT(*) FROM EMP; --==>> 157.142857142857142857142857142857142857 --// 전체 직원들의 커미션의 평균 값
- AVG(), STDDEV() , VARIANCE()
평균, 표준편차, 분산
--※ 표준편차의 제곱이 분산 -- 분산의 제곱근이 표준편차 SELECT AVG(SAL), VARIANCE(SAL), STDDEV(SAL) FROM EMP; --==>> /* 2073.214285714285714285714285714285714286 1398313.87362637362637362637362637362637 1182.503223516271699458653359613061928508 */ SELECT POWER(STDDEV(SAL),2), VARIANCE(SAL) FROM EMP; --==>> /* 1398313.87362637362637362637362637362637 1398313.87362637362637362637362637362637 */ SELECT SQRT(VARIANCE(SAL)), STDDEV(SAL) FROM EMP; --==>> /* 1182.503223516271699458653359613061928508 1182.503223516271699458653359613061928508 */
- MAX(), MIN()
-- MAX(), MIN() -- 최대값 / 최소값 반환 SELECT MAX(SAL), MIN(SAL) FROM EMP; --==>> 5000 800
- 그룹함수 주의점
- ROLLUP()
더보기--※ 주의 SELECT ENAME, SAL FROM EMP; --==>> /* SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 */ SELECT ENAME, SUM(SAL) FROM EMP; --==>> 에러발생 /* ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" *Cause: *Action: 1,012행, 8열에서 오류 발생 */ SELECT DEPTNO, SUM(SAL) FROM EMP; --==>> 에러 발생 /* ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" *Cause: *Action: 1,023행, 8열에서 오류 발생 */ SELECT DEPTNO "부서번호" , SUM(SAL) "급여합" FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO; --==>> /* 부서번호 급여합 ---------- ---------- 10 8750 20 10875 30 9400 */ SELECT DEPTNO "부서번호" , SUM(SAL) "급여합" FROM EMP GROUP BY ROLLUP(DEPTNO); --==>> /* 10 8750 20 10875 30 9400 (null) 29025 -- 모든 부서 급여 합 */
SELECT * FROM TBL_EMP; --○ 데이터 추가 입력 INSERT INTO TBL_EMP VALUES (8001, 'ㅊㅇㅅ', 'CLERK', 7566, SYSDATE, 1500, 10, NULL); INSERT INTO TBL_EMP VALUES (8002, 'ㅇㅅㅇ', 'CLERK', 7566, SYSDATE, 1000, 0, NULL); INSERT INTO TBL_EMP VALUES (8003, 'ㅊㅎㄱ', 'SALESMAN', 7698, SYSDATE, 2000, NULL, NULL); INSERT INTO TBL_EMP VALUES (8004, 'ㅇㅇㅅ', 'SALESMAN', 7698, SYSDATE, 2500, NULL, NULL); INSERT INTO TBL_EMP VALUES (8005, 'ㅇㅎㅈ', 'SALESMAN', 7698, SYSDATE, 1000, NULL, NULL); --==>> 1 행 이(가) 삽입되었습니다. * 5 --○ 확인 SELECT * FROM TBL_EMP; --==>> /* 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-07-13 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 8001 ㅊㅇㅅ CLERK 7566 2023-03-28 1500 10 8002 ㅇㅅㅇ CLERK 7566 2023-03-28 1000 0 8003 ㅊㅎㄱ SALESMAN 7698 2023-03-28 2000 8004 ㅇㅇㅅ SALESMAN 7698 2023-03-28 2500 8005 ㅇㅎㅈ SALESMAN 7698 2023-03-28 1000 */ --○ 커밋 COMMIT; --==>> 커밋 완료. SELECT DEPTNO "부서번호", SUM(SAL) "급여합" FROM TBL_EMP GROUP BY DEPTNO; --==>> /* 30 9400 8000 20 10875 10 8750 */ SELECT DEPTNO "부서번호", SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> /* 10 8750 20 10875 30 9400 8000 -- 부서번호가 NULL 인 데이터들끼리의 급여 합 37025 -- 모든 부서의 급여 합 */ -- 위에서 조회한 내용을 아래와 같이 조회될 수 있도록 쿼리문을 구성한다. /* 부서번호 급여합 ----------- --------- 10 8750 20 10875 30 9400 인턴 8000 모든부서 37025 */ DESC TBL_EMP; SELECT TO_CHAR(NVL(DEPTNO,-1))"부서번호", SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); SELECT CASE WHEN TO_CHAR(DEPTNO) IS NOT NULL THEN TO_CHAR(DEPTNO) WHEN TO_CHAR(DEPTNO) IS NULL THEN '인턴' ELSE '모든부서' END "부서번호" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> /* 10 8750 20 10875 30 9400 인턴 8000 인턴 37025 */ SELECT CASE WHEN T.부서번호 IS NULL THEN '모든부서' ELSE T.부서번호 END "부서번호" , SUM(T.급여) "급여합" FROM ( SELECT SAL"급여", CASE WHEN TO_CHAR(DEPTNO) IS NOT NULL THEN TO_CHAR(DEPTNO) WHEN TO_CHAR(DEPTNO) IS NULL THEN '인턴' ELSE '모든부서' END "부서번호" FROM TBL_EMP )T GROUP BY ROLLUP(T.부서번호); --==>> /* 부서번호 급여합 ---------------------------------------- ---------- 10 8750 20 10875 30 9400 인턴 8000 모든부서 3702 */
728x90'SsY > Class' 카테고리의 다른 글
- 어제 이어서..