Home » Other » Training & Certification » simple but complex query (Oracle 9i, Unix)
simple but complex query Tue, 30 October 2007 12:40
 beetel Messages: 96Registered: 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.

Re: simple but complex query [message #277590 is a reply to message #277584] Tue, 30 October 2007 13:02
 Michel Cadot Messages: 67880Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 BlackSwan Messages: 26766Registered: 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
 Maaher Messages: 7064Registered: 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: Sun Jun 13 23:51:13 CDT 2021