Need some more help
558661Jan 30 2007 — edited Feb 1 2007I 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