Informatica Solutions

Scenario:

Source data looks like below

Target 1 (Event number of occurrences)

A has come 2 times in the source, it should go to Target 1

Target 2 (odd number of occurrences)

If source data repeats odd number of times then it should go to Target 2

Solution:

create table test_odd_even_src

(

col1 varchar2(3)

);

create table test_odd_tgt

(

col1 varchar2(3),

col2 int

);

create table test_even_tgt

(

col1 varchar2(3),

col2 int

)

;

insert into test_odd_even_src values('A');

insert into test_odd_even_src values('B');

insert into test_odd_even_src values('C');

insert into test_odd_even_src values('D');

Mapping Looks like this:

Aggregator Transformation:

Joiner:

Router Transformation:

Now try to insert 2 more records into source and run the session again (Before that truncate target tables)

insert into test_odd_even_src values('A');

insert into test_odd_even_src values('B');

Source Data after inserting 2 new records.

select * from test_even_tgt

select * from test_odd_tgt

SQL Scenario:

Source
id,color
a1,yellow
a2,unknown
a1,green
a3,unknown
a1,unknown
a2,blue

Target
id,color
a1,yellow
a1,green
a3,unknown
a2,blue

Data Preparation:

create table test_color_src

(

id varchar2(30),

color varchar2(30)

);

create table test_color_tgt

(

id varchar2(30),

color varchar2(30)

);

insert into test_color_src values('a1','yellow');

insert into test_color_src values('a2','unknown');

insert into test_color_src values('a1','green');

insert into test_color_src values('a3','unknown');

insert into test_color_src values('a1','unknown');

insert into test_color_src values('a2','blue');

Solution:

Source Data:

SQL Query and Its output:

select id,color from test_color_src where color <> 'unknown'

union

(select id,'unknown' from test_color_src where color = 'unknown'

MINUS

select id,'unknown' from test_color_src where color <> 'unknown'

)

Thanks for Reading my blog..!!

SQL questions are about monorail company. Database consists of tables for a monorail company.

Below listed table definition with column name and one row of sample data.

1. Table employee contains information of all the employee in the organization (including drivers of monorail).

| eid | ename | salary |

| 555 | Raheem | 235 |

2. Table monorail Contains the information aboubt each monorail.

| rid | rname | running_range |

| 1234 | Speedster | 454 |

3. Table route_info contains the description of route on which monorails used to travel.

| 4456 | Gwalior | Delhi | 543 |

4. Table assignment contains the data about which monorail is driven by which employee. This can be a many-to-many relation.

| eid | rid |

| 101 | 4454 |

NOTE: Make all necessary assumptions.

Table structure repeated below for quick reference:

employee(eid,ename,salary)

monorail(rid,rname,running_range)

route_info(route_no,origin,destination,distance,cost)

assignment(eid,rid)

Table structures:

create table employee

(

eid int,

ename varchar2(50),

salary int

)

;

create table monorail

(

rid int,

rname varchar2(50),

running_range int

);

create table route_info

(

route_no int,

origin varchar2(50),

destination varchar2(50),

distance int,

cost int

);

create table assignment

(

eid int,

rid int

);

Problem 1: Underpaid Employee :

Find the names of employees (“ename”) whose salary is less than the price (“cost”) of cheapest route from ‘Bangalore’ to ‘Delhi’ (No need to consider route with connections).

Problem 2: one stop Route:

Find one stop from ‘Bangalore’ to ‘Delhi’. The route will need to make one stop transfer and don’t need to consider time and cost. Query should return two columns : first section route_no and second section route_no.

Problem 3: How many Monorails Does Each Drive:

Provide all employee names (“ename”) along with status on whether they are driving monorail or not (Y/N) and also the number of monorails driving (0 if they are not driving monorail).

Note: The format of output should be like below

Raheem N 0

Nusrat Y 3

……….

Problem 4: Cheapest route to destination:

List all the route destination along with their route_no of the cheapest route to that destination from any origin. Output should have three column: destination, route_no and cost of the route.

Problem 5: Top Paid Drivers:

List all the monorails along with the top 2 paid (salary ranked 1st or 2nd) drivers of each monorail. The output should have four columns: the name of the monorail (rname), the salary ranking in the drivers of same monorail, the name of the driver (ename), the driver’s salary (salary).

Solution 1: Underpaid Employee :

select ename from employee where salary < (select min(cost) from route_info where origin='Bangalore' and destination='Delhi');

Solution 2: one stop Route:

select a.route_no as first_section_route,b.route_no as sec_section_route

from route_info a,route_info b where a.origin='Bangalore'

and b.destination='Delhi' and a.destination=b.origin

Solution 3: How many Monorails Does Each Drive:

select emp.ename, case when xx.cnt is NULL then 'N' else 'Y' end, nvl(xx.cnt,0)

from employee emp left outer join

(

select eid,ename,nvl(count(1),0) as cnt from

(select e.eid,e.ename,a.rid from employee e inner join assignment a

on e.eid=a.eid) x

group by x.eid,x.ename

) xx

on emp.eid=xx.eid

Solution 4: Cheapest route to destination:

Select r.destination, r.route_no,r.cost from route_info r, (select destination,min(cost) as mr_cost from route_info group by destination) mr

where r.cost = mr_cost and r.destination=mr.destination

Solution 5: Top Paid Drivers:

select m.rname,x.salary_rank, x.ename, x.salary from

(

select a.rid, a.eid,e.ename,e.salary, rank() over (partition by a.rid order by e.salary desc) as salary_rank from assignment a inner join employee e on a.eid=e.eid

) X , monorail m

where x.rid=m.rid and salary_rank<=2

Click on below link for Part 1.

For SQL Test : Sample Questions from Amazon Test (1/2)