[SQL] 공부 Review

▶▶▶ 각 업무별로 연봉의 총합을 구하고자 한다. 

연봉 총합이 가장 높은 업무부터 업무명과 연봉 총합을 조회하시오.

단 연봉총합이 30,000보다 큰 업무만 출력

 

SELECT

j.job_title 직업이름,

SUM(e.salary) 연봉총합

FROM employees e, jobs j

WHERE e.job_id=j.job_id   

GROUP BY j.job_title

HAVING SUM(e.salary)>30000

ORDER BY SUM(e.salary) DESC;

그룹으로 묶는데 조건을 주고싶을때 쓰는 HAVING

▶▶▶각 사원에 대해서 사번,이름,업무명, 부서명을 조회

단 도시명이 씨에틀인 지역의 부서에 근무하는 직원만 출력

SELECT

e.employee_id EMPLOYEE_ID,

e.first_name FIRST_NAME,

j.job_title JOB_TITLE,

d.department_name DEPARTMENT_NAME

FROM employees e,departments d,jobs j,locations l

WHERE e.job_id=j.job_id

          AND e.department_id=d.department_id

  AND d.location_id=l.location_id

  AND l.city='Seattle';

▶▶▶ 부서명, 부서별 최소급여, 최대급여, 평균급여를 나타내는 뷰 작성

SELECT

d.department_name AS NAME,

MIN(e.salary) AS MINSAL,

MAX(e.salary) AS MAXSAL,

AVG(e.salary) AS AVGSAL

FROM employees e,departments d

WHERE e.department_id=d.department_id

GROUP BY d.department_name

ORDER BY d.department_name;

▶▶▶ 자신이 속한 팀의 평균연봉보다 많은 연봉을 받는 사원의 아이디,부서아이디,연봉,팀의평균연봉 검색

SELECT

e.employee_id,

e.department_id,

e.salary,

ROUND(vt.AVG,0)

FROM employees e,(SELECT

                          AVG(salary) AVG,

  department_id

  FROM employees

  GROUP BY department_id) vt

WHERE e.salary>vt.AVG

        AND e.department_id=vt.department_id;

▶▶▶ 80년대 입사한 직원의 이름, 입사일, 관리자사번,관리자이름 조회

관리자가 없는 사원정보도 출력결과에 포함되어야 합니다.

SELECT

e1.first_name,

e1.hire_date,

e2.employee_id,

e2.first_name

FROM employees e1,employees e2

WHERE e1.manager_id=e2.employee_id(+)

  AND e1.hire_date BETWEEN '1980-01-01' AND '1989-12-31'

ORDER BY e2.employee_id DESC NULLS LAST;

//slef join, outer join

NULL FIRST, LAST

▶▶▶ SALES 부서에 속한 직원의 이름 급여 부서이름을 조회

단 급여는 100번 부서의 평균보다 적게 받는 직원 정보만 출력

SELECT

e.first_name,

e.salary,

d.department_name

FROM employees e,departments d

WHERE e.department_id=d.department_id

           AND d.department_name='Sales'

   AND e.salary<(SELECT

     AVG(e1.salary)

     FROM employees e1

     WHERE e1.department_id=100);

===================================jdbc

<04-문제3>

SELECT

e.employee_id,

e.first_name,

e.salary

FROM employees e,jobs j

WHERE e.job_id=j.job_id

               AND j.job_title IN('Accountant','Stock Clerk');

<05-문제3>

SELECT

e.employee_id,

d.department_name,

e.salary,

vt.봉급

FROM employees e,departments d,(SELECT

AVG(e1.salary) 봉급

FROM employees e1,departments d1

WHERE  e1.department_id=d1.department_id

AND d1.department_id=30) vt

WHERE  e.department_id=d.department_id AND

d.department_id=30; 

<06-문제3>

SELECT

e.last_name,

d.department_name

FROM employees e,departments d,locations l

WHERE e.department_id=d.department_id 

               AND d.location_id=l.location_id

       AND l.city='South San Francisco'

       AND e.salary BETWEEN 7000 AND 10000;

       SELECT e.last_name,d.department_name FROM employees e,departments d,locations l WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND l.city='South San Francisco' AND e.salary BETWEEN 7000 AND 10000;