Folks,
In this blog we will explore some latest Oracle SQL – Scenario questions for Interview!
Scenario 1:
Select numeric & character values in separate columns using data present in single column.
Input Data – One column having both numeric & character values in it. See below sample data.
create table data (value varchar2(20));
insert into data values ('1');
insert into data values ('a');
insert into data values ('b');
insert into data values ('2');
insert into data values ('c');

Output – Show numeric & character values in two separate columns using select query only. See below output.

Solution of Scenario 1:
Step 1: Separate the data values using REGEXP_LIKE & digit class [:digit:].
select null as numeric_value, value as character_value from data WHERE REGEXP_LIKE(value, '[^[:digit:]]')
union all
select value as numeric_value, null as character_value from data WHERE REGEXP_LIKE(value, '[[:digit:]]')
Output of Step 1:

Step 2 – Remove null values from the data.
select nv.numeric_value, cv.character_value
from
( select numeric_value , rownum rnum from
(select null as numeric_value, value as character_value from data WHERE REGEXP_LIKE(value, '[^[:digit:]]')
union all
select value as numeric_value, null as character_value from data WHERE REGEXP_LIKE(value, '[[:digit:]]')
)
where numeric_value is not null ) nv
full join
(select character_value , rownum rnum from
(select null as numeric_value, value as character_value from data WHERE REGEXP_LIKE(value, '[^[:digit:]]')
union all
select value as numeric_value, null as character_value from data WHERE REGEXP_LIKE(value, '[[:digit:]]')
)
where character_value is not null ) cv
on nv.rnum=cv.rnum
Output Step 2:

Scenario 2:
Print data on the basis of Even/Odd data present in Id Column.
Input Data – See below sample data.
create table data (ID number, value varchar2(20));
insert into data values (1,'A');
insert into data values (2,'B');
insert into data values (3,'C');
insert into data values (4,'D');
insert into data values (5,'E');
insert into data values (6,'F');
insert into data values (7,'G');
insert into data values (8,'H');

Output Required: Suppose we have to print the Even ID – Values

Solution of Scenario 2:
Output 2.1: – With null values
select decode(mod(id,2),0,value, null) as value from data ;

Output 2.2: – Without null values
-- 1st way
select value from data where (mod(id,2))=0;
-- 2nd way
select value
from data
where id in (
select decode(mod(id,2),0,id,null) from data
);


For Odd – Just change the decode condition, see below code.
-- Simple Way
select value from data where (mod(id,2))<>0;
-- Without Null Values
select value from data where id in ( select decode(mod(id,2),0,null,id) from data );
-- With Null Values
select decode(mod(id,2),0,null,value) as value from data ;

Scenario 3:
Fetch alternate data from table – Using Even/Odd rownum.
Suppose we have only one column in the table & we have to fetch alternate records using rowid & rownum concepts.
Input Data –
create table data ( value varchar2(20));
insert into data values ('A');
insert into data values ('B');
insert into data values ('C');
insert into data values ('D');
insert into data values ('E');
insert into data values ('F');
insert into data values ('G');
insert into data values ('H');

Solution of Scenario 3:
Simple way:-
SELECT value FROM
(SELECT(mod(rownum,2)) AS rn,value FROM data
) WHERE rn=0;
For Even Rowid-
-- Without Null Values
select value
from data
where rowid in (
select decode(mod(rownum,2),0,rowid, null) from data
);
-- With Null Values
select decode(mod(rownum,2),0,value, null) as value from data ;


For Odd Rowid-
Simple way:-
SELECT value FROM
(SELECT(mod(rownum,2)) AS rn,value FROM data
) WHERE rn<>0;
-- Other way
-- Without Null Values
select value
from data
where rowid in (
select decode(mod(rownum,2),0, null, rowid) from data
);
-- With Null Values
select decode(mod(rownum,2),0, null, value) as value from data ;

