국비학원 16일차) ORACLE 그룹함수

 

1)

부서ID, 부서별 월급의 합, 부서 인원을 구해보자

 

SELECT DEPARTMENT_ID, SUM(SALARY), COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
;

 

 

2)

SALARY가 1000단위만큼 *을 붙여줘

 

2-1)

SELECT LAST_NAME, SALARY,
LPAD('*', SALARY/1000, '*') AS STAR
FROM EMPLOYEES;

2-2)

SELECT LAST_NAME, SALARY,
LPAD(' ', SALARY/1000+1, '*') AS STAR
FROM EMPLOYEES;

 

 

 

 

 

3)

부서별 급여의 합, 평균, 최대급여, 최소 급여

출력-> 직무ID, 급여의 합

 

SELECT
JOB_ID,
MAX(SALARY) AS MAXIMUM,
MIN(SALARY) AS MINIMUM,
SUM(SALARY) AS SUM,
ROUND(AVG(SALARY)) AS AVG
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID
;

 

 

 

4) 

관리자는 나열하지 말고, 관리자의 수만 확인하세요

 

SELECT COUNT(MANAGER_ID) AS "NUMBER OF MANAGER"
FROM EMPLOYEES
GROUP BY JOB_ID, MANAGER_ID;

 

 

 

5) 부서 번호가 10, 20인 부서별 최대 급여

->부서 번호, 최대 월급

 

SELECT DEPARTMENT_ID, MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID IN(10,20)
;

 

 

 

 

6) 최고 급여와 최소 급여의 차액을 표시하세요

 

SELECT MAX(SALARY)-MIN(SALARY) AS DIFFERENT
FROM EMPLOYEES
;

 

 

 

 

7) 재고 관련 업무를 담당하는 사원의 월별 입사 인원 (ST)

- 단, 입사 인원이 2달 이하는 달은 제외

- 출력 : 월, 입사자 수

 

 

SELECT TO_CHAR(HIRE_DATE,'MM'),COUNT(*)
FROM EMPLOYEES
WHERE JOB_ID LIKE 'ST%'
GROUP BY TO_CHAR(HIRE_DATE,'MM')
HAVING COUNT(*)>2
ORDER BY TO_CHAR(HIRE_DATE,'MM')
;

 

 

 

8)

관리자 번호 및 해당 관리자에 속한 사원의 최저 급여 표시

관리자를 알 수 없는 사원이나 최저 급여가 6000원 미만인 사람은 표시X

결과는 내림차순

 

SELECT MANAGER_ID, MIN(SALARY)
FROM EMPLOYEES
GROUP BY MANAGER_ID
HAVING MIN(SALARY)>6000 AND MANAGER_ID IS NOT NULL
ORDER BY MIN(SALARY) DESC
;

 

 

 

 

 

 

9) 

부서별 평균 월급 구하기

-부서원이 10명 이상인 부서만

-부서ID, 평균 월급, 부서원 수

 

SELECT DEPARTMENT_ID, TRUNC(AVG(SALARY)), COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>=10
;

 

 

 

 

10)

년도별로 입사한 인원 수

 

SELECT TO_CHAR(HIRE_DATE,'YYYY'), COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'YYYY')
ORDER BY TO_CHAR(HIRE_DATE,'YYYY')
;

 

 

 

 

11)

입사한 달 별 인원수

 

SELECT TO_CHAR(HIRE_DATE,'MM') AS HIRE_MONTH, COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'MM')
ORDER BY TO_CHAR(HIRE_DATE,'MM')
;

 

EXTRACT 사용

 

SELECT EXTRACT(MONTH FROM HIRE_DATE), COUNT(*)
FROM EMPLOYEES
GROUP BY EXTRACT(MONTH FROM HIRE_DATE)
ORDER BY EXTRACT(MONTH FROM HIRE_DATE)
;

 

 

 

12)

JOB_ID를 기준으로 2008년 입사자들의 수를 구하시오

 

 

 

TO_CHAR 이용

SELECT JOB_ID, COUNT(*)
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2008'
GROUP BY JOB_ID
HAVING
JOB_ID LIKE 'SA_REP' OR
JOB_ID LIKE 'SH_CLERK' OR
JOB_ID LIKE 'ST_CLERK' OR
JOB_ID LIKE 'SA_MAN'
ORDER BY COUNT(*) DESC
;

 

 

