24일차//[Oracle] SUB QUERY (1)

/*
SELECT 
FROM
WHERE



*/

SELECT e.employee_id, e.first_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
    AND e.employee_id = 103;

SELECT a.first_name, a.employee_id,
    a.manager_id ,b.employee_id,
    b.first_name

FROM employees a, employees b  -- a는 사원  b는 상사
WHERE a.manager_id = b.manager_id;

--SUB QUERY
--QUERY안의   QUERY
--한개의 행( ROW) 에서 결과 값이 반환되는 QUERY
--RECORD ->한명의 데이터
/*

SELECT 단일 row 단일 column(산출되는 데이터는 한개 칼럼도 한개)  -- 무조건 한개로 나와야된다.
FROM        다중ROW다중 COLUMN이 가능하다
WHERE       다중ROW다중 COLUMN이 가능하다

*/
SELECT first_name 
FROM employees;

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

SELECT SUM(SALARY)
FROM employees;


--select절
SELECT employee_id, first_name,
    (SELECT first_name
    FROM employees
    WHERE employee_id = 100)

FROM employees;

--SELECT first_name,
--(SELECT SUM(SALARY) FROM employees)
---FROM employees;

--FROM절  
SELECT employee_id, first_name, salary
FROM (SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 100)
WHERE salary > 8000;

--부서번호가 50번 급여가 6000이상인 사원
SELECT
    *
FROM employees
WHERE department_id = 50
    and salary >= 6000;

-- FROM 절을 사용    
SELECT employee_id, salary, department_id 
FROM ( SELECT employee_id, salary, department_id 

        FROM employees
        WHERE department_id = 50)
WHERE  salary >= 6000;

--급여의 합계와 ,인원수 ,사원명, 월급
SELECT job_id 
FROM employees
GROUP BY job_id;

--급여의 합계와 ,인원수 ,사원명, 월급 FROM절 사용함
SELECT e.employee_id, e.salary,
    e.job_id, j.job_id,
    j."급여의 합계", j.인원수
FROM employees e, (SELECT job_id, SUM(salary) as  "급여의 합계" ,count(*) as  인원수
    FROM employees
    GROUP BY job_id) j 

    WHERE e.job_id = j.job_id;

--where
--평균급여보다 많이 받는 사원
--평균급여를 섭 쿼리로
SELECT first_name,  salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

 -- department_id = 90의 업무(job_id)를 산출하고싶다
 SELECT job_id, first_name
 FROM employees
 WHERE department_id = 90;

 SELECT job_id, first_name, salary
 FROM employees
 WHERE job_id IN( SELECT job_id
 FROM employees
 WHERE department_id = 90);

 --부서별로 가장 급여를 적게 받는 사원과 같은 급여를 받는 사원
 SELECT e.employee_id, e.first_name,e.last_name, e.salary, d.department_name
 FROM employees e, departments d
 WHERE salary = (SELECT MIN(salary)  FROM employees);


 SELECT employee_id, first_name, salary, department_id
 FROM employees 
 WHERE salary IN (SELECT MIN(salary)  
                    FROM employees
                    GROUP BY department_id);


--부서별로 가장 급여를 적게 받는 사원과 급여   
SELECT department_id, first_name, salary
FROM employees
WHERE (department_id,salary) IN(SELECT department_id, MIN(salary)
                        FROM employees
                        GROUP BY department_id)
                    ORDER BY department_id;



SELECT
    *
FROM departments;

SELECT
    *
FROM employees;