[oracle] 연습2

2020. 8. 24. 17:55Database/practice

employees 테이블
departments 테이블
countries 테이블

 

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
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
--outer join
--employees과 departments 를 비교해서 employees 에만 있는 row기 추가로 출력된다
select e.EMPLOYEE_ID, e.FIRST_NAME, e.HIRE_DATE, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
  from EMPLOYEES e, DEPARTMENTS d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID(+);
 
--scalar subquery (select 절에 subquery가 위치하는 경우)
select e.EMPLOYEE_ID, e.FIRST_NAME, e.DEPARTMENT_ID,
(select d.department_name
from DEPARTMENTS d where e.DEPARTMENT_ID=d.DEPARTMENT_ID)
from EMPLOYEES e;
 
--outer join
--employees과 departments 를 비교해서  departments 에만 있는 row만 출력된다
select e.EMPLOYEE_ID, e.FIRST_NAME, e.HIRE_DATE, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
  from EMPLOYEES e, DEPARTMENTS d
where e.DEPARTMENT_ID(+= d.DEPARTMENT_ID;
 
--hr 계정으로 로그인하세요
--문제1) employees테이블에서 salary + (salary * commision_pct) 를 계산하여 상위 6명을 추출한다.
--사원번호,이름,급여,부서명,업무명
--급여많은 순으로 정렬 ( Inner Join 사용 )
 
-- 주의
select *
from (select EMPLOYEE_ID, concat(LAST_NAME, FIRST_NAME), SALARY, DEPARTMENT_NAME, (SALARY*nvl(COMMISSION_PCT,0)) 급여
       from EMPLOYEES e,DEPARTMENTS d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
order by 급여 desc)
where rownum <= 6;
 
 
--문제 2) Employees 테이블에서 업무(job_id)별 급여의 합이 5000을 초과하는 업무에 대하여 업무코드, 급여의합, 업무명를 출력하여라.
--월급여 합계를 정렬(내림차순)하세요.
--급여의 합이 높은 상위 10개만 출력하세요
--급여합은  $259,378  형태로 포맷하세요.( Inline View 사용 )
 
select *
from
     (select j1.JOB_ID 업무코드, TO_CHAR(급여총합, '$999,999,999,999,999') 급여총합, JOB_TITLE 업무명
     from JOBS j1,
          (select JOB_ID, sum(SALARY) 급여총합 from EMPLOYEES group by job_id having sum(SALARY) > 5000) j2
     where j1.JOB_ID = j2.JOB_ID
     order by 급여총합 desc)
where rownum <= 10;
 
 
--문제3) employees 테이블에서 월급이 자신이 속한 부서의 평균 월급보다 높은 사원의
--사원번호,이름,급여,평균급여,부서번호 출력
--평균급여는 반올림하세요
--급여가 높은 순으로 10명만 출력하세요.( Inline View 사용 )
select *
from (select EMPLOYEE_ID, FIRST_NAME, SALARY, 평균월급, e1.DEPARTMENT_ID
from EMPLOYEES e1,
     (select DEPARTMENT_ID, round(avg(SALARY)) 평균월급 from EMPLOYEES group by DEPARTMENT_ID) e2
where e1.DEPARTMENT_ID = e2.DEPARTMENT_ID and SALARY>평균월급
order by SALARY desc)
where rownum <= 10;
 
 
--문제 4) employees 테이블에서 관리자중에서 부하직원을 7명이상 관리하는 관리자의
--사원번호, 이름, 부하직원수 출력
--부하직원수 많은 순서대로, 사원번호 순으로 정렬 ( Inline View 사용 )
select EMPLOYEE_ID 사원번호, FIRST_NAME || ' '|| LAST_NAME 이름, 부하직원수
from EMPLOYEES e1,
     (select MANAGER_ID, count(*) 부하직원수 from EMPLOYEES group by MANAGER_ID) e2
where e1.EMPLOYEE_ID = e2.MANAGER_ID and 부하직원수 >= 7;
 
 
--문제5) 입사일자가 가장 오래된 5명의 직원을 추출한다.
--사원번호, 이름, 입사일, 급여, 근속년수,부서명
--입사일자 순으로 정렬 ( Inline View 사용 )
select *
from (select EMPLOYEE_ID 사원번호,
       FIRST_NAME || ' '|| LAST_NAME 이름,
       SALARY 급여,
       TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)/12,0) 근속년수,
       DEPARTMENT_NAME 부서명
       from EMPLOYEES e, (select DEPARTMENT_ID, DEPARTMENT_NAME from DEPARTMENTS) d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