Scenario 4:
Print like this using select query.
2 * 1 = 2
2 * 2 = 4
2 * 3 = 6
2 * 4 = 8
2 * 5 = 10
2 * 6 = 12
2 * 7 = 14
2 * 8 = 16
2 * 9 = 18
2 * 10 = 20
Solution of Scenario 4:
SELECT '2 * ' || rownum || ' = ' || rownum *2 as t
FROM DUAL
CONNECT BY rownum <= 10

Scenario 5:
Print how many ‘e’ in ‘elephant’ using select query.
Solution –
select length('elephant') - length(replace('elephant', 'e', '')) from dual;
-- case-insensitive matching.
SELECT REGEXP_COUNT ('Elephant', 'e', 1, 'i')
FROM dual;
-- case-sensitive matching.
SELECT REGEXP_COUNT ('Elephant', 'e', 1, 'c')
FROM dual;
Output – Using length & Replace

Output – Using Regexp_count

Scenario 6:
create table data ( city varchar2(10), gender char(1) );
insert into data values ('Delhi','M');
insert into data values ('Delhi','F');
insert into data values ('Delhi','M');
insert into data values ('Delhi','M');
insert into data values ('Pune','F');
insert into data values ('Pune','M');
insert into data values ('Pune','F');
insert into data values ('Pune','F');
insert into data values ('Banglore','F');
insert into data values ('Banglore','F');
Input Data – Output Required –

Solution of Scenario 6 using CASE statement:
select
city,
Count ( case gender when 'M' then 1 end) as Male_Count,
Count ( case gender when 'F' then 1 end) as Female_Count,
Count(*) as Total_Count
from DATA
group by city;

Using Pivot-
with w_data as (SELECT * FROM
(
SELECT city, gender
FROM data
)
PIVOT
(
count(gender)
FOR gender IN ('M' as Male_Count , 'F' as Female_Count)
))
select city, Male_Count,Female_Count,Male_Count+Female_Count as Total_Count from w_data
;
Using Decode-
SELECT city,SUM(DECODE(gender, 'M', 1,0)) AS Male_Count,
SUM(DECODE(gender, 'F', 1,0)) AS Female_Count,
count(*) AS Total_Count
FROM DATA
group by city;
Scenario 7:
See below data table. Update product_name ‘CAR’ with ‘BIKE’ & ‘BIKE’ with ‘CAR’ using single update statement.
create table data (Product_id number, Product_name char(1));
insert into data values (1,'CAR');
insert into data values (2,'BIKE');
insert into data values (3,'CAR');
insert into data values (4,'BIKE');
insert into data values (5,'CAR');
insert into data values (6,'BIKE');
insert into data values (7,'CAR');
insert into data values (8,'BIKE');
insert into data values (9,'CAR');
insert into data values (10,'BIKE');

Solution of Scenario 7:
-- Using Decode Statement
update DATA
set product_name = decode (product_name,'CAR', 'BIKE','CAR')
-- Using Case Statement
update DATA
set product_name = ( case product_name
when 'CAR' then 'BIKE'
when 'BIKE' then 'CAR' END)
Scenario 8:
8.1) In ‘abcde12xys2254’ string, replace all numeric data with null.
select regexp_replace('abcde12xys2254', '[0-9]', '') from dual;

8.2) In ‘abcde12xys2254’ string, replace all character data with null.
select regexp_replace('abcde12xys2254', '[^0-9]', '') from dual;

Scenario 9: Binary Tree
Input:- See binary tree below

- Root Node: If node is root node. Which doesn’t have any parent node.
- Leaf: If node is leaf node. Which doesn’t have any further child node.
- Inner: If node is neither root nor leaf node.
Here we have a table, Data, containing two columns: Node and Parent_Node.
create table data (node number, parent_node number);
insert into data values (40,null);
insert into data values (10,20);
insert into data values (20,40);
insert into data values (30,20);
insert into data values (60,40);
insert into data values (50,60);
insert into data values (70,60);

Output Required:-
Query to display the node & node type of Binary Tree.

