[oracle] 연습1

2020. 8. 24. 11:56Database/practice

EMP 테이블
DEPT 테이블
SALGRADE 테이블

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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
-- 1. EMP TABLE 에서 이름, 급여, 커미션 금액,
-- 총액 (SAL + COMM) 을 구하여 총액이 많은 순서로 출력하라. 
-- 단 커미션이 NULL인 사람은 제외한다.
select ename 이름, sal 급여, nvl(COMM, 0) 커미션금액,
       sal+nvl(COMM, 0) 총액
from emp
order by 총액;
 
 
-- 2. EMP와 DEPT TABLE 을
-- inner JOIN 하여 부서번호, 부서명, 이름, 급여를 출력하라.
select d.DEPTNO, dname, ENAME, SAL
from DEPT d, EMP e
    where d.DEPTNO = e.DEPTNO;
 
 
--Ansi SQL (표준 SQL)
 
-- 3. EMP 테이블에서
-- 사원번호가 7521인 사원과 업무가 같고,
-- 급여가 7934인 사원보다 많은 사원의
-- 사원번호, 이름, 담당업무, 입사일자, 급여를 출력하여라.
-- where 단일행 subquery
select empno, ename, job, HIREDATE, SAL
from emp
    where job = (select job from emp where EMPNO='7521')
        and sal > (select sal from emp where EMPNO='7934');
 
 
-- 4. EMP 테이블에서 평균급여보다 적은 급여를 받는 사원의 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하라.
-- 단일행 subquery
select  avg(sal) from emp;
 
select EMPNO, ename, job, sal, d.deptno
from emp e, dept d
where e.DEPTNO = d.DEPTNO
 
-- 5. EMP TABLE 에 있는 EMPNO와 MGR을 이용하여 서로의 관계를 다음과 같이 출력하라.
-- SMITH의 매니저는 FORD이다.
-- || (oracle) , concat( mysql )
-- 5.1 (self join) 같은 emp 테이블 2개를 조인합니다.
select e.ENAME || '의 매니저는 ' || m.ENAME || '이다'
from EMP e, EMP m
where e.MGR = m.EMPNO;
 
-- 5.2 inline view
select e.ename || '의 매니저는 ' || m.ENAME || '이다'
from emp e, (select  * from emp)  m
where e.MGR = m.EMPNO;
 
 
--6. ALLEN의 직무와 같은 사람의 이름, 부서명, 급여, 직무를 출력하라.
-- where 단일행 subquery
select ename, DEPTNO, SAL, job from EMP
where job = (select job from emp where ename = 'ALLEN');
 
--7. EMP 테이블에서 SALES 부서 사원의 이름,업무,부서번호를 출력하는 SELECT문을 작성하시오.
--7.1 Nested Query
select ename, job, e.DEPTNO
from EMP e, DEPT d
where e.DEPTNO = d.DEPTNO and d.DNAME = 'SALES';
 
--7.2 Inline View
select  ename, JOB, DEPTNO
from emp
where deptno = (select DEPTNO from DEPT where DNAME='SALES');
 
select DEPTNO from DEPT where DNAME='SALES';
 
select empno, job, d.deptno
from emp e, (select DEPTNO, dname from dept where DNAME='SALES') d
where d.DEPTNO= e.DEPTNO;
 
--8. EMP 테이블에서 이름에 “T”가 있는 사원이 근무하는 부서에서 근무하는 모든 사원에 대해
-- 사원 번호,이름,급여를 출력하는 SELECT문을 작성하시오.
-- 단 사원번호 순으로 출력하여라.
-- where in 다중행 subquery
select EMPNO, ename, SAL
    from emp
where ENAME like '%T%' order by  EMPNO;
 
select empno, ename, sal from emp
where deptno in (select deptno from emp where ename like '%T%');
 
-- 9. 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일, 지역, 급여를 출력하라.
-- where 단일행 subquery
select EMPNO, ENAME, dname, to_char(HIREDATE, 'yyyy-mm-dd'), LOC, SAL
from EMP e, DEPT d
where e.DEPTNO = d.DEPTNO and SAL > (select round(avg(sal)) from emp);
 
select round(avg(sal)) from emp;
 
 
-- 10. EMP 테이블에서 관리자중에서 부하직원을 2명이상 관리하는 관리자의 이름을 출력하세요.
-- select mgr,count(*) as emp_cnt
--     from emp
--     group by mgr having emp_cnt >= 2;
 
-- 10.1 Nested Query (where 절에 subquery)
select ENAME
from emp
where EMPNO in (select mgr from emp group by mgr having count(*)>=2);
 
