SELECT dno,sal FROM EMP WHERE sal>ANY(SELECT AVG(sal)

i have a table
eno dno sal
1 10 200
2 10 150
3 10 100
4 20 75
5 20 100

i want to get sal which is less than the avg sal of thri dept.

eno dno sal
2 10 150
3 10 100
4 20 75

Question Posted / mohan

12 Answers
19954 Views
IBM, I also Faced
E-Mail Answers

Answer Posted / mohan

SELECT dno,sal
FROM EMP
WHERE sal>ANY(SELECT AVG(sal)
FROM emp
GROUP BY dno)
ORDER BY dno;

Is This Answer Correct ?

0 Yes

2 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

How do we use distinct statement? What is its use?

1033

Difference between truncate, delete and drop commands?

1083

Explain how to use transactions efficiently : transact sql

986

Is left join inner or outer?

1023

How many times can we commit in a loop?

1022

What is the usage of distinct keyword?

1067

How is a process of pl/sql compiled?

1161

What are the advantages of pl sql?

1122

What is a temp table?

1031

What is the command used to fetch the first 5 characters of a string?

1209

What is mutating sql table?

1116

How do I run a sql query?

1026

What is secondary key?

935

What is pl/sql table? Why it is used?

1038

How to fetch alternate records from a table?

1245