Author: Dev

The PL/SQL MERGE statement in Oracle database is a powerful and flexible way to perform conditional insert, update, or delete operations in a single SQL statement. It is also commonly referred to as an “upsert” operation, as it can insert a new row or update an existing one based on a specified condition. The MERGE…(Continue Reading)

PL SQL Interview Questions and Answers 1. What is an index? Index is an object in a database that provides direct access and quick search without scanning the entire table. The index is used for the most used columns for queries. 2. What are the types of indexes? B-tree index (default) Bitmap index Partition Function-based…(Continue Reading)

Sys_Connect_By_Path SYS_CONNECT_BY_PATH returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition. SYS_CONNECT_BY_PATH is valid only in hierarchical queries. Syntax: SYS_CONNECT_BY_PATH( column, char ) Example: SELECT SYS_CONNECT_BY_PATH(ENAME, ‘/’) “Path” FROM EMP START WITH ENAME = ‘BLAKE’ CONNECT BY PRIOR EMPNO…(Continue Reading)

Rank RANK calculates the rank of a value in a group of values. The return type is NUMBER. Syntax: RANK( expression ) WITHIN GROUP ( ORDER BY expression ) RANK( ) OVER ([query_partition_clause] order_by_clause) Example 1: SELECT RANK(1000, 300) WITHIN GROUP (ORDER BY SAL, COMM) from EMP; Example 2: SELECT e.EMPNO, e.ENAME, e.DEPTNO, e.SAL, RANK()…(Continue Reading)

Dense_Rank DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. Syntax: DENSE_RANK( expression ) WITHIN GROUP ( ORDER BY expression ) DENSE_RANK( ) OVER ([query_partition_clause] order_by_clause) Example 1: select DENSE_RANK(1000, 300) WITHIN GROUP (ORDER BY SAL,…(Continue Reading)