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
19983 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

Is there any restriction on the use of union in embedded sql?

1170

What is oracle and pl sql?

1129

Explain some predefined exceptions.

1047

who introduced sql?

1028

Why trigger is used in sql?

984

What is the usage of distinct keyword?

1074

What are the ddl commands?

1021

how to drop an existing table in mysql? : Sql dba

1057

Explain the advantages and disadvantages of stored procedure?

1155

Are stored procedures faster than queries?

954

Which are the different character-manipulation functions in sql?

1077

Can a commit statement be executed as part of a trigger?

1065

how to dump a table to a file with 'mysqldump'? : Sql dba

1045

What view means?

990

What is the sql query to display the current date?

1056