IQ

Experience Level ~3

Informatica

Qn1 – load file containing 100 records in a target file, following below approach

1-10 à file 1

11-20à file 2 .. and so on

Qn2 - Active and passive transformations

Qn3 – Is sorter and Union active transformation if yes, why?

Qn4 – I am having a file as below

EID, ENAME,SAL

1,Robert, 30

2,William, 40 .. until 10 records

I want a footer record in a flat file target with the above records which would make the file look as below

EID, ENAME,SAL

1,Robert, 30

2,William, 40 .. until 10 records

The sum of Salary is 70

(considering only 2 rows given in the example above) . This question was most frequently asked.

Qn5 – Which transformation create Caches.

Qn6 – I have a file with some duplicate records. I want all the unique records pushed in a file and repeated records pushed to a different file.

Src

1,1,2,3,4,5,5,6,7,2,1,6,3,5

Tgt 1- unique

1,2,3,4,5,6,7

Static and Dynamic lookup cache

Qn7 – Lookup is an active or passive transformation

Qn8 – SCD 2 implementation

Qn9 – Different caches in Lookup Transformation.

Qn10 – Difference between Static and Dynamic lookup cache

Qn11- Different type of ports and their order of execution of ports in Informatica expression transformation.

UNIX

Qn1 – command used to replace string

Qn2 – Command to fetch third column from a CSV file

Qn3 – Status of last executed command

SQL

Qn1– delete all remaining records except the latest record

DELETE FROM student

WHERE ApplicationDateTime <> (SELECT max(ApplicationDateTime)

                              FROM student s2

                              WHERE s2.StudentID  = student.StudentID)

Qn2 – second highest salary

Qn3 – Analytical functions. This was asked in a twisted manner. The question asked was I have data as below,

EmpID	Dept	Sal
1	10	100
2	20	400
3	10	800
4	30	400
5	20	300
6	20	300

I want the output to look like

EmpID	Dept	Sal	Total Sal
1	10	100	900
2	20	400	1000
3	10	800	900
4	30	400	400
5	20	300	1000
6	20	300	1000

Where total Sal comprises of data summed up dept wise.

Experience level 5+

Informatica

Qn1 - I have a file in the below format

USD

50,55,40,23

60,34,20,36

INR

1,3,4,6

5,3,6,5

EUR

80,84,85,88

85,86,88,89

I want the output in the below tabular format

Currency	Val1	Val2	Val3	Val4
USD	50	55	40	23
USD	60	34	20	36
INR	1	3	4	6
INR	5	3	6	5
EUR	80	84	85	88
EUR	85	86	88	89

Qn2 - 3 Flat files having same structure are to be loaded in the target table.. We need to load the file in the target table ad ensure no duplicates are loaded.

2 scenarios here

1)      There can be duplicates in the file which needs to be avoided.

2)      The files can have duplicates between them i.e  File 1 and file 2 can have same record. We need to avoid these as well.

Qn3 – This was asked as a follow up on other question. If my joiner transformation receives inputs from 2 pipelines coming from the same source, is there a specific property I need to set to allow that?

Qn4 – Cache files created in informatica and what will the files contain.

SQL

Qn1 – I have data in a table as below

Seq	Emp ID	Start Date	End Date
1	1	1/1/2016
2	2	2/20/2016
3	3	3/11/2016
4	1	1/1/2017
5	2	8/31/2016
6	1	3/1/2017

The above table has some data discrepancies, wherein the end dates have been deleted accidently. We need to write a single update statement, which would fill the end dates as below

Seq	Emp ID	Start Date	End Date
1	1	1/1/2016	12/31/2016
2	2	2/20/2016	8/30/2016
3	3	3/11/2016	NULL
4	1	1/1/2017	2/28/2017
5	2	8/31/2016	NULL
6	1	3/1/2017

So end date in seq 1 is start date of seq 4 minus 1 day.

Most of the questions asked to 3+ were repeated to 5+ as well. Also there were a few adhoc questions asked as people were solving scenario based qns trying to understand the reason behind a particular implementation like, why a sorter is used here, order of ports when writing the variable logic, etc.