Author: Dev

The Oracle PL/SQL Boolean data type is a data type that can store two values: TRUE or FALSE. The Boolean data type is often used in programming to store values that can only be one of two options, such as true or false. Syntax To declare a variable with the Boolean data type, you use…(Continue Reading)

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)