Home » Other » Training & Certification » simple but complex query (Oracle 9i, Unix)
simple but complex query [message #277584] Tue, 30 October 2007 12:40 Go to next message
Messages: 96
Registered: April 2007
I have a table:
create or replace table sample 
(empno  number,
 sal    number,
 deptno varchar2(10));

insert into sample values (1, 300, 'HR');
insert into sample values (2, 2000, 'OPS');
insert into sample values (3, 1200, 'HR');
insert into sample values (4, 220, 'Admin');
insert into sample values (5, 1500, 'Admin');
insert into sample values (6, 110, 'HR');
insert into sample values (7, 800, 'OPS');
insert into sample values (8, 3000, 'OPS');
insert into sample values (9, 4000, 'HR');
insert into sample values (10, 560, 'Admin');

Now, I need to get the average salary per department and the employee in that department who has salary above or equal to that average. Without using analytical functions, please help me with this SQL.

Thanks in advance..
Re: simple but complex query [message #277590 is a reply to message #277584] Tue, 30 October 2007 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ why without analytic function?
2/ then use a subquery with averages

Re: simple but complex query [message #277593 is a reply to message #277584] Tue, 30 October 2007 13:16 Go to previous messageGo to next message
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>1/ why without analytic function?
because those are the rules for this homework assignment.
Re: simple but complex query [message #277670 is a reply to message #277593] Wed, 31 October 2007 02:13 Go to previous message
Messages: 7064
Registered: December 2001
Senior Member
I'll give you some pointers:

1 .To calculate the average salary, Oracle has a function AVG (AVeraGe).
SELECT AVG(salary) avg_sal
FROM   yourtable

2. To get this for each department, use GROUP BY

3. To get the employees with a salary higher than the average for his department, you can use a correlated subquery. A subquery in the where clause that references a table in the from clause.

Previous Topic: institues giving training on Oracle CRM
Next Topic: Table modification
Goto Forum:

Current Time: Sun Jun 13 23:51:13 CDT 2021