Datastage Scenario Based Questions - Part 2

Datastage Scenario Based Questions - Part 2

1. Consider the following employees data as source?

employee_id, salary
-------------------
10,          1000
20,          2000
30,          3000
40,          5000

Q1. Create a job to load the cumulative sum of salaries of employees into target table?
The target table data should look like as

employee_id, salary, cumulative_sum
-----------------------------------
10,          1000,   1000
20,          2000,   3000
30,          3000,   6000
40,          5000,   11000

Q2. Create a job to get the pervious row salary for the current row. If there is no pervious row exists for the current row, then the pervious row salary should be displayed as null.

The output should look like as

employee_id, salary, pre_row_salary
-----------------------------------
10,          1000,    Null
20,          2000,    1000
30,          3000,    2000
40,          5000,    3000

Q3. Create a job to get the next row salary for the current row. If there is no next row for the current row, then the next row salary should be displayed as null.

The output should look like as

employee_id, salary, next_row_salary
------------------------------------
10,          1000,       2000
20,          2000,       3000
30,          3000,       5000
40,          5000,       Null

Q4. Create a job to find the sum of salaries of all employees and this sum should repeat for all the rows.

The output should look like as

employee_id, salary, salary_sum
-------------------------------
10,          1000,   11000
20,          2000,   11000
30,          3000,   11000
40,          5000,   11000

2. Consider the following employees table as source

department_no, employee_name
----------------------------
20,            R
10,            A
10,            D
20,            P
10,            B
10,            C
20,            Q
20,            S

Q1. Create a job to load a target table with the following values from the above source?

department_no, employee_list
--------------------------------
10,            A
10,            A,B
10,            A,B,C
10,            A,B,C,D
20,            A,B,C,D,P
20,            A,B,C,D,P,Q
20,            A,B,C,D,P,Q,R
20,            A,B,C,D,P,Q,R,S

Q2. Create a job to load a target table with the following values from the above source?

department_no, employee_list
----------------------------
10,            A
10,            A,B
10,            A,B,C
10,            A,B,C,D
20,            P
20,            P,Q
20,            P,Q,R
20,            P,Q,R,S

Q3. Create a job to load a target table with the following values from the above source?

department_no, employee_names
-----------------------------
10,            A,B,C,D
20,            P,Q,R,S

Recommended Reading

More Scenarios Based Interview Questions on Datastage at
Datastage Scenario Based Questions - Part 3
Datastage Scenario Based Questions - Part 1