20120925 Oracle

/*

    부서별로 최대급여를 받는 사원의 정보를 출력하라.

    EMPLOYEES emp(예명)

    DEPARTMENTS dept(예명)

    

    사원번호/이름/급여/부서이름

    emp.employee_id/emp.last_name/emp.salary/dept.department_name

    

    사원번호  / 이름      / 급여  / 부서이름

      100      King      24000  Executive

      103      Hunold    9000  IT

      108      Greenberg  12008  Finance

      114      Raphaely  11000  Purchasing

      121      Fripp      8200  Shipping

      145      Russell    14000  Sales

      200      Whalen    4400  Administration

      201      Hartstein  13000  Marketing

      203      Mavris    6500  Human Resources

      204      Baer      10000  Public Relations

      205      Higgins    12008  Accounting

*/

select dept.department_name 

from employees emp, departments dept 

where emp.department_id = dept.department_id;

select max(salary), department_id 

from employees 

group by department_id

order by department_id asc;

/*

   사원번호/이름/급여/부서이름

   emp.employee_id/emp.last_name/emp.salary/dept.department_name

*/

SELECT emp.EMPLOYEE_ID 사원번호, emp.LAST_NAME 이름, emp.SALARY 급여, dept.DEPARTMENT_NAME

FROM EMPLOYEES emp, DEPARTMENTS dept

WHERE emp.DEPARTMENT_ID = dept.DEPARTMENT_ID

AND (dept.DEPARTMENT_ID,emp.SALARY) IN (

SELECT DEPARTMENT_ID, MAX(salary)

                    FROM EMPLOYEES

                    GROUP BY DEPARTMENT_ID

)

ORDER BY emp.EMPLOYEE_ID asc;

                                          

select emp.employee_id as 사원번호, emp.last_name as 이름, 

          emp.salary as 급여, dept.department_name as 부서이름

from employees emp, departments dept

where emp.department_id = dept.department_id

and salary in (

                  select max(salary)

                  from employees

                  group by department_id

              )

order by emp.employee_id asc;

select emp.last_name, dept.department_name, empl.first_name 

from (

        select emp.* 

        from employees emp, departments dept

        where emp.department_id = dept.department_id

      ) emp, departments dept, employees empl

where empl.department_id = dept.department_id

and emp.employee_id = empl.employee_id

and emp.employee_id = 198;

/*

  where empl.department_id = dept.department_id

*/

select * 

from departments dept, employees empl

where dept.department_id = empl.department_id;

/*

  and emp.employee_id = empl.employee_id

*/

select * 

from (

        select emp.* 

        from employees emp, departments dept

        where emp.department_id = dept.department_id

      ) emp, employees empl

where emp.employee_id = empl.employee_id;

/*

  emp

*/

select emp.* 

from employees emp, departments dept

where emp.department_id = dept.department_id

/*

  dept

*/

select * 

from departments;

/*

  emp

*/

select * 

from departments;