1. emp 테이블에서 30번 부서만 PAY를 계산 후 막대그래프를 아래와 같이 그리는 쿼리 작성
( 필요한 부분은 결과 분석하세요~ PAY가 100 단위당 # 한개 , 반올림처리 )
[실행결과]
DEPTNO ENAME PAY BAR_LENGTH
---------- ---------- ---------- ----------
30 BLAKE 2850 29 #############################
30 MARTIN 2650 27 ###########################
30 ALLEN 1900 19 ###################
30 WARD 1750 18 ##################
30 TURNER 1500 15 ###############
30 JAMES 950 10 ##########
SELECT deptno, ename, sal + NVL( comm, 0 ) pay
, ROUND ( ( sal + NVL(comm, 0)) / 100 ) BAR_LENGTH
, RPAD(' ', ROUND ( ( sal + NVL(comm, 0)) / 100 )+1, '#' ) bar
FROM emp
WHERE deptno = 30
ORDER BY pay DESC;
--+1 왜 했는가
RPAD
1) 기능
2) 매개변수 ROUND ( ( sal + NVL(comm, 0)) / 100 ) 전체 자리수
3) 반환값
1-2. insa 테이블에서 주민등록번호를 123456-1****** 형식으로 출력하세요 .
( LPAD, RPAD 함수 사용 )
[실행결과]
홍길동 770423-1022432 770423-1******
이순신 800423-1544236 800423-1******
이순애 770922-2312547 770922-2******
SELECT name, ssn
-- ,SUBSTR (ssn, 0, 8)
, RPAD(SUBSTR (ssn, 0, 8), 14, '*' )
, SUBSTR( ssn, 0, 8) || '******'
-- , CONCAT
FROM insa;
2. SELECT TRUNC( SYSDATE, 'YEAR' )
, TRUNC( SYSDATE, 'MONTH' )
, TRUNC( SYSDATE )
FROM dual;
위의 쿼리의 결과를 적으세요 .
ㄱ. TRUNC() 함수 이해
-특정 위치에서 절삭 / ( FLOOR() 함수와 차이점 )
TRUNC ( 숫자 ) 절삭
TRUNC( 날짜 ) 절삭***
ㄴ. SYSDATE 함수 - 현재 시스템의 날짜 + 시간을 반환하는 함수
SELECT SYSDATE -- '23/03/20'
, TRUNC ( SYSDATE, 'YEAR' ) -- '23', '23/01/01'
, TRUNC ( SYSDATE, 'MONTH' ) -- '23/03' '23/03/01'
, TRUNC ( SYSDATE ) --'23/03/20 00:00:00' 시간 분 초 절삭됨
FROM dual;
3. emp 에서 평균PAY 보다 같거나 큰 사원들만의 급여합을 출력.
[실행결과]
NAME SAL COMM PAY AVG_PAY
---------- ---------- ---------- ---------- ----------
JONES 2975 2975 2260.41667
MARTIN 1250 1400 2650 2260.41667
BLAKE 2850 2850 2260.41667
CLARK 2450 2450 2260.41667
KING 5000 5000 2260.41667
FORD 3000 3000 2260.41667
WITH
SELECT ename, sal, comm, sal + NVL(comm, 0) pay
-- , ROUND ( AVG( sal + NVL(comm, 0) ), 5 ) avg_pay
,()
FROM emp
SELECT ROUND ( AVG( sal + NVL(comm, 0) ), 5 ) avg_pay
, SUM( sal + NVL(comm, 0) ) tot_pay
, COUNT(*) tot_cnt
, SUM( sal + NVL(comm, 0) / count(*), 5 ) avg_pay
FROM emp;
4. emp 테이블에서 각 부서별 급여를 가장 많이 받는 사원의 pay를 출력
풀이 방법 1) 순위( RANK ) 함수 사용
풀이 방법 2) TOP-N 방식
--
1) UNION SET 연산자 사용
--(문제점) AND 조건절 추가
10 최고급여자
UNION
20 최고급여자
UNION
30 최고급여자
UNION
40 최고급여자
2) 상관서브쿼리 사용
SELECT *
FROM emp a
WHERE a.sal + NVL(a.comm, 0) = ( --( 같은 부서의 최고급액과 같다면 )
SELECT MAX(b.sal + NVL(b.comm, 0)) max_pay
FROM emp b
WHERE b.deptno = a.deptno
);
5. 이번 달이 몇일 까지 있는 지 출력하세요.
(LAST_DAY 함수)
1) 로직: '23/04/01' - 하루 '23/03/마지막 날짜' -일만 얻어오면
SELECT SYSDATE
, ADD_MONTHS(SYSDATE, 1) ㄱ--1달 더하기
, TRUNC( ADD_MONTHS(SYSDATE, 1), 'MONTH') ㄴ --'23/04/01'
-- 날짜 - 날짜 = 일수
-- 날짜 + 일수 = 날짜
-- 날짜 - 일수 = 날짜
-- 날짜 + 수/24 = 날짜
, TRUNC( ADD_MONTHS(SYSDATE, 1), 'MONTH') -1 ㄷ
, TO_CHAR( TRUNC ( ADD_MONTHS(SYSDATE, 1), 'MONTH') -1, 'DD' ) ㄹ
FROM dual;
2) LAST_DAY 함수 사용해서 처리
SELECT TO_CHAR ( LAST_DAY(SYSDATE), 'DD') ㄹ
FROM dual;
6. 다음 주 월요일은 휴강일이다.. 몇 일인가요 ?
(NEXT_DAY) 함수
SELECT SYSDATE
, NEXT_DAY( SYSDATE, '월요일')
FROM dual;
7. emp 테이블에서
각 사원들의 입사일자를 기준으로 10년 5개월 20일째 되는 날 ?
[실행결과]
HIREDATE ADD_MONT
-------- --------
80/12/17 91/06/06
81/02/20 91/08/12
81/02/22 91/08/14
81/04/02 91/09/22
81/09/28 92/03/18
81/05/01 91/10/21
81/06/09 91/11/29
81/11/17 92/05/07
81/09/08 92/02/28
81/12/03 92/05/23
81/12/03 92/05/23
82/01/23 92/07/12
12개 행이 선택되었습니다.
-- 10년 5개월 20일째
SELECT hiredate
-- , hiredate + 20
-- , ADD_MONTHS( hiredate, 5 )
-- , ADD_MONTHS( hiredate, 5 ) + 20
,ADD_MONTHS( hiredate, 12*10 + 5 ) + 20
FROM emp;
8. insa 테이블에서
사원번호(num) 가 1002 인 사원의 주민번호의 월,일만을 오늘날짜로 수정하세요.
ssn = '80XXXX-1544236'
--1002 '80XXXX-154423' -> ssn = '800320-1544236'
SELECT num, ssn
FROM insa
WHERE num = 1002;
--오늘 날짜에서 '0320'월일만 필요
SELECT SYSDATE --23/03/20
, TO_CHAR( SYSDATE, 'YYYY' ) year
, TO_CHAR( SYSDATE, 'MM' ) month
, TO_CHAR( SYSDATE, 'DD' ) "DATE"
, TO_CHAR( SYSDATE, 'MMDD' ) md
, TO_CHAR( SYSDATE, 'DAY' ) day
FROM dual;
--수정
UPDATE insa
-- 80 03 20 -1544236
SET ssn = SUBSTR( ssn, 0, 2) || TO_CHAR (SYSDATE, 'MM') || TO_CHAR (SYSDATE, 'DD')|| SUBSTR(ssn, -8)
SET ssm = SUBSTR( ssn, 0, 2) || TO_CHAR( SYSDATE, 'MMDD') || SUBSTR( ssn, -8 )
WHERE num = 1002;
COMMIT;
8-2. insa 테이블에서 오늘을 기준으로 생일이 지남 여부를 출력하는 쿼리를 작성하세요 .
( '지났다', '안지났다', '오늘 ' 처리 )
SELECT num, name, ssn
-- , SUBSTR( ssn, 0, 6 )
, SUBSTR( ssn, 3, 4 ) --MMDD
, TO_CHAR( SYSDATE, 'MMDD' )
--양수( 1 생일 지나지 않음), 음수( -1 생일 지남 ), 0 ( 오늘 생일 )
, SIGN ( SUBSTR( ssn, 3, 4 ) - TO_CHAR( SYSDATE, 'MMDD' ) ) s
FROM insa;
9. 함수 설명
9-1. NULLIF() 함수 설명
9-2. COALESCE() 함수 설명
9-3. DECODE() 함수 설명
9-4. LAST_DAY() 함수 설명
9-5. ADD_MONTHS() 함수 설명
9-6. MONTHS_BETWEEN() 함수 설명
9-7. NEXT_DAY() 함수 설명
9-8. EXTRACT() 함수 설명
ㄱ. ASCII() 함수 설명
ㄴ. CHR() 함수 설명
ㄷ. GREATEST() 함수 설명
ㄹ. LEAST() 함수 설명
ㅁ. UPPER() 함수 설명
ㅂ. LOWER() 함수 설명
ㅅ. LENGTH() 함수 설명
ㅇ. SUBSTR() 함수 설명
ㅈ. INSTR() 함수 설명
10. emp 테이블의 ename, pay , 최대pay값 5000을 100%로 계산해서
각 사원의 pay를 백분률로 계산해서 10% 당 별하나(*)로 처리해서 출력
( 소숫점 첫 째 자리에서 반올림해서 출력 )
[실행결과]
ename pay max_pay 퍼센트 별갯수
SMITH 800 5000 16% 2 **
ALLEN 1900 5000 38% 4 ****
WARD 1750 5000 35% 4 ****
JONES 2975 5000 59.5% 6 ******
MARTIN 2650 5000 53% 5 *****
BLAKE 2850 5000 57% 6 ******
CLARK 2450 5000 49% 5 *****
KING 5000 5000 100% 10 **********
TURNER 1500 5000 30% 3 ***
JAMES 950 5000 19% 2 **
FORD 3000 5000 60% 6 ******
MILLER 1300 5000 26% 3 ***
SELECT t.*
, (t.pay * 100 ) / t.max_pay || '%' percent
, ROUND( (t.pay * 100 ) / t.max_pay / 10 ) ) 별갯수
, RPAD( ' ', ROUND( (t.pay * 100 ) / t.max_pay / 10 ) +1 , '*' ) BAR
FROM (
SELECT ename, sal + NVL(comm, 0) pay
, ( SELECT MAX( sal + NVL(comm, 0) ) mas_pay FROM emp) max_pay
,
FROM emp
)t;
--------------------------------------------------------------------------------
--수업
SELECT SYSDATE
, CURRENT_DATE
, CURRENT_TIMESTAMP
, EXTRACT( YEAR FROM SYSDATE )
FROM dual;
-- TO_CHAR(숫자 또는 날짜) -> 문자 변환함수
-- TO_TIMESTAMP(문자) -> 날짜 (TIMESTAMP) 변환함수
-- TO_DATE(문자) -> 날짜( DATE) 변환함수
오라클 날짜 자료형: DATE, TIMESTAMP
SELECT TO_TIMESTAMP('2004-8-20 1:30:00', 'YYYY-MM-DD HH:MI:SS')
FROM dual;
-- [변환 함수의 종류]
1) TO_NUMBER() : 문자 -> 숫자로 변환하는 함수
2) TO_CHAR( 숫자 ) : 숫자 -> 문자로 변환
TO_CHAR( 날짜 ) : 날짜 -> 문자로 변환
3) TO_DATE( 문자 ) : 문자 -> 날짜로 변환
4) CONVERT : 문자 한 국가형식 -> 다른 국가 형식 변환
5) HEXTORAW : 16진수 문자 -> 2진수 문자로 변환
--1) TO_NUMBER() : 문자 -> 숫자로 변환하는 함수
SELECT '12' "문자열12" , 12 "숫자12"
, '12' - 12 --자동으로 숫자 12로 형 변환 (자바는 X)
, TO_NUMBER('12') - 12
, 12 - 12
FROM dual;
--오라클 문자(열) 자료형: CHAR, VARCHAR2, NCHAR
SELECT name, ssn
, SUBSTR( ssn, -7, 1 ) gender --'문자'
, MOD( TO_NUMBER( '1' ), 2 )
, MOD( '1' , 2 )
FROM insa;
2)
ㄱ. TO_CHAR( 숫자 ) : 숫자 -> 다양한 형식의 문자로 변환
【형식】
TO_CHAR( n [,'형식 (format)' [,'nlsparam']])
--
SELECT 12345
, TO_CHAR( 12345 ) ㄱ -- '12345'
, TO_CHAR( 12345, '99,999' ) ㄴ -- 세자리마다 콤마 찍어서 출력 '12,345'
, TO_CHAR( 12345, '99,999.00' ) ㄷ -- . 0: 자릿수가 비면 0을 채움 '12,345.00'
, TO_CHAR( 12345, 'L99,999') ㄹ -- L '￦12,345'
, TO_CHAR( 12345, 'S99999') ㅁ -- '+12345'
FROM dual;
--
--sal+NVL(comm,0) *12 연봉
SELECT ename, sal, comm
, TO_CHAR( sal+NVL(comm,0) *12, '$99,999.99' )연봉
FROM emp
WHERE comm IS NOT NULL;
ㄴ. TO_CHAR( 날짜 ) : 날짜 -> 문자로 변환
【형식】
TO_CHAR( date [,'fmt' [,'nlsparam']])
-- [YY와 RR의 차이점]
--YY 두 자리의 년도 : 무조건 시스템 상의 년도를 붙인다.
-- [20]65/01/02
-- [20]12/02/24
--RR 두 자리의 년도
-- [19]65/01/02
-- [20]12/02/24
--현재 년도의 세기
SELECT SYSDATE
,TO_CHAR( SYSDATE, 'CC' ) --21세기
FROM dual;
SELECT ename, hiredate
FROM emp;
-- RR/MM/DD
SMITH [19]80/12/17
ALLEN [19]81/02/20
WARD [19]81/02/22
JONES [19]81/04/02
MARTIN [19]81/09/28
--
SELECT SYSDATE
, TO_CHAR(SYSDATE, 'YYYY') a
, TO_CHAR(SYSDATE, 'MM') b
, TO_CHAR(SYSDATE, 'MONTH') b
, TO_CHAR(SYSDATE, 'MON') b
, TO_CHAR(SYSDATE, 'DD') c --달의 년월[일]
, TO_CHAR(SYSDATE, 'D') c --주의 년원[일]
, TO_CHAR(SYSDATE, 'DDD') c -- 년의 년월[일]
, TO_CHAR(SYSDATE, 'WW') h --년중 몇번째 주 12
, TO_CHAR(SYSDATE, 'W') h --월중 몇번째 주 3
, TO_CHAR(SYSDATE, 'IW') h --1년 중 몇째 주 12
--복습 과제 WWW와
, TO_CHAR(SYSDATE, 'MMDD') d
, TO_CHAR(SYSDATE, 'BC') d --서기
, TO_CHAR(SYSDATE, 'Q') e --1~3 1분기 4~6 2분기 7~9 3분기 10~12 4분기
, TO_CHAR(SYSDATE, 'HH') f --[시간], 분, 초
, TO_CHAR(SYSDATE, 'HH24') f --[시간], 분, 초
, TO_CHAR(SYSDATE, 'MI') f --시간, [분], 초
, TO_CHAR(SYSDATE, 'SS') f --시간, 분, [초]
, TO_CHAR(SYSDATE, 'SSSSS') f -- 00:00:00 (자정) ~ 지난 시간 (초)
, TO_CHAR(SYSDATE, 'DY') g --요일 --월
, TO_CHAR(SYSDATE, 'DAY') g --요일 --월요일
FROM dual;
SELECT
-- 2023년 3월 20일 월요일
TO_CHAR( SYSDATE, 'DL' ) -- long date format
-- 2023/03/20
TO_CHAR( SYSDATE, 'DS' ) -- fhor date format
FROM dual;
--
SELECT ename, hiredate
, TO_CHAR( hiredate, 'DS')
FROM emp;
--'FF' 밀리세컨드
--ORA-01821: date format not recognized
-- DATE : SYSDATED 밀리세컨드 값을 가지고있지 않으므로 못씀
SELECT SYSDATE
-- , TO_CHAR(SYSDATE, 'HH24:MI:SS,FF' )
, TO_CHAR( CURRENT_TIMESTAMP, 'HH24:MI:SS.FF3' )
, TO_CHAR( SYSDATE , 'TS' ) --오후 12:52:55 (시간의 간략표기)
FROM dual;
[문제] 오늘 날짜를 TO_CHAR() 함수를 사용해서
'2023년 3월 20일 오후 14:03:32 (월)' 형식으로 출력
SELECT SYSDATE
-- , TO_CHAR( SYSDATE, 'YYYY' ) || '년 ' || , TO_CHAR( SYSDATE, 'MM' ) || '월 ' ㄱ
--" " 결과와 함께 출력할 문자열
, TO_CHAR( SYSDATE, 'YYYY"년" MM"월" DD"일" AM HH24:MI:SS (DY)'; ) ㄴ
FROM dual;
--SP 접미사를 붙이면 숫자(기수)를 영문으로 표시
SELECT TO_CHAR (SYSDATE, 'DDSP')
FROM dual;
--ROUND( 날짜 ), TRUNC( 날짜 )에 사용할 FORMAT 형식
3) TO_DATE(숫자, 문자) 날짜로 변환하는 함수
【형식】
TO_DATE( char [,'fmt' [,'nlsparam']])
[문제] 우리 수료일 ( 2023.7.10 )
오늘부터 수료일까지 남은 일수?
SELECT SYSDATE
, '2023.07.10'
--, SYSDATE - '2023.7.10' "남은 일수"
, CEIL(ABS(SYSDATE - TO_DATE('2023.07.10') ) ) "남은일수"
, CEIL(ABS(SYSDATE - TO_DATE('2023.07.10', 'YYYY.MM.DD') ) ) "남은일수"
FROM dual;
--ORA-01722: invalid number 왜? 날짜 - 날짜 = 일수 오류
--원인: 날짜가 아니라 문자로 인식된다 '2023.07.10'
--해결 : 문자로 인식되는 '2023.07.10'을 날짜로 변환
-- TO_DATE(문자)
--날짜 - 날짜 = 일수
SELECT ename, hiredate, SYSDATE
, CEIL (SYSDATE - hiredate) 근무일수
from emp;
--4자리의 숫자 형식으로 0010, 0040 부서번호를 출력
SELECT deptno
, TO_CHAR( deptno, '0999' )
FROM dept;
-- [일반 함수]
1) NVL
2) NVL2
3) NULLIF
4) NANVL
5) COALESCE : 병합(합동, 연합)하다
나열해 놓은 값을 순차적으로 체크하여 NULL이 아닌 값을 리턴하는 함수
【형식】
COALESCE(expr[,expr,...])
SELECT ename, sal + NVL(comm, 0) pay
, COALESCE ( sal + comm, sal , 0 ) pay
FROM emp;
--' ' == NULL로 처리한다(기억)
SELECT COALESCE( '', '', 'arirang', 'Kunsan' )
FROM dual;
6) DECODE (시험)
- 여러 개의 조건을 주어 조건에 맞을 경우 해당 값을 리턴하는 함수
- 비교 연산은 =만 가능하다
- PL/SQL 안에서 사용할 오라클 함수
- if() {} else if(){} ... else{}
--자바에서
int x = 10;
if( x == 11 ) {
return C;
}
--오라클 DECODE 사용해서
DECODE( X , 11 , C ) --X가 11이라면 C를 반환한다.
--자바에서
int x = 10;
if( x == 11 ) {
return C;
}else {
return D;
}
--오라클 DECODE 사용해서
DECODE( X , 11 , C , D ) --X가 11이라면 C를 반환한다.
--자바에서
int x = 10;
if( x == 11 ) {
return C;
}else if ( x == 12 ) {
return D;
}else if ( x == 13 ) {
return E;
}else {
return F;
}
--오라클 DECODE 사용해서
DECODE( X , 11 , C , 12, D, 13, E, F )
[문제] insa 테이블에서 주민등록번호 (ssn)를 가지고 "남자", "여자"라고 출력
SELECT name, ssn
,MOD ( SUBSTR( ssn, -7, 1 ), 2 ) gender
-- DECODE(X, 11, C, D )
, DECODE( MOD( SUBSTR(ssn, -7, 1), 2 ), 1, '남자', '여자' ) gender
FROM insa;
[문제] 아침 복습문제
8-2. insa 테이블에서 오늘을 기준으로 생일이 지남 여부를 출력하는 쿼리를 작성하세요 .
( '지났다', '안지났다', '오늘 ' 처리 )
( DECODE 함수 사용해서 처리 )
-- 1002 이순신 800320-1544236 오늘 날짜이므로 0 이 나와야하는데 1이 나옴
-- TRUNC 함수 추가해서 시간을 절삭해줘야 함
SELECT name, ssn
, SIGN ( TRUNC (SYSDATE) - TO_DATE( SUBSTR(ssn, 3, 4 ), 'MMDD' ) ) s
, DECODE( SIGN ( TRUNC (SYSDATE) - TO_DATE( SUBSTR(ssn, 3, 4 ), 'MMDD' ) ), -1, '지X', 1, '지O', 0 , '오늘' )
FROM insa;
(기억)
SELECT
--TO_DATE('2023')
TO_DATE('2023', 'YYYY') --23/03/01 (기억)
, TO_DATE('04', 'MM') -- 23/04/01 (기억)
, TO_DATE('10', 'DD') -- 23/03/10 (기억)
, TO_DATE('0522', 'MMDD') -- 23/05/22 (기억)
FROM insa;
[문제] emp 테이블에서 각 사원의 번호, 이름, 급여(pay) 출력
1) 10번 부서원은 15% 급여(pay) 인상
2) 20번 부서원은 30% 급여(pay) 인상
3) 그외 부서원은 5% 급여(pay) 인상
이 되는 쿼리 작성하세요..
(DECODE 함수)
SELECT deptno, empno, ename
, COALESCE( sal + comm, sal, 0 ) pay
, DECODE( deptno, 10, 15, 20, 30, 5 ) || '%' 인상퍼센트
, COALESCE( sal + comm, sal, 0 ) * DECODE( deptno, 10, 0.15, 20, 0.3, 0.05 ) 인금액
, COALESCE( sal + comm, sal, 0 ) * DECODE( deptno, 10, 1.15, 20, 1.3, 1.05 ) 인금된금액
FROM emp
ORDER BY deptno ASC;
7) CASE 함수 (시험)
-여러 개의 조건을 주어 조건에 맞을 경우 해당 값을 리턴하는 함수
-DECODE 함수의 확장
-뭐가 확장되었나? DECODE 함수는 비교 연산 =만 가능 + 다른 비교연산자도 사용가능
【형식】
CASE 컬럼명|표현식 WHEN 조건1 THEN 결과1
[WHEN 조건2 THEN 결과2
......
WHEN 조건n THEN 결과n
ELSE 결과4]
END
--예
[문제] insa 테이블에서 주민등록번호 (ssn)를 가지고 "남자", "여자"라고 출력
SELECT name, ssn
,MOD ( SUBSTR( ssn, -7, 1 ), 2 ) gender
, DECODE( MOD( SUBSTR(ssn, -7, 1), 2 ), 1, '남자', '여자' ) decode_gender
, CASE MOD( SUBSTR(ssn, -7, 1), 2 )
WHEN 1 THEN '남자'
--WHEN 0 THEN
ELSE '여자'
END case_gender
FROM insa;
8-2. insa 테이블에서 오늘을 기준으로 생일이 지남 여부를 출력하는 쿼리를 작성하세요 .
( '지났다', '안지났다', '오늘 ' 처리 )
( DECODE 함수 사용해서 처리 )
-- 1002 이순신 800320-1544236 오늘 날짜이므로 0 이 나와야하는데 1이 나옴
-- TRUNC 함수 추가해서 시간을 절삭해줘야 함
SELECT name, ssn
, SIGN ( TRUNC (SYSDATE) - TO_DATE( SUBSTR(ssn, 3, 4 ), 'MMDD' ) ) s
, DECODE( SIGN ( TRUNC (SYSDATE) - TO_DATE( SUBSTR(ssn, 3, 4 ), 'MMDD' ) ), -1, '지X', 1, '지O', 0 , '오늘' ) ㄱ
, CASE SIGN ( TRUNC (SYSDATE) - TO_DATE( SUBSTR(ssn, 3, 4 ), 'MMDD' )
WHEN -1 THEN '지X'
WHEN 1 THEN '지O'
WHEN 0 THEN '오늘'
ELSE
END ㄴ
, CASE --컬럼명, 표현식X
WHEN TRUNC (SYSDATE) - TO_DATE( SUBSTR(ssn, 3, 4 ), 'MMDD') < 0 THEN '지x'
WHEN TRUNC (SYSDATE) - TO_DATE( SUBSTR(ssn, 3, 4 ), 'MMDD') > 0 THEN '지O'
ELSE '오늘'
END ㄷ
FROM insa;
[문제] emp 테이블에서 각 사원의 번호, 이름, 급여(pay) 출력
1) 10번 부서원은 15% 급여(pay) 인상
2) 20번 부서원은 30% 급여(pay) 인상
3) 그외 부서원은 5% 급여(pay) 인상
이 되는 쿼리 작성하세요..
(DECODE 함수)
SELECT deptno, empno, ename
, COALESCE( sal + comm, sal, 0 ) pay
, DECODE( deptno, 10, 15, 20, 30, 5 ) || '%' 인상퍼센트
, COALESCE( sal + comm, sal, 0 ) * DECODE( deptno, 10, 0.15, 20, 0.3, 0.05 ) 인금액
, COALESCE( sal + comm, sal, 0 ) * DECODE( deptno, 10, 1.15, 20, 1.3, 1.05 ) 인금된금액
, COALESCE( sal + comm, sal, 0 ) * CASE deptno
WHEN 10 THEN 1.15
WHEN 20 THEN 1.3
ELSE 1.05
END case_인금된금액
, COALESCE( sal + comm, sal, 0 ) * CASE
WHEN deptno = 10 THEN 1.15
WHEN deptno = 20 THEN 1.3
ELSE 1.05
END case_인금된금액
FROM emp
ORDER BY deptno ASC;
-- [ 정규 표현식 함수 ]
1) REGEXP_LIKE()
2) REGEXP_INSTR()
3) REGEXP_SUBSTR()
4) REGEXP_REPLACE()
-- [그룹 함수]
- 그룹당 하나의 결과를 출력한다.
- SELECT절 및 HAVING 절에서 사용할 수 있다.
- GROUP BY절은 행들을 그룹화 한다.
- HAVING 절은 그룹을 제한한다.
1) AVG() : 평균
총 사원 12명: comm 이 NULL인 사원이 8명
SELECT ename, comm
FROM emp;
[문제] sal 평균, comm 평균 계산해서 출력
SELECT AVG( sal ) sal_avg
-- comm의 평균 계산
, AVG( comm ) comm_avg --550 --null 제외
, SUM( comm ) / COUNT(*) comm_avg --183.3333 --null 포함
FROM emp;
SELECT comm
FROM emp;
2) COUNT : NULL이 아닌 행의 갯수 반환하는 함수
형식】
COUNT([* ? DISTINCT ? ALL] 컬럼명) [ [OVER] (analytic 절)]
여기서
* NULL을 포함한 모든 행의 수
DISTINCT 중복되는 값을 제외한 행의 수
ALL 중복되는 값을 포함한 행의 수, 기본 값은 ALL
사용 가능한 데이터 타입 CHAR, VARCHAR2, NUMBER, DATE 타입
SELECT COUNT ( comm ) --4명 comm이 NULL인 사원 제외 8
, COUNT( deptno ) --11명 deptno이 NULL인 사원 제외 1
, COUNT( sal ) --12명 sal 이 NULL인 사원 제외 X
, COUNT( * ) --NULL을 포함한 행의 수 == 모든 사원 수
FROM emp;
[문제] insa 테이블에서 부서의 수를 출력... ( 7개의 부서 )
SELECT COUNT ( DISTINCT buseo )
FROM insa
ORDER BY buseo ASC;
3) MAX
4) MIN
6) SUM : NULL을 제외한 n의 합계를 리턴한다.
【형식】
SUM ([DISTINCT ? ALL] expr)
[OVER (analytic_clause)]
[문제] emp 테이블의 모든 sal의 합
SELECT SUM(sal) tot_sal
,SUM(comm) tot_comm
FROM emp;
5) STDDEV 표준편차
7) VARIANCE 분산
[문제] emp 테이블에서 각 부서의 사원수를 출력(조회)
1. SET 연산자 ( UNION, UNION ALL )
SELECT '10' deptno, COUNT(*)
FROM emp
WHERE deptno = 10
UNION ALL
SELECT '20', COUNT(*)
FROM emp
WHERE deptno = 20
UNION ALL
SELECT '30', COUNT(*)
FROM emp
WHERE deptno = 30
UNION ALL
SELECT '40', COUNT(*)
FROM emp
WHERE deptno = 40
UNION ALL
SELECT 'NULL', COUNT(*)
FROM emp
WHERE deptno IS NULL;
2. (암기) COUNT(), DECODE()
--부서가 없는 사원수 출력 X
SELECT
COUNT(*) "총 사원수"
, COUNT ( DECODE( deptno, 10, 'O' ) ) "10번 사원수"
, COUNT ( DECODE( deptno, 20, 'O' ) ) "20번 사원수"
, COUNT ( DECODE( deptno, 30, 'O' ) ) "30번 사원수"
, COUNT ( DECODE( deptno, 40, 'O' ) ) "40번 사원수"
FROM emp;
3.
--ORA-00937: not a single-group group function
--ORA-00979: not a GROUP BY expression
--SELECT deptno, job, COUNT(*)
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno -- 집계함수 MAX, MIN, AVG, SUM, COUNT 등등
ORDER BY deptno ASC;
(문제점) 결과 확인해보면 40 0 X
10 2
20 3
30 6
1
--------------------
-- SCOTT --
--------------------------------------------------------------------------------
-- 10:30 복습 문제 풀이~
--------------------------------------------------------------------------------
1. TO_CHAR( , 'format') 함수에서 'format'에 사용되는 기호를 적으세요.
ㄱ. 년도 : Y, YY, YYY, [YYYY], IY,IYY, IYYY, IYYYY, YEAR, SYEAR, RR, RRRR
ㄴ. 월 : MM, MONTH, MON
ㄷ. 월의 일 : DD
주의 일 : D
년의 일 : DDD
ㄹ. 요일 : DY, DAY
ㅁ. 월의 주차 : W
년의 주차 : WW, IW
ㅂ. 시간/24시간 : HH, HH12, HH24
ㅅ. 분 : MI
ㅇ. 초 : SS
ㅈ. 자정에서 지난 초 : SSSSS
ㅊ. 오전/오후 : AM, PM
--------------------------------------------------------------------------------
2. 본인의 생일로부터 오늘까지 살아온 일수, 개월수, 년수를 출력하세요..
SELECT SYSDATE ㄱ
, '1993.12.10' ㄴ
-- 날짜 - 날짜 = 일수
-- , SYSDATE - '1993.12.10'
-- ORA-01722: invalid number
-- '1993.12.10' 을 날짜로 형 변환
-- 문자열 -> 날짜 변환 함수 ? TO_DATE()
--【형식】
-- TO_DATE( char [,'fmt' [,'nlsparam']])
--, SYSDATE - TO_DATE( '1993.12.10' ,'YYYY.MM.DD' ) ㄷ
, CEIL( SYSDATE - TO_DATE( '1993.12.10' ) ) "살아온 일수"
, ROUND( MONTHS_BETWEEN( SYSDATE, TO_DATE( '1993.12.10' ) ), 2 ) "개월수"
, ROUND( MONTHS_BETWEEN( SYSDATE, TO_DATE( '1993.12.10' ) ) /12, 2 ) "년수"
FROM dual;
반올림 : ROUND
절상(올림) : CEIL
절삭(내림) : TRUNC, FLOOR 차이점
--------------------------------------------------------------------------------
3. IW와 WW 의 차이점.
ㅁ. 월의 주차 : W
년의 주차 : WW, IW
WW : 1일~7일을 기준으로 주차 표시
1~7 1주차
8~14 2주차
:
IW : 일요일을 기점으로 주차 표시
[2022.1]
일 월 화 수 목 금 토
[1 -- WW 1주차 , IW 52주차
<2 7] 9> -- IW 1주차
10 16
17 23
24 30
31
'2022.01.01' IW
SELECT TO_CHAR( TO_DATE('2022.01.01') , 'IW' ) -- 년중의 52주차 토
, TO_CHAR( TO_DATE('2022.01.02') , 'IW' ) -- 년중의 52주차 일
2022/01
26 27 28 29 30 31 1
2 3 9
10
-- , TO_CHAR( TO_DATE('2022.01.01') , 'WW' ) -- 년중의 1주차
-- , TO_CHAR( TO_DATE('2022.01.07') , 'WW' ) -- 년중의 1주차
-- , TO_CHAR( TO_DATE('2022.01.08') , 'WW' ) -- 년중의 2주차
FROM dual;
11:05 수업 시작~
--------------------------------------------------------------------------------
4-1. 이번 달이 몇 일까지 있는 확인. (
ㄱ. LAST_DAY() 마지막 날짜 얻어올 수 있고,
SELECT SYSDATE
, LAST_DAY( SYSDATE )
, TO_CHAR( LAST_DAY( SYSDATE ) , 'DD' ) -- 문자 반환
, EXTRACT( DAY FROM LAST_DAY( SYSDATE ) ) -- 숫자 반환
-- ㄴ. 다음 달 1일 날짜 생성 '2023.04.01'
-- 날짜 - 일수 = 날짜
, TO_CHAR(SYSDATE , 'YYYY.MM' )
, ADD_MONTHS( SYSDATE, 1 )
, TO_CHAR( ADD_MONTHS( SYSDATE, 1 ) , 'YYYY.MM')
-- ORA-01861: literal does not match format string
-- , TO_DATE( '2023.04' , 'YYYY.MM' ) -- 23/04/01
, TO_DATE( TO_CHAR( ADD_MONTHS( SYSDATE, 1 ) , 'YYYY.MM') , 'YYYY.MM' ) -- 23/04/01
, TO_DATE( TO_CHAR( ADD_MONTHS( SYSDATE, 1 ) , 'YYYY.MM') , 'YYYY.MM' ) - 1
FROM dual;
4-2. 오늘이 년중 몇 째 주, 월중 몇 째주인지 확인.
SELECT SYSDATE
, TO_CHAR( SYSDATE, 'W' ) -- 그 달의 몇 번째 주
, TO_CHAR( SYSDATE, 'IW' ) -- 그 해의 몇 번째 주
, TO_CHAR( SYSDATE, 'WW' ) -- 그 해의 몇 번째 주
FROM dual;
--------------------------------------------------------------------------------
5. emp 에서 pay 를 NVL(), NVL2(), COALESCE()함수를 사용해서 출력하세요.
SELECT
-- sal + comm pay NULL 연산 -> NULL
-- NULL 처리 함수사용해서
sal + NVL(comm, 0) pay
, sal + NVL2( comm, comm, 0) pay
, COALESCE( sal + comm, sal , 0 ) pay
, sal + COALESCE( comm, 0) pay
FROM emp;
5-2. emp테이블에서 mgr이 null 인 경우 -1 로 출력하는 쿼리 작성
ㄱ. nvl()
ㄴ. nvl2()
ㄷ. COALESCE()
SELECT deptno, ename
, NVL( mgr , -1 ) mgr
, NVL2( mgr , mgr , -1 ) mgr
, COALESCE(mgr, -1) mgr
FROM emp;
--------------------------------------------------------------------------------
6. insa 에서 이름,주민번호, 성별( 남자/여자 ), 성별( 남자/여자 ) 출력 쿼리 작성-
*** ㄱ. DECODE()
*** ㄴ. CASE 함수
SELECT name, ssn
, SUBSTR( ssn, -7, 1 ) ㄱ
, MOD( SUBSTR( ssn, -7, 1 ), 2 ) ㄴ -- 1(남자) , 0(여자)
-- , DECODE( MOD( SUBSTR( ssn, -7, 1 ), 2 ) , 1 , '남자', 0 , '여자')
, DECODE( MOD( SUBSTR( ssn, -7, 1 ), 2 ) , 1 , '남자', '여자') gender
-- , DECODE( MOD( SUBSTR( ssn, -7, 1 ), 2 ) , 1 , '남자' ) gender
, CASE MOD( SUBSTR( ssn, -7, 1 ), 2 )
WHEN 1 THEN '남자'
ELSE '여자'
END gender
, CASE
WHEN MOD( SUBSTR( ssn, -7, 1 ), 2 ) = 1 THEN '남자'
ELSE '여자'
END gender
FROM insa;
--------------------------------------------------------------------------------
7. emp 에서 평균PAY 보다 같거나 큰 사원들만의 급여합을 출력.
( DECODE, CASE 사용해서 풀이 )
SELECT SUM( sal + NVL(comm, 0) ) tot_pay -- 27125
FROM emp;
-- 평균 PAY 확인
SELECT AVG( sal + NVL(comm, 0) ) avg_pay -- 2260.416666666666666666666666666666666667
FROM emp;
-- 평균 PAY보다 많이 받는 사원 조회.
WITH
temp AS (
SELECT empno, ename, sal + NVL(comm, 0) pay
FROM emp
WHERE sal + NVL(comm, 0) >= (
SELECT AVG( sal + NVL(comm, 0) ) avg_pay -- 2260.416666666666666666666666666666666667
FROM emp
)
)
SELECT SUM( t.pay ) -- 18925
FROM temp t;
-- 2번째 방법
WITH
temp AS (
SELECT empno, ename, sal + NVL(comm, 0) pay
, ( SELECT AVG( sal + NVL(comm, 0 )) FROM emp ) avg_pay
FROM emp
)
-- t.pay - t.avg_pay 음수가 나오면 평균 PAY보다 작다.
-- ORA-00942: table or view does not exist
SELECT
SUM( DECODE( SIGN( t.pay - t.avg_pay ) , -1, NULL, t.pay ) ) -- 18925
,SUM(
CASE SIGN( t.pay - t.avg_pay )
WHEN -1 THEN NULL
ELSE t.pay
END
)
,SUM(
CASE
WHEN SIGN( t.pay - t.avg_pay ) >= 0 THEN t.pay
-- ELSE NULL
END
)
FROM temp t;
12:00 수업 시작
[문제] insa 테이블에서 남자사원수, 여자사원수 출력(조회)
SELECT name, ssn
--, SUBSTR( ssn, -7, 1)
, MOD( SUBSTR( ssn, -7, 1) , 2 ) gender
FROM insa;
-- 첫 번째 방법
SELECT '총 사원수', COUNT(*)
FROM insa
UNION ALL
SELECT '남자 사원수',COUNT(*)
FROM insa
WHERE MOD( SUBSTR( ssn, -7, 1) , 2 ) = 1
UNION ALL
SELECT '여자 사원수',COUNT(*)
FROM insa
WHERE MOD( SUBSTR( ssn, -7, 1) , 2 ) = 0;
-- 두 번째 방법
SELECT
COUNT(*) "총 사원수"
, COUNT( DECODE( MOD( SUBSTR( ssn, -7, 1) , 2 ), 1, 100 ) ) "남자 사원수"
-- , COUNT( DECODE( MOD( SUBSTR( ssn, -7, 1) , 2 ), 0, 100 ) ) "여자 사원수"
, COUNT(
CASE MOD( SUBSTR( ssn, -7, 1) , 2 )
WHEN 0 THEN 'F' -- ORA-00907: missing right parenthesis 괄호 누락되었다..
ELSE NULL
END
) "여자 사원수"
FROM insa;
-- 세 번째 방법
SELECT MOD( SUBSTR( ssn, -7, 1) , 2 ), COUNT(*) "사원수"
FROM insa
GROUP BY MOD( SUBSTR( ssn, -7, 1) , 2 );
[ 결과 ]
성별 사원수
----------------------- ----------
남자 31
여자 29
SELECT DECODE( MOD( SUBSTR( ssn, -7, 1) , 2 ) , 1 , '남자', '여자') "성별"
, COUNT(*) "사원수"
FROM insa
GROUP BY MOD( SUBSTR( ssn, -7, 1) , 2 );
[문제] emp 테이블에서 각 부서별 사원수 조회(출력 )
-- 1) UNION ALL ( SET 연산자 )
SELECT COUNT(*)
FROM emp
WHERE deptno = 10
UNION ALL
SELECT COUNT(*)
FROM emp
WHERE deptno = 20
UNION ALL
SELECT COUNT(*)
FROM emp
WHERE deptno = 30
UNION ALL
SELECT COUNT(*)
FROM emp
WHERE deptno = 40
UNION ALL
SELECT COUNT(*)
FROM emp
WHERE deptno IS NULL;
-- 2) COUNT, DECODE
SELECT COUNT(*)
, COUNT( DECODE( deptno, 10 , 'O' ) ) "10"
, COUNT( DECODE( deptno, 20 , 'O' ) ) "20"
, COUNT( DECODE( deptno, 30 , 'O' ) ) "30"
, COUNT( DECODE( deptno, 40 , 'O' ) ) "40"
, COUNT( DECODE( deptno, NULL , 'O' ) ) "NULL"
FROM emp;
-- 3) GROUP BY
-- 문제점 ) 사원이 존재하지 않는 부서 정보는 출력(조회)되지 않는다.
-- 추가 ) 전체 사원수 출력 : UNION ALL ( SET 연산자 )
SELECT deptno , COUNT(*)
FROM emp
GROUP BY deptno
UNION ALL
SELECT 0 deptno, COUNT(*)
FROM emp
UNION ALL
SELECT 40 deptno, COUNT(*)
FROM emp
WHERE deptno = 40
ORDER BY deptno; -- ORA-00904: "DEPTNO": invalid identifier
10 2
20 3
30 6
null 1
*** 40 0 *** OUTER JOIN ( 조인 )
[확인] 각 부서별 총사원수, 총급여합, 최고급여액, 최저급여핵
SELECT
deptno
, COUNT(*) "사원수"
, SUM( sal + NVL(comm, 0) ) "총급여합"
, MAX( sal + NVL(comm, 0) ) "최고급여합"
, MIN( sal + NVL(comm, 0) ) "최저급여합"
, AVG( sal + NVL(comm, 0) ) "평균급여합"
FROM emp
GROUP BY deptno
ORDER BY deptno;
--------------------------------------------------------------------------------
8. emp 에서 사원이 존재하는 부서의 부서번호만 출력 40
SELECT deptno, dname, loc
FROM dept;
-- 10 ACCOUNTING NEW YORK
-- 20 RESEARCH DALLAS
-- 30 SALES CHICAGO
-- 40 OPERATIONS BOSTON
SELECT *
FROM emp
ORDER BY deptno;
[문제] 사원이 존재하지 않는 부서번호만 출력.
-- SET 연산자 ( MINUS )
SELECT deptno
FROM dept
MINUS
SELECT DISTINCT deptno
FROM emp;
-- OUTER JOIN( 외부 조인) --
SELECT d.deptno --, COUNT(e.ename) 사원수
FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno
HAVING COUNT(e.ename) = 0 ;
-- JOIN (조인 == 결합하다.) (암기) --
[문제] emp 테이블에서 사원 정보 조회
( 부서번호, 부서명, 사원번호, 사원명, 입사입자 )
X
SELECT deptno, empno, ename, hiredate
FROM emp;
-- dept(부서) 테이블에 dname 컬럼으로 부서명이 존재..
SELECT *
FROM dept;
-- dept 과 emp 을 결합(JOIN)해야지 우리가 원하는 컬럼 정보를 조회할 수 있다.
-- dept : deptno, dname
-- emp : deptno, empno, ename, hiredate
-- ORA-00918: column ambiguously defined
-- deptno 컬럼 애매모호하게 정의(선언)되었다.
-- 왜 ? dept - deptno, emp - deptno
SELECT dept.deptno, dept.dname, emp.empno, emp.ename,emp.hiredate
FROM dept, emp
WHERE dept.deptno = emp.deptno;
--
SELECT d.deptno, d.dname, e.empno, e.ename, e.hiredate
FROM dept d, emp e
WHERE d.deptno = e.deptno;
-- d.deptno, e.deptno (EQUI JOIN)
SELECT e.deptno, dname, empno, ename, hiredate
FROM dept d, emp e
WHERE d.deptno = e.deptno;
-- 위의 쿼리랑 똑같은 쿼리
SELECT e.deptno, dname, empno, ename, hiredate
FROM dept d JOIN emp e ON d.deptno = e.deptno;
--------------------------------------------------------------------------------
9. 아래 코딩을 DECODE()를 사용해서 표현하세요.
ㄱ. [자바]
if( A == B ){
return X;
}
DECODE( A, B, X )
ㄴ. [자바]
if( A==B){
return S;
}else if( A == C){
return T;
}else{
return U;
}
DECODE( A, B, S, C, T, U )
ㄷ. [자바]
if( A==B){
return XXX;
}else{
return YYY;
}
DECODE( A, B, XXX, YYY )
--------------------------------------------------------------------------------
10. insa테이블에서 1001, 1002 사원의 주민번호의 월/일 만 오늘 날짜로 수정하는 쿼리를 작성
SELECT SYSDATE
, TO_CHAR( SYSDATE, 'MMDD' ) -- '0321'
FROM dual;
--
SELECT num, name , ssn
FROM insa
WHERE num IN ( 1001, 1002 );
--
UPDATE insa
SET ssn = SUBSTR(ssn, 0,2 ) || TO_CHAR( SYSDATE, 'MMDD' ) || SUBSTR( ssn, 7 )
WHERE num IN ( 1001, 1002 );
COMMIT;
10-2. insa테이블에서 오늘('2023.03.21')을 기준으로 아래와 같이 출력하는 쿼리 작성.
( DECODE, CASE 함수 사용 )
결과)
장인철 780506-1625148 생일 후
김영년 821011-2362514 생일 전
나윤균 810810-1552147 생일 후
김종서 751010-1122233 오늘 생일
유관순 801010-2987897 오늘 생일
정한국 760909-1333333 생일 후
-- 3:00 수업 시작~
;
-- 생일 전 == 생일이 지나지 않았다.
-- 생일 후 == 생일이 지났다. 3/3
SELECT name, ssn
-- , SUBSTR( ssn , 3, 4)
-- , TO_CHAR( SYSDATE, 'MMDD')
, SUBSTR( ssn , 3, 4) - TO_CHAR( SYSDATE, 'MMDD')
, SIGN( SUBSTR( ssn , 3, 4) - TO_CHAR( SYSDATE, 'MMDD') ) s
, DECODE( SIGN( SUBSTR( ssn , 3, 4) - TO_CHAR( SYSDATE, 'MMDD') ), 0 , '오늘 생일', 1 , '생일 전', -1 , '생일 후' ) ㄱ
, CASE
WHEN SUBSTR( ssn , 3, 4) - TO_CHAR( SYSDATE, 'MMDD') > 0 THEN '생일 전'
WHEN SUBSTR( ssn , 3, 4) - TO_CHAR( SYSDATE, 'MMDD') < 0 THEN '생일 후'
ELSE '오늘 생일'
END ㄴ
FROM insa;
10-3. insa테이블에서 오늘('2023.03.21')기준으로 이 날이 생일인 사원수,지난 사원수, 안 지난 사원수를 출력하는 쿼리 작성.
[실행결과]
생일 전 사원수 생일 후 사원수 오늘 생일 사원수
---------- ---------- ----------
48 12 0
-- 첫 번째 방법
-- ORA-00923: FROM keyword not found where expected
SELECT COUNT( DECODE( s, 0 , 'O' ) ) "오늘 생일 사원수"
, COUNT( DECODE( s, -1 , 'O' ) ) "생일 후 사원수"
, COUNT( DECODE( s, 1 , 'O' ) ) "생일 전 사원수"
FROM (
SELECT num, name, ssn , SIGN( SUBSTR( ssn , 3, 4) - TO_CHAR( SYSDATE, 'MMDD') ) s
FROM insa
) t ;
-- 두 번째 방법
SELECT
CASE s
WHEN 0 THEN '오늘 생일'
WHEN 1 THEN '생일 전'
WHEN -1 THEN '생일 후'
END ㄱ_case
, DECODE( s , 0 , '오늘 생일', 1 , '생일 전', '생일 후' ) ㄴ_decode
, COUNT(*) "사원수"
FROM (
SELECT num, name, ssn , SIGN( SUBSTR( ssn , 3, 4) - TO_CHAR( SYSDATE, 'MMDD') ) s
FROM insa
) t
GROUP BY s ;
--------------------------------------------------------------------------------
11. emp 테이블에서 10번 부서원들은 급여 15% 인상
20번 부서원들은 급여 10% 인상
30번 부서원들은 급여 5% 인상
40번 부서원들은 급여 20% 인상
하는 쿼리 작성.
SELECT deptno, ename, sal , comm, sal + NVL(comm, 0) pay
, DECODE( deptno, 10, 15, 20, 10, 30, 5, 40, 20 ) || '%' "인상율"
, comm, (sal + NVL(comm, 0)) * DECODE( deptno, 10, 0.15, 20, 0.1, 30, 0.05, 40, 0.2 ) "인상액"
, (sal + NVL(comm, 0)) * CASE deptno
WHEN 10 THEN 0.15
WHEN 20 THEN 0.1
WHEN 30 THEN 0.05
WHEN 40 THEN 0.2
END "인상액"
FROM emp;
--------------------------------------------------------------------------------
12. emp 테이블에서 각 부서의 사원수를 조회하는 쿼리
( 힌트 : DECODE, COUNT 함수 사용 )
-- 첫 번째 방법 : GROUP BY절
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno;
-- 문제점 40번 부서 X
-- [EQUI JOIN ==INNER JOIN]
-- OUTER JOIN
-- 1) LEFT [OUTER] JOIN
-- 2) RIGHT [OUTER] JOIN
-- 3) FULL [OUTER] JOIN
SELECT d.deptno , COUNT(ename)
FROM dept d FULL OUTER JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno
ORDER BY d.deptno ASC;
--------------------------------------------------------------------------------
13. emp, salgrade 두 테이블을 참조해서 아래 결과 출력 쿼리 작성.
ENAME SAL GRADE
----- ----- ---------
SMITH 800 1
ALLEN 1900 3
WARD 1750 3
JONES 2975 4
MARTIN 2650 4
BLAKE 2850 4
CLARK 2450 4
KING 5000 5
TURNER 1500 3
JAMES 950 1
FORD 3000 4
MILLER 1300 2
SELECT *
FROM salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
-- 첫 번째 방법
SELECT ename, sal
, CASE
WHEN sal BETWEEN 700 AND 1200 THEN 1
WHEN sal BETWEEN 1201 AND 1400 THEN 2
WHEN sal BETWEEN 1401 AND 2000 THEN 3
WHEN sal BETWEEN 2001 AND 3000 THEN 4
WHEN sal BETWEEN 3001 AND 9999 THEN 5
END grade
FROM emp;
-- 두 번째 방법 ( NON-EQUI JOIN ) ( 암기 )
SELECT ename, sal
, losal || ' ~ ' || hisal grade_range
, grade
FROM emp e , salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
--------------------------------------------------------------------------------
14. emp 테이블에서 급여를 가장 많이 받는 사원의 empno, ename, pay 를 출력.
SELECT empno, ename, sal + NVL(comm, 0) pay
FROM emp
WHERE sal >= ALL( SELECT sal+NVL(comm,0) pay FROM emp ) ;
WHERE sal = ( SELECT MAX( sal + NVL(comm, 0)) max_pay FROM emp );
-- RANK 순위 함수
-- TOP -N 방식...
4:00 수업 시작~
14-2. emp 테이블에서 각 부서별 급여를 가장 많이 받는 사원의 pay를 출력
1) UNION ALL 사용해서 풀기
2) GROUP BY 사용해서 풀기
SELECT deptno , MAX( sal + NVL(comm, 0)) max_pay
FROM emp
GROUP BY deptno
ORDER BY deptno;
--
SELECT d.deptno , NVL( MAX( sal + NVL(comm, 0)) , 0 ) max_pay
FROM emp e FULL OUTER JOIN dept d ON d.deptno = e.deptno
GROUP BY d.deptno
ORDER BY d.deptno;
3) 상관 서브쿼리 사용해서 풀기 + 조인
SELECT deptno, ename, sal + NVL(comm, 0) pay
FROM emp a
WHERE sal + NVL(comm, 0) = (
SELECT MAX( sal + NVL(comm, 0)) deptno_max_pay
FROM emp b
WHERE b.deptno = a.deptno
);
--------------------------------------------------------------------------------
-- [문제] emp 테이블에서 pay를 많이 받는 3명 정보를 조회 ( TOP-N 방식)
SELECT ROWNUM, t.*
FROM (
SELECT empno, ename, hiredate, sal + NVL(comm, 0) pay, deptno
FROM emp
ORDER BY pay DESC
) t
WHERE ROWNUM BETWEEN 3 AND 5 ; -- 중간의 순번(ROWNUM)은 가져올 수 없다. (주의 )
WHERE ROWNUM <= 5 ;
-- [ TOP-N 방식]
1) 최대값이나 최소값을 가진 컬럼을 질의할 때 유용하게 사용되는 분석방법.
2) inline view에서 ORDER BY 절을 사용할 수 있으므로
데이터를 원하는 순서로 정렬도 가능하다.
3) ROWNUM 컬럼은 subquery에서 반환되는
각 행에 순차적인 번호를 부여하는 의사(pseudo)컬럼이다 .
4) n값은 < 또는 >=를 사용하여 정의하며, 반환될 행의 개수를 지정한다.
5) 형식】
SELECT 컬럼명,..., ROWNUM
FROM (
SELECT 컬럼명,... from 테이블명
ORDER BY top_n_컬럼명
)
WHERE ROWNUM <= n;
--------------------------------------------------------------------------------
-- 순위(RANK) 함수
-- [문제] emp 테이블에서 pay를 많이 받는 3명 정보를 조회 ( 순위(RANK) 함수 )
-- 1) DENSE_RANK
-- 2) PERCENT_RANK
-- 3) RANK
-- 4) FIRST / LAST
-- *** 5) ROW_NUMBER ***
- 분석(analytic) 함수.
- 분할별로 정렬된 결과에 대해 순위를 부여하는 기능이다.
- 분할은 전체 행을 특정 컬럼을 기준으로 분리하는 기능으로
GROUP BY 절에서 그룹화하는 방법과 같은 개념이다.
- 【형식】
ROW_NUMBER ()
OVER ([query_partition_clause] order_by_clause )
SELECT t.*
FROM (
SELECT deptno, ename, sal + NVL(comm, 0) pay
, ROW_NUMBER() OVER( ORDER BY sal + NVL(comm, 0) DESC ) 순위
FROM emp
) t
WHERE 순위 BETWEEN 3 AND 5;
WHERE 순위 <= 1;
WHERE 순위 <= 3;
--------------------------------------------------------------------------------
[문제] 각 부서별로 최고 급여를 받는 사원 1명을 조회.
SELECT t.*
FROM (
SELECT buseo, name, basicpay + sudang pay
, ROW_NUMBER() OVER( PARTITION BY buseo ORDER BY basicpay + sudang DESC ) 순위
FROM insa
) t
WHERE 순위 = 2;
WHERE 순위 = 1;
WHERE 순위 <= 3;
--
SELECT DISTINCT buseo
FROM insa;
총무부
개발부
영업부
기획부
인사부
자재부
홍보부
--
--------------------------------------------------------------------------------
-- DENSE_RANK
- 그룹 내에서 차례로 된 행의 rank를 계산하여 NUMBER 데이터타입으로 순위를 반환한다.
- 해당 값에 대한 우선순위를 결정(중복 순위 계산 안함)
【Aggregate 형식】
DENSE_RANK ( expr[,expr,...] ) WITHIN GROUP
(ORDER BY expr [[DESC ¦ ASC] [NULLS {FIRST ¦ LAST} , expr,...] )
【Analytic 형식】
DENSE_RANK ( ) OBER ([query_partion_clause] order_by_clause )
-- RANK
이 함수는 그룹 내에서 위치를 계산하여 반환한다.
해당 값에 대한 우선순위를 결정(중복 순위 계산함)
반환되는 데이터타입은 NUMBER이다.
【Aggregate 형식】
RANK(expr[,...]) WITHIN GROUP
(ORDER BY {expr [DESC ¦ ASC] [NULLS {FIRST ¦ LAST}]
} )
【Analytic 형식】
RANK() OVER( [query_partition_clause] order_by_clause
-- ROW_NUMBER
--------------------------------------------------------------------------------
SELECT empno, ename, sal
, RANK() OVER( ORDER BY sal DESC ) rank_순위 -- 중복 순위 계산 O
, DENSE_RANK() OVER( ORDER BY sal DESC ) dense_rank_순위 -- 중복 순위 계산 X DENSE == 밀집한
, ROW_NUMBER() OVER( ORDER BY sal DESC ) row_number_순위
FROM emp;
7654 MARTIN 1250 9 9 9
7521 WARD 1250 9 9 10
7900 JAMES 950 11 10 11
--------------------------------------------------------------------------------
SELECT deptno, empno, ename, sal
, RANK() OVER( PARTITION BY deptno ORDER BY sal DESC ) rank_순위 -- 중복 순위 계산 O
, DENSE_RANK() OVER( PARTITION BY deptno ORDER BY sal DESC ) dense_rank_순위 -- 중복 순위 계산 X DENSE == 밀집한
, ROW_NUMBER() OVER( PARTITION BY deptno ORDER BY sal DESC ) row_number_순위
FROM emp;
--------------------------------------------------------------------------------
[ 참고 ] 자바에서 처럼 등수 처리
한명 총점 -> 등수를 1등씩 증가
SELECT deptno, ename, sal
, ( SELECT COUNT(*) FROM emp b WHERE b.sal > a.sal ) +1 순위
FROM emp a
--------------------------------------------------------------------------------
[문제] emp 테이블에서 sal가 상위 20% 사원 정보 조회.
--------------------------------------------------------------------------------
SELECT t.*
FROM (
SELECT deptno, ename, sal
, RANK() OVER( ORDER BY sal DESC ) r
FROM emp
) t
WHERE r <= ( SELECT COUNT(*) FROM emp )*0.2 ;
'-java spring notes- > db' 카테고리의 다른 글
| day09~day10 (0) | 2023.03.24 |
|---|---|
| 관계를 맺자! 조인 (18) | 2023.03.24 |
| 오라클 2 -데이터 베이스 객체 (1) | 2023.03.21 |
| 1오라클 - 오라클 이란? (1) | 2023.03.20 |
| postgres실습,docker (1) | 2023.01.21 |