-- 해당 조건을 만족하는 매니저가 여러 명있기 때문에 where in으로 해야 한다.
select mgr from emp group by mgr having count(*)>=2;
 
-- 10.2 Inline View (from 절에 subquery)
select e.ename
from emp e, (select mgr from EMP group by mgr having count(*)>=2 ) m
where e.EMPNO = m.MGR;
 
 
-- 11.EMP 테이블에서 CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
select ENAME, JOB
from EMP e, DEPT d
where e.DEPTNO = d.DEPTNO and LOC = 'CHICAGO';
 
 
-- 11.1.1 Nested Query ( where 절에 in 구문에  subquery 대입 )
select  ename, JOB
from emp e
where e.DEPTNO in (select DEPTNO from DEPT where loc = 'CHICAGO');
 
-- 11.2 Inline View
select ename, JOB
from emp e1, (select DEPTNO from dept where loc = 'CHICAGO') e2
where e1.DEPTNO = e2.DEPTNO;
 
select a.deptno, a.loc, b.job
from dept a, emp b
where a.DEPTNO= b.DEPTNO and a.loc='CHICAGO';
 
-- 12.EMP 테이블에서 업무가 JONES와 같거나 월급이 FORD이상인 사원의 이름,업무,부서번호,급여를 출력하는 SELECT문을 작성하시오.
-- 단 업무별, 월급이 많은 순으로 출력하여라.
-- where 단일행 subquery
select ename, job, DEPTNo, sal
from EMP
where job = (select job from emp where ENAME='JONES')
   or sal >= (select sal from emp where ENAME='FORD')
order by job, sal desc;
 
 
-- order by sal;
-- 13. EMP 테이블에서 업무별로 최소 급여를 받는 사원의
-- 사원번호, 이름, 업무, 입사일자, 급여, 부서번호를 출력하여라.
 
-- inline view
select EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO
from EMP
where SAL in (select min(sal) from emp group by JOB);
 
-- 14. emp와 dept 테이블에서 업무가 manager인 사원의 이름, 업무, 부서명, 근무지를 출력하여라.
-- Inline View 를 사용하자
select ename, job, DNAME, LOC
from emp e, dept d
where e.DEPTNO=e.DEPTNO and e.job='MANAGER';
 
select ename, job, DNAME, LOC
from (select ename, job, DEPTNO
    from emp where job= 'MANAGER') e,
     dept d
where e.DEPTNO = d.DEPTNO;
 
-- 15. EMP 테이블에서 30번 부서원 중 최저급여를 받는 사원을 제외한
-- 나머지 사원들의 모든 정보를 출력하는 SELECT문을 작성하시오.
select *
from emp
where DEPTNO = '30'
  and sal not in
      (select min(sal) from emp group by DEPTNO);
 
-- # Multi Column Subquery
 
-- # 16. EMP 테이블에서 말단 사원의 사원번호,이름,업무,부서번호를
-- 출력하는 SELECT문을 작성하시오.(말단사원: 다른 사원을 관리하지 않는 사원)
-- # - ORACLE : NVL(VALUE1, VALUE2)
-- # - MSSQL  : ISNULL(VALUE1, VALUE2)
-- # - MYSQL  : IFNULL(VALUE1, VALUE2)
select EMPNO, ename , job, DEPTNO
from emp
where HIREDATE in
      (select max(HIREDATE) from emp group by JOB);
 
 
-- # 17. EMP 테이블에서
-- 사원번호, 이름, 업무, 급여, 급여의 등급을 출력하되
-- 3등급 이상인 사원의 정보만을 출력하세요.
-- # (emp와 salgrade 테이블을 이용);
select EMPNO, ename, job, sal, grade
from emp, SALGRADE
where sal between LOSAL and HISAL and GRADE >= 3;
 
 
-- # 18. 부서번호, 부서에 속한
-- 직원수, 부서명, 도시명을 출력하세요.
-- # 직원수가 5명이상인 부서만 출력하세요. ( inline view )
select totalEMP, dname, LOC
from DEPT,
     (select count(*) totalEMP
     from emp
     group by DEPTNO)
where totalEMP >= 5;
 
 
-- # 19. EMP 테이블에서
-- 적어도 한 명 이상으로부터
-- 보고를 받을 수 있는
-- 사원의 업무,이름,사원번호,부서번호를 출력하시오.
-- # (즉 관리자를 출력하세요)
select job, ename, DEPTNO
from emp
where EMPNO in
      (select mgr
      from emp
      group by mgr
      having count(*)>=1)
cs