Find the employee name,salary,department number and average, Database Management System

Example-Find the employee name, salary, department number and average salary of his/her department, for those employees whose salary is more than the average salary of that department.

EMPNO

ENAME

JOB

SAL

DEPTNO

7566

Nirmal

MANAGER

2975

10

7788

Kailash

ANALYST

3000

10

7839

Karuna

PRESIDENT

5000

20

7902

Ashwin

ANALYST

3000

20

7905

Ashwini

MANAGER

4000

20

 

SELECT A.ENAME, A.SAL, A.DEPTNO, B.AVGSAL

FROM EMP A, (   SELECT DEPTNO, AVG (SAL) AVGSAL

                              FROM EMP

                               GROUP BY DEPTNO) B

WHERE A.DEPTNO=B.DEPTNO AND A.SAL> B. AVGSAL;

Output:

ENAME

SAL

DEPTNO

AVGSAL

Kailash

3000

10

2987.5

Karuna

5000

20

4000

Posted Date: 3/11/2013 3:59:56 AM | Location : United States







Related Discussions:- Find the employee name,salary,department number and average, Assignment Help, Ask Question on Find the employee name,salary,department number and average, Get Answer, Expert's Help, Find the employee name,salary,department number and average Discussions

Write discussion on Find the employee name,salary,department number and average
Your posts are moderated
Related Questions
What are the situations while DBMS should not be used? DBMS should not be used in situations like: (i) No need of security. (ii) Not difficulty to access the data (iii) N

Data Warehousing & Mining 1. What is the concept of knowledge discovery in database? 2. Describe the following types of Multidimensional Data Models. • Stars, Snowflakes and

Explain the Exec statement in SQL Ans: Exec statement has the structure    EXEC SQL END-EXEC   Embeded SQl statements are of a form identical to non embedded SQL statem

What is Natural Language Interfaces? Natural Language Interfaces - These interfaces accept requests written within English or a few other language and attempt to "understand" t

Write the syntax of creating an INDEX ? Syntax of Creating an Index: CREATE [BITMAP] [UNIQUE] INDEX ON ( [, ] . . .);

What is the use of Union and intersection operation? Union: The result of this operation contains all tuples that are either in r1 or in r2 or in both r1 and r2.Duplicate tuple

Explain Composite attribute in SQL ? Composite attribute: A composite attribute has many components, each of that is atomic or composite.

What function divides one numeric expression by another and returns the remainder? MOD function divides one numeric expression by another and returns the remainder.

Association as a cache This association must be updated when any new element is added to it, but testing for overlap using association is more efficient.    Figure:

A relationship set is a set of relationships of the similar type. For example, let consider the relationship among two entities sets course and student. Collection of all the in