Informatica ETL and Sql interview question :-

1)Write a query to display only friday dates from Jan, 2000 to till now?

SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'DY') FROM DUAL;

SYSDATE   TO_

--------- ---

24-DEC-16 SAT

SELECT C_DATE,TO_CHAR(C_DATE,'DY') FROM

 (

SELECT TO_DATE('01-JAN-2016','DD-MON-YYYY')+LEVEL-1 C_DATE FROM DUAL CONNECT BY LEVEL <=(SYSDATE - TO_DATE('01-JAN-2016','DD-MON-YYYY')+1)) WHERE TO_CHAR(C_DATE,'DY')='FRI';

2)EVERY day i am gettiing a file how to display jan 5th file in unix
ls -l  | grep 'yyyy-mm-dd'ls -l | grep --color=auto '2006-01-05'

You can sort it as follows:
ls -lu | grep --color=auto '2006-01-05'
List ALL *.c File Accessed 30 Days Ago

Type the following command

find /home/you -iname "*.c" -atime -30 -type -f

3)difference bet rownum and rowid

rowid has a physical significance i.e you can read a row if you know rowid. It is complete physical address of a row.

While rownum is temporary serial number allocated to each returned row during query execution.

ROWID is a unique pseudo number assigned to a row.

Rownum returns the number of rows for a resultant query. BOTH are pseudo columns not occupying any physical space in the database.

Example of Rownum:

Sort salary and return first 5 rows.

select * from

( select *

       from emp 

       order by sal desc ) 

where ROWNUM <= 5;

Example of Rowid:

Below query selects address of all rows that contain data for students in department 20

SELECT ROWID, last_name

       FROM student

       WHERE department_id = 20;

RowId represents a row in a table internally. It can be used for fast access to the row. Rownum is a function of the result set. 

select * from Student where rownum = 2 will get the first 2 rows of your result set.

INFORMATICA - Target Update Override : Updating Target
Table without any Primary Keys defined.
http://ramakantshankar.blogspot.in/2014/03/informatica-target-update-override.html

UDATE     
SET            = :TU.   

                   , [Other columns need to be updated]
WHERE      = :TU.
AND          [other conditions]

• Example:

UPDATE    EMPL_POST_HIST

SET            POST = :TU.POST

                , UPDATE_DATE = :TU.UPDATE_DATE

WHERE EMPL = :TU.EMPL

4 ways to delete duplicate records Oracle

1. Using rowid

SQL > delete from emp
where rowid not in
(select max(rowid) from emp group by empno);

This technique can be applied to almost scenarios. Group by operation should be on the columns which identify the duplicates.

2. Using self-join

SQL > delete from emp e1
where rowid not in
(select max(rowid) from emp e2
where e1.empno = e2.empno );

3. Using row_number()

SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);

This is another efficient way to delete duplicates

4. Using dense_rank()

SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
dense_rank() over(partition by empno order by rowid) rn
from emp
)
where rn > 1
);

Here you can use both rank() and dens_rank() since both will give unique records when order by rowid.

USING UNIX:-

$ sort file | uniq


$ sort -u file

What is an Inline View?INLINE VIEW

It is not a schema object like a normal view.
It is sub query with a name (alias) placed in the from clause of another select statement (main query) for which it (the sub query) acts as a data source.
The outer query will have a reference of the inline view.
The inline view can have a GROUP BY clause, order by clause or even inline view itself can be join.
Inline views are useful for performing the Top-N (Top 3 sales reps or top 10 students etc) analysis.
See AN EXAMPLE OF INLINE VIEW, WHICH HAS THE GROUP BY CLAUSE.  The query finds the employees in the emp table whose salary is less than the maximum salary of their department.

SQL> SELECT ENAME,SAL,E1.DEPTNO,E2.MAXSAL FROM EMP E1,
 (SELECT DEPTNO,MAX(SAL) MAXSAL FROM EMP GROUP BY DEPTNO)E2
 WHERE E1.DEPTNO=E2.DEPTNO AND E1.SAL
