What is the difference between DenseRank and Rank function in oracle?

RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK again gives you the ranking within your ordered partition, but no ranks are skipped if there are ranks with multiple items.

E.g:- Suppose we have a table called SalaryDetails as shown below

Query:- Select * from SalaryDetails;

O/P:-

Id	EmployeeDetailsId	Salary
101	1	2000.50
102	2	3050.50
103	3	4000.00
104	4	2000.50
105	5	2000.50

Condition:- Fetch the records from the table and assign the rank to the EmployeeDetailsId on the basis of salary ascending to descending order.

Query:- Select EmployeeDetailsId,Salary,

                            DENSE_RANK() Over(Order By Salary) as DenseRank_Salary,

                           Rank() Over(Order by Salary) as Rank_Salary

              from SalaryDetails;

O/P:-

EmployeeDetailsId	Salary	DenseRank_Salary	Rank_Salary
1	2000.50	1	1
5	2000.50	1	1
4	2000.50	1	1
2	3050.50	2	4
3	4000.50	3	5

How to Use Case Statement with Order By Clause in Oracle?

Suppose we have a condition to get the result from SQL query on the basis of check conditions.
E.g:- we have a table called EmpoyeeDetails

EmployeeDetails
Id	Name
101	Ravi
102	Yash
103	Harsh

Condition:- We have a condition to fetch the Id, Name from EmployeeDetails table when Name is “Yash” then it will come in first priority, when the name is “Ravi” then it will come in the second priority then rest of the records.

Query:-  select emp.* from EMPLOYEESDETAILS emp ------> (Emp is alias of the table)

               order by case when emp.Name='Yash' then 1

                                       when emp.Name='Ravi' then 2 else 3

                               end;

O/P:-

EmployeeDetails
Id	Name
102	Yash
101	Ravi
103	Harsh

Difference between Scalar Function, Table-Valued function, and Aggregate Function in Oracle

Scalar Function:-  Scalar Function are the function that returns a single value, not the result set. we can not use aggregate functions in the scalar function because scalar functions execute row by row, not in the set of values.

E.g:- Upper(), Lower(),Length() etc.

Query:- Select Upper('database dynasty') from dual;

O/P:-      DATABASE DYNASTY

Table-Valued Function:- Table-Valued functions return result set and it can be used in  a From clause, Joins , Outer apply of any query but it can not target any DML operations like (Insert, Update, Delete).

E.g:- we have two tables one is EmployeesDetails and another one is SalaryDetails.

EmployeesDetails			SalaryDetails
Id	Name		Id	EmployeesDetailsId	Salary
1	Ravi		101	1	2000.50
2	Yash		102	2	3050.50

Create or replace Type Typ_EmployeesDetails as Object

(Id Number(10),

Name Varchar2(100));

O/P:- TYPE TYP_EMPLOYEESDETAILS compiled

Create or replace type Tbl_EmployeesDetails as table of Typ_EmployeesDetails;

O/P:- TYPE Tbl_EmployeesDetails compiled

Create or replace function FN_EmployeeDetails

return Tbl_EmployeesDetails

Is 

V_Emp Tbl_EmployeesDetails;

Begin

Select Typ_EmployeesDetails (Id,Name)

Bulk Collect into V_Emp

from EmployeesDetails;

Return V_Emp;

End;

/

O/P:- FUNCTION FN_EMPLOYEEDETAILS compiled

To Execute or use the function with join in select statement.

Select Emp.ID as EmployeeId, Emp.Name,s.Salary

from SalaryDetails s

Inner Join Table(FN_EmployeeDetails())Emp on Emp.Id=s.EmployeeDetailsId;

O/P:- 

EmployeeId	Name	Salary
1	Ravi	2000.50
2	Yash	3050.50

Aggregate Function:- Aggregate Function are user-defined aggregates like Sum(), Count(), Min(), Max() etc. and it typically requires the Group By clause. 

E.g:-  Suppose we have table called Emp 

Query:- Select * from Emp;

O/P:-   

Id	Name
101	Database
102	Dynasty

Condition:- Check the total number from records in Emp table
Query:- Select Count(*) from Emp;
O/P:-    2

How to Create a Package in Oracle?

PL/SQL package is like a library that once written stored in the Oracle database and can be used by many applications. For E.g:- 

Create Table EMP_Package

(Id Number(5),

Name Varchar2(100));

O/P:- table EMP_PACKAGE created.

Create or Replace Package PKG_Test as

Procedure Insert_Record (v_id in number,v_name in varchar2);

Procedure Update_Record (v_id in number,v_name in varchar2);

Procedure Delete_Record (v_id in number);

end;

/

O/P:-  PACKAGE PKG_TEST compiled

Note:- v_id and v_name are the input variables

Now we need to create the body of the package for the procedures that we have defined in the package.

Create or Replace Package Body PKG_Test as

--Procedure to Insert Record

Procedure Insert_Record (v_id in number,v_name in varchar2) as

begin

Insert into EMP_Package (id,name)

values (v_id,v_name);

commit;

end;

--Procedure to Update Record

Procedure Update_Record (v_id in number,v_name in varchar2) as

begin

Update EMP_Package

Set Name=v_name Where Id=v_id;

commit;

end;

--Procedure to Delete Record

Procedure Delete_Record (v_id in number) as

begin

Delete from EMP_Package Where Id=v_id;

commit;

end;

end;

/

O/P:- PACKAGE BODY PKG_TEST compiled

To execute the package follow the below commands:-

*Insert the record into the table i.e EMP_Package through package

  Exec PKG_Test.Insert_Record(1,'Database Dynasty');

  select * From EMP_Package;

  O/P:-

