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 8:
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;
If you have optimized answers for these scenarios, then please comment.
Thanks! Happy Learning! Your feedback would be appreciated!
Follow @shobhitsinghIN