Shortening nested SQL Query

List the names of employees whose salary is greater than the average salary of the department in which the given employee works.

I have came out with the following solution: column names are: emp_id, name, dept, salary in the table named employee.

select name from employee
where salary >
(
select avg(salary) from employee
where dept= (select dept from employee where emp_i开发者_如何学Pythond = 'a10')
) 
and
dept = (select dept from employee where emp_id = 'a10')
;

Can i make this a little shorter query, expecting the same result?

This query is Oracle specific, but it has the advantage of only hitting the employee table once:

select name from 
   (select name, 
           salary, 
           avg(salary) over (partition by dept) as avg_salary 
      from employee)
where salary > avg_salary;

select name from employee e1
join
  (
    select dept, avg(salary) avg_sal
    from employee e2
    where emp_id = 'a10'
    group by dept
  ) e2 
on e2.dept = e1.dept and
e1.salary > e2.avg_sal 

Try that

No, unfortunately I think what you have is about as short and simple as you can make it to accomplish your desired result.

Maybe not much simpler but from 3 to 2 queries:

SELECT e3.name FROM employee e3
INNER JOIN (
  SELECT e1.dept, AVG(e1.salary) avg_salary
  FROM employee e1 INNER JOIN employee e1 ON e1.dept = e2.dept AND e2.emp_id='a10'
  GROUP BY e1.dept
) t ON t.dept = e3.dept AND e3.salary > avg_salary

Pretty sure I can get it down to 1 by replacing the nested query with another join.

Maybe try this:

SELECT e1.name
FROM employee e1
  INNER JOIN employee e2 ON e2.dept = e1.dept
  INNER JOIN employee e3 ON e2.dept = e3.dept AND e3.emp_id='a10'
GROUP BY e1.name
HAVING AVG(e2.salary) > e1.salary   

normally short or long query should not be focus but efficient query should be focus. It may be off-topic, but ensure proper indexing.