Display those employees that have salary > average salary

murali_andaluri · New User Joined: 07 May 2005 Posts: 22

1. An EMPLOYEE table has following fields: EMP_ID, SALARY.
a) Write a query to display those employees that have salary > average salary of all employees.
b) Write a query to display count of those employees that have salary > average salary of all employees.

2.EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 30

DEPTNO DNAME LOC
10 STORE CHICAGO
20 RESEARCH DALLAS
30 SALES NEW YORK
40 MARKETING BOSTON

a). List all employees who are working in a department located in BOSTON:
b). List all those employees who are working in the same department as their manager
c). Retrieve the minimum and maximum salary of clerks for each department having
more than three clerks.

nuthan · Posted: Wed Oct 17, 2007 11:54 am

1 A) SELECT EMPID FROM TABLENAME WHERE SALARY > (SELECT AVG(SALARY) FROM TABLENAME);
1 B) SELECT COUNT(*) FROM TABLENAME WHERE SALARY > (SELECT AVG(SALARY) FROM TABLENAME);

and for second one are u using one table or two tables.

murali_andaluri · New User Joined: 07 May 2005 Posts: 22

Thanks nuthan...

second one is using two tables

nuthan · Posted: Wed Oct 17, 2007 12:54 pm

Try and let me know.

2 A) SELECT EMPNO, ENAME FROM EMPTABLE A, DEPTABLE B WHERE A.DEPNO = B.DEPTNO AND B.LOC = 'BOSTON';

2 B) SELECT A.EMPNO, A.ENAME FROM EMPTABLE A, EMPTABLE B WHERE A.MGR = B.EMPNO AND A.DEPTNO = B.DEPTNO;

2 C) SELECT deptno, MIN(SALARY), MAX(SALARY) FROM TMPTABLE GROUP BY DEPTNO HAVING COUNT(*) > 3;