SUBQUERY(2)

5) Scalar Subquery

• 사원의 사번, 이름, 부서번호, 부서명을 출력하시오

SELECT employee_id, first_name, departments.department_id, department_name
FROM employees JOIN departments ON (employees.department_id = departments.department_id)

• 사원의 사번, 이름, 부서번호, 부서명을 출력하시오

SELECT employee_id, first_name, department_id, 
	(SELECT department_name
    FROM departments
    WHERE department_id = employees.department_id)
From employees; 

• 사원의 부서별 부서번호, 부서명, 총급여를 출력하시오

SELECT e.department_id, department_name, SUM(salary)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY e.department_id, department_name; 

• 사원의 부서별 부서번호, 부서명, 총급여를 출력하시오(SCALA)

SELECT department_id,
(SELECT department_name
FROM departments
WHERE department_id = employees.department_id)
SUM(salary)
FROM employees
GROUP department_id;

• 사원의 부서별 부서번호, 부서명, 총급여를 출력하시오
  단, 부서없는 사원도 출력한다

SELECT e.department_id, department_name, SUM(salary)
FROM employees e LEFT JOIN departments d ON (e.department_id = d.department_id)
GROUP BY e.department_id, department_name; 

• 사원의 부서별 부서번호, 부서명, 총급여를 출력하시오(SCALA)

SELECT department_id,
	   (SELECT department_name 
       FROM departments
       WHERE department_id = employees.department_id),
       SUM(salary)
FROM employees
GROUP BY department_id;

• 'Sales'부서의 평균급여를 구하시오 (JOIN과 Scalar Subquery 둘다 해보기)

SELECT e.department_id, department_name, AVG(salary)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
WHERE department_name = 'Sales'
GROUP BY e.department_id, department_name; 

1번
SELECT AVG(salary)
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name= 'Sales');

• 'Sales'부서의 평균급여보다 많은 급여를 받는 사원의 부서번호, 급여를 출력하시오

내가 끄적거린거
SELECT employee id, department_id, salary
FROM employees
WHERE salary = 
		(SELECT* 
        FROM department 
        WHERE department_name = 'Sales' 
        AND
		salary > (SELECT AVG(salary) 
		FROM employees
        WHERE department_id = 80);

ㅇㄹ
SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
				FROM employees
				WHERE department_id = (SELECT department_id 
                       				  FROM departments 
                       				  WHERE department_name = 'Sales')) 
                       				  AND 
                                      department_id = (SELECT department_id 
                       FROM departments 
                       WHERE department_name = 'Sales');

1번을 서브쿼리
쌤
SELECT employee_id, department_id, salary
FROM employees
WHERE salary >  (SELECT AVG(salary)
				FROM employees
				WHERE department_id = (SELECT department_id 
                					  FROM departments 
                                      WHERE department_name= 'Sales')
                                      );

• 'Sales'부서의 평균급여보다 많은 급여를 받는 'Sales'사원의 부서번호, 급여를 출력하시오

SELECT employee_id, department_id, salary
FROM employees
WHERE department_id = (SELECT department_id --1번
									FROM departments 
                        			WHERE department_name= 'Sales') 


AND salary > (SELECT AVG(salary)
				FROM employees
			 	WHERE department_id = (SELECT department_id 
                					  FROM departments 
                                      WHERE department_name= 'Sales')
                                      );

SELECT employee_id, department_id, salary
FROM employees e
WHERE department_id = (SELECT department_id --1번
									FROM departments 
                        			WHERE department_name= 'Sales') 
AND salary > (SELECT AVG(salary)
			 FROM employees
			 WHERE department_id = e.department_id);

80번 부서를 밑에서 e 로 씀
메인쿼리의 컬럼을 서브쿼리에서 사용
상호연관서브쿼리

--'Sales'부서번호 : 80번
SELECT*FROM departments WHERE department_name = 'Sales';

--'Sales'평균급여 : 8955.88
SELECT AVG(salary) FROM employees WHERE department_id = 80;

--Sales평균급여보다 

SELECT employee_id, department_id, salary
FROM employees e
WHERE e.salary > (
                 SELECT ROUND(AVG(salary),0)
                 FROM employees e
                 JOIN departments d ON(e.department_id = d.department_id) 
                 WHERE department_name ='Sales'
                 GROUP BY department_name
                 ) AND e.department_id = 
                                       (SELECT department_id
                                        FROM departments
                                        WHERE department_name = 'Sales');