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
beetel
Messages: 96
Registered: April 2007
Member
Hi,
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: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ why without analytic function?
2/ then use a subquery with averages

Regards
Michel
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
BlackSwan
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
Maaher
Messages: 7065
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.

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


Current Time: Fri Mar 29 08:38:42 CDT 2024