Solution of Scenario 9:
SELECT Node ,
CASE
WHEN Parent_node IS NULL
THEN 'Root'
WHEN EXISTS
(SELECT 1 FROM Data B WHERE B.Parent_Node=A.Node
)
THEN 'Inner'
ELSE 'Leaf'
END AS Node_Type
FROM Data A
ORDER BY 2 DESC;
Some more questions.
Question 10: Can we use where clause in case statement in SQL? or Vice-Versa Can we use case in where statement in SQL.?
Question 11: Can we use aggregate functions in update statement.
Question 12: Can two procedure in a package have the same name & input parameters differ only by datatype ?
Answer 12 : – Yes, this is possible. This is a concept of Overloading.
Overloading – Creating multiple procedures/functions of the same name in a package, having different numbers of arguments and / or where the arguments have different datatypes.
Create or replace PACKAGE PKG_TEST AS
PROCEDURE PRC_SAMPLE (
INPUT_ID IN VARCHAR2 );
PROCEDURE PRC_SAMPLE (
INPUT_ID IN NUMBER );
END PKG_TEST;
Create or replace PACKAGE BODY PKG_TEST AS
PROCEDURE PRC_SAMPLE (INPUT_ID IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
END PRC_SAMPLE;
PROCEDURE PRC_SAMPLE (INPUT_ID IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
END PRC_SAMPLE;
END PKG_TEST;

Question 13: Can two standalone procedure have the same name. Only input parameters differ by datatype ?
Answer 13: – Yes, this is possible.
CREATE OR REPLACE PROCEDURE SA_PRC_SAMPLE (INPUT_ID IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
END SA_PRC_SAMPLE;
CREATE OR REPLACE PROCEDURE SA_PRC_SAMPLE (INPUT_ID IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
END SA_PRC_SAMPLE;
EXEC SA_PRC_SAMPLE(1);
EXEC SA_PRC_SAMPLE('1');

Question 14: Can 2 functions have the same name & input parameters differ only by datatype ?
Answer : Yes
create or replace PACKAGE PKG_TEST AS
FUNCTION FNC_SAMPLE (
INPUT_ID IN VARCHAR2 ) RETURN VARCHAR2 ;
FUNCTION FNC_SAMPLE (
INPUT_ID IN NUMBER ) RETURN NUMBER ;
END PKG_TEST;
create or replace PACKAGE BODY PKG_TEST AS
FUNCTION FNC_SAMPLE (INPUT_ID IN VARCHAR2)
return VARCHAR2
IS
lv_id VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
lv_id:= INPUT_ID;
return lv_id;
END FNC_SAMPLE;
FUNCTION FNC_SAMPLE (INPUT_ID IN NUMBER)
return NUMBER
IS
lv_id NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
lv_id:= INPUT_ID;
return lv_id;
END FNC_SAMPLE;
END PKG_TEST;


Question 15: Can 2 functions have the same name & same input parameters. Only return type differ by datatype ?
Answer: To overload a function, you must change the “signature” of the function, which is the defined by the position and datatype of the inputs. Return is not a part of the function signature, so it can’t be used for overloading.
I have created sample functions of same name, same parameters but different return type in package. Package Spec & Body compile successfully. But it not work while calling function.
create or replace PACKAGE PKG_TEST AS
FUNCTION FNC_SAMPLE (
INPUT_ID IN VARCHAR2 ) RETURN VARCHAR2 ;
FUNCTION FNC_SAMPLE (
INPUT_ID IN VARCHAR2 ) RETURN NUMBER ;
END PKG_TEST;
/
create or replace PACKAGE BODY PKG_TEST AS
FUNCTION FNC_SAMPLE (INPUT_ID IN VARCHAR2)
return VARCHAR2
IS
lv_id VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
lv_id:= INPUT_ID;
return lv_id;
END FNC_SAMPLE;
FUNCTION FNC_SAMPLE (INPUT_ID IN VARCHAR2)
return NUMBER
IS
lv_id NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
lv_id:= to_number(INPUT_ID);
return lv_id;
END FNC_SAMPLE;
END PKG_TEST;
/

If you have optimized answers for these scenarios, then please comment.
Thanks! Happy Learning! Your feedback would be appreciated!