29. Oracle 01/23 (1) - 패스트캠퍼스 백엔드 부트캠프 3기

-- 각 부서별 평균 급여
SELECT dept_id, AVG(salary) FROM S_EMP
GROUP BY dept_id;

-- 각 부서별 직책이 사원인 직원들의 평균 급여
SELECT dept_id, AVG(salary) FROM S_EMP
WHERE title = '사원'
GROUP BY dept_id;

-- 각 지역별로 몇 개의 부서가 있는가
SELECT region_id, COUNT(name) AS 부서 FROM S_DEPT
GROUP BY region_id;

-- 각 부서별로 평균 급여를 구하되 평균 급여가 2000이상인 부서만
SELECT dept_id, AVG(salary) FROM S_EMP
WHERE salary > 2000
GROUP BY dept_id;

-- 각 직책별로 급여의 총합을 구하되 직책이 부장인 사람은 제외하시오
-- 단 급여총합이 8000(만원) 이상인 직책만 나타내며, 급여 총합에 대한 오름차순으로 정렬
SELECT title, AVG(salary) 평균, SUM(salary) AS 총합
FROM S_EMP
WHERE title NOT LIKE '%부장%'
GROUP BY title
HAVING SUM(salary) >= 8000
ORDER BY 총합 ASC;

-- 각 부서별로 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오
SELECT dept_id, AVG(salary) FROM S_EMP
WHERE title = '사원'
GROUP BY dept_id;

-- 각 부서내에서 각 직책별로 몇 명의 인원이 있는지를 나타내시오
SELECT dept_id, title, COUNT(*)FROM S_EMP
GROUP BY dept_id, title;

-- 각 부서내에서 몇 명의 직원이 근무하는지를 나타내시오
SELECT DEPT_ID, COUNT(*) FROM S_EMP
GROUP BY DEPT_ID;

-- 각 부서별로 급여의 최소값과 최대값을 나타내시오. 단, 최소값과
-- 최대값이 같은 부서는 출력하지 마시오
SELECT DEPT_ID, MIN(SALARY), MAX(SALARY) FROM S_EMP
GROUP BY DEPT_ID
HAVING MAX(SALARY) > MIN(SALARY);

-- 직원테이블(S_EMP)과 부서테이블(S_DEPT)을 JOIN하여 사원의 이름과 부서, 부서명을 나타내시오
SELECT 
S_EMP.NAME,
S_EMP.DEPT_ID,
S_DEPT.NAME
FROM S_EMP, S_DEPT
WHERE S_EMP.DEPT_ID = S_DEPT.ID;

-- 서울 지역에 근무하는 사원에 대해 각 사원의 이름과 근무하는 부서명을 나타내시오
SELECT
S_REGION.NAME,
S_EMP.NAME,
S_DEPT.NAME
FROM  S_REGION, S_EMP, S_DEPT
WHERE S_REGION.ID = S_DEPT.REGION_ID AND S_EMP.DEPT_ID = S_DEPT.ID AND S_REGION.ID = 1;

-- 직원 테이블(S_EMP)와 급여 테이블(SALGRADE)을 JOIN하여 사원의 이름과 급여, 그리고 해당 급여등급을 나타내시오
SELECT 
S_EMP.NAME,
S_EMP.SALARY,
SALGRADE.GRADE
FROM S_EMP, SALGRADE
WHERE LOSAL < S_EMP.SALARY AND HISAL > S_EMP.SALARY;

-- 직원(S_EMP) 테이블과 고객(S_CUSTOMER)테이블에서 사원의 이름과 사번, 그리고 각 사원의 담당고객 이름을 나타내시오
-- 단, 고객에 대하여 담당영업사원이 없더라도 모든 고객의 이름을 나타내고, 사번 순으로 오름차순 정렬하시오.
SELECT 
S_EMP.NAME AS 사원,
S_EMP.ID,
S_CUSTOMER.NAME AS 고객
FROM S_CUSTOMER
LEFT OUTER JOIN S_EMP
ON S_EMP.ID = S_CUSTOMER.SALES_REP_ID
ORDER BY S_EMP.ID ASC;

-- 직원 중에 '김정미' 와 같은 직책(title)을 가지는 사원의 이름과 직책, 급여, 부서번호를 나타내시오(SELF JOIN을 사용할 것)
SELECT
D.NAME,
D.TITLE,
D.SALARY,
D.DEPT_ID
FROM S_EMP S
LEFT OUTER JOIN S_EMP D
ON S.TITLE = D.TITLE
WHERE S.NAME = '김정미';

-- 가장 적은 평균급여를 받는 직책에 대해 그 직책과 평균급여를 나타내시오
SELECT
TITLE, AVG(SALARY) AVG_S
FROM S_EMP
GROUP BY TITLE
HAVING AVG(SALARY) = (SELECT
                        MIN(AVG_S)
    FROM ( 
    SELECT AVG(SALARY) AVG_S
    FROM S_EMP
    GROUP BY TITLE
    )
);