DDIT-SQL/20200709.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
153
154
155
156
157
158
159
160
161
162
163
164
SELECT *
FROM cycle
WHERE cid=1
 AND pid IN (SELECT pid
            FROM cycle
            WHERE cid=2);

select *
from customer;


SELECT c.cid, cu.cnm, c.pid, p.pnm, c.day, c.cnt
FROM cycle c, product p , customer cu
WHERE c.cid=1
 AND c.pid IN (SELECT pid
            FROM cycle
            WHERE cid=2)
AND c.pid=p.pid
AND cu.cid = c.cid;

SELECT *
FROM emp
WHERE EXISTS(SELECT 'x'
            FROM emp);

SELECT *
FROM emp e
WHERE EXISTS (SELECT 'x'
            FROM emp m
            WHERE m.empno=e.mgr);

SELECT e.*
FROM emp e, emp m
WHERE e.mgr = m.empno;

SELECT *
FROM emp
WHERE mgr IS NOT NULL;

SELECT *
FROM emp
WHERE mgr > 0;


SELECT *
FROM product p
WHERE EXISTS (SELECT *
            FROM Cycle
            WHERE pid =p.pid
            AND cid =1);

SELECT *
FROM product p
WHERE pid IN (SELECT pid
            FROM Cycle
            WHERE cid =1);

SELECT *
FROM product p
WHERE NOT EXISTS (SELECT *
            FROM Cycle
            WHERE pid =p.pid
            AND cid =1);


SELECT empno, ename
FROM emp
WHERE empno IN (7566, 7698)

UNION

SELECT empno, ename
FROM emp
WHERE empno IN (7566, 7698);


SELECT empno, ename
FROM emp
WHERE empno IN (7566, 7698)

UNION ALL

SELECT empno, ename
FROM emp
WHERE empno IN (7566, 7698);


SELECT empno, ename
FROM emp
WHERE empno IN (7369, 7566, 7499)

INTERSECT

SELECT empno, ename
FROM emp
WHERE empno IN (7566, 7698);


SELECT empno, ename
FROM emp
WHERE empno IN (7369, 7566, 7499)

MINUS

SELECT empno, ename
FROM emp
WHERE empno IN (7566, 7698);


SELECT empno, ename
FROM emp
WHERE empno IN (7369, 7566, 7499)

MINUS

SELECT empno, ename
FROM emp
WHERE empno IN (7566, 7698)
ORDER BY ename ;


INSERT INTO dept (deptno, dname,loc)
VALUES (99, 'DDIT','daejeon');

SELECT *
FROM dept;

desc dept;

INSERT INTO dept
VALUES (98, 'DDIT2','대전');

SELECT *
FROM dept;

INSERT INTO emp (empno,ename,job,hiredate)
VALUES (9998,'sally','RANGER',SYSDATE);

INSERT INTO emp (empno,ename,job,hiredate)
VALUES (9997,'moon','RANGER',TO_DATE('2020/07/01','YYYY/MM/DD'));


SELECT *
FROM dept;


INSERT INTO emp(empno, ename,job, hiredate)
SELECT 9998,NULL,'RANGER',SYSDATE
FROM dual
UNION ALL
SELECT 9997,'moon','RANGER',TO_DATE('2020/07/01','YYYY/MM/DD')
FROM dual;


SELECT *
FROM dept;

INSERT INTO dept
VALUES (90, 'ddit', '대전');

UPDATE dept SET dname = '대덕it',
                loc = 'daejeon'
WHERE deptno = 90;