1.날짜처리
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select trunc(months_between(sysdate,to_date('19970101','yyyymmdd'))/12) "년",
trunc(months_between(sysdate,to_date('19970101','yyyymmdd'))-
trunc(months_between(sysdate,to_date('19970101','yyyymmdd'))/12)*12) "개월",
trunc(months_between(sysdate,to_date('19970101','yyyymmdd'))-
trunc(months_between(sysdate,to_date('19970101','yyyymmdd'))) * 30.5) "일" from dual;
2.Null값처리
select empno, sal*12+nvl(comm,0) as "연봉2" from emp;
select empno, nvl2(comm,sal*12+comm, sal+12) as "연봉4" from emp;
3.count
select count(mgr) from emp;
select count(distinct(mgr)) from emp;
select count(*) from emp;
select count(1) from emp;
select avg(comm), avg(nvl(comm,0)), count(comm), count(nvl(comm,0)) from emp;
4.rownum
select * from emp where rownum <= 5 order by sal desc;
select * from
(select * from emp order by sal desc)
where rownum <=5;
5.Decode
*AND
select sum(decode(substr(dname,1,1),'A',decode(substr(loc,1,3),'NEW',1),0)) from dept;
*OR
select decode(substr(dname,1,1),'A',1,decode(substr(loc,1,3),'NEW',1,0)) from dept;
6.decode vs case
SELECT job,
SUM(DECODE(deptno, 10, 1, 0)) AS dept10,
SUM(DECODE(deptno, 20, 1, 0)) AS dept20,
SUM(DECODE(deptno, 30, 1, 0)) AS dept30,
COUNT(*) AS total
FROM EMP
GROUP BY job;
SELECT job,
SUM(CASE WHEN deptno=10 THEN 1 ELSE 0 end) AS dept10,
SUM(CASE WHEN deptno=20 THEN 1 ELSE 0 end) AS dept20,
SUM(CASE WHEN deptno=30 THEN 1 ELSE 0 end) AS dept30,
COUNT(*) AS total
FROM EMP
GROUP BY job;
SELECT job,
ename,
sal,
DECODE(job, 'CLERK', sal*2, 'SALESMAN', sal*1.05, 'MANAGER', sal*1.1, sal*1.15) AS newsal FROM EMP;
#CASE문
SELECT deptno, TO_CHAR(hiredate, 'yyyy') hireyear, COUNT(*) coun,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'01',sal)) m01,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'02',sal)) m02,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'03',sal)) m03,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'04',sal)) m04,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'05',sal)) m05,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'06',sal)) m06,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'07',sal)) m07,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'08',sal)) m08,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'09',sal)) m09,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'10',sal)) m10,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'11',sal)) m11,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'12',sal)) m12,
SUM(sal) tot
FROM EMP
GROUP BY deptno, TO_CHAR(hiredate,'yyyy');
#rollup
SELECT deptno, TO_CHAR(hiredate, 'yyyy') hireyear, COUNT(*) coun,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'01',sal)) m01,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'02',sal)) m02,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'03',sal)) m03,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'04',sal)) m04,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'05',sal)) m05,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'06',sal)) m06,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'07',sal)) m07,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'08',sal)) m08,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'09',sal)) m09,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'10',sal)) m10,
SUM(DECODE(TO_CHAR(hiredate,'mm'),'11',sal)) m11,
SUM(CASE WHEN(TO_CHAR(hiredate,'mm')='12') THEN sal ELSE 0 end) m12,
SUM(sal) tot
FROM EMP
GROUP BY ROLLUP((deptno, TO_CHAR(hiredate,'yyyy')));
#
CREATE TABLE S_EMP NOLOGGING
AS SELECT empno, ename, job, mgr, sal, sal/100 AS hour, comm, deptno FROM EMP;
#case문으로 변환
SELECT SUM(hour) hour,
SUM(sal) sal,
SUM(CASE WHEN COMM IS NOT NULL THEN hour end) s_h,
SUM(CASE WHEN COMM IS NOT NULL THEN sal end) s_s,
SUM(CASE WHEN comm IS NULL OR length(comm)=4 THEN hour ELSE 0 end) s_h22,
SUM(CASE WHEN comm IS NULL OR LENGTH(comm)=4 THEN sal ELSE 0 end) s_s2
FROM S_EMP;
#10g, 11g IN, OR 못쓰고 풀아우터조인 안됨.
SELECT e1.ename,
e1.mgr,
e2.ename AS "Manager",
e2.empno
FROM EMP e1, EMP e2
WHERE e1.mgr = e2.empno(+)
AND e2.deptno(+) IN (10, 20);
#풀아우터조인(ANSI)
SELECT a.id,
b.id,
A.NAME,
B.NAME
FROM FULLOUTER1 A FULL outer join FULLOUTER2 B
ON a.id = b.id;
#풀아우터조인 조작
SELECT (CASE WHEN a.id IS NOT NULL THEN a.id
ELSE b.id end) AS id,
A.NAME,
B.NAME
FROM FULLOUTER1 A FULL outer JOIN FULLOUTER2 B
ON A.id = B.id
#인라인뷰+아우터조인
SELECT a.customer, sales1, sales2
FROM
(SELECT customer
FROM TEST_U1
WHERE cdate BETWEEN '20120501' AND '20120505'
UNION
SELECT customer
FROM TEST_U2
WHERE cdate BETWEEN '20120501' AND '20120505')A,
(SELECT customer, SUM(sales) sales1
ORDER BY 1;
#스칼라서브쿼리+아우터조인
SELECT a.customer,
(SELECT SUM(b.sales) sales1 FROM TEST_U1 b
WHERE b.customer = a.customer
AND b.cdate BETWEEN '20120501' AND '20120505'
GROUP BY b.customer) sales1,
(SELECT SUM(c.sales) sales2 FROM TEST_U2 c
WHERE c.customer = a.customer
AND c.cdate BETWEEN '20120501' AND '20120505'
GROUP BY c.customer) sales2
FROM
(SELECT customer
FROM TEST_U1
WHERE cdate BETWEEN '20120501' AND '20120505'
UNION
SELECT customer
FROM TEST_U2
WHERE cdate BETWEEN '20120501' AND '20120505') a
ORDER BY a.customer;
FROM TEST_U1
WHERE cdate BETWEEN '20120501' AND '20120505'
GROUP BY customer)B,
(SELECT customer, SUM(sales) sales2
FROM TEST_U2
WHERE cdate BETWEEN '20120501' AND '20120505'
GROUP BY customer)C
WHERE a.customer = b.customer(+)
AND a.customer = c.customer(+)
ORDER BY a.customer;
#Union all
SELECT customer, SUM(sales1) sales1, SUM(sales2) sales2
FROM
(SELECT customer, sales sales1, 0 sales2
FROM TEST_U1
WHERE cdate BETWEEN '20120501' AND '20120505'
UNION ALL
SELECT customer, 0 sales1, sales sales2
FROM TEST_U2
WHERE cdate BETWEEN '20120501' AND '20120505')
GROUP BY customer
ORDER BY customer;
#풀아우터조인
SELECT b.customer, c.customer, sales1, sales2
FROM
(SELECT b.customer, SUM(sales) sales1
FROM TEST_U1 b
WHERE cdate BETWEEN '20120501' AND '20120505'
GROUP BY customer) b FULL outer JOIN
(SELECT c.customer, SUM(sales) sales2
FROM TEST_U2 c
WHERE cdate BETWEEN '20120501' AND '20120505'
GROUP BY customer) c
ON b.customer = c.customer
ORDER BY b.customer;
# Union All로 논리적인 하나의 테이블 만든 후
SELECT a.ym, a.key1, b.amt, a.amt1, a.amt2, a.amt3
FROM
(SELECT ym, key1, amt1 amt1, 0 amt2, 0 amt3
FROM TEST31
UNION ALL
SELECT ym, key1, 0 amt1, amt2 amt2, 0 amt3
FROM TEST32
UNION ALL
SELECT ym, key1, 0 amt1, 0 amt2, amt3 amt3
FROM test33) a, TEST30 b
WHERE a.key1 = b.key1;
# 계층형쿼리 (위->밑)
SELECT LEVEL, empno, ename, mgr
FROM EMP START WITH empno = 7839
CONNECT BY PRIOR empno = mgr;
# 계층형쿼리 (밑->위)
SELECT LEVEL, empno, ename, mgr
FROM EMP START WITH empno = 7369
CONNECT BY PRIOR mgr= empno;
#실행계획보기
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL, 'advanced allstats last'));
======================================================
1. Rollup
SELECT empno, SUM(sal)
FROM EMP
GROUP BY ROLLUP(empno);
SELECT empno, ename, SUM(sal)
FROM EMP
GROUP BY ROLLUP(empno, ename);
SELECT deptno, job, ename, comm, SUM(sal)
FROM EMP
GROUP BY ROLLUP((deptno, job, ename, comm));
SELECT deptno, empno, ename, sal,
SUM(sal), grouping_id(empno, ename, sal) gb
FROM EMP
GROUP BY deptno, ROLLUP((EMPno, ename, sal));
#합계찍기
SELECT DECODE(GROUPING(empno),1,'합계',empno) empno, SUM(sal), GROUPING(empno)
FROM EMP
GROUP BY ROLLUP(empno);
SELECT deptno,
DECODE(GROUPING(empno),1,'소계',empno) empno, ename, sal,
SUM(sal), GROUPING(deptno) a, GROUPING(empno) b, GROUPING(ename) c,
grouping_id(empno, ename) d, grouping_id(empno, ename, sal) gb
FROM EMP
GROUP BY deptno, ROLLUP((empno, ename, SAL));
SELECT deptno, empno,
DECODE(GROUPING(ename),1,'소계',ename) ename, sal,
SUM(sal), GROUPING(deptno) a, GROUPING(empno) b, GROUPING(ename) c,
grouping_id(empno, ename) d, grouping_id(empno, ename, sal) gb
FROM EMP
GROUP BY deptno, ROLLUP((empno, ename, SAL));
#총계의 이중복제
SELECT deptno, job, ename, comm, SUM(sal),
DECODE(grouping_id(1,job), 3, AVG(sal), SUM(sal)) AA,
grouping_id(1,job) gb
FROM EMP
GROUP BY ROLLUP(1, (deptno, job, ename, comm));
#부서별합계의 이중복제
SELECT a.deptno, a.job, b.dname, b.loc, SUM(sal),
grouping_id(a.deptno, 1, a.job)
FROM EMP a, DEPT b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.deptno, 1, (a.job, b.dname, b.loc))
ORDER BY a.deptno, a.job;
#Rollup 최고봉
SELECT deptno, DECODE(GROUPING(empno),1,DECODE(group_id(),0,deptno||' 합계',1, deptno||' 평균',2, deptno||' 최소값',3, deptno||' 최고값',empno))empno,
DECODE(group_id(),0,SUM(sal),1,ROUND(AVG(sal)),2,MIN(sal),3,MAX(sal)) sal,
grouping_id(1,deptno,1,empno) aa, group_id()
FROM EMP
GROUP BY 1, ROLLUP(1,1,1,deptno,1,1,1,empno)
ORDER BY deptno, aa, empno DESC;
SELECT owner, object_name, rn, DECODE(grouping_id(1), 0, SUM(object_id), 1, SUM(object_id) over(ORDER BY CEIL(rn/10))) SUM, GROUPING(1), CEIL(rn/10)
FROM (SELECT owner, object_name, object_id, ROWNUM rn FROM all_objects WHERE ROWNUM <=100)
GROUP BY CEIL(rn/10), ROLLUP(1,(owner,object_name, rn, object_id));
#Union All로 작성
SELECT center, team, SUM(amt)
FROM TEMP23
WHERE center='대전' AND team LIKE '개발%'
GROUP BY center, team
UNION ALL
SELECT '대전', '개발팀소계', SUM(SUM(amt))
FROM TEMP23
WHERE center='대전' AND team LIKE '개발%'
GROUP BY center, team
UNION ALL
SELECT center, team, SUM(amt)
FROM TEMP23
WHERE center='대전' AND team LIKE '업무%'
GROUP BY center, team
UNION ALL
SELECT '대전', '업무팀소계', SUM(SUM(amt))
FROM TEMP23
WHERE center='대전' AND team LIKE '업무%'
GROUP BY center, team
UNION ALL
SELECT '대전', '팀소계', SUM(SUM(amt))
FROM TEMP23
WHERE center='대전'
GROUP BY center, team
UNION ALL
SELECT center, team, SUM(amt)
FROM TEMP23
WHERE center='서울'
GROUP BY center, team
UNION ALL
SELECT '서울', '소계', SUM(SUM(amt))
FROM TEMP23
WHERE center='서울'
GROUP BY center, team
UNION ALL
SELECT center, team, SUM(amt)
FROM TEMP23
WHERE center='제주'
GROUP BY center, team
UNION ALL
SELECT '제주', '소계', SUM(SUM(amt))
FROM TEMP23
WHERE center='제주'
GROUP BY center, team
UNION ALL
SELECT '합계', '', SUM(SUM(amt))
FROM TEMP23
GROUP BY center, team;
#With문으로
WITH tt23 AS
(SELECT center, team, SUM(amt) amt
FROM TEMP23
GROUP BY center, team)
SELECT center, team, amt
FROM tt23
WHERE center='대전' AND team LIKE '개발%'
UNION ALL
SELECT '대전','개발팀소계', SUM(amt)
FROM tt23
WHERE center='대전' AND team LIKE '개발%'
UNION all
SELECT center, team, amt
FROM tt23
WHERE center='대전' AND team LIKE '업무%'
UNION ALL
SELECT '대전','업무팀소계', SUM(amt)
FROM tt23
WHERE center='대전' AND team LIKE '업무%'
UNION ALL
SELECT '대전','팀소계', SUM(amt)
FROM tt23
WHERE center='대전'
UNION all
SELECT center, team, amt
FROM tt23
WHERE center='서울'
UNION ALL
SELECT '서울','팀소계', SUM(amt)
FROM tt23
WHERE center='서울'
UNION all
SELECT center, team, amt
FROM tt23
WHERE center='제주'
UNION ALL
SELECT '제주','팀소계', SUM(amt)
FROM tt23
WHERE center='제주'
UNION all
SELECT '합계', '', SUM(amt)
FROM tt23;
#rollup변환 최고
SELECT nvl(center, '합계') center,
DECODE(grouping_id(center,team),0, team, 1, DECODE(center, '대전', SUBSTR(team,1,2)||'팀')||'소계') team,
SUM(amt) amt, grouping_id(center, SUBSTR(team,1,2))
FROM TEMP23
GROUP BY ROLLUP(center, SUBSTR(team,1,2), team)
HAVING center = '대전'
OR grouping_id(center, SUBSTR(team,1,2))!=1
ORDER BY 1;
#
SELECT key1, a, b, c, ROUND(100*(a-b)/a) per
FROM (SELECT key1, SUM(a) a, SUM(b) b, SUM(a-b) c
FROM (SELECT key1,
SUM(DECODE(key2, 'A', amt)) a,
SUM(DECODE(key2, 'B', amt)) b
FROM TEST35 GROUP BY key1)
GROUP BY key1)
UNION ALL
SELECT key1, a, b, c, ROUND(100*(a-b)/a) per
FROM (SELECT '합계' AS key1, SUM(a) a, SUM(b) b, (SUM(a-b)) c
FROM (SELECT KEY1,
SUM(DECODE(key2, 'A', amt)) a,
SUM(DECODE(key2, 'B', amt)) b
FROM TEST35 GROUP BY key1))
ORDER BY key1;
#rank함수
SELECT deptno, empno, sal,
RANK() OVER (ORDER BY sal) AS asc_rank,
RANK() OVER (ORDER BY sal DESC nulls last) AS desc_rank,
DENSE_RANK() OVER( ORDER BY sal DESC nulls last) AS drank2,
ROW_NUMBER() OVER (ORDER BY sal DESC nulls last) AS drank3
FROM EMP;
#over함수
SELECT deptno, empno, sal, SUM(sal)
OVER (PARTITION by deptno ORDER BY empno ROWS unbounded preceding) csum
FROM EMP;
#부서별 sal 총합
SELECT emp.*, SUM(sal) OVER(PARTITION BY deptno) sum_sal
FROM EMP;
#
SELECT empno, ename, job, mgr, sal, deptno, rnum, rnum2, rnum3
FROM (SELECT emp.*,
ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) rnum,
RANK() OVER(PARTITION BY deptno ORDER BY sal) rnum2,
DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal) rnum3
FROM emp) WHERE rnum <= 3;
#
SELECT TO_NUMBER(month),
SUM(tot_sales) monthly_sales,
SUM(SUM(tot_sales)) OVER (ORDER BY TO_NUMBER(month) ROWS BETWEEN unbounded preceding AND unbounded following) total_sales
FROM ORDERS
where year = 2012 AND region_id = 6
GROUP BY TO_NUMBER(month)
ORDER BY TO_NUMBER(month);
#
SELECT TO_NUMBER(month),
SUM(tot_sales) monthly_sales,
TRUNC(AVG(SUM(tot_sales)) OVER(ORDER BY TO_NUMBER(month) ROWS BETWEEN 1 preceding AND 1 following)) rolling_avg
FROM ORDERS
where year = 2012 AND region_id = 6
GROUP BY TO_NUMBER(month)
ORDER BY TO_NUMBER(month);
#first_value/last_value
SELECT TO_NUMBER(month),
FIRST_VALUE(SUM(tot_sales)) OVER(ORDER BY TO_NUMBER(month) ROWS BETWEEN 1 preceding AND 1 following) prev_month,
SUM(tot_sales) monthly_sales,
LAST_VALUE(SUM(tot_sales)) OVER(ORDER BY TO_NUMBER(month) ROWS BETWEEN 1 preceding AND 1 following) next_month
FROM ORDERS
where year = 2012 AND region_id = 6
GROUP BY TO_NUMBER(month)
ORDER BY TO_NUMBER(month);
#lag/lead 함수
SELECT cdate 일자, amt 금액, crate,
LAG(crate, 1) OVER (ORDER BY cdate) AS "D-1",
LAG(crate, 2) OVER (ORDER BY cdate) AS "D-2",
LAG(crate, 3) OVER (ORDER BY cdate) AS "D-3",
LAG(crate, 4) OVER (ORDER BY cdate) AS "D-4",
LAG(crate, 5) OVER (ORDER BY cdate) AS "D-5"
FROM TEST02
WHERE cdate BETWEEN '20010101' AND '20120331';
#
SELECT cdate 일자, amt 금액, crate,
LAG(crate, 1) OVER (ORDER BY cdate) AS "D-1",
amt*LAG(crate, 1) OVER (ORDER BY cdate) AS "D-1"
FROM TEST02
WHERE cdate BETWEEN '20010101' AND '20120331';
#
SELECT cdate 일자, amt 금액, crate,
lead(crate, 1) OVER (ORDER BY cdate) AS "D+1",
lead(crate, 2) OVER (ORDER BY cdate) AS "D+2",
lead(crate, 3) OVER (ORDER BY cdate) AS "D+3",
lead(crate, 4) OVER (ORDER BY cdate) AS "D+4",
lead(crate, 5) OVER (ORDER BY cdate) AS "D+5"
FROM TEST02
WHERE cdate BETWEEN '20010101' AND '20120331';
#누적분산함수
SELECT RANK(10000) within GROUP (ORDER BY SUM(tot_sales) desc) hyp_rank,
DENSE_RANK(10000) within GROUP (ORDER BY SUM(tot_sales) desc) hyp_dense_rank,
CUME_DIST(10000) within GROUP (ORDER BY SUM(tot_sales) desc) hyp_cume_dist,
PERCENT_RANK(10000) within GROUP (ORDER BY SUM(tot_sales) desc) hyp_percent_rank
FROM ORDERS
WHERE year = 2012
GROUP BY month;
#MERGE(insert+update)
MERGE INTO EMP_TEST eu USING EMP e
ON (eu.empno = e.empno)
WHEN matched THEN UPDATE SET eu.sal = e.sal*2
WHEN NOT matched THEN
INSERT VALUES(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno);
#MERGE(update+delete)
MERGE INTO EMP_TEST eu USING EMP e
ON (eu.empno = e.empno)
WHEN matched THEN UPDATE SET eu.sal = e.sal*2
WHERE eu.empno = e.empno
DELETE WHERE (eu.mgr IS null)
#rollup을 이용한 복제
SELECT coll, DEPT, fre, sup, jun, sen, MIN(fre),
grouping_id(coll,0,1,2)a
FROM TEST11
GROUP BY ROLLUP((coll,dept,fre,sup,jun,sen),0,1,2)
ORDER BY grouping_id(coll,0,1,2);
SELECT coll, DEPT,
DECODE(a,0,'1학년',1,'2학년',3,'3학년',7,'4학년') key3,
DECODE(a,0,fre,1,sup,3,jun,7,sen) jumsu
FROM( SELECT coll, DEPT, fre, sup, jun, sen, MIN(fre),
grouping_id(coll,0,1,2)a
FROM TEST11
GROUP BY ROLLUP((coll,dept,fre,sup,jun,sen),0,1,2)
ORDER BY grouping_id(coll,0,1,2))
WHERE coll IS NOT null;
SELECT 'A' TYPE, SUM(amt)
FROM TEST34
WHERE key_type IN ('1', '3')
UNION ALL
SELECT 'B' TYPE, SUM(amt)
FROM TEST34
WHERE key_type IN('2','3');
SELECT
'A' type1, SUM(CASE WHEN key_type IN ('1', '3') THEN amt ELSE 0 end) amt1,
'B' type2, SUM(CASE WHEN key_type IN ('2', '3') THEN amt ELSE 0 end) amt2
FROM TEST34
ORDER BY 1;
SELECT DECODE(t2.r_cnt, 1, 'A', 2, 'B') TYPE,
SUM(amt) amt
FROM TEST34 T1,
(SELECT ROWNUM r_cnt FROM TEST34
WHERE ROWNUM <3) T2
WHERE T1.key_type = DECODE(T1.key_type,3,t1.key_type,t2.r_cnt)
GROUP BY DECODE(t2.r_cnt,1,'A',2,'B');
SELECT DECODE(key_type, 1, 'A', 2, 'B') KEY,
SUM(amt),
LEAD(SUM(amt), DECODE(key_type, '1', 2, 1)) OVER (ORDER BY DECODE(key_type, 1, 'A', 2, 'B')) AS plus,
SUM(amt)+LEAD(SUM(amt), DECODE(key_type, '1', 2, 1)) OVER (ORDER BY DECODE(key_type, 1, 'A', 2, 'B')) AS amt
FROM TEST34
GROUP BY key_type;
#페이징처리
SELECT *
FROM (SELECT a.*, ROWNUM rnum
FROM ( SELECT
s.empno, s.ename, s.job, s.hiredate, s.sal, s.deptno
FROM EMP s
ORDER BY s.empno, s.ename, s.job
) a
WHERE ROWNUM <= 10)
WHERE rnum >= 1;
#시퀀스생성
CREATE SEQUENCE TT_S_11
INCREMENT BY 1
START WITH 1
MAXVALUE 9999999999999
CYCLE
CACHE 1000
ORDER;
SELECT TT_S_11.NEXTVAL FROM dual;
SELECT ROWID, ROWNUM, empno, ename
FROM EMP
ORDER BY empno, ROWID;
ALTER TABLE EMP
DISABLE CONSTRAINT PK_EMP;
ALTER TABLE EMP
DISABLE CONSTRAINT fk_deptno;
INSERT INTO EMP values(7369, 'MOODAE', 'SALESMAN',0,'',0,0,0);
SELECT empno, ename FROM EMP A
WHERE ROWID > (SELECT MIN(rowid)
FROM EMP b
WHERE a.empno = b.empno);
DELETE EMP a
WHERE ROWID > (SELECT MIN(rowid)
FROM EMP b
WHERE a.empno = b.empno);
COMMIT;
ALTER TABLE EMP
enABLE CONSTRAINT PK_EMP;
ALTER TABLE EMP
enABLE CONSTRAINT fk_deptno;
#
SELECT hour*MAN AS MH, SUM(bi) AS money
FROM MH
WHERE NO BETWEEN 1001 AND 1008
GROUP BY hour*man;
SELECT 판매부서,
SUM( CASE WHEN 판매일자 = TO_date('20120122', 'yyyymmdd') THEN 수량 ELSE 0 END ) AS 당일합계,
SUM( CASE WHEN 판매일자 BETWEEN TO_DATE('20120122', 'yyyymmdd')-7 AND TO_DATE('20120122', 'yyyymmdd') THEN 수량 ELSE 0 END ) AS 주간합계,
substr(MAX(TO_NUMBER(판매일자||수량)),9,20) AS 최근최고분,
SUM(판매가격) AS 월간판매합계
FROM SALE_T
WHERE 판매일자 LIKE '201201%'
GROUP BY 판매부서;
#풀아우터조인
SELECT NVL(a.상품,b.상품) AS 상품,
nvl(a.계획연월, b.판매연월) AS 연월,
NVL(계획수량, 0) 계획수량,
NVL(판매수량, 0) 판매수량
FROM (SELECT 상품, 계획연월, SUM(계획수량) 계획수량
FROM 판매계획
WHERE 계획연월 BETWEEN '20120101' AND '20120331'
GROUP BY 상품, 계획연월)a
FULL outer JOIN
(SELECT 상품, 판매연월, SUM(판매수량) 판매수량
FROM 판매실적
WHERE 판매연월 BETWEEN '20120101' AND '20120331'
GROUP BY 상품, 판매연월)b
ON a.상품 = b.상품
AND a.계획연월 = b.판매연월
ORDER BY 1,2;
#Union all사용
SELECT 상품, 연월, NVL(SUM(계획수량),0) AS 계획수량,
NVL(SUM(실적수량),0) AS 실적수량
FROM (SELECT 상품, 계획연월 AS 연월, 계획수량,
TO_NUMBER(null) AS 실적수량
FROM 판매계획
WHERE 계획연월 BETWEEN '20120101' AND '20120331'
UNION ALL
SELECT 상품, 판매연월 AS 연월,
TO_NUMBER(null) AS 계획수량, 판매수량
FROM 판매실적
WHERE 판매연월 BETWEEN '20120101' AND '20120331'
)a
GROUP BY 상품, 연월
ORDER BY 상품, 연월;
#
SELECT a.pandate, a.qty, SUM(b.qty) AS 누적판매량
FROM TEST_U3 a, TEST_U3 b
WHERE a.pandate > b.pandate
OR (a.pandate = b.pandate AND a.qty>=b.qty)
GROUP BY a.PANDATE, a.qty
ORDER BY 1;
SELECT pandate, qty, SUM(qty) OVER(ORDER BY pandate ROWS BETWEEN unbounded preceding AND CURRENT row)
FROM TEST_U3;
#
SELECT a.br_name, SUM(c.sale_amt * c.unit_price) AS sum_sale_amt
FROM 점포 A, 고객 B, 매출 C
WHERE a.br_no = b.br_no
AND b.cust_no = c.cust_no
AND c.saledate Between '20120101' AND '20121231'
GROUP BY a.br_name;
#
SELECT SUM(v.total_qty) AS total_sale_qty,
SUM(v.total_qty * a.unit_price) AS totao_sale_amt
FROM (SELECT prod_no, SUM(sale_amt) AS total_qty
FROM 매출
WHERE saledate BETWEEN '20120101' AND '20120331'
GROUP BY prod_no)v, 물품 a
WHERE a.prod_no = v.prod_no
AND a.prod_class IN ('FOOD', 'MUSIC');
#WITH절로변환
WITH
v as
(SELECT prod_no, SUM(sale_amt) AS total_qty
FROM 매출
WHERE saledate BETWEEN '20120101' AND '20120331'
GROUP BY prod_no)
SELECT
SUM(v.total_qty) AS total_sale_qty,
SUM(v.total_qty * a.unit_price) AS totao_sale_amt
FROM v, 물품 a
WHERE a.prod_no = v.prod_no
AND a.prod_class IN ('FOOD', 'MUSIC');
#
SELECT DISTINCT a.cust_name, b.br_name
FROM 고객 a, 점포 b, 매출 c
WHERE a.BR_NO = b.BR_NO
AND a.CUST_NO = c.CUST_NO
AND c.SALEDATE LIKE '2012%';
SELECT DISTINCT a.cust_name, b.br_name
FROM 고객 a, 점포 b
WHERE a.BR_NO = b.BR_NO
AND a.cust_no IN ( SELECT cust_no FROM 매출
WHERE saledate LIKE '2012%');
SELECT DISTINCT a.cust_name, b.br_name
FROM 고객 a, 점포 b
WHERE a.br_no = b.BR_NO
AND EXISTS(SELECT 'x' FROM 매출 c
WHERE a.cust_no = c.cust_no AND saledate LIKE '2012%');
#
SELECT ROWNUM AS order_sale,
cust_no,
sum_sale_amt AS sum_sale_amt
FROM ( SELECT cust_no,
SUM(DECODE(sale_class, 'R', sale_amt*unit_price*1.1, sale_amt*unit_price)) AS sum_sale_amt
FROM 매출
WHERE saledate BETWEEN '20120101' AND '20120331'
GROUP BY cust_no
ORDER BY 2 desc)
WHERE ROWNUM <=5;
#
SELECT cust_no FROM 고객 a
WHERE a.cust_no IN ( SELECT cust_no FROM 매출
WHERE saledate LIKE '2012%'
GROUP BY cust_no
HAVING SUM(sale_amt*unit_price)<10)
OR NOT EXISTS (SELECT 'x' FROM 매출 c
WHERE c.cust_no = a.cust_no
AND c.saledate LIKE '2012%');
'IT > DB' 카테고리의 다른 글
| 오라클 DB SQL 교육 정리 (초급) (0) | 2019.11.30 |
|---|