Id	Name
1	Database Dynasty

*Update the record into the table i.e EMP_Package through package

  Exec PKG_Test.Update_Record(1,'Database Dynasty Update');

  select * From EMP_Package;

  O/P:-

Id	Name
1	Database Dynasty Update

*Delete the record from the table i.e EMP_Package through package

  Exec PKG_Test.Delete_Record(1);

  select * From EMP_Package;

  O/P:-

Id	Name

What is Cascade delete in Oracle with foreign key constraint?

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in Oracle. See the e.g below:

Master Table:-

Create Table Emp

(Id Number(10),

Name Varchar2(100),

Constraint PK_Emp_Id Primary Key(Id));

Child Table:-

Create Table Emp_Salary

(Id Number(10),

EmpId Number(10),

Salary decimal(5,2),

Constraint PK_EMP_Salary_ID Primary Key(Id),

Constraint FK_EMP_Salary_EmpId Foreign Key (Empid) References Emp(Id) ON DELETE CASCADE

);

Insert Record into Master table and Child table both:-

Insert into Emp (Id,Name) Values (1,'Jon');

Insert into Emp (Id,Name) Values (2,'Paul');

Insert Into Emp_Salary (Id,EmpId,Salary) Values (101,1,'100.01');

Insert Into Emp_Salary (Id,EmpId,Salary) Values (102,2,'200.01');

Commit;

Check the records from both tables:-

Query For Master table:- Select  Id, Name from Emp;

Query For Child table :- Select Id, EmpId, Salary from Emp_Salary;

O/P from both tables:-

Emp			Emp_Salary
Id	Name		Id	EmpId	Salary
1	Jon		101	1	100.01
2	Paul		102	2	200.01

Now delete the record from master table and child record will be automatically deleted because we have created foreign key constraint with cascade delete.

Query:- Delete From Emp Where Id=2; Commit;

Again check the record from both tables

Query For Master table:- Select  Id, Name from Emp;

Query For Child table :- Select Id, EmpId, Salary from Emp_Salary;

O/P from both tables:-

Emp			Emp_Salary
Id	Name		Id	EmpId	Salary
1	Jon		101	1	100.01

Update Single and Multiple Column of the table where condition matched

Suppose we have two tables “Products” and “Customers” and “ProductsId” is a foreign key in the “Customers” table from "Products" table.

Condition 1 - For Single Column update:- Update the “Items” column in Customers from Products “Items”, where Products.Id equals to Customers.ProductsId.

Query:-

UPDATE Customers cust -------à (“cust” alias name of Customers table)

SET cust.Items=(SELECT prod.Items

                            FROM Products prod  ---------à (“prod" alias name of the Products table)

                            WHERE prod.id=cust.productsid); 

Condition 2- For Multiple Column Update:- Update “Items” and “ExpiryDate” from Products where Products.Id equals to Customers.ProductsId.

Query :-

MERGE

INTO     Customers cust -------à (“cust” alias name of Customers table)

USING  (SELECT Id, Items, ExpriyDate FROM Products

              )prod ----------à (“Prod” alias name of the subquery results i.e output of Products table)

ON        (cust.productid=prod.id) --------à (Here we are matching the conditions)

WHEN MATCHED THEN

UPDATE

SET cust.item=prod.items,

        cust.ExpiryDate=prod.ExpiryDate

/*****Here we can add update multiple columns, before that we need to select those columns from Products table in the above subquery ******/

;

Oracle Tunning Advisor/ Performance Tunning

For Executing this script we need to connect our Oracle Database as SYSDBA then follow below steps:-

Step 1:-  Execute the below anonymous block for the tunning advisor.

Set ServerOutPut On;
DECLARE

 V_SQLQuery        VARCHAR2(500);

 V_SqlTunningTaskId VARCHAR2(100);

 V_CheckTask Number(10);

BEGIN

Select count(task_id) into V_CheckTask 

from  DBA_Advisor_Log 

WHERE TASK_NAME='Tunning_Task1' ;

IF V_CheckTask is not null and V_CheckTask >0 then -----> (Check if Exists in DB it will delete the task 

                                                                                                    --or you can replace the name)

       Delete from DBA_Advisor_Log WHERE TASK_NAME='Tunning_Task1';

      Commit;

End If;

 V_SQLQuery := 'SELECT Id,Name from Emp Where id=1';---------> Query to Check through advisor

 V_SqlTunningTaskId := DBMS_SQLTUNE.create_tuning_task (

             sql_text  => V_SQLQuery,

             user_name  => 'SCOTT',-----------> Name of the owner

             scope    => DBMS_SQLTUNE.scope_comprehensive,

             time_limit => 60,

             task_name  => 'Tunning_Task1',

             description => 'Performance Tunning');-------> Description for Tunning Advisor

 DBMS_OUTPUT.put_line('V_SqlTunningTaskId: ' || V_SqlTunningTaskId);

END;

/

O/P:-  anonymous block completed

            V_SqlTunningTaskId: Tunning_Task1

Step 2:- 

Query:- EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'Tunning_Task1'); 

O/P:-   anonymous block completed

Step 3:- Check tunning task executed and inserted in DBA_Advisor_Log table.

Query:- SELECT task_name, status, execution_start FROM dba_advisor_log 

             WHERE  TASK_NAME='Tunning_Task1';

Step 4:- This below query will generate the report.

Query:- SELECT DBMS_SQLTUNE.report_tuning_task('Tunning_Task1') AS recommendations 

             FROM dual;  

 Note:- Here "Tunning_Task1" is the name of the task.