Display Odd/ Even number of records?

Display Odd/ Even number of records?

i:Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
Output:-
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
Output:-
2
4
6
ii) for even number of records

select*from emp where rowid in(select decode(mod(rownum,2),0,rowid) from emp);

for odd number of records

select*from emp where rowid in(select decode(mod(rownum,2),1,rowid) from emp);

Can a primary key contain more than one columns?

yes.. 

                                              A PRIMARY KEY is meant for one column.. If a PRIMARY KEY is defined on more than one column then it is called COMPOSITE PRIMARY KEY.. if you have a table having course id and student id where as one student can apply for many courses and in such a way you can define PRIMARY KEY on both columns like COURSEID AND STUDENTID.

What is a view?

Question : What is a view?

                           View is a logical table which based on one or more than one table or anather view. The table(s) on which view creats are called as base table(s). It does not have it's own data rather it shows the data from the base table(s).

                           View is a virtual table based on the result set of an SQL statement.It contains rows and columns just like real table.

What is a cursor N Details

Question : What is a cursor?

Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

---

Cursor is a variable.
it is similar to a 2D array .
used for processing multiple rows.
used for storing data temporarily.

cursors is a private sql area and are 2 types implict cursor and explicit.implict cursor are predefined and explicit cursor defined by the programmer.

Question : What is use of a cursor variable? How it is defined?

A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. Acursor variable is reference type (like a pointer in C).
Declaring a cursor variable:
TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list
that will eventually be returned by the cursor variable

Question : Difference between an implicit & an explicit cursor.

     PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL
statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

---

The implicit cursor is used to process INSERT, UPDATE,DELETE, and SELECT INTO statements. During the processing ofan implicit cursor,Oracle automatically performs the OPEN,FETCH, and CLOSE operations.

Where as in explicit cursors,the process of its working isdone in 4 steps namely DECLARE a cursor,OPEN a cursor,FETCH from cursor and CLOSE a cursor.

IMPLICT CURSOR:- Automatically porvide by oracle which perform DML statements. queries returns only one row.

EXPLICT CURSOR:- Defined by user. queries returns more than rows.

Explicit Cursor:-We are not able to Handle NO_DATA_FOUND Exception.

Implicit Cursor:-We are able to Handle NO_DATA_FOUND Exception.

According to pl/sql where the data is temporarily stored is called cursor.
The Implicit cursor is created by Oracle engine itself while Explicit cursor is created by programmer by mean of programming statement.
The Exception can be handled in the Implicit cursor whereas Explicit cursor is unable to handle Exceptions.

There are properties of cursor

%open - check cursor is open or not.
%found - Row found out or not.
%not found - yes if no row found.
%row count - Rows affected by statement.

There are 4 steps of working of cursor named DECLARE-cursor is declared, OPEN- cursor is opened, FETCH- data fetched, CLOSE- close the cursor.

Informatica PowerCenter

Informatica Repository Manager

Q. What type of repositories can be created using Informatica Repository Manager?

A. Informatica PowerCenter includeds following type of repositories :

• Standalone Repository : A repository that functions individually and this is unrelated to any other repositories.
• Global Repository : This is a centralized repository in a domain. This repository can contain shared objects across the repositories in a domain. The objects are shared through global shortcuts.
• Local Repository : Local repository is within a domain and it� not a global repository. Local repository can connect to a global repository using global shortcuts and can use objects in it� shared folders.
• Versioned Repository : This can either be local or global repository but it allows version control for the repository. A versioned repository can store multiple copies, or versions of an object. This features allows to efficiently develop, test and deploy metadata in the production environment.

Q. What is a code page?

A. A code page contains encoding to specify characters in a set of one or more languages. The code page is selected based on source of the data. For example if source contains Japanese text then the code page should be selected to support Japanese text.

When a code page is chosen, the program or application for which the code page is set, refers to a specific set of data that describes the characters the application recognizes. This influences the way that application stores, receives, and sends character data.

Q. Which all databases PowerCenter Server on Windows can connect to?

A. PowerCenter Server on Windows can connect to following databases:

• IBM DB2
• Informix
• Microsoft Access
• Microsoft Excel
• Microsoft SQL Server
• Oracle
• Sybase
• Teradata

Q. Which all databases PowerCenter Server on UNIX can connect to?

A. PowerCenter Server on UNIX can connect to following databases:

• IBM DB2
• Informix
• Oracle
• Sybase
• Teradata

Infomratica Mapping Designer

Q. How to execute PL/SQL script from Informatica mapping?

