4. 0722_ sql4

[리뷰]

- grouping & aggregation(avg, max, min, count)

 

- 부서별로 평균급여

  *조건 : 평균급여가 7000 이상 -> having 사용

  *조건 : 입사일자가 2005/01/01 이후여야 함 -> where 사용

 

- 집합연산 : group by 에 등장하는 컬럼이나 집합함수를 select에서 사용할 수 있다.

 

- class : 변수, 함수, 생성자

- subquery

---

--min(salary)를 받는 세 사람을 출력하시오.
select *
from (select rownum, employee_id, salary, last_name --pseudo cloumn
      from employees
      order by salary) sal_emp
where rownum < 4;

--최근 입사일자 세사람을 출력하시오
--도시별 부서개수 많은 순서 2개 도시를 출력하시오
--===============================================================

select employee_id, first_name, last_name, email, phone_number, to_char(hire_date,'YYYY/MM/DD'), job_id, to_char(salary*1200, 'FM999,999,999'), department_id
from employees
where department_id in (select department_id from departments where location_id = (select location_id from locations where locations.city = ?))

select employee_id, first_name, last_name, email, phone_number, to_char(hire_date,'YYYY/MM/DD'), job_id, to_char(salary*1200, 'FM999,999,999'), department_id
from employees
where locations.city = (select *
                        from locations
                        where locations.location_id = (select *
                                                      from departments
                                                      where department_id = employees.employee_id));


--locations.city
select city
from locations;

--employees.department_id = departments.department_id
--departments.location_id = locations.location_id

select d.department_name, round(avg(salary*1200),0)
from employees e, departments d 
where d.department_id = e.department_id
group by d.department_name;