DDIT-SQL/20200707.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
SELECT *
FROM fastfood;

SELECT sido, sigungu, gb
FROM fastfood;

SELECT sido, sigungu,
SUM( DECODE(gb, 'KFC', 1)), SUM(DECODE(gb, '맥도날드', 1)),
SUM( DECODE(gb, '버거킹', 1)) ,SUM(DECODE(gb, '롯데리아', 1))
FROM fastfood
WHERE gb IN ('KFC', '버거킹', '맥도날드','롯데리아')
GROUP BY sido,sigungu;

SELECT *
FROM burgerstore;

SELECT ROWNUM ham_rn, hamburger.*
FROM (SELECT sido, sigungu,
ROUND((NVL(SUM( DECODE(storecategory, 'KFC', 1)),0) + NVL(SUM(DECODE(storecategory, 'MACDONALD', 1)),0) +
NVL(SUM( DECODE(storecategory, 'BURGER KING', 1)),0)) / NVL(SUM(DECODE(storecategory, 'LOTTERIA', 1)),1), 2) score
FROM burgerstore
WHERE storecategory IN ('KFC', 'BURGER KING', 'MACDONALD','LOTTERIA')
GROUP BY sido,sigungu
ORDER BY score desc) hamburger;

도시발전순위, 햄버거시도, 햄버거 시군그, 햄버거 발전지수, 근로소득 순위, 근로소득 순위, 근로소득 시도, 근로소득 시군구, 1인당 근로소득액

SELECT *
FROM tax;

SELECT ROWNUM tax_rn, per_tax.*
FROM
(SELECT sido, sigungu, round((sal/people),2) per_sal
FROM tax
ORDER BY per_sal DESC) per_tax;


SELECT * FROM
        (SELECT ROWNUM ham_rn, hamburger.*
            FROM (SELECT sido ham_sido, sigungu ham_sigungu,
                    ROUND((NVL(SUM( DECODE(storecategory, 'KFC', 1)),0) + NVL(SUM(DECODE(storecategory, 'MACDONALD', 1)),0) +
                    NVL(SUM( DECODE(storecategory, 'BURGER KING', 1)),0)) / NVL(SUM(DECODE(storecategory, 'LOTTERIA', 1)),1), 2) score
                    FROM burgerstore
                    WHERE storecategory IN ('KFC', 'BURGER KING', 'MACDONALD','LOTTERIA')
                    GROUP BY sido,sigungu
                    ORDER BY score desc) hamburger),
        (SELECT ROWNUM tax_rn, per_tax.*
        FROM (SELECT sido tax_sido, sigungu tax_sigungu, round((sal/people),2) per_sal
                FROM tax
                ORDER BY per_sal DESC) per_tax)
WHERE tax_rn = ham_rn(+)
ORDER BY tax_rn;


SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
FROM emp e, dept d;

SELECT cid, cnm, pid, pnm
FROM customer CROSS JOIN product;


SELECT *
FROM emp
WHERE deptno = (SELECT deptno
                FROM emp
                WHERE ename = 'SMITH');


;
SELECT *

FROM ALL_TAB_COLUMNS

WHERE TABLE_NAME = 'emp';


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

SELECT *
FROM emp
WHERE deptno = (SELECT deptno
                FROM emp
                WHERE ename='SMITH');

SELECT count(*)
FROM emp
WHERE sal > (SELECT avg(sal)
FROM emp);

SELECT *
FROM emp
WHERE sal > (SELECT avg(sal)
FROM emp);