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
 

 

DB2 Interview question ---

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

New User


Joined: 21 Nov 2006
Posts: 50
Location: India

PostPosted: Tue Jul 06, 2010 6:55 pm    Post subject: DB2 Interview question ---
Reply with quote

Hi,

i have student table as below

S_id subject marks
----- -------- -------
S1 T1 50
S1 T2 60
S1 T3 70
S1 T4 80
S1 T5 90
S2 T1 50
S2 T2 50
S2 T3 50
S3 T1 50
S3 T2 60
S3 T3 70
S3 T4 80
S3 T5 90

1. I want to sleect where the student has all the five subject marks in the table
In the above case i want to selct S1 and S3.


2. I want to display the student marks in horizontal order as below
S-iD Sub1 Sub2 Sub3 Sub4 Sub5 total
S1 50 60 70 80 90 350
S2 50 50 50 150


I searched the forum and i didn't fopund the solution.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Jul 06, 2010 9:30 pm    Post subject:
Reply with quote

1: join 5 times :
Code:
from tab1  A, tab1 B, tab1 C , tab1 D, tab1 E
where a.s_id = b.s_id and a.s_id = c.s_id and ...
  and a.subject = 'T1'
  and b.subject = 'T2'
...



2:use CASE in combination with GROUP BY :
Code:
select s_ID
, sum(case when subject = 'T1' then marks else 0) as Sub1
, sum(case when subject = 'T2' then marks else 0) as Sub2
, sum(case when subject = 'T3' then marks else 0) as Sub3
, sum(case when subject = 'T4' then marks else 0) as Sub4
, sum(case when subject = 'T5' then marks else 0) as Sub5
, sum(marks) as total
from tab1
group by _ID
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Sat Jul 17, 2010 11:56 am    Post subject:
Reply with quote

1: i didn't run the query though.....let me know if there is anything wrong in the query. we can work with the query to get other info also (considering s_id and subject are unique)

Code:

SELECT S_ID
FROM TAB
GROUP BY S_ID
HAVING COUNT(*) > 5
Back to top
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 205
Location: Bangalore

PostPosted: Sat Jul 17, 2010 9:53 pm    Post subject:
Reply with quote

Nice use of case statement in second query.
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 Fault Analyzer - listings question. egrove IBM Tools 4 Thu Aug 11, 2016 5:31 pm
No new posts dataset copy question - REPRO or some... atulxp TSO/ISPF 2 Wed Aug 03, 2016 10:56 pm
No new posts SQL Order By related question Joseph K Thomas DB2 8 Fri Mar 18, 2016 12:53 pm
No new posts DFSort Question krrp DFSORT/ICETOOL 9 Mon Mar 07, 2016 7:11 pm
No new posts Where can i find Interview questions ... Elixir Mainframe Interview Questions 4 Sat Feb 20, 2016 12:38 am


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