order by 근속년수 desc)
where rownum <= 5;
 
select e.EMPLOYEE_ID, e.FIRST_NAME, e.HIRE_DATE,
       e.SALARY, e.근속년수, d.DEPARTMENT_NAME
from(select EMPLOYEE_ID,
       FIRST_NAME,
       HIRE_DATE,
       SALARY,
       trunc((sysdate - HIRE_DATE) / 365) 근속년수,
       DEPARTMENT_ID
        from employees e) e, DEPARTMENTS d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
order by 근속년수 desc;
 
 
 
 
--문제6)employees 테이블과 job_history 테이블을 조인하여 변동기록이 있는 사원은 기록이 나오게 하고,
--변동기록이 없는 사원은 일반정보만 출력.
--사원번호, 이름, 업무코드, 부서번호, 부서명, 시작날짜, 종료날짜를 출력하세요
--사원번호 순으로 정렬 오름차순,  출력결과 110 개의 ROW
 
select distinct e.EMPLOYEE_ID, FIRST_NAME, e.JOB_ID, e.DEPARTMENT_ID, START_DATE, END_DATE
from JOB_HISTORY j right join EMPLOYEES e
on j.JOB_ID=e.JOB_ID
order by e.EMPLOYEE_ID;
 
select e.EMPLOYEE_ID, FIRST_NAME, e.JOB_ID, e.DEPARTMENT_ID,  START_DATE, END_DATE
from EMPLOYEES e, JOB_HISTORY h
where e.EMPLOYEE_ID = h.EMPLOYEE_ID(+)
order by e.EMPLOYEE_ID;
 
--문제7) 유럽과 아메리카 대륙에서 근무하는 사원들을 추출한다.
--7.1 사원번호,이름, 업무코드,부서번호, 부서명, 도시, 국가명, 대륙명을 출력하세요
--사원번호 순으로 정렬 오름차순   출력결과 106 Row (Inner Join)
 
select EMPLOYEE_ID, FIRST_NAME, JOB_ID, DEPARTMENT_ID, DEPARTMENT_NAME, 도시명, 국가명, 대륙명
from EMPLOYEES inner join
     (select DEPARTMENT_ID, DEPARTMENT_NAME, 국가명, 대륙명, 도시명
        from DEPARTMENTS in_d inner join
             (select LOCATION_ID, city 도시명, 국가명, 대륙명
                 from LOCATIONS in_l inner join
                      (select COUNTRY_ID, COUNTRY_NAME 국가명, 대륙명
                      from COUNTRIES in_c inner join
                            (select REGION_ID, REGION_NAME 대륙명 from REGIONS)
                      using(REGION_ID))
                 using(COUNTRY_ID))
         using(LOCATION_ID))
using(DEPARTMENT_ID)
order by EMPLOYEE_ID;
 
 
select EMPLOYEE_ID,
       FIRST_NAME,
       JOB_ID,
       d.DEPARTMENT_ID,
       DEPARTMENT_NAME,
       CITY,
       REGION_NAME
from EMPLOYEES e,
     DEPARTMENTS d,
     LOCATIONS l,
     COUNTRIES c,
     REGIONS r
 
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
and d.LOCATION_ID = l.LOCATION_ID
and l.COUNTRY_ID = c.COUNTRY_ID
and c.REGION_ID = r.REGION_ID
and r.REGION_NAME in ('Europe''Americas')
order by e.EMPLOYEE_ID;
 
 
--7.2 위에서 작성한 쿼리를 활용하여 각 대륙별 직원수를 출력하세요
--대륙명, 직원수
--직원수 많은 순서대로 정렬
select 대륙명, count(*) 직원수
from
     (select EMPLOYEE_ID,
             FIRST_NAME,
             JOB_ID, DEPARTMENT_ID,
             DEPARTMENT_NAME,
             도시명,
             국가명,
             대륙명
     from EMPLOYEES inner join
     (select DEPARTMENT_ID,
             DEPARTMENT_NAME,
             국가명,
             대륙명,
             도시명
        from DEPARTMENTS in_d inner join
             (select LOCATION_ID,
                     city 도시명,
                     국가명,
                     대륙명
                 from LOCATIONS in_l inner join
                      (select
                              COUNTRY_ID,
                              COUNTRY_NAME 국가명,
                              대륙명
                      from COUNTRIES in_c inner join
                            (select
                                    REGION_ID,
                                    REGION_NAME 대륙명
                            from REGIONS)
                      using(REGION_ID))
                 using(COUNTRY_ID))
         using(LOCATION_ID))
using(DEPARTMENT_ID))
group by 대륙명
order by 직원수 desc;
 
