Shortening nested SQL Query

3
votes

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_id = 'a10')
) 
and
dept = (select dept from employee where emp_id = 'a10')
;

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

5
mysqloracledatabase
CAPS LOCK KEY STUCK DOWN. HOW TO UNSTICK IT. It helps to properly use Upper and Lower Case letters in the title of a question. UPPPERCASE ONLY IS NOT GOOD. It looks like you're SHOUTING. - S.Lott
As written, your SQL does not appear to answer the question. Or, rather, it answer the question only for the department that employs employee 'a10', and not for any other departments. If you solve the more general question, your query will get smaller. - Larry Lustig
Also, if this is homework, please tag it as such. - Larry Lustig

5 Answers

2
votes

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;
2
votes
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

0
votes

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

0
votes

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
0
votes

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