hyndb
[데이터베이스] hr 스키마 연습문제 (3_4, 5차) 본문
-----------------------------------------------------------3장 4차 연습문제
1. 사원 급여의 최고, 최저, 합, 평균을 구하라. Maximum, Minimum, Sum, Average로 컬럼 이름을 정한다.
select max(salary) as Maximum, min(salary) Minimum, sum(salary) Sum, avg(salary) Average
from employees;
MAXIMUM MINIMUM SUM AVERAGE
---------- ---------- ---------- ----------
24000 2100 691416 6461.83178
2. 각 JOB마다(job_id 별로 = group by) 사원 급여의 최고, 최저, 합, 평균을 구하라. Maximum, Minimum, Sum, Average로 컬럼 이름을 정한다.같은 JOB을 하는 사원의 숫 자를 출력하라. JOB_ID를 포함한다.
select job_id, max(salary) as Maximum, min(salary) Minimum, sum(salary) Sum, avg(salary) Average, count(*) as employeecount
from employees
group by job_id;
JOB_ID MAXIMUM MINIMUM SUM AVERAGE EMPLOYEECOUNT
-------------------- ---------- ---------- ---------- ---------- -------------
SH_CLERK 4200 2500 64300 3215 20
HR_REP 6500 6500 6500 6500 1
AD_VP 17000 17000 34000 17000 2
FI_ACCOUNT 9000 6900 39600 7920 5
PU_CLERK 3100 2500 13900 2780 5
AC_MGR 12008 12008 12008 12008 1
PU_MAN 11000 11000 11000 11000 1
ST_CLERK 3600 2100 55700 2785 20
AD_ASST 4400 4400 4400 4400 1
AC_ACCOUNT 8300 8300 8300 8300 1
IT_PROG 9000 4200 28800 5760 5
JOB_ID MAXIMUM MINIMUM SUM AVERAGE EMPLOYEECOUNT
-------------------- ---------- ---------- ---------- ---------- -------------
SA_MAN 14000 10500 61000 12200 5
FI_MGR 12008 12008 12008 12008 1
ST_MAN 8200 5800 36400 7280 5
MK_MAN 13000 13000 13000 13000 1
AD_PRES 24000 24000 24000 24000 1
MK_REP 6000 6000 6000 6000 1
PR_REP 10000 10000 10000 10000 1
SA_REP 11500 6100 250500 8350 30
19 행이 선택되었습니다.
3. MANAGER 역할(상사)을 하고 있는 사원의 숫자를 출력하라.(부서의 상사가 아니다.)
select count(distinct manager_id) Employee
from employees;
EMPLOYEE
-----------------
18
4. 급여가 $5000 이하인 사원들만 대상으로, 부서번호별 급여의 합을 출력하 라.
select department_id, sum(salary) Sum
from employees
where salary <= 5000
group by department_id;
DEPARTMENT_ID SUM
------------- ----------
50 120000
30 13900
10 4400
60 13800
5. 사원의 수가 5명 이상인 JOB_ID 별로 급여의 최소값을 구하라.
select job_id, min(salary) Minimum
from employees
group by job_id
having count(*) >= 5;
JOB_ID MINIMUM
-------------------- ----------
SH_CLERK 2500
FI_ACCOUNT 6900
PU_CLERK 2500
ST_CLERK 2100
IT_PROG 4200
SA_MAN 10500
ST_MAN 5800
SA_REP 6100
8 행이 선택되었습니다.
6. 같은 부서에서 같은 JOB을 수행중인 사원의 숫자를 부서번호별, JOB_ID별 로 출력하라. 부서번호가 커지는 순서로 정렬하되, 부서번호가 같은 경우는 JOB_ID가 커지는 순서로 정렬하라. -------------?
select department_id, job_id, count(*) as employeecount
from employees
group by department_id, job_id
order by department_id ASC, job_id ASC;
DEPARTMENT_ID JOB_ID EMPLOYEECOUNT
------------- -------------------- -------------
10 AD_ASST 1
20 MK_MAN 1
20 MK_REP 1
30 PU_CLERK 5
30 PU_MAN 1
40 HR_REP 1
50 SH_CLERK 20
50 ST_CLERK 20
50 ST_MAN 5
60 IT_PROG 5
70 PR_REP 1
DEPARTMENT_ID JOB_ID EMPLOYEECOUNT
------------- -------------------- -------------
80 SA_MAN 5
80 SA_REP 29
90 AD_PRES 1
90 AD_VP 2
100 FI_ACCOUNT 5
100 FI_MGR 1
110 AC_ACCOUNT 1
110 AC_MGR 1
SA_REP 1
20 행이 선택되었습니다.
7. 급여의 평균이 5000이 넘는 부서를 대상으로, 부서번호와 급여의 합을 구하라.
select department_id, sum(salary) SUM
from employees
group by department_id
having avg(salary) >= 5000; //having은 집계함수절
DEPARTMENT_ID SUM
------------- ----------
40 6500
110 20308
70 10000
90 58000
7000
20 19000
60 28800
100 51608
80 304500
9 행이 선택되었습니다.
--------------------------------------------------------------------3장 5차 연습문제(조인)
1. 100번 이하 부서에 소속된 사원의 사원번호, 성, 부서번호, 부서명을 출력 하라.
select e.employee_id, e.last_name, e.department_id, d.department_name
from employees e join departments d on e.department_id = d.department_id
where d.department_id <= 100;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
----------- -------------------------------------------------- ------------- ------------------------------------------------------------
198 OConnell 50 Shipping
199 Grant 50 Shipping
200 Whalen 10 Administration
201 Hartstein 20 Marketing
202 Fay 20 Marketing
203 Mavris 40 Human Resources
204 Baer 70 Public Relations
100 King 90 Executive
101 Kochhar 90 Executive
102 De Haan 90 Executive
103 Hunold 60 IT
... 104행이 선택되었습니다.
2. 모든 부서의 주소를 출력한다. location ID, street address, city, state 또는 province, country를 출력해야 한다. NATURAL JOIN을 사용하라.
(= 부서 테이블이랑 내추럴 조인/ 부서의 주소(=locations 테이블) 출력이니까)
select *
from locations
natural join departments;
LOCATION_ID STREET_ADDRESS
POSTAL_CODE CITY
----------- -------------------------------------------------------------------------------- ------------------------ ------------------------------------------------------------
STATE_PROVINCE COUN DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
-------------------------------------------------- ---- ------------- ------------------------------------------------------------ ----------
1400 2014 Jabberwocky Rd
26192 Southlake
Texas US 60 IT
103
1500 2011 Interiors Blvd
99236 South San Francisco
California US 50 Shipping
121
1700 2004 Charade Rd
98199 Seattle
Washington US 30 Purchasing 114
...27행이 선택되었습니다.
3. Toronto에서 근무하는 사원의 last name, job(job id), department number, department name을 출력하라.
select e.last_name, e.job_id, d.department_id, d.department_name
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id
where l.city = 'Toronto';
LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME
-------------------------------------------------- -------------------- ------------- ------------------------------------------------------------
Hartstein MK_MAN 20 Marketing
Fay MK_REP 20 Marketing
4. 사원의 번호, 성, JOB_CODE, JOB_TITLE을 출력하라.
select e.employee_id, e.last_name, j.job_id, j.job_title
from employees e
join jobs j on e.job_id = j.job_id;
EMPLOYEE_ID LAST_NAME JOB_ID JOB_TITLE
----------- -------------------------------------------------- -------------------- ----------------------------------------------------------------------
137 Ladwig ST_CLERK Stock Clerk
138 Stiles ST_CLERK Stock Clerk
139 Seo ST_CLERK Stock Clerk
124 Mourgos ST_MAN Stock Manager
123 Vollman ST_MAN Stock Manager
122 Kaufling ST_MAN Stock Manager
121 Fripp ST_MAN Stock Manager
120 Weiss ST_MAN Stock Manager
107 행이 선택되었습니다.
5. 모든 부서의 번호, 이름, 부서장의 성을 출력하라.
set linesize 200
select d.department_id, d.department_name, e.last_name
from departments d
left join employees e on d.manager_id = e.manager_id;
DEPARTMENT_ID DEPARTMENT_NAME LAST_NAME
------------- ------------------------------------------------------------ --------------------------------------------------
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
63 행이 선택되었습니다.
6. 같은 JOB을 하는 사원의 숫자를 출력하라. JOB의 이름이 출력되어야 한다.
select job_id, count(employee_id)
from employees
group by job_id
order by job_id;
JOB_ID COUNT(EMPLOYEE_ID)
-------------------- ------------------
AC_ACCOUNT 1
AC_MGR 1
AD_ASST 1
AD_PRES 1
AD_VP 2
FI_ACCOUNT 5
FI_MGR 1
HR_REP 1
IT_PROG 5
MK_MAN 1
MK_REP 1
JOB_ID COUNT(EMPLOYEE_ID)
-------------------- ------------------
PR_REP 1
PU_CLERK 5
PU_MAN 1
SA_MAN 5
SA_REP 30
SH_CLERK 20
ST_CLERK 20
ST_MAN 5
19 행이 선택되었습니다.
7. JOB 이력(history)가 있는 사원의 사원 성과 이름, JOB_HISTORY 기록 횟수 를 출력하라. -------?
select e.last_name, e.first_name, count(*) as job_history
from employees e
join job_history j on e.employee_id = j.employee_id
group by e.employee_id, e.last_name, e.first_name;
LAST_NAME FIRST_NAME
JOB_HISTORY
-------------------------------------------------- ---------------------------------------- -----------
Raphaely Den
1
Whalen Jennifer
2
Hartstein Michael
1
Kochhar Neena
2
Kaufling Payam
1
Taylor Jonathon
2
De Haan Lex
1
7 행이 선택되었습니다.
8. 급여가 4000 이상인 사원들이 속한 부서의 이름을 중복을 제거하고 출력하라.
select distinct d.department_name
from departments d
join employees e on d.department_id = e.department_id
where e.salary >= 4000;
DEPARTMENT_NAME
------------------------------------------------------------
Sales
Marketing
Administration
Purchasing
Shipping
IT
Executive
Finance
Public Relations
Human Resources
Accounting
11 행이 선택되었습니다.
'데이터베이스 > hr scheme' 카테고리의 다른 글
| [데이터베이스] hr 스키마 연습문제(3_7차 DDL) (0) | 2023.11.30 |
|---|---|
| [데이터베이스] hr 스키마 연습문제 (3_6차) (1) | 2023.11.23 |
| [데이터베이스] hr 스키마 연습문제(3_2, 3차) (1) | 2023.11.23 |
