-- SCOTT --
1. emp , salgrade 테이블을 사용해서 아래와 같이 출력. [JOIN] 사용
SELECT e.ename, e.sal, s.losal || '~' || s.hisal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
SELECT ename, sal, losal || '~' || hisal, grade
FROM emp
JOIN salgrade ON sal BETWEEN losal AND hisal;
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
문제) deptno, dname, ename, hiredate, sal, grade 컬럼 조회(join)
SELECT d.deptno, dname, ename, hiredate, sal, grade
FROM dept d, emp e, salgrade s
WHERE d.deptno = e.deptno AND e.sal BETWEEN s.losal AND s.hisal;
SELECT d.deptno, dname, ename, hiredate, sal, grade
FROM dept d JOIN emp e ON d.deptno = e.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
1-2. 위의 결과에서 등급(grade)가 1등급인 사원만 조회하는 쿼리 작성
( 조건 : TOP-N 방식 사용 )
SELECT empno, ename, sal, grade
FROM emp JOIN salgrade ON sal BETWEEN losal AND hisal
WHERE grade = 1;
결과)
EMPNO ENAME SAL GRADE
---------- ---------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 1
2. emp 에서 최고급여를 받는 사원의 정보 출력 ( JOIN ~ ON 구문 )
( 조건 : 아래 컬럼 출력 )
SELECT dname, ename, sal + NVL(comm, 0) pay
FROM dept d, emp e
WHERE d.deptno(+) = e.deptno -- JOIN 조건
AND sal + NVL(comm, 0) = (
SELECT MAX(sal + NVL(comm, 0)) max_pay
FROM emp
);
-- 근데 이렇게 하면 INNER JOIN이라 양쪽에서 공통적으로 갖는 deptno의 데이터만 가져와 제대로된 결과가 나오지 않는다.
-- RIGHT OUTER JOIN으로 바꾸어주면 원하는 결과를 얻을 수 있다.
SELECT dname, ename, sal + NVL(comm, 0) pay
FROM dept d RIGHT OUTER JOIN emp e ON d.deptno = e.deptno
WHERE sal + NVL(comm, 0) = (
SELECT MAX(sal + NVL(comm, 0)) max_pay
FROM emp
);
DNAME ENAME PAY
-------------- ---------- ----------
ACCOUNTING KING 5000
문제) RANK()
-- 순위를 매기려면 정렬이 되어야 한다.
WITH
temp AS (
SELECT dname, ename, sal + NVL(comm, 0) pay
, RANK() OVER(ORDER BY sal + NVL(comm, 0) DESC) "R"
, DENSE_RANK() OVER(ORDER BY sal + NVL(comm, 0) DESC) "DR"
, ROW_NUMBER() OVER(ORDER BY sal + NVL(comm, 0) DESC) "RN"
FROM dept d RIGHT OUTER JOIN emp e ON d.deptno = e.deptno
)
SELECT t.*
FROM temp t
WHERE t.R = 1;
WHERE t.R = -3;
WHERE t.R BETWEEN 3 AND 5;
문제) DENSE_RANK()
문제) ROW_NUMBER()
문제) TOP-N 방식
1) FROM 인라인 뷰 에서 급여순으로 정렬
2) 서브쿼리 묶기
3) 의사컬럼으로 순번
SELECT t.*, ROWNUM 순번
FROM (
SELECT dname, ename, sal + NVL(comm, 0) pay
FROM dept d RIGHT OUTER JOIN emp e ON d.deptno = e.deptno
ORDER BY pay DESC
) t
WHERE ROWNUM <= 3;
2-2. emp 에서 각 부서별 최고급여를 받는 사원의 정보 출력 ( JOIN )
DEPTNO DNAME ENAME PAY
---------- -------------- ---------- ----------
10 ACCOUNTING KING 5000
20 RESEARCH FORD 3000
30 SALES BLAKE 2850
1) 상관서브 쿼리로 풀기(서브쿼리가 main이랑 관련있기 때문에 다 한번에 실행해야 함.)
SELECT d.deptno, dname, ename, sal + NVL(comm,0) pay
FROM dept d FULL JOIN emp e ON d.deptno = e.deptno
WHERE sal + NVL(comm, 0) = (
SELECT MAX(sal + NVL(comm, 0))
FROM emp
WHERE deptno = d.deptno
);
2) 문제점이 있는 풀이
SELECT d.deptno, dname, ename, sal + NVL(comm,0) pay
FROM dept d FULL JOIN emp e ON d.deptno = e.deptno
WHERE sal + NVL(comm, 0) IN (
SELECT MAX(sal + NVL(comm, 0))
FROM emp
GROUP BY deptno
);
3) 순위 함수
SELECT t.*
FROM (
SELECT d.deptno, dname, ename, sal + NVL(comm,0) pay
, ROW_NUMBER() OVER(PARTITION BY d.deptno ORDER BY sal + NVL(comm, 0) DESC) 순위
-- PARTITION으로 부서별로 정렬 후 내림차순 정렬해서 보여줌
FROM dept d RIGHT JOIN emp e ON d.deptno = e.deptno --RIGHT JOIN 주면 사원이 없는 부서는 안 나옴
) t
WHERE 순위 <= 1;
3. emp 에서 각 사원의 급여가 전체급여의 몇 %가 되는 지 조회.
( % 소수점 3자리에서 반올림하세요 )
무조건 소수점 2자리까지는 출력.. 7.00%, 3.50%
SELECT SUM(sal + NVL(comm, 0)) totalpay
FROM emp;
SELECT ename, sal + NVL(comm, 0) pay
FROM emp;
SELECT t.*
, ROUND(t.pay/t.totalpay * 100, 2) || '%' "비율"
, TO_CHAR(ROUND(t.pay/t.totalpay * 100, 2), '99.00') || '%' "비율"
FROM (
SELECT ename, sal + NVL(comm, 0) pay
, (SELECT SUM(sal + NVL(comm, 0)) FROM emp) totalpay
FROM emp
)t;
ENAME PAY TOTALPAY 비율
---------- ---------- ---------- -------
SMITH 800 27125 2.95%
ALLEN 1900 27125 7.00%
WARD 1750 27125 6.45%
JONES 2975 27125 10.97%
MARTIN 2650 27125 9.77%
BLAKE 2850 27125 10.51%
CLARK 2450 27125 9.03%
KING 5000 27125 18.43%
TURNER 1500 27125 5.53%
JAMES 950 27125 3.50%
FORD 3000 27125 11.06%
MILLER 1300 27125 4.79%
12개 행이 선택되었습니다.
4. emp 에서 가장 빨리 입사한 사원 과 가장 늦게(최근) 입사한 사원의 차이 일수 ?
SELECT ABS(MAX(hiredate) - MIN(hiredate))
FROM emp;
5. insa 에서 사원들의 만나이 계산해서 출력
( 만나이 = 올해년도 - 출생년도 - 1( 생일이지나지 않으면) )
SELECT*
FROM insa;
ㄱ. 올해연도
ㄴ. 생일연도 4자리
ㄷ. 생일 전/후 체크
SELECT t.name, t.ssn
, ㄱ-ㄴ+1 CountingAge
, ㄱ-ㄴ+ DECODE(ㄷ, -1, -1, 0) AmericanAge
FROM (
SELECT name, ssn
, TO_CHAR(SYSDATE, 'YYYY') ㄱ -- 2023
-- 1800/1900/2000 년대는 어떻게?
, SUBSTR(ssn, 0, 2) + CASE
WHEN SUBSTR(ssn, -7, 1) IN (1,2,5,6) THEN 1900
WHEN SUBSTR(ssn, -7, 1) IN (3,4,7,8) THEN 2000
WHEN SUBSTR(ssn, -7, 1) IN (9,0) THEN 1800
END ㄴ
-- 생일 지나면 1, 지나지 않으면 -1, 오늘이 생일이면 0
, SIGN(TO_CHAR(SYSDATE, 'MMDD') - SUBSTR(ssn, 3, 4)) ㄷ
FROM insa
) t;
6. insa 테이블에서 아래와 같이 결과가 나오게 ..
[총사원수] [남자사원수] [여자사원수] [남사원들의 총급여합] [여사원들의 총급여합] [남자-max(급여)] [여자-max(급여)]
---------- ---------- ---------- ---------- ---------- ---------- ----------
60 31 29 51961200 41430400 2650000 2550000
SELECT COUNT(*) 총사원수
, COUNT( DECODE( MOD(SUBSTR(ssn, -7, 1), 2),1,'O')) 남자사원수
, COUNT( DECODE( MOD(SUBSTR(ssn, -7, 1), 2),1,'O')) 여자사원수
, TO_CHAR(SUM(DECODE( MOD(SUBSTR(ssn, -7, 1), 2), 1, basicpay + sudang)), 'L9,999,999,999') 남자총급여합
, SUM(DECODE( MOD(SUBSTR(ssn, -7, 1), 2), 0, basicpay + sudang)) 여자총급여합
, TO_CHAR(MAX(DECODE( MOD(SUBSTR(ssn, -7, 1), 2), 1, basicpay + sudang)), 'L9,999,999,999') 남자MAX급여합
, MAX(DECODE( MOD(SUBSTR(ssn, -7, 1), 2), 0, basicpay + sudang)) 여자MAX급여합
FROM insa;
7. TOP-N 방식으로 풀기 ( ROWNUM 의사 컬럼 사용 )
emp 에서 최고급여를 받는 사원의 정보 출력
DEPTNO ENAME PAY PAY_RANK
---------- ---------- ---------- ----------
10 KING 5000 1
SELECT t.*, ROWNUM
FROM (
SELECT deptno, ename, sal + NVL(comm, 0) pay
FROM emp
ORDER BY pay DESC
) t
WHERE ROWNUM = 1;
8.순위(RANK) 함수 사용해서 풀기
emp 에서 각 부서별 최고급여를 받는 사원의 정보 출력
DEPTNO ENAME PAY DEPTNO_RANK
---------- ---------- ---------- -----------
10 KING 5000 1
20 FORD 3000 1
30 BLAKE 2850 1
-- 서브쿼리 사용
SELECT t.*
FROM (
SELECT deptno, ename, sal + NVL(comm, 0) pay
, RANK() OVER(ORDER BY sal + NVL(comm, 0) DESC) "전체 순위"
, RANK() OVER(PARTITION BY deptno ORDER BY sal + NVL(comm, 0) DESC) "부서순위"
FROM emp
)t
WHERE "부서순위" = 1;
-- JOIN 사용
ㄱ. 각부서의 최고 급여액
SELECT deptno, MAX(sal + NVL(comm, 0))
FROM emp
GROUP BY deptno;
--
SELECT a.deptno, b.ename, a.max_pay, 1 AS deptno_rank -- 고정값 1 주면 1등 찍힘
FROM (
SELECT deptno, MAX(sal + NVL(comm, 0)) max_pay
FROM emp
GROUP BY deptno
) a JOIN emp b ON a.deptno = b.deptno
WHERE a.max_pay = b.sal+NVL(comm, 0); -- 최고값하고 같은 부서이면서 같은 사람
9. emp테이블에서 각 부서의 사원수, 부서총급여합, 부서평균을 아래와 같이 출력하는 쿼리 작성.
결과)
DEPTNO 부서원수 총급여합 평균
---------- ---------- ---------- ----------
10 3 8750 2916.67
20 3 6775 2258.33
30 6 11600 1933.33
-- 첫번째 방법 --
SELECT deptno
, COUNT(*) 부서원수
, SUM(sal + NVL(comm, 0)) 총급여합
, TO_CHAR(ROUND(AVG(sal + NVL(comm, 0)), 2), '9999.00') 평균
FROM emp
GROUP BY deptno
ORDER BY deptno;
10-1. emp 테이블에서 30번인 부서의 최고, 최저 SAL을 출력하는 쿼리 작성.
결과)
MIN(SAL) MAX(SAL)
---------- ----------
950 2850
SELECT MAX(sal), MIN(sal)
FROM emp
WHERE deptno = 30;
각 부서별 최대, 최솟값
SELECT deptno, MAX(sal), MIN(sal)
FROM emp
GROUP BY deptno
ORDER BY deptno;
10-2. emp 테이블에서 30번인 부서의 최고, 최저 SAL를 받는 사원의 정보 출력하는 쿼리 작성.
결과)
EMPNO ENAME HIREDATE SAL
---------- ---------- -------- ----------
7698 BLAKE 81/05/01 2850
7900 JAMES 81/12/03 950
-- 서브쿼리 이용
SELECT empno, ename, hiredate, sal
FROM emp
WHERE deptno =30
AND sal IN (
(SELECT MAX(sal) FROM emp WHERE deptno = 30),
(SELECT MIN(sal) FROM emp WHERE deptno = 30)
);
-- JOIN 이용
SELECT empno, ename, hiredate, sal
FROM (
SELECT MAX(sal) max, MIN(sal) min
FROM emp
WHERE deptno = 30
) a, emp b
WHERE b.deptno =30 AND b.sal = a.max OR b.sal = a.min;
11. insa 테이블에서
[실행결과]
부서명 총사원수 부서사원수 성별 성별사원수 부/전% 부성/전% 성/부%
개발부 60 14 F 8 23.3% 13.3% 57.1%
개발부 60 14 M 6 23.3% 10% 42.9%
기획부 60 7 F 3 11.7% 5% 4 2.9%
기획부 60 7 M 4 11.7% 6.7% 57.1%
영업부 60 16 F 8 26.7% 13.3% 50%
영업부 60 16 M 8 26.7% 13.3% 50%
인사부 60 4 M 4 6.7% 6.7% 100%
자재부 60 6 F 4 10% 6.7% 66.7%
자재부 60 6 M 2 10% 3.3% 33.3%
총무부 60 7 F 3 11.7% 5% 42.9%
총무부 60 7 M 4 11.7% 6.7% 57.1%
홍보부 60 6 F 3 10% 5% 50%
홍보부 60 6 M 3 10% 5% 50%
1) 총 사원 수 가져오기
SELECT COUNT(*)
FROM insa;
2) 부서 사원수
SELECT buseo, COUNT(*) 부서사원수
FROM insa
GROUP BY buseo;
3) 부서별 성별, 사원 수
SELECT buseo, MOD(SUBSTR(ssn, -7, 1), 2) 성별, COUNT(*) 부서별사원수
FROM insa
GROUP BY buseo, MOD(SUBSTR(ssn, -7, 1), 2)
ORDER BY buseo, MOD(SUBSTR(ssn, -7, 1), 2);
--
SELECT t2.*
, ROUND(t2.부서사원수/t2.총사원수 * 100, 2) || '%' "부/전%"
, ROUND(t2.성별사원수/t2.총사원수 * 100, 2) || '%' "부성/전%"
, ROUND(t2.성별사원수/t2.부서사원수 * 100, 2) || '%' "부성/부서%"
FROM (
SELECT t.buseo "부서명"
,(SELECT COUNT(*) FROM insa) "총사원수"
,(SELECT COUNT(*) FROM insa WHERE buseo = t.buseo) "부서사원수"
, t.gender "성별", COUNT(*) "성별사원수"
FROM (
SELECT buseo, name, ssn
, DECODE(MOD(SUBSTR(ssn, -7, 1), 2), 1, 'M', 'F') gender
FROM insa
)t
GROUP BY t.buseo, t.gender
ORDER BY t.buseo, t.gender
) t2;
12. insa테이블에서 여자인원수가 5명 이상인 부서만 출력.
-- 첫번째 방법: 서브쿼리 이용 --
SELECT t.*
FROM (
SELECT buseo, COUNT(*) 여자사원수
FROM insa
WHERE MOD(SUBSTR(ssn, -7, 1), 2) = 0
GROUP BY buseo
) t
WHERE t.여자사원수 >= 5;
-- 두번째 방법: GROUP BY절의 조건절인 HAVING절 이용 --
SELECT buseo, COUNT(*) 여자사원수
FROM insa
WHERE MOD(SUBSTR(ssn, -7, 1), 2) = 0
GROUP BY buseo
HAVING COUNT(*) >= 5; -- 이거와 같다. WHERE t.여자사원수 >= 5;
13. insa 테이블에서 급여(pay= basicpay+sudang)가 상위 15%에 해당되는 사원들 정보 출력
SELECT t.*
FROM (
SELECT buseo, name, basicpay+sudang pay
, RANK() OVER(ORDER BY basicpay+sudang DESC) "급여순위"
FROM insa
) t
WHERE 급여순위 <= (SELECT COUNT(*) FROM insa) * 0.15;
14. emp 테이블에서 sal의 전체사원에서의 등수 , 부서내에서의 등수를 출력하는 쿼리 작성
SELECT deptno, ename, sal
, RANK() OVER(ORDER BY sal DESC) "전체등수"
, RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) "부서등수"
, (SELECT COUNT(*) + 1 FROM emp b WHERE b.sal > a.sal) "전체등수2"
, (SELECT COUNT(*) + 1 FROM emp b WHERE b.sal > a.sal AND b.deptno = a.deptno) "부서등수2"
FROM emp a
ORDER BY deptno ASC;
문제) 20번, 40번 부서 제외하고 조회
부서 사원수가 3명 이상인 부서정보만 출력
SELECT e.deptno, d.dname, COUNT(*)
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.deptno NOT IN(20, 40)
GROUP BY e.deptno, d.dname
HAVING COUNT(*) >= 3;
------------
SELECT buseo, jikwi, COUNT(*)
FROM insa i
GROUP BY buseo, jikwi
ORDER BY buseo, jikwi
JOIN dept d ON e.deptno = d.deptno
WHERE e.deptno NOT IN(20, 40)
GROUP BY e.deptno, d.dname
HAVING COUNT(*) >= 3;
-----------------
1. emp 테이블에서 job 별로 사원수 몇 명 조회(출력) 하는 쿼리 작성.
[실행결과]
CLERK SALESMAN PRESIDENT MANAGER ANALYST
---------- ---------- ---------- ---------- ----------
3 4 1 3 1
2. emp 테이블에서 [JOB별로] 각 월별 입사한 사원의 수를 조회
ㄱ. COUNT(), DECODE() 사용
JOB COUNT(*) 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
CLERK 3 1 0 0 0 0 0 0 0 0 0 0 2
SALESMAN 4 0 2 0 0 0 0 0 0 2 0 0 0
PRESIDENT 1 0 0 0 0 0 0 0 0 0 0 1 0
MANAGER 3 0 0 0 1 1 1 0 0 0 0 0 0
ANALYST 1 0 0 0 0 0 0 0 0 0 0 0 1
ㄴ. GROUP BY 절 사용
월 인원수
---------- ----------
1 1
2 2
4 1
5 1
6 1
9 2
11 1
12 3
8개 행이 선택되었습니다.
3. emp 테이블에서 각 부서별 급여 많이 받는 사원 2명씩 출력
실행결과)
SEQ EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
1 7839 KING PRESIDENT 81/11/17 5000 10
2 7782 CLARK MANAGER 7839 81/06/09 2450 10
1 7902 FORD ANALYST 7566 81/12/03 3000 20
2 7566 JONES MANAGER 7839 81/04/02 2975 20
1 7698 BLAKE MANAGER 7839 81/05/01 2850 30
2 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
-- HR --
SELECT TO_CHAR( TO_DATE('2022.01.03'), 'IW' ) -- ISO 표준 주 월~일까지 1주.
, TO_CHAR( TO_DATE('2022.01.09'), 'IW' )
, TO_CHAR( TO_DATE('2022.02.04'), 'WW' ) -- 년의 주 1~7 끝어짐
, TO_CHAR( TO_DATE('2022.02.05'), 'WW' )
FROM dual;
-- SCOTT --
--------------------------------------------------------------------------------
-- 복습문제
--------------------------------------------------------------------------------
[실행결과]
CLERK SALESMAN PRESIDENT MANAGER ANALYST
---------- ---------- ---------- ---------- ----------
3 4 1 3 1
--1) GROUP BY job + COUNT(*) 사용
SELECT job, COUNT(*) "사원수"
FROM emp
GROUP BY job;
--2) 가로 출력
SELECT
COUNT (DECODE( job, 'CLERK', 1) ) CLERK
,COUNT (DECODE( job, 'SALESMAN', 1) ) SALESMAN
,COUNT (DECODE( job, 'PRESIDENT', 1) ) PRESIDENT
,COUNT (DECODE( job, 'MANAGER', 1) ) MANAGER
,COUNT (DECODE( job, 'ANALYST', 1) ) ANALYST
FROM emp;
--[피봇 (PIVOT) / 언피봇(UNPIVOT) 함수]
1. 오라클 11g부터 제공하는 함수
2. 행과 열을 뒤집는 함수
3. [형식]
SELECT *
FROM (피벗 대상 쿼리문) -- 1. 서버쿼리(세로를 가로로 만들기)
PIVOT (그룹함수(집계컬럼) FOR 피벗컬럼(대상) IN(피벗컬럼 값 AS 별칭...))
4. 순서는
1)피벗 대상 쿼리문
2) IN(목록)
3) FOR 피벗컬럼
4) 그룹함수(집게컬럼)
1) SELECT job FROM emp;
2) IN(목록)
3) FOR 피벗컬럼
4) 그룹함수(집게컬럼)
예)
SELECT *
FROM (
SELECT job
FROM emp
)
PIVOT (COUNT(job) FOR job IN('CLERK', 'SALESMAN', 'PRESIDENT', 'MANAGER', 'ANALYST' ))
2. emp 테이블에서 [JOB별로] 각 월별 입사한 사원의 수를 조회
ㄱ. COUNT(), DECODE() 사용
JOB COUNT(*) 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
CLERK 3 1 0 0 0 0 0 0 0 0 0 0 2
SALESMAN 4 0 2 0 0 0 0 0 0 2 0 0 0
PRESIDENT 1 0 0 0 0 0 0 0 0 0 0 1 0
MANAGER 3 0 0 0 1 1 1 0 0 0 0 0 0
ANALYST 1 0 0 0 0 0 0 0 0 0 0 0 1
SELECT job, COUNT(*)
, COUNT(decode( to_char(hiredate, 'MM'), '01', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '02', 'O')) "02월"
, count(decode( to_char(hiredate, 'mm'), '03', 'O')) "03월"
, count(decode( to_char(hiredate, 'mm'), '04', 'O')) "04월"
, count(decode( to_char(hiredate, 'mm'), '05', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '06', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '07', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '08', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '09', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '10', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '11', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '12', 'O')) "12월"
FROM emp
GROUP BY JOB;
SELECT job, EXTRACT( MONTH FROM hiredate) 월 , COUNT(*) 인원수
FROM emp
GROUP BY job, EXTRACT( MONTH FROM hiredate)
ORDER BY job, 월
ㄴ. GROUP BY 절 사용
월 인원수
---------- ----------
1 1
2 2
4 1
5 1
6 1
9 2
11 1
12 3
8개 행이 선택되었습니다.
--1)
SELECT TO_NUMBER(TO_CHAR( hiredate, 'MM' ) )월, COUNT(*) 인원수
FROM emp
GROUP BY TO_NUMBER(TO_CHAR(hiredate, 'MM' ) )
ORDER BY TO_NUMBER(TO_CHAR(hiredate, 'MM' ) );
--2) EXTRACT
SELECT EXTRACT( MONTH FROM hiredate)월, COUNT(*) 인원수
FROM emp
GROUP BY EXTRACT( MONTH FROM hiredate)
ORDER BY 월;
[문제] 피봇함수를 사용해서 아래와 같이 출력.
JOB 1월 2 3 4 5 6 7 8 9 10 11 12
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
CLERK 1 0 0 0 0 0 0 0 0 0 0 2
SALESMAN 0 2 0 0 0 0 0 0 2 0 0 0
PRESIDENT 0 0 0 0 0 0 0 0 0 0 1 0
MANAGER 0 0 0 1 1 1 0 0 0 0 0 0
ANALYST 0 0 0 0 0 0 0 0 0 0
1) 피봇 대상 쿼리
SELECT EXTRACT( MONTH FROM hiredate ) hire_month
FROM emp;
2) IN ( 목록 )
1,2,3,4,5,6,7,8,9, 10, 11, 12
3) FOR 피봇 컬럼
hire_month
4) 집계함수(컬럼 ))
COUNT( hire_month )
-- 전체 사원들이 입사한 월별 사원수 파악
SELECT *
FROM (
SELECT EXTRACT( MONTH FROM hiredate ) hire_month
FROM emp
)
PIVOT (COUNT(hire_month ) FOR hire_month IN(1,2,3,4,5,6,7,8,9, 10, 11, 12 ));
-- 전체 사원들이 입사한 월별 사원수 파악
SELECT *
FROM (
SELECT job, EXTRACT( MONTH FROM hiredate ) hire_month
FROM emp
)
PIVOT (COUNT(hire_month ) FOR hire_month IN(1 AS "1월",2,3,4,5,6,7,8,9, 10, 11, 12 ));
-- [ UNPIVOT() 정리 ]
3. emp 테이블에서 각 부서별 급여 많이 받는 사원 2명씩 출력
실행결과)
SEQ EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
1 7839 KING PRESIDENT 81/11/17 5000 10
2 7782 CLARK MANAGER 7839 81/06/09 2450 10
1 7902 FORD ANALYST 7566 81/12/03 3000 20
2 7566 JONES MANAGER 7839 81/04/02 2975 20
1 7698 BLAKE MANAGER 7839 81/05/01 2850 30
2 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
-- 1) TOP-N 방식
-- 2) RANK 함수
SELECT t.*
FROM (
SELECT empno, ename, job, mgr, hiredate, sal , comm, deptno
, RANK() OVER( PARTITION BY deptno ORDER BY sal + NVL(comm, 0) ) SEQ
FROM emp
) t
WHERE seq <= 2;
--------------------------------------------------------------------------------
[피봇문제 1] emp 테이블에서
1등급 2등급 ... 5등급
2 3 1
-- 등급) salgrade 테이블 - grade, losal, hisal + emp 테이블 조인
-- 부모,자식테이블 관계 X
-- NON EQUI JOIN 조건 BETWEEN ~ AND 연산자
SELECT empno, ename, sal , grade
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
-- 각 sal등급의 인원수 몇 명인지 조회 ?
1) GROUP BY
SELECT grade || '등급' , COUNT(*) 사원수
FROM (
SELECT empno, ename, sal , grade
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
) t
GROUP BY grade
ORDER BY grade ASC;
2) COUNT() + DECODE()
SELECT
COUNT( DECODE( grade, 1 , 'O' ) ) "1등급"
, COUNT( DECODE( grade, 2 , 'O' ) ) "2등급"
, COUNT( DECODE( grade, 3 , 'O' ) ) "3등급"
, COUNT( DECODE( grade, 4 , 'O' ) ) "4등급"
, COUNT( DECODE( grade, 5 , 'O' ) ) "5등급"
FROM (
SELECT empno, ename, sal , grade
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
) t;
3) PIVOT()
(1) 피봇대상
SELECT *
FROM (
SELECT grade
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
)
PIVOT( COUNT(grade) FOR grade IN ( 1 AS "1등급", 2, 3, 4, 5 ) );
11:00 수업 시작~
[피봇문제 2] emp 테이블에서 년도별 입사사원수를 조회
( 1980, 1981, 1982 )
SELECT DISTINCT TO_CHAR( hiredate, 'YYYY') YEAR
FROM emp;
1) GROUP BY
SELECT TO_CHAR( hiredate, 'YYYY') HIRE_YEAR, COUNT(*) 사원수
FROM emp
GROUP BY TO_CHAR( hiredate, 'YYYY')
ORDER BY HIRE_YEAR;
2) COUNT(), DECODE()
SELECT
COUNT( DECODE( TO_CHAR( hiredate, 'YYYY'), 1980 , 'O' ) ) "1980년 사원수"
, COUNT( DECODE( TO_CHAR( hiredate, 'YYYY'), 1981 , 'O' ) ) "1981년 사원수"
, COUNT( DECODE( TO_CHAR( hiredate, 'YYYY'), 1982 , 'O' ) ) "1982년 사원수"
FROM emp;
3) PIVOT()
SELECT *
FROM (
SELECT TO_CHAR( hiredate, 'YYYY') hire_year
FROM emp
)
PIVOT( COUNT( hire_year ) FOR hire_year IN ( 1980, 1981, 1982 ));
--
SELECT DISTINCT TO_CHAR( hiredate, 'YYYY') hire_year
FROM emp;
-- PIVOT() IN (목록 서브쿼리 사용할 수 없다. ) X
SELECT *
FROM (
SELECT TO_CHAR( hiredate, 'YYYY') hire_year
FROM emp
)
PIVOT( COUNT( hire_year ) FOR hire_year IN ( SELECT DISTINCT TO_CHAR( hiredate, 'YYYY') hire_year FROM emp ));
--------------------------------------------------------------------------------
-- [ ROLLUP 절과 CUBE 절 ]
ㄴ GROUP BY절에서 사용되어 그룹별 소계를 추가로 보여주는 역할을 한다.
ㄴ 즉, 추가적인 집계 정보를 보여준다 .
예) insa 테이블에서 남자사원수, 여자사원수를 조회 + 총사원수
SELECT
CASE MOD(SUBSTR( ssn , -7, 1), 2)
WHEN 1 THEN '남자'
ELSE '여자'
END gender
, COUNT(*) 사원수
FROM insa
GROUP BY MOD(SUBSTR( ssn , -7, 1), 2)
UNION ALL
SELECT '', COUNT(*)
FROM insa;
-- GROUP BY + ROLLUP 사용..
SELECT
CASE MOD(SUBSTR( ssn , -7, 1), 2)
WHEN 1 THEN '남자'
--ELSE '여자'
WHEN 0 THEN '여자'
END gender
, COUNT(*) 사원수
FROM insa
GROUP BY ROLLUP( MOD(SUBSTR( ssn , -7, 1), 2) );
-- 예) ROLLUP, CUBE 차이점 체크
-- insa 테이블에서 부서별 1차 그룹핑
-- ㄴ 직급별 2차 그룹핑
SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
GROUP BY buseo, jikwi
ORDER BY buseo, jikwi;
-- 1) 각 부서별 사원수 추가 조회
SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
GROUP BY buseo, jikwi
ORDER BY buseo, jikwi;SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
GROUP BY buseo, jikwi
ORDER BY buseo, jikwi;
-- 2)
SELECT buseo, COUNT(*) 부서원수
FROM insa
GROUP BY buseo;
-- 1) + 2) 출력.
SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
GROUP BY buseo, jikwi
UNION ALL
SELECT buseo, '' , COUNT(*) 부서원수
FROM insa
GROUP BY buseo
UNION ALL
SELECT '' ,'' , COUNT(*)
FROM insa
UNION ALL
SELECT '' ,jikwi , COUNT(*)
FROM insa
GROUP BY jikwi
--ORDER BY buseo, jikwi ;
-- ROLLUP 절 사용.
SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
GROUP BY CUBE( buseo, jikwi )
-- GROUP BY ROLLUP( buseo, jikwi )
ORDER BY buseo, jikwi;
-- 분할( parial) ROLLUP
SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
-- GROUP BY CUBE( buseo, jikwi )
-- GROUP BY buseo, ROLLUP(jikwi ) -- 전체사원수 60 X
GROUP BY ROLLUP( buseo), jikwi -- 전체사원수 60 X, 직위 부분집합 O, 부서 부분집합 X
ORDER BY buseo, jikwi;
-- 12:05 수업시작~
-- +5
-- GROUPING SETS 함수GROUPING SETS 함수
SELECT buseo , '' , COUNT(*)
FROM insa
GROUP BY buseo
UNION ALL
SELECT '', jikwi , COUNT(*)
FROM insa
GROUP BY jikwi;
--
SELECT buseo, jikwi , COUNT(*)
FROM insa
GROUP BY GROUPING SETS( buseo, jikwi )
ORDER BY buseo, jikwi;
--------------------------------------------------------------------------------
-- 자바 : 임의의 수(난수) 0.0 <= Math.random() < 1.0
-- 오라클 : dbms_random 패키지 == 관련 함수, 프로시저 등등
SELECT DBMS_RANDOM.VALUE -- JAVA Math.random()
, TRUNC( DBMS_RANDOM.VALUE(0,101) ) -- 0<= <101
, FLOOR( DBMS_RANDOM.VALUE(0,101) ) -- 0<= <101
, FLOOR(DBMS_RANDOM.VALUE(0, 45 )) + 1 -- 1<= <=45
, DBMS_RANDOM.STRING('U', 5 ) -- 대문자 5개
, DBMS_RANDOM.STRING('L', 5 ) -- 소문자 5개
, DBMS_RANDOM.STRING('A', 5 ) -- 대소문자 5개
, DBMS_RANDOM.STRING('', 5 ) -- 대소문자 5개 + 특수문자
FROM dual;
[문제] SMS 인증번호 숫자 6자리
DBMS_RANDOM.VALUE 함수를 사용해서
SELECT
TRUNC( DBMS_RANDOM.VALUE( 100000 , 1000000 ) ) -- 100000 <= <= 999999
, LPAD(FLOOR(DBMS_RANDOM.VALUE(0, 1000000)), 6, '0') -- 1 <= 숫자 <=45
FROM dual;
-- [ 오라클 자료형 ( Data Type )) 정리 ]
1) CHAR
ㄱ) "고정길이" 문자 스트링에 사용된다. <-> "가변길이"
예) 주민등록번호 저장 -> 모든 사람들이 14자리
ㄴ. ['a']['b']['c']['']['']['']['']['']
ㄷ. [][][] abcd 에러 발생
ㄹ. DB 설정에 따라 1문자 1~4바이트 처리.
ㅁ. 형식
CHAR(SIZE [byte] | char])
최대 2000바이트 문자를 저장한다.
예) CHAR == CHAR(1 BYTE) == CHAR(1)
CHAR(3) == CHAR(3 BYTE)
CHAR(3 BYTE) -- 3바이트
CHAR(3 CHAR) -- 3문자
ㅂ.
CREATE TABLE tbl_char (
-- 컬럼명 자료형([크기])
aa CHAR -- CHAR(1) == CHAR(1 BYTE)
, bb CHAR(3) -- CHAR(3 BYTE) 알파벳 3문자, 한글 1문자
, cc CHAR(3 CHAR)
);
-- Table TBL_CHAR이(가) 생성되었습니다.
SELECT *
FROM tbl_char;
--
INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( 'A','ABC','abc') ;
COMMIT;
INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( '한','ABC','abc') ;
-- ORA-12899: value too large for column "SCOTT"."TBL_CHAR"."AA" (actual: 3, maximum: 1)
INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( '1','홍길','abc') ;
INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( '1','MBC','홍길동') ;
INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( '1','MBC','abc') ;
--
SELECT VSIZE('A'), VSIZE('한')
FROM dual;
COMMIT;
--
DROP TABLE tbl_char ;
-- Table TBL_CHAR이(가) 삭제되었습니다.
CHAR[( 3 [BYTE] | CHAR )] 고정길이 ['a'][blank][blank] 고정길이, 2000바이트
2) NCHAR
U[N]ICODE + CHAR 'A' '홍' 2바이트
NCHAR(SIZE)
NCHAR(3) 'abc' / '홍길동' 고정길이, 2000바이트
NCHAR(1) == NCHAR
고정길이 : CHAR, NCHAR
예) 주민등록번호 : CHAR(14 BYTE)
예) 한글 고정 6자리 : NCHAR(6)
3) NVARCHAR2(size) 4000 바이트
4) VARCHAR2(SIZE BYTE | CHAR ) 4000 바잍트
VAR+CHAR2(SIZE BYTE|CHAR) 가변길이
N+VAR+CHAR2(size) 가변길이
예) CHAR(12) [a][b][c][blank][][][][][][][][blank]
VARCHAR2(12 BYTE)[a][b][c]
예) 게시글의 제목 : CHAR / NCHAR
VARCHAR2 / N+VARCHAR2 가변길이
DESC EMP;
ENAME VARCHAR2(10)
INSERT INTO emp ( empno , ename ) VALUES ( 9999, '홍길동님')
5) VARCHAR == VARCHAR2의 시노님
6) LONG - 가변길이, 2GB
-- 자바 long 정수 -900경~ 900경
예) 게시판 글 내용
content LONG
7) NUMBER[(p[,s])] 숫자( 정수, 실수 )
예) p : precision 전체 자리수(정밀도) 1~38
s : scale 소숫점자리수 -84~127
NUMBER == NUMBER( 38, 127 )
NUMBER(3) 정수 == NUMBER(3, 0)
NUMBER(5,2) 실수
예)
CREATE TABLE tbl_number(
name NVARCHAR2(10) -- 문자열 char,nchar,varchar,varchar2, nvarchar2, long
, kor NUMBER(3)-- NUMBER 0~100 정수 999 ~ -999
, eng NUMBER(3)
, mat NUMBER(3)
, tot NUMBER(3)
, avg NUMBER(5,2) -- 100.00
, r NUMBER(2) --- 99~99
);
-- Table TBL_NUMBER이(가) 생성되었습니다.
INSERT INTO tbl_number VALUES
( '홍길동', 90, 89, 100, null, null, null );
INSERT INTO tbl_number VALUES
( '윤재민', 90, 80.12, 78, null, null, null );
COMMIT;
INSERT INTO tbl_number VALUES
( '탁인혁', 90, 20, 78, null, null, null );
ROLLBACK;
-- 총점, 평균 계산 UPDATE
UPDATE tbl_number
SET tot = kor+eng+mat , avg = ( kor +eng +mat)/3;
-- WHERE
COMMIT;
[문제] R 등수 null 처리 UPDATE
SELECT tot
, ( SELECT COUNT(*) +1 FROM tbl_number WHERE tot > t1.tot ) r
FROM tbl_number t1;
-- 모든 학생들 등수 처리..
UPDATE tbl_number t1
SET r = ( SELECT COUNT(*) +1 FROM tbl_number WHERE tot > t1.tot );
-- WHERE;
COMMIT;
SELECT *
FROM tbl_number;
ROLLBACK;
3:01 수업 시작~~~
실제 데이터
NUMBER 선언
저장되는 값
123.89 NUMBER 123.89
123.89 NUMBER(3) 124
123.89 NUMBER(3,2) precision을 초과
123.89 NUMBER(4,2) precision을 초과
123.89 NUMBER(5,2) 123.89
123.89 NUMBER(6,1) 123.9
123.89 NUMBER(6,-2) 100
.01234 NUMBER(4,5) .01234
.00012 NUMBER(4,5) .00012
.000127 NUMBER(4,5) .00013
.0000012 NUMBER(2,7) .0000012
.00000123 NUMBER(2,7) .0000012
1.2e-4 NUMBER(2,5) 0.00012
1.2e-5 NUMBER(2,5) 0.00001
DESC emp;
DESC dept;
DESC salgrade;
DESC bonus;
8) FLOAT(p) == 내부적으로는 NUMBER 처리된다.
9) DATE
ㄴ 날짜, 시간
ㄴ 고정 길이 7 byte 저장.
TIMESTAMP[(n)] == TIMESTAMP(6)
00:00:00.000000000 나노세컨드
SELECT SYSDATE -- '23/03/23'
FROM dual;
SELECT hiredate -- 80/12/17
, TO_CHAR( hiredate, 'TS')
FROM emp;
예) 게시판 작성일 : DATE O , TIMESTAMP X
10) RAW(size) 2진데이터( 0,1 ) 2000바이트
LONGRAW 2GB
RAW == 가공하지 않은 , 날것,
홍길동.gif 이미지파일 -> TABLE 저장
01010111 01010111
게시판 글 쓰기 + (첨부파일*3개) 특정 폴더 : 첨부파일 저장하고
TABLE : 저장된 파일의 경로만 저장.
11) BFILE 2진데이터 (0,1) , 외부 파일 형태로 저장
12) LOB = [L]arge [OB]ject
B + LOB = Binary(2진 데이터)
C + LOB = Char
NC + LOB = NChar
게시판 글 내용(CONTENT ) CLOB/LONG/NVARCHAR2(2000)
13) ROWID
ROW(행) + ID(고유한값)
SELECT ROWID, emp.*
FROM emp;
--------------------------------------------------------------------------------
오라클 문자 : char, nchar, varchar, varchar2, nvarchar2
long, clob
숫자 : number(p,s) , float
날짜 : date, timestamp
이진데이터 : RAW/LONGRAW , BFILE , BLOB
LOB : blob, clob, nclob
--------------------------------------------------------------------------------
-- [ COUNT 함수 ]
ㄴ 쿼리한 행의 수를 반환한다.
ㄴ COUNT(컬럼명) 함수는 NULL이 아닌 행의 수를 출력하고
COUNT(*) 함수는 NULL을 포함한 행의 수를 출력한다.
【형식】
COUNT([* ¦ DISTINCT ¦ ALL] 컬럼명) [ [OVER] (analytic 절)]
--
SELECT COUNT( DISTINCT buseo )
FROM insa;
-- ORA-00937: not a single-group group function
-- 복수행 함수 이기에 name, basicpay랑 같이 사용 X
SELECT name, basicpay
-- OVER절을 사용하면 누적된 수를 카운팅한다.
, COUNT(*) OVER( ORDER BY basicpay ASC )
FROM insa;
-- 부서로 그룹핑한 후 누적된 수를 조회...
SELECT name, basicpay , buseo
, COUNT(*) OVER( PARTITION BY buseo ORDER BY basicpay ASC )
FROM insa;
-- SUM()
-- basicpay의 누적된 합을 조회
SELECT name, basicpay
, SUM(basicpay) OVER( ORDER BY basicpay ASC )
FROM insa;
-- 부서로 그룹핑한 후 누적된 수를 조회...
SELECT name, basicpay , buseo
, SUM(basicpay) OVER( PARTITION BY buseo ORDER BY basicpay ASC )
FROM insa;
-- AVG()
-- basicpay의 누적된 평균을 조회
SELECT name, basicpay
, AVG(basicpay) OVER( ORDER BY basicpay ASC )
FROM insa;
-- 부서로 그룹핑한 후 누적된 평균를 조회...
SELECT name, basicpay , buseo
, AVG(basicpay) OVER( PARTITION BY buseo ORDER BY basicpay ASC )
FROM insa;
--------------------------------------------------------------------------------
4:03 수업 시작~~
테이블 생성/수정/삭제 CRUD ~ + 제약조건
--------------------------------------------------------------------------------
[데이터 저장] - CRUD = 테이블(table)
***[ DB 모델링 ] ***
요구분석 -> 개념적 모델링 -> 논리적 모델링 -> 물리적모델링
[오라클] 테이블생성
MySQL
;
-- [회원정보]를 관리하는 테이블 생성 : 회원테이블
컬럼명(열) 물리적컬럼명 자료형 크기 필수입력 주석
아이디 id VARCHAR2 10 BYTE NOT NULL PRIMARY KEY
이름 name NVARCHAR2 10 문자 NOT NULL
나이 age NUMBER 3
전화번호 tel CHAR 13 NOT NULL
010-1234-1234
생일 birth DATE
기타 etc NVARCHAR2 200
LONG 2GB
CLOB
:
:
재무상태(연봉,부..)
회사/직급/연봉
-- 테이블 생성
【형식】
CREATE TABLE 테이블명
(컬럼명 데이터타입 [, 컬럼명 데이터타입]...)
[TABLESPACE tablespace명]
[PCTFREE 정수]
[PCTUSED 정수]
[INITRANS 정수]
[MAXTRANS 정수]
[STORAGE storage절]
[LOGGING ¦ NOLOGGING]
[CACHE ¦ NOCACHE];
SELECT *
FROM emp;
-- 테이블 생성( 가장 단순한 방법 )
【형식】
CREATE [GLOBAL TEMPORARY] TABLE [schema.] table {relational_table ¦
object_table ¦
XML_Type_table}
【relational_table의 형식】
[(relational_properties) [ON COMMIT {DELETE ¦ PRESERVE} ROWS]
[physical_properties] [table_properties];
【object_table의 형식】
OF [schema.]object_type [object_table_substitution]
[(object_properties) [ON COMMIT {DELETE ¦ PRESERVE} ROWS]
[OID_clause] [OID_index_clause] [physical_properties] [table_properties];
【XMP_Type_table의 형식】
OF XMLTYPE [(object_properties) [XMLTYPE XML_Type_storage] [XML_Schema_spec]
[XML_Type_virtual_columns] [ON COMMIT {DELETE ¦ PRESERVE} ROWS]
[OID_clause] [OID_index_clause] [physical_properities] [table_properities]
이 문을 실행하려면 자신의 스키마에서는 CREATE TABLE 시스템권한이 있어야 하고
다른 사용자 스키마내에서 테이블을 생성하려면 CREATE ANY TABLE 시스템권한이 있어야 하며,
테이블스페이스를 위해서 UNLIMITED TABLE 시스템권한이 있어야 한다.
【간단한형식】
CREATE [GLOBAL TEMPORARY] TABLE [schema.] table
(
열이름 데이터타입 [DEFAULT 표현식] [제약조건]
[,열이름 데이터타입 [DEFAULT 표현식] [제약조건] ]
[,...]
);
실습)
기타 etc NVARCHAR2 200
CREATE TABLE scott.tbl_member
(
id VARCHAR2(10) NOT NULL PRIMARY KEY
, name NVARCHAR2(10) NOT NULL
, age NUMBER(3)
, tel CHAR(13) NOT NULL
, birth DATE
, etc NVARCHAR2(200)
);
-- Table SCOTT.TBL_MEMBER이(가) 생성되었습니다.
1) 생성된 테이블 확인
SELECT *
FROM tabs -- user_tables
WHERE REGEXP_LIKE( table_name , 'member' , 'i');
WHERE table_name LIKE '%MEMBER%';
2) 테이블 삭제.
【형식】
DROP TABLE [schema.]table [CASCADE CONSTRAINTS] [PURGE];
PURGE(퍼지) : 사전적의지 깨끗이하다, 제거하다.
DROP TABLE scott.tbl_member PURGE;
-- HR --
SELECT TO_CHAR( TO_DATE('2022.01.03'), 'IW' ) -- ISO 표준 주 월~일까지 1주.
, TO_CHAR( TO_DATE('2022.01.09'), 'IW' )
, TO_CHAR( TO_DATE('2022.02.04'), 'WW' ) -- 년의 주 1~7 끝어짐
, TO_CHAR( TO_DATE('2022.02.05'), 'WW' )
FROM dual;
-- SCOTT --
--------------------------------------------------------------------------------
-- 복습문제
--------------------------------------------------------------------------------
[실행결과]
CLERK SALESMAN PRESIDENT MANAGER ANALYST
---------- ---------- ---------- ---------- ----------
3 4 1 3 1
--1) GROUP BY job + COUNT(*) 사용
SELECT job, COUNT(*) "사원수"
FROM emp
GROUP BY job;
--2) 가로 출력
SELECT
COUNT (DECODE( job, 'CLERK', 1) ) CLERK
,COUNT (DECODE( job, 'SALESMAN', 1) ) SALESMAN
,COUNT (DECODE( job, 'PRESIDENT', 1) ) PRESIDENT
,COUNT (DECODE( job, 'MANAGER', 1) ) MANAGER
,COUNT (DECODE( job, 'ANALYST', 1) ) ANALYST
FROM emp;
--[피봇 (PIVOT) / 언피봇(UNPIVOT) 함수]
1. 오라클 11g부터 제공하는 함수
2. 행과 열을 뒤집는 함수
3. [형식]
SELECT *
FROM (피벗 대상 쿼리문) -- 1. 서버쿼리(세로를 가로로 만들기)
PIVOT (그룹함수(집계컬럼) FOR 피벗컬럼(대상) IN(피벗컬럼 값 AS 별칭...))
4. 순서는
1)피벗 대상 쿼리문
2) IN(목록)
3) FOR 피벗컬럼
4) 그룹함수(집게컬럼)
1) SELECT job FROM emp;
2) IN(목록)
3) FOR 피벗컬럼
4) 그룹함수(집게컬럼)
예)
SELECT *
FROM (
SELECT job
FROM emp
)
PIVOT (COUNT(job) FOR job IN('CLERK', 'SALESMAN', 'PRESIDENT', 'MANAGER', 'ANALYST' ))
2. emp 테이블에서 [JOB별로] 각 월별 입사한 사원의 수를 조회
ㄱ. COUNT(), DECODE() 사용
JOB COUNT(*) 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
CLERK 3 1 0 0 0 0 0 0 0 0 0 0 2
SALESMAN 4 0 2 0 0 0 0 0 0 2 0 0 0
PRESIDENT 1 0 0 0 0 0 0 0 0 0 0 1 0
MANAGER 3 0 0 0 1 1 1 0 0 0 0 0 0
ANALYST 1 0 0 0 0 0 0 0 0 0 0 0 1
SELECT job, COUNT(*)
, COUNT(decode( to_char(hiredate, 'MM'), '01', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '02', 'O')) "02월"
, count(decode( to_char(hiredate, 'mm'), '03', 'O')) "03월"
, count(decode( to_char(hiredate, 'mm'), '04', 'O')) "04월"
, count(decode( to_char(hiredate, 'mm'), '05', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '06', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '07', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '08', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '09', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '10', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '11', 'O')) "01월"
, count(decode( to_char(hiredate, 'mm'), '12', 'O')) "12월"
FROM emp
GROUP BY JOB;
SELECT job, EXTRACT( MONTH FROM hiredate) 월 , COUNT(*) 인원수
FROM emp
GROUP BY job, EXTRACT( MONTH FROM hiredate)
ORDER BY job, 월
ㄴ. GROUP BY 절 사용
월 인원수
---------- ----------
1 1
2 2
4 1
5 1
6 1
9 2
11 1
12 3
8개 행이 선택되었습니다.
--1)
SELECT TO_NUMBER(TO_CHAR( hiredate, 'MM' ) )월, COUNT(*) 인원수
FROM emp
GROUP BY TO_NUMBER(TO_CHAR(hiredate, 'MM' ) )
ORDER BY TO_NUMBER(TO_CHAR(hiredate, 'MM' ) );
--2) EXTRACT
SELECT EXTRACT( MONTH FROM hiredate)월, COUNT(*) 인원수
FROM emp
GROUP BY EXTRACT( MONTH FROM hiredate)
ORDER BY 월;
[문제] 피봇함수를 사용해서 아래와 같이 출력.
JOB 1월 2 3 4 5 6 7 8 9 10 11 12
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
CLERK 1 0 0 0 0 0 0 0 0 0 0 2
SALESMAN 0 2 0 0 0 0 0 0 2 0 0 0
PRESIDENT 0 0 0 0 0 0 0 0 0 0 1 0
MANAGER 0 0 0 1 1 1 0 0 0 0 0 0
ANALYST 0 0 0 0 0 0 0 0 0 0
1) 피봇 대상 쿼리
SELECT EXTRACT( MONTH FROM hiredate ) hire_month
FROM emp;
2) IN ( 목록 )
1,2,3,4,5,6,7,8,9, 10, 11, 12
3) FOR 피봇 컬럼
hire_month
4) 집계함수(컬럼 ))
COUNT( hire_month )
-- 전체 사원들이 입사한 월별 사원수 파악
SELECT *
FROM (
SELECT EXTRACT( MONTH FROM hiredate ) hire_month
FROM emp
)
PIVOT (COUNT(hire_month ) FOR hire_month IN(1,2,3,4,5,6,7,8,9, 10, 11, 12 ));
-- 전체 사원들이 입사한 월별 사원수 파악
SELECT *
FROM (
SELECT job, EXTRACT( MONTH FROM hiredate ) hire_month
FROM emp
)
PIVOT (COUNT(hire_month ) FOR hire_month IN(1 AS "1월",2,3,4,5,6,7,8,9, 10, 11, 12 ));
-- [ UNPIVOT() 정리 ]
3. emp 테이블에서 각 부서별 급여 많이 받는 사원 2명씩 출력
실행결과)
SEQ EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
1 7839 KING PRESIDENT 81/11/17 5000 10
2 7782 CLARK MANAGER 7839 81/06/09 2450 10
1 7902 FORD ANALYST 7566 81/12/03 3000 20
2 7566 JONES MANAGER 7839 81/04/02 2975 20
1 7698 BLAKE MANAGER 7839 81/05/01 2850 30
2 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
-- 1) TOP-N 방식
-- 2) RANK 함수
SELECT t.*
FROM (
SELECT empno, ename, job, mgr, hiredate, sal , comm, deptno
, RANK() OVER( PARTITION BY deptno ORDER BY sal + NVL(comm, 0) ) SEQ
FROM emp
) t
WHERE seq <= 2;
--------------------------------------------------------------------------------
[피봇문제 1] emp 테이블에서
1등급 2등급 ... 5등급
2 3 1
-- 등급) salgrade 테이블 - grade, losal, hisal + emp 테이블 조인
-- 부모,자식테이블 관계 X
-- NON EQUI JOIN 조건 BETWEEN ~ AND 연산자
SELECT empno, ename, sal , grade
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
-- 각 sal등급의 인원수 몇 명인지 조회 ?
1) GROUP BY
SELECT grade || '등급' , COUNT(*) 사원수
FROM (
SELECT empno, ename, sal , grade
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
) t
GROUP BY grade
ORDER BY grade ASC;
2) COUNT() + DECODE()
SELECT
COUNT( DECODE( grade, 1 , 'O' ) ) "1등급"
, COUNT( DECODE( grade, 2 , 'O' ) ) "2등급"
, COUNT( DECODE( grade, 3 , 'O' ) ) "3등급"
, COUNT( DECODE( grade, 4 , 'O' ) ) "4등급"
, COUNT( DECODE( grade, 5 , 'O' ) ) "5등급"
FROM (
SELECT empno, ename, sal , grade
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
) t;
3) PIVOT()
(1) 피봇대상
SELECT *
FROM (
SELECT grade
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
)
PIVOT( COUNT(grade) FOR grade IN ( 1 AS "1등급", 2, 3, 4, 5 ) );
11:00 수업 시작~
[피봇문제 2] emp 테이블에서 년도별 입사사원수를 조회
( 1980, 1981, 1982 )
SELECT DISTINCT TO_CHAR( hiredate, 'YYYY') YEAR
FROM emp;
1) GROUP BY
SELECT TO_CHAR( hiredate, 'YYYY') HIRE_YEAR, COUNT(*) 사원수
FROM emp
GROUP BY TO_CHAR( hiredate, 'YYYY')
ORDER BY HIRE_YEAR;
2) COUNT(), DECODE()
SELECT
COUNT( DECODE( TO_CHAR( hiredate, 'YYYY'), 1980 , 'O' ) ) "1980년 사원수"
, COUNT( DECODE( TO_CHAR( hiredate, 'YYYY'), 1981 , 'O' ) ) "1981년 사원수"
, COUNT( DECODE( TO_CHAR( hiredate, 'YYYY'), 1982 , 'O' ) ) "1982년 사원수"
FROM emp;
3) PIVOT()
SELECT *
FROM (
SELECT TO_CHAR( hiredate, 'YYYY') hire_year
FROM emp
)
PIVOT( COUNT( hire_year ) FOR hire_year IN ( 1980, 1981, 1982 ));
--
SELECT DISTINCT TO_CHAR( hiredate, 'YYYY') hire_year
FROM emp;
-- PIVOT() IN (목록 서브쿼리 사용할 수 없다. ) X
SELECT *
FROM (
SELECT TO_CHAR( hiredate, 'YYYY') hire_year
FROM emp
)
PIVOT( COUNT( hire_year ) FOR hire_year IN ( SELECT DISTINCT TO_CHAR( hiredate, 'YYYY') hire_year FROM emp ));
--------------------------------------------------------------------------------
-- [ ROLLUP 절과 CUBE 절 ]
ㄴ GROUP BY절에서 사용되어 그룹별 소계를 추가로 보여주는 역할을 한다.
ㄴ 즉, 추가적인 집계 정보를 보여준다 .
예) insa 테이블에서 남자사원수, 여자사원수를 조회 + 총사원수
SELECT
CASE MOD(SUBSTR( ssn , -7, 1), 2)
WHEN 1 THEN '남자'
ELSE '여자'
END gender
, COUNT(*) 사원수
FROM insa
GROUP BY MOD(SUBSTR( ssn , -7, 1), 2)
UNION ALL
SELECT '', COUNT(*)
FROM insa;
-- GROUP BY + ROLLUP 사용..
SELECT
CASE MOD(SUBSTR( ssn , -7, 1), 2)
WHEN 1 THEN '남자'
--ELSE '여자'
WHEN 0 THEN '여자'
END gender
, COUNT(*) 사원수
FROM insa
GROUP BY ROLLUP( MOD(SUBSTR( ssn , -7, 1), 2) );
-- 예) ROLLUP, CUBE 차이점 체크
-- insa 테이블에서 부서별 1차 그룹핑
-- ㄴ 직급별 2차 그룹핑
SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
GROUP BY buseo, jikwi
ORDER BY buseo, jikwi;
-- 1) 각 부서별 사원수 추가 조회
SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
GROUP BY buseo, jikwi
ORDER BY buseo, jikwi;SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
GROUP BY buseo, jikwi
ORDER BY buseo, jikwi;
-- 2)
SELECT buseo, COUNT(*) 부서원수
FROM insa
GROUP BY buseo;
-- 1) + 2) 출력.
SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
GROUP BY buseo, jikwi
UNION ALL
SELECT buseo, '' , COUNT(*) 부서원수
FROM insa
GROUP BY buseo
UNION ALL
SELECT '' ,'' , COUNT(*)
FROM insa
UNION ALL
SELECT '' ,jikwi , COUNT(*)
FROM insa
GROUP BY jikwi
--ORDER BY buseo, jikwi ;
-- ROLLUP 절 사용.
SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
GROUP BY CUBE( buseo, jikwi )
-- GROUP BY ROLLUP( buseo, jikwi )
ORDER BY buseo, jikwi;
-- 분할( parial) ROLLUP
SELECT buseo, jikwi, COUNT(*) 사원수
FROM insa
-- GROUP BY CUBE( buseo, jikwi )
-- GROUP BY buseo, ROLLUP(jikwi ) -- 전체사원수 60 X
GROUP BY ROLLUP( buseo), jikwi -- 전체사원수 60 X, 직위 부분집합 O, 부서 부분집합 X
ORDER BY buseo, jikwi;
-- 12:05 수업시작~
-- +5
-- GROUPING SETS 함수GROUPING SETS 함수
SELECT buseo , '' , COUNT(*)
FROM insa
GROUP BY buseo
UNION ALL
SELECT '', jikwi , COUNT(*)
FROM insa
GROUP BY jikwi;
--
SELECT buseo, jikwi , COUNT(*)
FROM insa
GROUP BY GROUPING SETS( buseo, jikwi )
ORDER BY buseo, jikwi;
--------------------------------------------------------------------------------
-- 자바 : 임의의 수(난수) 0.0 <= Math.random() < 1.0
-- 오라클 : dbms_random 패키지 == 관련 함수, 프로시저 등등
SELECT DBMS_RANDOM.VALUE -- JAVA Math.random()
, TRUNC( DBMS_RANDOM.VALUE(0,101) ) -- 0<= <101
, FLOOR( DBMS_RANDOM.VALUE(0,101) ) -- 0<= <101
, FLOOR(DBMS_RANDOM.VALUE(0, 45 )) + 1 -- 1<= <=45
, DBMS_RANDOM.STRING('U', 5 ) -- 대문자 5개
, DBMS_RANDOM.STRING('L', 5 ) -- 소문자 5개
, DBMS_RANDOM.STRING('A', 5 ) -- 대소문자 5개
, DBMS_RANDOM.STRING('', 5 ) -- 대소문자 5개 + 특수문자
FROM dual;
[문제] SMS 인증번호 숫자 6자리
DBMS_RANDOM.VALUE 함수를 사용해서
SELECT
TRUNC( DBMS_RANDOM.VALUE( 100000 , 1000000 ) ) -- 100000 <= <= 999999
, LPAD(FLOOR(DBMS_RANDOM.VALUE(0, 1000000)), 6, '0') -- 1 <= 숫자 <=45
FROM dual;
-- [ 오라클 자료형 ( Data Type )) 정리 ]
1) CHAR
ㄱ) "고정길이" 문자 스트링에 사용된다. <-> "가변길이"
예) 주민등록번호 저장 -> 모든 사람들이 14자리
ㄴ. ['a']['b']['c']['']['']['']['']['']
ㄷ. [][][] abcd 에러 발생
ㄹ. DB 설정에 따라 1문자 1~4바이트 처리.
ㅁ. 형식
CHAR(SIZE [byte] | char])
최대 2000바이트 문자를 저장한다.
예) CHAR == CHAR(1 BYTE) == CHAR(1)
CHAR(3) == CHAR(3 BYTE)
CHAR(3 BYTE) -- 3바이트
CHAR(3 CHAR) -- 3문자
ㅂ.
CREATE TABLE tbl_char (
-- 컬럼명 자료형([크기])
aa CHAR -- CHAR(1) == CHAR(1 BYTE)
, bb CHAR(3) -- CHAR(3 BYTE) 알파벳 3문자, 한글 1문자
, cc CHAR(3 CHAR)
);
-- Table TBL_CHAR이(가) 생성되었습니다.
SELECT *
FROM tbl_char;
--
INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( 'A','ABC','abc') ;
COMMIT;
INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( '한','ABC','abc') ;
-- ORA-12899: value too large for column "SCOTT"."TBL_CHAR"."AA" (actual: 3, maximum: 1)
INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( '1','홍길','abc') ;
INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( '1','MBC','홍길동') ;
INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( '1','MBC','abc') ;
--
SELECT VSIZE('A'), VSIZE('한')
FROM dual;
COMMIT;
--
DROP TABLE tbl_char ;
-- Table TBL_CHAR이(가) 삭제되었습니다.
CHAR[( 3 [BYTE] | CHAR )] 고정길이 ['a'][blank][blank] 고정길이, 2000바이트
2) NCHAR
U[N]ICODE + CHAR 'A' '홍' 2바이트
NCHAR(SIZE)
NCHAR(3) 'abc' / '홍길동' 고정길이, 2000바이트
NCHAR(1) == NCHAR
고정길이 : CHAR, NCHAR
예) 주민등록번호 : CHAR(14 BYTE)
예) 한글 고정 6자리 : NCHAR(6)
3) NVARCHAR2(size) 4000 바이트
4) VARCHAR2(SIZE BYTE | CHAR ) 4000 바잍트
VAR+CHAR2(SIZE BYTE|CHAR) 가변길이
N+VAR+CHAR2(size) 가변길이
예) CHAR(12) [a][b][c][blank][][][][][][][][blank]
VARCHAR2(12 BYTE)[a][b][c]
예) 게시글의 제목 : CHAR / NCHAR
VARCHAR2 / N+VARCHAR2 가변길이
DESC EMP;
ENAME VARCHAR2(10)
INSERT INTO emp ( empno , ename ) VALUES ( 9999, '홍길동님')
5) VARCHAR == VARCHAR2의 시노님
6) LONG - 가변길이, 2GB
-- 자바 long 정수 -900경~ 900경
예) 게시판 글 내용
content LONG
7) NUMBER[(p[,s])] 숫자( 정수, 실수 )
예) p : precision 전체 자리수(정밀도) 1~38
s : scale 소숫점자리수 -84~127
NUMBER == NUMBER( 38, 127 )
NUMBER(3) 정수 == NUMBER(3, 0)
NUMBER(5,2) 실수
예)
CREATE TABLE tbl_number(
name NVARCHAR2(10) -- 문자열 char,nchar,varchar,varchar2, nvarchar2, long
, kor NUMBER(3)-- NUMBER 0~100 정수 999 ~ -999
, eng NUMBER(3)
, mat NUMBER(3)
, tot NUMBER(3)
, avg NUMBER(5,2) -- 100.00
, r NUMBER(2) --- 99~99
);
-- Table TBL_NUMBER이(가) 생성되었습니다.
INSERT INTO tbl_number VALUES
( '홍길동', 90, 89, 100, null, null, null );
INSERT INTO tbl_number VALUES
( '윤재민', 90, 80.12, 78, null, null, null );
COMMIT;
INSERT INTO tbl_number VALUES
( '탁인혁', 90, 20, 78, null, null, null );
ROLLBACK;
-- 총점, 평균 계산 UPDATE
UPDATE tbl_number
SET tot = kor+eng+mat , avg = ( kor +eng +mat)/3;
-- WHERE
COMMIT;
[문제] R 등수 null 처리 UPDATE
SELECT tot
, ( SELECT COUNT(*) +1 FROM tbl_number WHERE tot > t1.tot ) r
FROM tbl_number t1;
-- 모든 학생들 등수 처리..
UPDATE tbl_number t1
SET r = ( SELECT COUNT(*) +1 FROM tbl_number WHERE tot > t1.tot );
-- WHERE;
COMMIT;
SELECT *
FROM tbl_number;
ROLLBACK;
3:01 수업 시작~~~
실제 데이터
NUMBER 선언
저장되는 값
123.89 NUMBER 123.89
123.89 NUMBER(3) 124
123.89 NUMBER(3,2) precision을 초과
123.89 NUMBER(4,2) precision을 초과
123.89 NUMBER(5,2) 123.89
123.89 NUMBER(6,1) 123.9
123.89 NUMBER(6,-2) 100
.01234 NUMBER(4,5) .01234
.00012 NUMBER(4,5) .00012
.000127 NUMBER(4,5) .00013
.0000012 NUMBER(2,7) .0000012
.00000123 NUMBER(2,7) .0000012
1.2e-4 NUMBER(2,5) 0.00012
1.2e-5 NUMBER(2,5) 0.00001
DESC emp;
DESC dept;
DESC salgrade;
DESC bonus;
8) FLOAT(p) == 내부적으로는 NUMBER 처리된다.
9) DATE
ㄴ 날짜, 시간
ㄴ 고정 길이 7 byte 저장.
TIMESTAMP[(n)] == TIMESTAMP(6)
00:00:00.000000000 나노세컨드
SELECT SYSDATE -- '23/03/23'
FROM dual;
SELECT hiredate -- 80/12/17
, TO_CHAR( hiredate, 'TS')
FROM emp;
예) 게시판 작성일 : DATE O , TIMESTAMP X
10) RAW(size) 2진데이터( 0,1 ) 2000바이트
LONGRAW 2GB
RAW == 가공하지 않은 , 날것,
홍길동.gif 이미지파일 -> TABLE 저장
01010111 01010111
게시판 글 쓰기 + (첨부파일*3개) 특정 폴더 : 첨부파일 저장하고
TABLE : 저장된 파일의 경로만 저장.
11) BFILE 2진데이터 (0,1) , 외부 파일 형태로 저장
12) LOB = [L]arge [OB]ject
B + LOB = Binary(2진 데이터)
C + LOB = Char
NC + LOB = NChar
게시판 글 내용(CONTENT ) CLOB/LONG/NVARCHAR2(2000)
13) ROWID
ROW(행) + ID(고유한값)
SELECT ROWID, emp.*
FROM emp;
--------------------------------------------------------------------------------
오라클 문자 : char, nchar, varchar, varchar2, nvarchar2
long, clob
숫자 : number(p,s) , float
날짜 : date, timestamp
이진데이터 : RAW/LONGRAW , BFILE , BLOB
LOB : blob, clob, nclob
--------------------------------------------------------------------------------
-- [ COUNT 함수 ]
ㄴ 쿼리한 행의 수를 반환한다.
ㄴ COUNT(컬럼명) 함수는 NULL이 아닌 행의 수를 출력하고
COUNT(*) 함수는 NULL을 포함한 행의 수를 출력한다.
【형식】
COUNT([* ¦ DISTINCT ¦ ALL] 컬럼명) [ [OVER] (analytic 절)]
--
SELECT COUNT( DISTINCT buseo )
FROM insa;
-- ORA-00937: not a single-group group function
-- 복수행 함수 이기에 name, basicpay랑 같이 사용 X
SELECT name, basicpay
-- OVER절을 사용하면 누적된 수를 카운팅한다.
, COUNT(*) OVER( ORDER BY basicpay ASC )
FROM insa;
-- 부서로 그룹핑한 후 누적된 수를 조회...
SELECT name, basicpay , buseo
, COUNT(*) OVER( PARTITION BY buseo ORDER BY basicpay ASC )
FROM insa;
-- SUM()
-- basicpay의 누적된 합을 조회
SELECT name, basicpay
, SUM(basicpay) OVER( ORDER BY basicpay ASC )
FROM insa;
-- 부서로 그룹핑한 후 누적된 수를 조회...
SELECT name, basicpay , buseo
, SUM(basicpay) OVER( PARTITION BY buseo ORDER BY basicpay ASC )
FROM insa;
-- AVG()
-- basicpay의 누적된 평균을 조회
SELECT name, basicpay
, AVG(basicpay) OVER( ORDER BY basicpay ASC )
FROM insa;
-- 부서로 그룹핑한 후 누적된 평균를 조회...
SELECT name, basicpay , buseo
, AVG(basicpay) OVER( PARTITION BY buseo ORDER BY basicpay ASC )
FROM insa;
--------------------------------------------------------------------------------
테이블 생성/수정/삭제 CRUD ~ + 제약조건
--------------------------------------------------------------------------------
[데이터 저장] - CRUD = 테이블(table)
***[ DB 모델링 ] ***
요구분석 -> 개념적 모델링 -> 논리적 모델링 -> 물리적모델링
[오라클] 테이블생성
MySQL
;
-- [회원정보]를 관리하는 테이블 생성 : 회원테이블
컬럼명(열) 물리적컬럼명 자료형 크기 필수입력 주석
아이디 id VARCHAR2 10 BYTE NOT NULL PRIMARY KEY
이름 name NVARCHAR2 10 문자 NOT NULL
나이 age NUMBER 3
전화번호 tel CHAR 13 NOT NULL
010-1234-1234
생일 birth DATE
기타 etc NVARCHAR2 200
LONG 2GB
CLOB
:
:
재무상태(연봉,부..)
회사/직급/연봉
-- 테이블 생성
【형식】
CREATE TABLE 테이블명
(컬럼명 데이터타입 [, 컬럼명 데이터타입]...)
[TABLESPACE tablespace명]
[PCTFREE 정수]
[PCTUSED 정수]
[INITRANS 정수]
[MAXTRANS 정수]
[STORAGE storage절]
[LOGGING ¦ NOLOGGING]
[CACHE ¦ NOCACHE];
SELECT *
FROM emp;
-- 테이블 생성( 가장 단순한 방법 )
【형식】
CREATE [GLOBAL TEMPORARY] TABLE [schema.] table {relational_table ¦
object_table ¦
XML_Type_table}
【relational_table의 형식】
[(relational_properties) [ON COMMIT {DELETE ¦ PRESERVE} ROWS]
[physical_properties] [table_properties];
【object_table의 형식】
OF [schema.]object_type [object_table_substitution]
[(object_properties) [ON COMMIT {DELETE ¦ PRESERVE} ROWS]
[OID_clause] [OID_index_clause] [physical_properties] [table_properties];
【XMP_Type_table의 형식】
OF XMLTYPE [(object_properties) [XMLTYPE XML_Type_storage] [XML_Schema_spec]
[XML_Type_virtual_columns] [ON COMMIT {DELETE ¦ PRESERVE} ROWS]
[OID_clause] [OID_index_clause] [physical_properities] [table_properities]
이 문을 실행하려면 자신의 스키마에서는 CREATE TABLE 시스템권한이 있어야 하고
다른 사용자 스키마내에서 테이블을 생성하려면 CREATE ANY TABLE 시스템권한이 있어야 하며,
테이블스페이스를 위해서 UNLIMITED TABLE 시스템권한이 있어야 한다.
【간단한형식】
CREATE [GLOBAL TEMPORARY] TABLE [schema.] table
(
열이름 데이터타입 [DEFAULT 표현식] [제약조건]
[,열이름 데이터타입 [DEFAULT 표현식] [제약조건] ]
[,...]
);
실습)
기타 etc NVARCHAR2 200
CREATE TABLE scott.tbl_member
(
id VARCHAR2(10) NOT NULL PRIMARY KEY
, name NVARCHAR2(10) NOT NULL
, age NUMBER(3)
, tel CHAR(13) NOT NULL
, birth DATE
, etc NVARCHAR2(200)
);
-- Table SCOTT.TBL_MEMBER이(가) 생성되었습니다.
1) 생성된 테이블 확인
SELECT *
FROM tabs -- user_tables
WHERE REGEXP_LIKE( table_name , 'member' , 'i');
WHERE table_name LIKE '%MEMBER%';
2) 테이블 삭제.
【형식】
DROP TABLE [schema.]table [CASCADE CONSTRAINTS] [PURGE];
PURGE(퍼지) : 사전적의지 깨끗이하다, 제거하다.
DROP TABLE scott.tbl_member PURGE;
-- SYS --
-- FIRST / LAST 함수
SELECT MIN(sal) KEEP ( DENSE_RANK FIRST ORDER BY sal DESC ) "min_pay"
, MAX(sal) KEEP ( DENSE_RANK LAST ORDER BY sal ) "max_pay"
, SUM(sal) KEEP ( DENSE_RANK FIRST ORDER BY sal ) "min_pay"
, AVG(sal) KEEP ( DENSE_RANK LAST ORDER BY sal ) "max_pay"
FROM emp;
-- 조민경 --
SELECT d.deptno
, COUNT(e.deptno) 부서원수
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno
GROUP BY d.deptno
ORDER BY d.deptno ASC;
--
SELECT b.buseo, a.jikwi, nvl(b.cnt,0)
FROM (SELECT DISTINCT jikwi from INSA) a left join (SELECT buseo, jikwi, COUNT(jikwi) cnt
FROM insa
GROUP BY buseo, jikwi) b
PARTITION BY (b.buseo) on a.jikwi = b.jikwi
order by buseo, jikwi;
--참고사이트 :
--https://blog.naver.com/pino93/222708884741
--https://blog.naver.com/blacksmail/222349045230
--https://jungmina.com/858
ORACLE 10g PARTITION OUTER JOIN
'-java spring notes- > db' 카테고리의 다른 글
| 관계를 맺자! 조인 (18) | 2023.03.24 |
|---|---|
| 오라클 day7~day8 (0) | 2023.03.22 |
| 오라클 2 -데이터 베이스 객체 (1) | 2023.03.21 |
| 1오라클 - 오라클 이란? (1) | 2023.03.20 |
| postgres실습,docker (1) | 2023.01.21 |
