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 UNIT=AFF for Easytrieve code gandikk JCL & VSAM 7 Wed Jul 12, 2017 11:42 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts DB2 Program abending without giving a... rahulgarg14 DB2 7 Mon May 29, 2017 8:10 pm
No new posts Job failing with USER = 4093 REASON C... Pradeepa S ABENDS & Debugging 1 Wed May 17, 2017 3:35 pm
No new posts COBOL Code Parsers and Lineage Establ... balimanja COBOL Programming 2 Tue May 02, 2017 3:30 am


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