TO_DATE 이용

 

SELECT JOB_ID, COUNT(*)
FROM EMPLOYEES
WHERE
HIRE_DATE BETWEEN
TO_DATE('2008/01/01','YYYY/MM/DD') AND
TO_DATE('2009/01/01','YYYY/MM/DD')
GROUP BY JOB_ID
HAVING
JOB_ID LIKE 'SA_REP' OR
JOB_ID LIKE 'SH_CLERK' OR
JOB_ID LIKE 'ST_CLERK' OR
JOB_ID LIKE 'SA_MAN'
ORDER BY COUNT(*) DESC
;

 

 

 

 

--------------JOIN---------------

 

13)  겹치는 거 합쳐서 출력하자!

 

 

13-1) WHERE AND 이용

 

SELECT E.LAST_NAME, E.JOB_ID,D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID=80
AND D.LOCATION_ID = L.LOCATION_ID
AND D.DEPARTMENT_ID=80
;

 

13-2) JOIN ON 이용

 

SELECT E.LAST_NAME, E.JOB_ID,D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
WHERE E.DEPARTMENT_ID=80
;

 

 

 

 

14) 토론토에 근무하는 사원을 출력하세요

 

SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID, DEPARTMENT_NAME
FROM EMPLOYEES JOIN DEPARTMENTS
USING(DEPARTMENT_ID)
JOIN LOCATIONS USING(LOCATION_ID)
WHERE CITY= 'Toronto'
;

 

 

 

 

15)

모든 사람의 이름, 부서번호, 부서 이름을 출력하세요

 

SELECT LAST_NAME, DEPARTMENT_ID, DEPARTMENT_NAME
FROM EMPLOYEES JOIN DEPARTMENTS
USING(DEPARTMENT_ID)
;

 

 

16)

부서 80에 속하는 모든 업무의 고유 목록을 작성하고

출력 결과에 부서 위치를 포함시키세요

 

SELECT JOB_ID, LOCATION_ID
FROM EMPLOYEES JOIN DEPARTMENTS
USING(DEPARTMENT_ID)
JOIN LOCATIONS USING(LOCATION_ID)
GROUP BY JOB_ID, LOCATION_ID
HAVING LOCATION_ID = 2500
;

 

 

 

17)

커미션을 받는 모든 사원, 부서 이름, 위치 ID, 도시를 작성하시오

 

SELECT LAST_NAME, DEPARTMENT_NAME, LOCATION_ID, CITY
FROM EMPLOYEES JOIN DEPARTMENTS
USING(DEPARTMENT_ID)
JOIN LOCATIONS USING(LOCATION_ID)
WHERE COMMISSION_PCT IS NOT NULL
;

 

 

 

 

18)

2006도 입사자들의 부서 이름별로 급여 총액,평균을 출력하세요

 

SELECT JOB_ID, MAX(SALARY), ROUND(AVG(SALARY))
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN
TO_DATE('2006/01/01', 'YYYY/MM/DD') AND 
TO_DATE('2007/01/01', 'YYYY/MM/DD')
GROUP BY JOB_ID
;

 

 

 

 

19)

2006년도에 입사한 사원들의 도시 이름별로 급여의 총액, 평균

 

SELECT CITY, SUM(SALARY), ROUND(AVG(SALARY))
FROM EMPLOYEES JOIN DEPARTMENTS
USING(DEPARTMENT_ID)
JOIN LOCATIONS USING(LOCATION_ID)
WHERE HIRE_DATE BETWEEN
TO_DATE('2006/01/01', 'YYYY/MM/DD') AND 
TO_DATE('2007/01/01', 'YYYY/MM/DD')
GROUP BY CITY
;

 

 

 

 

20)

2005년 1/2 분기에 입사한 사원 정보를 가져오기

>사원 번호, 이름, 부서이름, 직무이름

 

SELECT DEPARTMENT_ID, LAST_NAME, DEPARTMENT_NAME, JOB_TITLE, HIRE_DATE
FROM EMPLOYEES JOIN DEPARTMENTS USING(DEPARTMENT_ID)
JOIN JOBS USING (JOB_ID)
WHERE HIRE_DATE>=TO_DATE('2005/01/01','YYYY/MM/DD')
AND HIRE_DATE<TO_DATE('2005/06/15','YYYY/MM/DD')
ORDER BY HIRE_DATE
;