User Tools

Site Tools


kurs:select_mit_where_in_subquery
select first_name, last_name, e.department_id, department_name, salary
from   employees e, departments d 
where  (salary, e.department_id) IN (select   max(salary), department_id 
                                  from     employees 
                                  group by department_id)
and e.department_id = d.department_id                                  
order by salary

Alternativ und evtl besser verständlich

with max_sal_per_dep as
(
select department_id,
       max(salary) max_sal
  from employees
  group by department_id
)
select first_name,
       last_name,
       salary,
       e.department_id
from employees e join max_sal_per_dep m
  on e.department_id = m.department_id and
     e.salary = m.max_sal
select last_name, first_name, department_id, salary from employees 
where salary in (select min(salary) from employees group by department_id) 


select first_name, last_name, job_id, salary from employees
where salary < all  (select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG'
order by salary desc


select last_name from employees where employee_id not in (select nvl(manager_id, -1) from employees)

select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null)
kurs/select_mit_where_in_subquery.txt · Last modified: 2017/03/20 11:28 by admin