A. Stored Procedure (SP) transformation can be used to execute PL/SQL Scripts. In SP Transformation PL/SQL procedure name can be specified. Whenever the session is executed, the session will call the pl/sql procedure.

Q. How can you define a transformation? What are different types of transformations available in Informatica?

A. A transformation is a repository object that generates, modifies, or passes data. The Designer provides a set of transformations that perform specific functions. For example, an Aggregator transformation performs calculations on groups of data. Below are the various transformations available in Informatica:

• Aggregator
• Application Source Qualifier
• Custom
• Expression
• External Procedure
• Filter
• Input
• Joiner
• Lookup
• Normalizer
• Output
• Rank
• Router
• Sequence Generator
• Sorter
• Source Qualifier
• Stored Procedure
• Transaction Control
• Union
• Update Strategy
• XML Generator
• XML Parser
• XML Source Qualifier

Q. What is a source qualifier? What is meant by Query Override?

A. Source Qualifier represents the rows that the PowerCenter Server reads from a relational or flat file source when it runs a session. When a relational or a flat file source definition is added to a mapping, it is connected to a Source Qualifier transformation.

PowerCenter Server generates a query for each Source Qualifier Transformation whenever it runs the session. The default query is SELET statement containing all the source columns. Source Qualifier has capability to override this default query by changing the default settings of the transformation properties. The list of selected ports or the order they appear in the default query should not be changed in overridden query.

Q. What is aggregator transformation?

A. The Aggregator transformation allows performing aggregate calculations, such as averages and sums. Unlike Expression Transformation, the Aggregator transformation can only be used to perform calculations on groups. The Expression transformation permits calculations on a row-by-row basis only.

Aggregator Transformation contains group by ports that indicate how to group the data. While grouping the data, the aggregator transformation outputs the last row of each group unless otherwise specified in the transformation properties.

Various group by functions available in Informatica are : AVG, COUNT, FIRST, LAST, MAX, MEDIAN, MIN, PERCENTILE, STDDEV, SUM, VARIANCE.

Q. What is Incremental Aggregation?

A. Whenever a session is created for a mapping Aggregate Transformation, the session option for Incremental Aggregation can be enabled. When PowerCenter performs incremental aggregation, it passes new source data through the mapping and uses historical cache data to perform new aggregation calculations incrementally.

Q. How Union Transformation is used?

A. The union transformation is a multiple input group transformation that can be used to merge data from various sources (or pipelines). This transformation works just like UNION ALL statement in SQL, that is used to combine result set of two SELECT statements.

Q. Can two flat files be joined with Joiner Transformation?

A. Yes, joiner transformation can be used to join data from two flat file sources.

Q. What is a look up transformation?

A. This transformation is used to lookup data in a flat file or a relational table, view or synonym. It compares lookup transformation ports (input ports) to the source column values based on the lookup condition. Later returned values can be passed to other transformations.

Q. Can a lookup be done on Flat Files?

A. Yes.

Q. What is the difference between a connected look up and unconnected look up?

A. Connected lookup takes input values directly from other transformations in the pipleline.

Unconnected lookup doesn� take inputs directly from any other transformation, but it can be used in any transformation (like expression) and can be invoked as a function using :LKP expression. So, an unconnected lookup can be called multiple times in a mapping.

Q. What is a mapplet?

A. A mapplet is a reusable object that is created using mapplet designer. The mapplet contains set of transformations and it allows us to reuse that transformation logic in multiple mappings.

Q. What does reusable transformation mean?

A. Reusable transformations can be used multiple times in a mapping. The reusable transformation is stored as a metadata separate from any other mapping that uses the transformation. Whenever any changes to a reusable transformation are made, all the mappings where the transformation is used will be invalidated.

Q. What is update strategy and what are the options for update strategy?

A. Informatica processes the source data row-by-row. By default every row is marked to be inserted in the target table. If the row has to be updated/inserted based on some logic Update Strategy transformation is used. The condition can be specified in Update Strategy to mark the processed row for update or insert.

Following options are available for update strategy :

• DD_INSERT : If this is used the Update Strategy flags the row for insertion. Equivalent numeric value of DD_INSERT is 0.
• DD_UPDATE : If this is used the Update Strategy flags the row for update. Equivalent numeric value of DD_UPDATE is 1.
• DD_DELETE : If this is used the Update Strategy flags the row for deletion. Equivalent numeric value of DD_DELETE is 2.
• DD_REJECT : If this is used the Update Strategy flags the row for rejection. Equivalent numeric value of DD_REJECT is 3.