Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need some more help

558661Jan 30 2007 — edited Feb 1 2007
I am struggling with the following two queries:

1. ) Show the employee number, last name, department number and the average salary in their department for all employees.

Now I have built the following query which I know is not correct:

SELECT employee_id, last_name, department_id, salary
FROM employees
WHERE salary IN (select avg(salary) from employees group by department_id);
This just selects the values which have salary of employee = avg salary of the department. I dont know what clause should be used to get all the employees average salaries.

Following is my o/p:

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY
178 Grant NULL 7000
141 Rajs 50 3500
200 Whalen 10 4400

ANd the expected output looks like:
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY
100 King 90 19333.33
101 Kochhar 90 19333.33
102 De Hann 90 19333.33
103 Hunold 60 6400
.
.
.

And so on for 19 others.

2.) This query is:
SHow all employees who were hired on the day of the week on which the highest number of employees were hired.

O/P looks like:
LAST_NAME DAY

Ernst Tuesday
Mourgous Tuesday
Rajs Tuesday
Taylor Tuesday
Higgins Tuesday
Gietz Tuesday

Help me!!
Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 1 2007
Added on Jan 30 2007
10 comments
744 views