DDIT-SQL/20200720.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
150
151
152
SELECT DECODE(GROUPING(job),1,'총계',0,job) job ,deptno, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY ROLLUP (job,deptno);

SELECT job,mgr, SUM(sal)
FROM emp
GROUP BY ROLLUP(job, mgr);

SELECT job,mgr, GROUPING(job),GROUPING(mgr),SUM(sal)
FROM emp
GROUP BY ROLLUP(job, mgr);

SELECT DECODE(GROUPING(job),1,'총',job) job , DECODE(GROUPING(job) +  GROUPING(deptno),2,'계',1,'소계',deptno) deptno, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY ROLLUP (job,deptno);


SELECT job, deptno,GROUPING(job), GROUPING(deptno), SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY ROLLUP (job,deptno);


SELECT deptno, job, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY ROLLUP (deptno, job);


SELECT dname, job, SUM(sal + NVL(comm,0)) sal
FROM emp , dept
WHERE emp.deptno = dept.deptno
GROUP BY ROLLUP (dname, job)
ORDER BY dname;


SELECT (SELECT dname FROM dept WHERE emp.deptno = dept.deptno) dname, job, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY ROLLUP (deptno, job);


SELECT DECODE(GROUPING(dname), 1,'총합',dname)dname , job, SUM(sal + NVL(comm,0)) sal
FROM emp , dept
WHERE emp.deptno = dept.deptno
GROUP BY ROLLUP (dname, job);


SELECT job, deptno, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY GROUPING SETS (job,deptno);

SELECT job, null deptno,  SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY job

UNION ALL

SELECT null, deptno, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY deptno;


SELECT job, deptno,mgr, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY GROUPING SETS ((job,deptno), mgr);

SELECT  job, deptno, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY CUBE (job, deptno);

SELECT job, deptno,mgr, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY job, ROLLUP(job, deptno), cube(mgr);

그룹
ROLLUP

job, deptno
job
"

CUBE

mgr
"


job  job deptno mgr
job  job mgr
job  '' mgr

job  job deptno ''
job  job ''
job  '' ''


job deptno mgr
job mgr
job deptno
job


DROP TABLE emp_test;

CREATE TABLE emp_test AS
SELECT *
FROM emp;

SELECT * FROM emp_test;

ALTER TABLE emp_test ADD (dname VARCHAR2(14));


SELECT empno, ename, deptno, (SELECT dname FROM dept WHERE dept.deptno = emp_test.deptno) dname
FROM emp_test;


UPDATE emp_test SET dname =
            (SELECT dname FROM dept
             WHERE dept.deptno = emp_test.deptno);


DROP TABLE dept_test;

CREATE TABLE dept_test AS
SELECT *
FROM dept;

ALTER TABLE dept_test ADD (empcnt NUMBER(5));

SELECT *
FROM dept_test;

SELECT COUNT(*)
FROM emp
GROUP BY deptno;

UPDATE dept_test SET empcnt = (SELECT cnt  FROM (SELECT deptno, COUNT(*) cnt
                                                        FROM emp
                                                        GROUP BY deptno) a
                                WHERE a.deptno = dept_test.deptno );

UPDATE dept_test SET empcnt = (SELECT COUNT(ename)
                                 FROM emp
                                 WHERE deptno = dept_test.deptno
                                 GROUP BY deptno) ;


SELECT *
FROM dept_test;