ENAME             SAL     DEPTNO     MAXSAL
---------- ---------- ---------- ----------
CLARK            2450         10       5000
MILLER          1300         10       5000
SMITH            800           20       3000
ADAMS           1100        20       3000
JONES            2975         20       3000
ALLEN            1600        30       2850
MARTIN         1250        30       2850
JAMES             950          30       2850
TURNER         1500         30       2850
WARD             1250         30       2850
10 rows selected.

SELECT ENAME,SAL,E1.DEPTNO,E2.MAXSAL FROM EMP E1,
 (SELECT DEPTNO,MAX(SAL) MAXSAL FROM EMP GROUP BY DEPTNO order by deptno)E2 WHERE E1.DEPTNO=E2.DEPTNO AND E1.SAL
ENAME             SAL     DEPTNO     MAXSAL
---------- ---------- ---------- ----------
CLARK            2450         10       5000
MILLER          1300         10       5000
SMITH            800           20       3000
ADAMS          1100         20       3000
JONES            2975         20       3000
ALLEN           1600         30       2850
MARTIN        1250         30       2850
JAMES           950            30       2850
TURNER       1500         30       2850
WARD           1250         30       2850

10 rows selected.

display max 3rd salary in oracle:-

select * from (select * from (select ename,sal from emp order by sal desc)

 where rownum < 4 order by sal)

 where rownum = 1;

SELECT name, salary FROM #Employee e1 WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2 WHERE e2.salary > e1.salary);

Solution to finding the 2nd highest salary in SQL

 

 SELECT MAX(Salary) FROM Employee

WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

What is the exact difference between joiner and lookup transformation:-

What is the exact difference between joiner and lookup transformation:-

A joiner is used to join data from different sources and a lookup is used to get a related values from another table or check for updates etc in the target table. 

for lookup to work the table may not exist in the mapping but for a joiner to work, the table has to exist in the mapping.

A  lookup may be unconnected while a joiner may not

lookup may not participate in mapping
lookup does only non equi join

joiner table must paraticipate in mapping
joiner does only outer join


Joiner :
> It support equiv Join only.
> It may be we can perform outer join only.
> Joiner is used to source only.
> It may be only "=" operator used.
> In joiner may be not present in lookup override.
Lookup :
> It supports Equiv and non equiv join.
> Lookup used to source as well as target.
> It can not perform outer join in lookup.
> It may be = , < , > , <= . >= are used.

> It may be present in lookup override option.

Difference between Lookup and joiner transformation in Informatica

Look up transformation :
a) Look up transformation can be used on a single table or a query to search for records that match incoming records. Matching condition can be specified in the lookup transformation. The result returned can be multiple columns or single column.
b) Lookup transformation can be unconnected or connected transformation. Unconnected transformation can return only single value.
c) Lookup transformation can be static or active. Dynamic lookups are active transformation.
d) Lookup transformation be used with more than one relational operator such as > , =, etc.

 

Joiner transformation :

a) Joiner transformation is used to usually to join data coming from two separate tables or source qualifiers. 
b) The join can be left outer join, inner join, right outer join, etc.
c) The joiner returns all the results that match the join condition.
d) The master records in the joiner transformation is cached. The detail records are not cached. Hence, joiner transformation is active transformation.

When do you use joiner or lookup transformation?

a) If  the table size is not too large then preferable to use lookup.
b) If result from a single matching records needs to be returned then use a lookup. If there is a query that needs to be used in a lookup to find the result for lookup then good to use a lookup. 
c) If you are doing lookup on a table that is updated in the session then use a lookup. Joiners are active so not preferable.
d) If look up table data does not change then the table can be made persistent and used in the lookup which gives even better performance. 
e) If data from two different source qualifiers need to be joined then use a joiner.
f) If data from two different databases are read and need to be joined with a outer or inner join then use joiner transformation.