DDIT-SQL/20200626.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
SELECT *
FROM emp
WHERE ename like 'S%';

SELECT mem_id, mem_name
FROM member
WHERE mem_name LIKE '신%';

SELECT mem_id, mem_name
FROM member
WHERE mem_name LIKE '%이%';

UPDATE member SET mem_name = '신이환'
WHERE mem_id = 'c001';

SELECT empno, ename, comm
FROM emp
WHERE comm = NULL;

SELECT empno, ename, comm
FROM emp
WHERE comm IS NOT NULL;

SELECT *
FROM emp
WHERE mgr = 7698 AND sal > 1000;

SELECT *
FROM emp
WHERE mgr = 7698 OR sal > 1000;

SELECT *
FROM emp
WHERE mgr NOT IN(7698, 7839);

SELECT *
FROM emp
WHERE job = 'SALESMAN'
AND hiredate >= TO_DATE('1981/06/01', 'YYYY/MM/DD');

SELECT *
FROM emp
WHERE deptno NOT IN 10 OR deptno IS NULL
AND hiredate >= TO_DATE('1981/06/01', 'YYYY/MM/DD');


SELECT *
FROM emp
WHERE deptno IN (20, 30)
AND hiredate >= TO_DATE('1981/06/01', 'YYYY/MM/DD');

SELECT *
FROM emp
WHERE job = 'SALESMAN'
OR hiredate >= TO_DATE('1981/06/01', 'YYYY/MM/DD');

SELECT *
FROM emp
WHERE job = 'SALESMAN'
  OR empno LIKE '78%';

SELECT *
FROM emp
WHERE job = 'SALESMAN'
    OR empno BETWEEN 7800 AND 7899
    OR empno BETWEEN 780 AND 789
    OR empno = 78;


--NOT IN (7698, 7839, null)
--!(mgr = 7698 OR mgr = 7839 OR mgr = NULL)
--(mgr != 7698 AND mgr != 7839 AND mgr != NULL);


SELECT *
FROM emp
WHERE job = 'SALESMAN'
    OR empno LIKE '78%' AND hiredate >= TO_DATE('1981/06/01','YYYY/MM/DD');

SELECT *
FROM emp
ORDER BY ename;

SELECT *
FROM emp
ORDER BY ename desc, mgr;

SELECT empno, ename, sal, sal*12 salary
FROM emp
ORDER BY 4;

SELECT *
FROM dept
ORDER BY dname;

SELECT *
FROM dept
ORDER BY loc desc;

SELECT *
FROM emp
WHERE comm > 0
ORDER BY comm desc, empno desc;


SELECT *
FROM emp
WHERE mgr IS NOT NULL
ORDER BY job, empno;

SELECT *
FROM emp
WHERE deptno IN(10, 30) AND sal > 1500
ORDER BY ename DESC;