的Oracle SQL子查询

Question

显示员工last_name，salary和谁的工作无论是在IT或MARKETING部门，赚取比收入最低的人多在ACCOUNTING部门job。按字母顺序排序输出。

我的回答：

select 
    last_name, salary, job_id 
from 
    employees 
where 
    salary > (select min(salary) 
       from employees 
       where department_id = (select department id 
            from departments 
            where department_name like'IT' 
             or like 'Marketing')); 

Answer 1

不清楚自己想要什么，但它听起来像这样。根据需要在选择查询中添加其他列。

http://sqlfiddle.com/#!9/cfb31c/5/0

select e.last_name,e.salary,e.department_id from 
employee e inner join department d 
on e.department_id=d.department_id 
where d.department_name in ('IT','MARKETING') 
and e.salary > (select min(e1.salary) from employee e1 
       inner join department d1 
       where e1.department_id=d1.department_id 
       and d1.department_name = 'ACCOUNTING') 
order by e.last_name 

更新

相同的语句，重新格式化。
另外除去inner关键字和固定on子句，因此SQL是厂商中立的，即工作在MySQL，MS SQL，甲骨文和PostgreSQL，...

select e.last_name, e.salary, e.department_id 
    from employee e 
    join department d on d.department_id = e.department_id 
where d.department_name in ('IT','MARKETING') 
    and e.salary > (select min(e1.salary) 
         from employee e1 
         join department d1 on d1.department_id = e1.department_id 
        where d1.department_name = 'ACCOUNTING') 
order by e.last_name