Author: Dev

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)

Listagg LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column. Syntax: LISTAGG (measure_expression [, ‘delimiter’]) WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)] Example: SELECT LISTAGG(e.ENAME, ‘; ‘) WITHIN GROUP (ORDER BY e.ENAME) FROM EMP e WHERE e.DEPTNO=10; Output: CLARK; KING; MILLER