connect by – BigDataEnthusiast

In this blog we will explore some more latest Oracle SQL scenario questions. Here we will cover RPAD, LPAD, Null Function, Instr, Substr, regex_substr, translate.

Part 1 Blog Link: Oracle SQL Scenario Questions Part 1 Topics: Regex, pivot, binary tree and other Oops related PL/SQL questions.
Part 2 Blog Link: Oracle SQL – Scenario Questions – Part 2 Topics: Analytical functions, list aggregate, date functions, union all, pivot & prime numbers.
Part 4 Blog Link: Oracle SQL – Scenario Questions Part 4 Topic: Unique Index, Non-Unique Index & Functional Index. etc.

Scenario 1. What will be the output of following SQL statement.

select rpad('TEST      ',12, '*') from dual;

length (‘TEST ‘) = 10

Output:

Scenario 2. What will be the output of following SQL statement.

select lpad('1234',12, '*') from dual;

Scenario 3: What will be the output of following SQL statement.

select nullif(12,12) from dual;
select nullif(12,13) from dual;

NULLIF (expression 1, expression 2)

NULLIF function returns NULL if expression 1 and expression 2 are equal.
NULLIF function returns expression 1 if not equal.
expression 1 cannot be literal null.
Datatypes should be same for both expression.

select nullif('NULL',12) from dual;

Scenario 4: What will be the output of following SQL statement.

select NVL2('', 'Name avalailbe', 'Name not avalailbe') from dual;
select NVL2('Ram', 'Name avalailbe', 'Name not avalailbe') from dual;

NVL2( input_string, value_if_not_null, value_if_null )

You can substitutes a value when a null value is encountered as well as when a non-null value is encountered.

Scenario 5: Rotating columns to rows, see below screenshot. See below source data.

select * from sales_data
unpivot
( SALES for MONTH_ in (JAN, FEB, MAR, APR) );

Scenario 6: Draw this pattern.

* 
* * 
* * * 
* * * * 
* * * * *

select rpad('*', rownum, '*') from dual connect by rownum &lt;=5;

Scenario 7: Draw this pattern.

* * * * * 
* * * * 
* * * 
* * 
*

select rpad('*', rownum, '*') from dual connect by rownum <=5
order by 1 desc;

Scenario 8: What will be the output of following SQL statement.

select translate ( 'HeXXo WorXd' , 'X', 'l') from dual;

Scenario 8: Extract the state code from these addresses.

Option 1: Using instr & substr

select ADDRESS1,  instr(ADDRESS1,','),
trim(substr(address1,instr(ADDRESS1,',')+1)) state_code from address;

Option 2: Easy way as you know that codes are last two char of string.

select address1, substr(address1,-2) state_code from address;

Scenario 8: Extract the state code from these addresses. Here after the 2nd occurrence of delimiter ~.

select ADDRESS1, regexp_substr(ADDRESS1, '[^~]+',1,2) state_name,
regexp_substr(ADDRESS1, '[^~]+',1,3) state_code from address;

Visit below blogs for more scenario questions.

Thanks!

Happy Learning! Your feedback would be appreciated!

Follow @shobhitsinghIN

BigDataEnthusiast

Tag: connect by

Oracle SQL Scenario Questions – Part 3