Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
sakthi_ksv

New User


Joined: 20 Jul 2006
Posts: 48
Location: Chennai

PostPosted: Mon Jul 23, 2007 10:35 am    Post subject: Is it possible to code a query in Having clause??
Reply with quote

Hi,

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

Thanks,
Sakthi.
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Mon Jul 23, 2007 10:43 am    Post subject:
Reply with quote

Sakthi,

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

Yes it is possible.
Back to top
View user's profile Send private message
sakthi_ksv

New User


Joined: 20 Jul 2006
Posts: 48
Location: Chennai

PostPosted: Mon Jul 23, 2007 1:14 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
amitava
Warnings : 1

Active User


Joined: 30 Oct 2005
Posts: 186
Location: India

PostPosted: Mon Jul 23, 2007 3:46 pm    Post subject:
Reply with quote

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

SELECT DEPT, MAX(SALARY), MIN(AGE) FROM <TABLE NAME> GROUP BY DEPT


By this it should fetch the max salary and min age for department.

Correct me if I misunderstood your query.


Regards,

Amitava
icon_wink.gif
Back to top
View user's profile Send private message
sreenivasreddyg

New User


Joined: 23 Apr 2005
Posts: 39
Location: delhi

PostPosted: Mon Jul 23, 2007 6:17 pm    Post subject:
Reply with quote

It should work
Back to top
View user's profile Send private message
sakthi_ksv

New User


Joined: 20 Jul 2006
Posts: 48
Location: Chennai

PostPosted: Mon Jul 23, 2007 6:33 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
amitava
Warnings : 1

Active User


Joined: 30 Oct 2005
Posts: 186
Location: India

PostPosted: Tue Jul 24, 2007 10:20 am    Post subject:
Reply with quote

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?

icon_razz.gif Correct me if I am wrong icon_wink.gif
Back to top
View user's profile Send private message
sakthi_ksv

New User


Joined: 20 Jul 2006
Posts: 48
Location: Chennai

PostPosted: Tue Jul 24, 2007 11:52 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts IMS region inactive - User abend code... gthmrj IMS DB/DC 5 Tue Mar 07, 2017 12:29 pm
No new posts CA Workload Automation event code help vinu78 CA Products 10 Wed Mar 01, 2017 10:05 am
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us