Is it possible to code a query in Having clause??

sakthi_ksv · New User Joined: 20 Jul 2006 Posts: 48 Location: Chennai

Hi,

I was asked in an interview that is it possible to code a query in having clause?? is it possible??

Thanks,
Sakthi.

murmohk1 · Posted: Mon Jul 23, 2007 10:43 am

Sakthi,

sakthi_ksv · New User Joined: 20 Jul 2006 Posts: 48 Location: Chennai

OK thanks. Can I have a result for the following question.
I have a EMP table with the following columns

EMPNO
EMPNAME
DEPT
SALARY
AGE

I was asked a question like this. 'List out all the departments with Max salary and min age in each depart.

Example is

Department MaxSal MinAge

Manager 100000 29

Could anyone tell me the query for this.

Thanks in advance,
Sakthi

amitava · Active User Joined: 30 Oct 2005 Posts: 186 Location: India

Hey Shakti,
I think the following SQL can do the ourpose what you are looking for -

sreenivasreddyg · New User Joined: 23 Apr 2005 Posts: 39 Location: delhi

It should work

sakthi_ksv · New User Joined: 20 Jul 2006 Posts: 48 Location: Chennai

ok thanks for quick response. If it should work then I am correct. Also the next question I was asked was to select the EMPNO for each department that will be the output of the above query. ie select EMPNO of the employees of each department who has the maximum salary and with minimum age. what will be the query for this.

amitava · Active User Joined: 30 Oct 2005 Posts: 186 Location: India

Hey Sakthi,
I think it is no way possible to fetch the empno also for the max(salary) and min(age) for each dept because there may be cases where more than one person are getting the maximum salary at minimum age. If that is that, then how you are going to fetch from the table?

Correct me if I am wrong

sakthi_ksv · New User Joined: 20 Jul 2006 Posts: 48 Location: Chennai

hi Amitava,
you are right. In that case you should display both the rows. If it is not possible it should display only one row.

Thanks and Regards,
Sakthi.