DDIT-SQL/20200721.sql at master · riboooo/DDIT-SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
SELECT *
FROM dept_test;


ALTER TABLE dept_test DROP COLUMN empcnt;

INSERT INTO dept_test VALUES ( 99,'ddit1', 'daejeon');
INSERT INTO dept_test VALUES ( 98,'ddit1', 'daejeon');


DELETE FROM dept_test
WHERE deptno IN (40, 98, 99);

ROLLBACK;

DELETE FROM dept_test
WHERE deptno NOT IN(SELECT deptno
                FROM emp
                GROUP BY deptno);

DELETE FROM dept_test
WHERE deptno NOT IN(SELECT deptno
                FROM emp
                WHERE emp.deptno = dept_test.deptno);


DELETE FROM dept_test
WHERE NOT EXISTS (SELECT 'X'
                FROM emp
                WHERE emp.deptno = dept_test.deptno);

SELECT *
FROM dept_test;


SELECT *
FROM emp_test;


SELECT *
FROM emp_test e
WHERE sal <=
            (SELECT avg(s.sal)
            FROM emp_test s
            GROUP BY s.deptno
            HAVING e.deptno = s.deptno );

UPDATE emp_test e
SET e.SAL = e.sal+200
WHERE sal <=
            (SELECT avg(s.sal)
            FROM emp_test s
            GROUP BY s.deptno
            HAVING e.deptno = s.deptno );


UPDATE emp_test e
SET e.SAL = e.sal+200
WHERE sal <=
            (SELECT avg(s.sal)
            FROM emp_test s
            WHERE e.deptno = s.deptno );

ROLLBACK;


SELECT TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 dt,
       TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd') d,
       TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'iw') iw,
       DECODE(TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd'),1,TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1,null) sun,
       DECODE(TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd'),2,TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1,null) mon,
       DECODE(TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd'),3,TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1,null) tue,
       DECODE(TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd'),4,TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1,null) wed,
       DECODE(TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd'),5,TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1,null) thu,
       DECODE(TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd'),6,TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1,null) fri,
       DECODE(TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd'),7,TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1,null) sat
FROM dual
CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(TO_DATE(:YEARMONTH, 'YYYYMM')),'DD');


SELECT
       MAX(DECODE(d , 1, dt)) sun ,MAX(DECODE(d , 2, dt)) mon ,MAX(DECODE(d , 3, dt)) tue ,
       MAX(DECODE(d , 4, dt)) wed,MAX(DECODE(d , 5, dt)) thu,MAX(DECODE(d , 6, dt)) fri ,MAX(DECODE(d , 7, dt)) sat
FROM
    (SELECT TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 dt,
            TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd') d,
            TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'iw') iw
      FROM dual
      CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(TO_DATE(:YEARMONTH, 'YYYYMM')),'DD'))
GROUP BY DECODE (d,1,iw+1,iw)
ORDER BY DECODE (d,1,iw+1,iw);

201912월
앞뒤 월 connect를 1일자 일요일, 마지막 토요일+
;

desc sales;

SELECT dt, DECODE (TO_CHAR(dt,'MM'),01,sales) jan, DECODE (TO_CHAR(dt,'MM'),02,sales) feb ,DECODE (TO_CHAR(dt,'MM'),03,sales) mar,
            DECODE (TO_CHAR(dt,'MM'),04,sales) apr ,DECODE (TO_CHAR(dt,'MM'),05,sales) may ,DECODE (TO_CHAR(dt,'MM'),06,sales) jun
FROM sales
;


SELECT SUM(DECODE (TO_CHAR(dt,'MM'),01,sales)) jan, SUM(DECODE (TO_CHAR(dt,'MM'),02,sales)) feb ,NVL(SUM(DECODE (TO_CHAR(dt,'MM'),03,sales)),0) mar,
           SUM( DECODE (TO_CHAR(dt,'MM'),04,sales)) apr ,SUM(DECODE (TO_CHAR(dt,'MM'),05,sales)) may ,SUM(DECODE (TO_CHAR(dt,'MM'),06,sales)) jun
FROM sales
;


SELECT
       MAX(DECODE(d , 1, dt)) sun ,MAX(DECODE(d , 2, dt)) mon ,MAX(DECODE(d , 3, dt)) tue ,
       MAX(DECODE(d , 4, dt)) wed,MAX(DECODE(d , 5, dt)) thu,MAX(DECODE(d , 6, dt)) fri ,MAX(DECODE(d , 7, dt)) sat
FROM
    (SELECT TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 dt,
            TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd') d,
            TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'iw') iw


      FROM dual
      CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(TO_DATE(:YEARMONTH, 'YYYYMM')),'DD'))
GROUP BY DECODE (d,1,iw+1,iw)
ORDER BY DECODE (d,1,iw+1,iw);


SELECT
       MAX(DECODE(d , 1, dt)) sun ,MAX(DECODE(d , 2, dt)) mon ,MAX(DECODE(d , 3, dt)) tue ,
       MAX(DECODE(d , 4, dt)) wed,MAX(DECODE(d , 5, dt)) thu,MAX(DECODE(d , 6, dt)) fri ,MAX(DECODE(d , 7, dt)) sat
FROM
    (SELECT TO_date(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL - 1 -(TO_CHAR(TO_DATE(:YEARMONTH , 'YYYYMM'),'D') -1) )  dt,
            TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 -(TO_CHAR(TO_DATE(:YEARMONTH , 'YYYYMM'),'D') -1) , 'd') d,
            TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'iw') iw,
            (TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1) - (TO_CHAR(TO_DATE(:YEARMONTH, 'YYYYMM') + LEVEL -1 , 'd')) + 1 -(TO_CHAR(TO_DATE(:YEARMONTH , 'YYYYMM'),'D')) sun

      FROM dual
      CONNECT BY LEVEL <= (TO_CHAR(TO_DATE(:YEARMONTH , 'YYYYMM'),'D') -1) +
                        TO_CHAR(LAST_DAY(TO_DATE(:YEARMONTH, 'YYYYMM')),'DD')
                   + (7 - TO_CHAR(LAST_DAY(TO_DATE(:YEARMONTH, 'YYYYMM')),'D')) )


GROUP BY sun
ORDER BY sun;