고정 헤더 영역
상세 컨텐츠
본문
문제1.
-- 위에서 조회한 내용을 아래와 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
부서번호 급여합
---------- ----------
10 8750
20 10875
30 9400
인턴 8000
모든부서 37025
---------------------
*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO), '인턴')
ELSE '모든부서' -- 문자타입
END "부서번호"
, SUM(SAL)
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
/*
10 8750
20 10875
30 9400
인턴 8000
모든부서 37025
*/-- 문제2
--○TBL_SAWON 테이블을 대상으로 다음과 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
-------------------
성별 | 급여합
-----------------
남 XXX
여 XXXX
모든사원 XXXXX
------------------
*/
SELECT CASE GROUPING(T.성별) WHEN 0 THEN T.성별
ELSE '모든사원'
END "성별"
, SUM(T.급여) "급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남'
WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여'
ELSE '성별판별불가'
END "성별"
,SAL "급여"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.성별);
/*
남 15100
여 11000
모든사원 26100
*/
-- 문제3
--○TBL_SAWON 테이블을 대상으로 다음과 같이 연령대별 인원수 형태로
-- 조회될 수 있도록 쿼리문을 구성한다.
/*
----------------
연령대 인원수
--------------
10 X
20 X
30 X
50 X
전체 XX
--------------
SELECT CASE GROUPING(Q.연령대) WHEN 0 THEN TO_CHAR(Q.연령대)
ELSE '전체'
END "연령대"
, COUNT(Q.연령대) "인원수"
FROM
(
SELECT CASE WHEN T.나이 >=50 AND T.나이<60 THEN 50
WHEN T.나이 >=40 THEN 40
WHEN T.나이 >=30 THEN 30
WHEN T.나이 >=20 THEN 20
WHEN T.나이 >=10 THEN 10
ELSE 0
END "연령대"
FROM
(
SELECT 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 "나이"
FROM TBL_SAWON
) T
) Q
GROUP BY ROLLUP(Q.연령대);
/*
연령대 인원수
------- ----------
20 5
30 1
40 3
50 4
전체 13
*/
방법2)
SELECT CASE GROUPING(T.연령대) WHEN 0 THEN TO_CHAR(T.연령대)
ELSE '전체'
END "연령대"
,COUNT (T.연령대) "인원수"
FROM
(
SELECT TRUNC(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 , -1) "연령대"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.연령대);
/*
연령대 인원수
------- ----------
20 5
30 1
40 3
50 4
전체 13
*/문제4.
--○TBL_EMP 테이블을 대상으로 입사년도별 인원수를 조회한다.
/*
-----------------------------
입사년도 인원수
-----------------------------
1980 1
1981 10
1982 1
1987 2
2025 5
전체 19
-----------------------------
*/
SELECT TO_CHAR(HIREDATE, 'YYYY') "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY')
ORDER BY 1;
/*
1980 1
1981 10
1982 1
1987 2
2025 5
*/
-- ■■■ HAVING ■■■ --
--○ EMP 테이블에서 부서번호가 20, 30인 부서를 대상으로
-- 부서의 총 급여가 10000 보다 적을 경우만 부서별 총 급여를 조회한다.
-- FWG HSO
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IN(20,30)
GROUP BY DEPTNO;
/*
30 9400
20 10875
*/
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IN(20,30)
AND SUM(SAL) < 10000
GROUP BY DEPTNO;
--==>> 에러발생
/*
ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:
736행, 9열에서 오류 발생
*/
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IN(20,30)
GROUP BY DEPTNO
-- 그룹에 대한 조건은 해빙절에 위치
HAVING SUM(SAL) < 10000; --파싱순서체크
--==>> 30 9400
-- CHECK~!!!
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
-- 그룹에 대한 조건은 해빙절에 위치 (그룹조건이 아닌 것은 쓰지 않는 것이 좋음)
HAVING SUM(SAL) < 10000 --
AND DEPTNO IN(20,30); --파싱순서체크
--==>> 30 9400
-- ※ 그룹 함수는 2 LEVEL 까지 중첩해서 사용할 수 있다.
-- 이마저도 MS-SQL 은 불가능하다.
SELECT ... MAX(SUM(SAL)) "결과확인" -- ...에 추가로 무엇을 한다는 안된다는 뜻임
FROM EMP
GROUP BY DEPTNO;
-- ※ RANK()
-- DENSE_RANK()
-- → ORACLE 9i 부터 적용 ... MS-SQL 2005 부터 적용...
--※ 위와 같이 하위 버전에서 RANK() 나 DENSE_RANK() 를 사용할 수 없기 때문에
-- 이를 대체하여 연산을 수행할 수 있는 방법을 강구해야 한다.
-- 예를 들어, 급여의 순위를 구해야 하는 상황이라면...
-- 해당 사원의 급여보다 더 큰 급여 값이 몇 개인지 확인한 후
-- 그 확인한 숫자에 +1을 추가 연산해주면 그것이 곧 등수가 된다.
SELECT ENAME, SAL
FROM EMP;
-- SMITH 사원의 급여 등수
SELECT COUNT(*) + 1 "SMITH급여등수"
FROM EMP
WHERE SAL > 800; -- SMITH의 급여
SELECT ENAME "사원명", SAL "급여", 1 "급여등수"
FROM EMP;
-- ※ 상관 서브 쿼리(서브 상관 쿼리)
-- 메인 쿼리에 있는 테이블의 컬럼이
-- 서브 쿼리의 조건절(WHERE절, HAVING절)에 사용되는 경우
-- 우리는 이 쿼리문을 상관 서브쿼리(서브 상관 쿼리)라고 부른다.
(1) "급여등수"
SELECT ENAME "사원명", SAL "급여", (SELECT COUNT(*) + 1 "SMITH급여등수"
FROM EMP
WHERE SAL > 800;
FROM EMP;
SELECT ENAME "사원명", SAL "급여", (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800) "급여등수"
FROM EMP;
SELECT ENAME "사원명", SAL "급여", (SELECT COUNT(*) + 1
FROM EMP E2
WHERE E2.SAL > E1.SAL) "급여등수" -- E2급여셀 > 스미스부터~ 의 급여
FROM EMP E1
ORDER BY 3;
/*
KING 5000 1
FORD 3000 2
SCOTT 3000 2
JONES 2975 4
BLAKE 2850 5
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
MARTIN 1250 10
ADAMS 1100 12
JAMES 950 13
SMITH 800 14
*/-- 문제5
--○ EMP 테이블을 대상으로
-- 사원명, 급여, 부서번호, 부서내급여등수, 전체급여등수 항목을 조회한다.
-- 단, RANK() 함수를 사용하지 않고, 서브 상관 쿼리를 활용할 수 있도록 한다.
SELECT COUNT(*) + 1
FROM EMP E2
WHERE SAL > 5000
AND DEPTNO = 10;
--위 수식을 짤라서 (100) 자리에 넣어주고 최종 쿼리문 완성
SELECT ENAME "사원명", SAL "급여", DEPTNO "부서번호"
,(SELECT COUNT(*) + 1
FROM EMP E2
WHERE E2.SAL > E1.SAL
AND E2.DEPTNO = E1.DEPTNO) "부서내급여등수"
,(SELECT COUNT(*) + 1
FROM EMP E2
WHERE E2.SAL > E1.SAL) "전체급여등수"
FROM EMP E1
ORDER BY E1.DEPTNO, E1.SAL DESC;
/*
KING 5000 10 1 1
CLARK 2450 10 2 6
MILLER 1300 10 3 9
SCOTT 3000 20 1 2
FORD 3000 20 1 2
JONES 2975 20 3 4
ADAMS 1100 20 4 12
SMITH 800 20 5 14
BLAKE 2850 30 1 5
ALLEN 1600 30 2 7
TURNER 1500 30 3 8
MARTIN 1250 30 4 10
WARD 1250 30 4 10
JAMES 950 30 6 13
*/-- 문제6
--○ EMP 테이블을 대상으로 다음과 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
----------------------------------------------------------------------------------------------------------------------
사원명 부서번호 입사일 급여 부서내입사별급여누적(→ 부서 내에서 입사일자별로 급여가 누적된 상황 확인)
----------------------------------------------------------------------------------------------------------------------
CLRAK 10 1981-06-09 2450 2450
KING 10 1981-11-17 5000 7450
MILLER 10 1982-01-23 1300 8750
SMITH 20 1980-12-17 800 800
JONES 20 1981-04-02 2975 3775
:
-----------------------------------------------------------------------------------------------------------------------
*/
SELECT ENAME "사원명", DEPTNO"부서번호",HIREDATE "입사일", SAL"급여"
,(SELECT SUM(E2.SAL)
FROM EMP E2
WHERE E2.DEPTNO = E1.DEPTNO -- 같은 부서이면서라는 조건
AND E2.HIREDATE <=E1.HIREDATE) "부서내입사별급여누적"
FROM EMP E1
ORDER BY 3,2;
--==>>
/*
CLARK 10 81/06/09 2450 2450
KING 10 81/11/17 5000 7450
MILLER 10 82/01/23 1300 8750
SMITH 20 80/12/17 800 800
JONES 20 81/04/02 2975 3775
FORD 20 81/12/03 3000 6775
SCOTT 20 87/07/13 3000 10875
ADAMS 20 87/07/13 1100 10875
ALLEN 30 81/02/20 1600 1600
WARD 30 81/02/22 1250 2850
BLAKE 30 81/05/01 2850 5700
TURNER 30 81/09/08 1500 7200
MARTIN 30 81/09/28 1250 8450
JAMES 30 81/12/03 950 9400
*/
-- 문제7
--○ TBL_EMP 테이블에서 입사한 사원의 수가 제일 많았을 때의
-- 입사년월과 인원수를 조회할 수 있도록 쿼리문을 구성한다.
/*
------------------------
입사년월 인원수
------------------------
2025-07 5
------------------------
*/
SELECT MAX(COUNT(*))
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM');
--==>> 5 -- 인원수 가장 많은 걸 알려줘
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM'));
--==>> 2025-07 5
-- ※ 게시판의 게시물 번호를
-- SEQUENCE 나 INDENTITY 를 사용하게 되며 (EX : 번호표 발행 모듈)
-- 특정 게시물을 삭제했을 경우, 삭제한 게시물의 자리에
-- 다음 번호를 가진 게시물이 등록되는 상황이 발생하게 된다.
-- 이는, 보안 측면에서나 ... 미관상 ... 바람직하지 않은 상황일 수 있기 때문에
-- ROW_NUMBER() 의 사용을 고려해 볼 수 있다.
-- 관리의 목적으로 사용할 때는 SEQUENCE 나 INDENTITY를 사용하지만
-- 단순히 게시물을 목록화하여 사용자에게 리스트 형식으로 보여줄 때는
-- 사용하지 않는 것이 좋다.
-- EX) 싸이월드 쪽지 NO. 79885414 << 게시물의 관리번호
UPDATE TBL_AAA
SET GRADE='A'
WHERE NO=6;
--==>> 1 행 이(가) 업데이트되었습니다.
-- 식별자를 갖고 있는것이 관리측면에서 좋음.
--○ SEQUENCE 생성(시퀀스, 주문번호)
-- → 사전적인 의미 : 1.(일련의) 연속적인 사건들 2.(사건,행동 등의) 순서
-- SEQUENCE생성시 리모컨 형태 - 은행에서 번호표 뽑을때마다
CREATE SEQUENCE SEQ_BOARD -- 시퀀스 기본 생성 구문(MYSQL의 INDENTITY와 동일한 개념)
START WITH 1 -- 시작값
INCREMENT BY 1 -- 증가값 (얼마씩 증가해 나갈래?)
NOMAXVALUE -- 최대값 제한 없음
NOCACHE; -- 캐시 사용 안함(없음) -- 번호표를 미리 많이 뽑아놓은것=그것이 캐시
--==>> Sequence SEQ_BOARD이(가) 생성되었습니다.
-- 시퀀스는 생성 후 수정 불가함.
--○ 테이블 생성
-- 테이블명 : TBL_BOARD
CREATE TABLE TBL_BOARD -- TBL_BOARD이름의 테이블 생성 → 게시판
( NO NUMBER -- 게시물 번호 X
, TITLE VARCHAR2(50) -- 게시물 제목 O
, CONTENTS VARCHAR2(2000) -- 게시물 내용 O
, NAME VARCHAR2(20) -- 게시물 작성자 △
, PW VARCHAR2(20) -- 게시물 패스워드 △
, CREATED DATE DEFAULT SYSDATE -- 게시물 작성일 X -- 제약조건의 일종
);
--==>> Table TBL_BOARD이(가) 생성되었습니다.'IT > DBMS' 카테고리의 다른 글
| Oracle - UNION / UNION ALL (1) | 2025.07.08 |
|---|---|
| Oracle - TABLE JOIN (1) | 2025.07.08 |
| Oracle -CASE WHEN THEN ELSE END 서브쿼리 , 인라인뷰 (0) | 2025.07.02 |
| Oracle - 날짜연산(TO_DATE, ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY) (1) | 2025.07.01 |
| Oracle - 숫자관련함수(ROUND, TRUNC, MOD, POWER, SQRT, LOG, SIGN, ASCII, CHR ) (0) | 2025.07.01 |
댓글 영역