SQL help

Posts 7 · Viewed 7975 · Go to Last Post

· Snapshot

Like · Likedby · 0

Write a query to display the department name, location name, number of
employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of
People, and Salary, respectively. Round
the average salary to two decimal places.
Match on deptno between the EMP and DEPT tables.

I'm totally lost on this one pls help!!!!!!!!1

gogurkha

· Snapshot

Like · Liked by · 0

It is been long I have not use SQL, since I left Nepal. Try links in this page, you will get the answers.

http://www.sql-tutorial.net/SQL-Aliases.asp

M$Hacks

· Snapshot

Like · Liked by · 0

SELECT department_name as dname, location_name as loc, sum(employees) as number_of_employees, average(salary) as salary
FROM Emp, Dept
WHERE Emp.deptno=Dept.deptno
GROUP BY department_name;

Hope this helps!

Last edited: 26-Mar-09 02:26 AM

Mr. Hyde

· Snapshot

Like · Liked by · 0

Try this let me know if it works:

Select d.dname as Department_Name, d.loc as Location_Name,
(select count(e.employee_id) from EMP e JOIN DEPT d ON e.deptno=d.deptno where e.deptno=d.deptno) as Number_of_People,
(select round(avg(e.salary),2) from EMP e JOIN DEPT d ON e.deptno=d.deptno where e.deptno=d.deptno) as Average_Salary
from EMP e, DEPT d
where e.deptno=d.deptno
group by d.dname

redlotus

· Snapshot

Like · Liked by · 0

it did not work man, problem was with group by everthing else was working

katziman

· Snapshot

Like · Liked by · 0

SELECT d.dept_name AS dname, d.loc_name AS loc, COUNT(*) AS [Number of employees], CAST(AVG(salary) AS numeric(10, 2)) AS [Salary]

FROM dbo.EMP e

INNER JOIN dbo.DEPT d ON d.deptno = e.deptno

GROUP BY d.dept_name, d.loc_name

MickeyBlueEyes

· Snapshot

Like · Liked by · 0

Here you go - To the 3 queries you posted.

Select a.employeename ||' earns '||a.salary||' monthly but wants '||a.salary*3 as DreamSalaries
from employeeTable a

Select a.LastName, a.hireDate, to_char(a.hiredate,'DAY')
from employeeTable a
order by to_char(a.hiredate,'D')

Select a.departmentname as dname, a.locationname as loc, count(*) as numberofpeople, round(avg(b.salary),2) Salary
from dept a, emp b
where a.deptno=b.deptno
group by a.departmentname

Please log in to reply to this post

You can also log in using your Facebook