|
View previous topic :: View next topic
|
| Author |
Message |
maintenure
New User
Joined: 22 Sep 2008
Posts: 10
Location: bangalore
|
|
|
|
Hi
Two tables are there as follows:
Employee table:
Employee ID | Employee name| Dept ID
Dept table:
Dept ID | Dept name
In employee table emplid is the key,dept table dept id is the key and dept id is the foriegn key.
I want the dept name with employees more than 100.
It is an interview question in IBM.I could nt able to answer. |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select d.name
from sysibm.sysdatabase D
, (select dbid from sysibm.systablespace
group by dbid
having count(*) > 100) A
where a.dbid = d.dbid |
|
|
| Back to top |
|
|
maintenure
New User
Joined: 22 Sep 2008
Posts: 10
Location: bangalore
|
|
|
|
| Thanks . |
|
| Back to top |
|
|
lvani
New User
Joined: 26 May 2010
Posts: 3
Location: Chennai
|
|
|
|
This is the correct query
select d.deptname from employee e , dept d
where e.deptid = d.deptid
group by dept_id
Having count(*) > 100 |
|
| Back to top |
|
|
maintenure
New User
Joined: 22 Sep 2008
Posts: 10
Location: bangalore
|
|
|
|
| Thanks ! |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| lvani wrote: |
This is the correct query
select d.deptname from employee e , dept d
where e.deptid = d.deptid
group by dept_id
Having count(*) > 100 |
funny, because this fails |
|
| Back to top |
|
|
|
|