select t.대륙명, count(*) 직원수
from (
        select EMPLOYEE_ID,
               FIRST_NAME,
               JOB_ID,
               d.DEPARTMENT_ID,
               DEPARTMENT_NAME,
               CITY,
               REGION_NAME 대륙명
        from EMPLOYEES e,
             DEPARTMENTS d,
             LOCATIONS l,
             COUNTRIES c,
             REGIONS r
 
        where e.DEPARTMENT_ID = d.DEPARTMENT_ID
        and d.LOCATION_ID = l.LOCATION_ID
        and l.COUNTRY_ID = c.COUNTRY_ID
        and c.REGION_ID = r.REGION_ID) t
group by t.대륙명
order by 직원수 desc;
 
 
--문제8) 부서번호, 부서명, 부서장사번, 부서장이름, 부서가 위치한 도시명, 직원수 를 출력하세요.
--부서장사번은 manager_id 컬럼임
--부서번호 순으로 정렬 (Innner Join, Inline View)
 
select 부서장이름,
       a.DEPARTMENT_ID 부서번호,
       부서명,
       도시명,
       직원수
from
     (select e.DEPARTMENT_ID,
             FIRST_NAME 부서장이름
     from EMPLOYEES e,
          DEPARTMENTS d
     where e.EMPLOYEE_ID=d.MANAGER_ID) a,
     (select
             DEPARTMENT_ID,
             DEPARTMENT_NAME 부서명,
             MANAGER_ID,
             CITY 도시명
     from DEPARTMENTS inner join LOCATIONS using(LOCATION_ID)) b,
     (select
             DEPARTMENT_ID,
             count(*) 직원수
     from EMPLOYEES
     group by DEPARTMENT_ID) c
where a.DEPARTMENT_ID=b.DEPARTMENT_ID and b.DEPARTMENT_ID=c.DEPARTMENT_ID
order by 직원수 desc;
 
 
 
 
--문제9) 업무별 직원수가 5명 이상인 업무의 업무코드, 업무명, 직원수, 평균급여, 최저급여, 최대급여 를 출력하세요.
--직원수가 많은 순으로 내림차순으로 정렬  ( Inline view )
select DEPARTMENT_ID 부서번호,
       DEPARTMENT_NAME 부서명,
       직원수,
       평균급여,
       최저급여,
       최대급여
from DEPARTMENTS a inner join
    (select DEPARTMENT_ID,
            count(*) 직원수,
            round(avg(salary)) 평균급여,
            min(SALARY) 최저급여,
            max(SALARY) 최대급여
    from EMPLOYEES
    group by DEPARTMENT_ID
    having count(*>=5)
using(DEPARTMENT_ID)
order by 직원수 desc;
 
 
 
--문제10) 전화번호의 앞3자리가 515 이면서 6월에 입사한 직원정보를 출력하세요.
--사원번호, 이름, 전화번호, 입사일자, 급여, 부서명, 업무명 출력하세요
select EMPLOYEE_ID 사원번호, 이름, 전화번호, 입사일자, 부서명, 업무명
from (select EMPLOYEE_ID,
             FIRST_NAME || ' '|| LAST_NAME 이름,
             PHONE_NUMBER 전화번호,
             HIRE_DATE 입사일자,
             JOB_TITLE 업무명,
             DEPARTMENT_ID
    from EMPLOYEES inner join JOBS using(JOB_ID))e inner join
     (select DEPARTMENT_ID,
             DEPARTMENT_NAME 부서명
     from DEPARTMENTS) d using(DEPARTMENT_ID)
where substr(전화번호, 13='515' and to_char(입사일자, 'MM'= '06';
cs