25일차//[Oracle] SUB QUERY 예제

--문제1) EMPLOYEES 테이블에서 Kochhar의 급여보다 많은 사원의 정보를 사원
--번호,이름,담당업무,급여를 출력하라.

SELECT employee_id, last_name, job_id, salary, ( SELECT salary
                    FROM employees
                    WHERE last_name  = 'Kochhar')
FROM employees
WHERE salary > ( SELECT salary
                    FROM employees
                    WHERE last_name  = 'Kochhar');

--문제2) EMPLOYEES 테이블에서 급여의 평균보다 적은 사원의 정보를 사원번호,
--이름,담당업무,급여,부서번호를 출력하여라.****                    

SELECT last_name, job_id, salary, department_id
FROM employees
HAVING AVG(salary)>(SELECT AVG(salary)
                    FROM employees
                    GROUP BY salary);

--문제3) EMPLOYEES 테이블에서 100번 부서의 최소 급여보다 최소 급여가
--많은 다른 모든 부서를 출력하라###다시보기

SELECT DISTINCT department_id
FROM employees
GROUP BY department_id
HAVING   MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 100)
;
--문제4) 업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호
--를 출력하여라. 단 업무별로 정렬하여라. ****
SELECT employee_id , first_name, job_id, department_id
FROM employees 
WHERE (job_id,salary )IN (SELECT job_id, MIN(salary)
        FROM employees
        GROUP BY job_id) -- 그룹을 하는이유는?
ORDER BY job_id;

--문제5) EMPLOYEES 과 DEPARTMENTS 테이블에서 업무가 세일드맨 사원의 정
--보를 이름,업무,부서명,근무지를 출력하라.

SELECT a.first_name, a.job_id, d.department_name,d.location_id
FROM employees a, departments d
WHERE job_id = (SELECT job_id 
                FROM employees 
                WHERE job_id = 'SA_MAN'
                GROUP BY job_id);

                --FROM 절에 하는방법--(이게 올바른 답)
                SELECT first_name , job_id, d.department_name, d.location_id

                FROM (SELECT first_name , job_id, department_id
                        FROM employees
                         WHERE job_id = 'SA_MAN') e, departments d
                WHERE e.department_id = d.department_id;


--문제6) EMP 테이블에서 가장 많은 사원을 갖는 MANAGER의 사원번호를
--출력하라.
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT(manager_id) IN (SELECT MAX(COUNT(manager_id))
                            FROM employees
                            GROUP BY manager_id);
--문제7) EMP 테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수를
--출력하라.
SELECT department_id, COUNT(*)--모든행수를 카운트한다
FROM employees
GROUP BY department_id
HAVING COUNT(department_id) IN(SELECT MAX(COUNT(department_id))
                                FROM employees
                                GROUP BY department_id);

--문제8) EMP 테이블에서 사원번호가 123인 사원의 직업과 같고
--                    사원번호가 192인 사원의 급여(SAL)보다 많은 사원의 사원번호, 이름,
--직업, 급여를 출력하라.
SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE job_id =(SELECT job_id
                        FROM employees
                        WHERE  employee_id = 123)

                        AND salary>
                        (SELECT salary
                        FROM employees
                        WHERE  employee_id = 192) ;

SELECT  employee_id, first_name, job_id, salary
FROM employees
WHERE employee_id = 123;

--문제9) 직업(JOB)별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업
--무, 부서명을 출력하라.
-- 조건1 : 직업별로 내림차순 정렬

SELECT a.employee_id, a.last_name, a.job_id, d.department_name
FROM employees a, departments d
WHERE a.department_id = d.department_id
    and salary IN( SELECT MIN(salary) 
                    FROM employees
                    GROUP BY job_id)  --job으로 묶어야함
    ORDER BY job_id DESC;

--문제10) EMPLOYEES 테이블에서 50번 부서의 최소 급여를 받는 사원 보다 많
--은 급여를 받는 사원의 정보를 사원번호,이름,업무,입사일자,급여,부서번호를
--출력하라. 단 50번은 제외 (ANY)

SELECT employee_id, last_name, job_id, hire_date, salary, department_id
FROM employees
WHERE salary > (SELECT MIN(salary)
                FROM employees
                WHERE department_id = 50)

                AND department_id !=50
                ORDER BY department_id DESC; 


--문제11) EMPLOYEES 테이블에서 50번 부서의 최고 급여를 받는 사원 보다
--많은 급여를 받는 사원의 정보를 사원번호,이름,업무,입사일자,급여,부서번호를
--출력하라. 단50번은 제외 (ALL

SELECT employee_id, last_name, job_id, hire_date, salary, department_id
FROM employees
WHERE salary > (SELECT MAX(salary)
                FROM employees
                WHERE department_id = 50)

                AND department_id !=50
                ORDER BY department_id DESC;            

SELECT SUM(SALARY), AVG(salary)  --두개이상의 칼럼을 뽑겠다는게 다중 컬럼이다
FROM employees;