Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query Help

Author

Topic

Kamlee
Starting Member

6 Posts

Posted - 2004-08-29 : 07:37:29

Write a command file to display the employee name (ename) and department (deptno) of all employees except those who are the lowest paid in each department that has two or more employees. Assume that all salaries are not null. Order the output by ename within deptno.

This is the question and this is the query I have used:

SELECT e.ename, e.deptno
FROM emp e, salgrade g, (SELECT deptno, count(*) total
FROM emp
GROUP BY deptno) d
WHERE e.sal between g.losal and g.hisal
AND g.losal = (SELECT min(losal) FROM salgrade)
AND d.total>2
AND e.deptno = d.deptno

Looking at the question this query should not include those that are the lowest paid in each department - am I unsure if that is included within the above query? Or should I have a command such as 'WHERE EXIST' - i am unsure?

Also should there be a 'ORDER BY' command at the end?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-29 : 08:14:21

>> should there be a 'ORDER BY' command at the end
The question asks for a specific order - that cannot be guaranteed without an order by clause.

SELECT e.ename, e.deptno
FROM emp e, salgrade g, (SELECT deptno, count(*) total
FROM emp
GROUP BY deptno) d

AND d.total>2
AND e.deptno = d.deptno

That bit looks good to get the depts that have two or more employees (except should be >=2).
You need to join e to g as well.
The rest of the where clause isn't going to work.
You can do the same as you've done with d to get the lowest salary per dept (could put it in the same query).
Remember that if the dept has 1 employee that it needs t obe included.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 08:26:18

Hi,

there are certainly variuos ways to write this,
try this one:

SELECT
	e.ename,
	e.deptno
FROM
	emp e
	JOIN salgrade g
		ON e.sal BETWEEN g.losal AND g.hisal
	JOIN ( SELECT deptno, COUNT(*) total FROM emp GROUP BY deptno ) d
		ON e.deptno = d.deptno
WHERE
	d.total = 1	-- Show all emp of depts with 1 employee
	OR		-- If there are >2 emps, only show those not within lowest salgrade
	( 	d.total > 1
		AND
		g.losal != ( SELECT MIN(losal) FROM salgrade ) )
ORDER BY 1,2 /*Edit*/

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-29 : 08:43:24

This looks like homework - I was trying not to do it for him.

p.s. that doesn't do the compare on the min salary per dept - see my previous post.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 08:47:15

Just the "global" min salary, yes I know.

Kamlee, is this homework

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources