June 18, 2011 at 12:39 pm
Consider the following tables:
department
deptid (type:INT)
deptname (type: TEXT)
hours (type:INT)
active (type:BIT)
employee
empid (type:INT)
empname (type: TEXT)
deptid (type: INT)
designation (type: TEXT)
salary (type: INT)
Write a query to return the columns empname and deptname of the employees belonging to those departments that have a head count of 4 or more. The records should be returned in alphabetical order of empname..
I write like this..
SELECT e1.empname, d.deptname from employee AS e1
FULL JOIN department AS d on e1.deptid = d.deptid
WHERE e1.deptid IN(
SELECT deptid FROM(
SELECT e2.deptid, COUNT(e2.empid)
FROM employee AS e2
GROUP BY e2.deptid
HAVING COUNT(e2.empid) >= 4
)
)
ORDER BY empname;
How would you improve on this?
June 18, 2011 at 1:26 pm
June 18, 2011 at 2:21 pm
Why are you using a full join instead of an inner join?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply