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.

Some interview Questions

1. How can you recognize whether or not the newly added rows in the source are gets insert in the target?

Version number

Flag value

Effective date Range 

2.What is the use of incremental aggregation? Explain me in brief with an example. 

                        Captured changes in the source to aggregate calculations in a session. If the source changes only incrementally and you can capture changes, you can configure the session to process only those changes. This allows the Informatica Server to update your target incrementally,

3.What is the difference between stop and abort? 

 

                                         In this case data query from source databases is stopped immediately but whatever data has been loaded into buffer there transformations and loading contunes.Abort: Same as Stop but in this case maximum time allowed for buffered data is 60 Seconds.

4.What is the Confirmed Dimensions?

Conformed Dimensions: The dimension which gives the same meaning across different star schemas is called Conformed dimension.

5.About DTM?

D.T.M. [Data Transformation Manager]: It is responsible for modifying the data according to the transformation rule given in the mapping. it stores the data in temporary memory call staging area.[buffer]. Where data transformation activities takes place.

6.Type of Dimensions? 

Fact constellation: its process of joining two fact tables.

Conformed dimension: a dimension table which can be shared by multiple fact tables is known as conformed dimension [common dimension].

Fact less fact table: a fact table without any fact is known as fact less fact table.

Junk dimensions: a dimension with a type a flag, bullion and description or not used to describe the key performance indicators are known as Junk dimensions.

Dirty dimensions: in a dimension table if a record existing more than once with difference non key attribute is known as dirty dimensions.