Row_Number() - Assigns sequential numbers to the records of a result-set or to the records within groups of a result-set Rank() - Returns the rank of each row within the partition of a result set. Dense_Rank() - Returns the rank of rows within the partition of a result set, without any gaps in the...

Purpose: It is a DML statement that is use for performing the Insert / Update or Delete statement to the target table based on the conditions supplied. It is introduce from Oracle 9i Let us see an example to understand this one First let us create a source table as under CREATE TABLE tblStudent...

Drop Synonym Players; Synonym dropped We have the same synonym in Sql Server too. Hope this helps.Thanks for reading...

Purpose:Generates auto number field in tables. Syntax CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; We can create a sequence as under SQL> CREATE SEQUENCE MySequence 2 MINVALUE 1 3 MAXVALUE 10...

Purpose: It is a dummy table in Oracle with one row and one Varchar2 column whose length is 1. It's structure looks as under SQL> Desc Dual; Name Null? Type ----------------------------------------- -------- ------------- DUMMY ...

Purpose: It compares the field values that sounds like the supplied input word. Syntax: SOUNDEX(Value to compare) SQL> Select * From tblPlayers 2 Where Soundex(BELONGSTO) = Soundex('Lindia'); PLAYERID PLAYERFIRS PLAYERLAST BELONGSTO DOB FEEPERMATCH ---------- ---------- ----...

Purpose: It replaces a value for Not a number while dealing with Binary_Float or Binary_Double values. Syntax: NANVL(Binary_Float/Binary_Double values, replace with) Let us create a test environment Create Table tblTestNANVL(Val1 Binary_Float, Val2 Binary_Double); Insert Into tblTestNANVL Valu...

Purpose: Evaluates a condition when the operand(s) of the condition is null. Syntax: LNNVL(Condition) LNNVL function works on the below principle Condition Value LNNVL Value True False False True Unknown/Null True Let us first create a test environment as under ...

Purpose: Sorts characters by using string of bytes. It can be use both as a comparison and sorting parlance. Syntax: NLSSORT(Sort_Column, NLS_PARAM) Where, Sort_Column => Column to sort NLS_PARAM => It takes the form of NLS_SORT = sort where <i>sort</i> is binary or linguistic sort sequence. ...

In Oracle, it is possible to split the record based on the columns and to insert them into multiple tables at the same time. Consider the below case where we have the tblEmployee as our base table SQL> Select * From tblEmployee; EMPID EMPNAME ...