Oracle SQL – Scenario Questions

Oracle SQL – Scenario Questions

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');

 

Untitled.png

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

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:

3.png

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:

4.png


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');
 

Untitled.png

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

output.png

Solution of Scenario 2:

Output 2.1:  – With null values

select decode(mod(id,2),0,value, null) as value from data ;

3.png

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
            );

1

4.png

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 ; 

1



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');
 

Untitled.png

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 ;

3

4

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 ;

3.png


 

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

output.png


 

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

Untitled

Output – Using Regexp_count

4    3


 

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 –

Untitled.png output.png

 

 

 

 

 

 

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;
 

 

3.png

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');

Untitled.png

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;
 

3.png

8.2) In ‘abcde12xys2254’ string, replace all character data with null.

select regexp_replace('abcde12xys2254', '[^0-9]', '') from dual;
 

4.png


Scenario 9: Binary Tree

Input:- See binary tree below

tree-1.jpg

  • 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);

treeee.JPG

Output Required:-

Query to display the node & node type of Binary Tree.

tre data    tree-1.jpg

 

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!

Advertisements

AWS RDS Instance Setup: Oracle DB on Cloud (Free Tier)

Folks,

AWS Free Tier (Link), the Amazon RDS Free Tier helps us to get started with a database instance in the cloud. Free for 12 months starting with the date on which we create our AWS account.

We can use this to develop new applications or simply gain hands-on experience with Cloud Computing. It is easy to set up, operate, and scale DB.

Database Available:- MySQL, MariaDB, PostgreSQL, Oracle & SQL Server.

Steps for creating your own: Oracle DB Instance on Cloud (Free Tier)

Login AWS with your amazon account. Link

0
Amazon Web Services Login Page

After login AWS redirect to this below Home Page.

Select “RDS” in the Database Section. RDS –  Relational Database Service.

1
AWS Home Page

After selecting “RDS” in the Database Section. Below RDS Dashboard comes up.

Click on “Launch a DB Instance” in this screen.

2
AWS RDS Dashboard

Now from here the main setup starts…


 Step 1: Select Engine: Select the DB Engine:

I’m selecting the Oracle EE Database Engine.

3

Step 2: Production Or Free Tier

For Free Tier: Select Option Second: “Dev/Test”

4

After that click on Next Step.

Step 3: Specify DB Details:

Select check box for options available for free tier RDS.

5

Provide DB details here like master username & password. Please note down these details for future reference.

6

After that click on Next Step.

Step 4:  Configure Advanced Settings:

Use default setting here for free tier service.

7

Now we can launch the Instance.

8

After launching: Db instance is being created.

9

Go to the RDS Dashboard: Instance Tab:  You can check your instance here.

10
DB Instance Creation is in process

 

After some time…

Oracle DB Instance is ready. Note down your Endpoint, which is basically the HOST.

11
Db Instance is available now

My End Point: shobhitdbdemo.cbxoxihdzrvi.us-west-2.rds.amazonaws.com:1521


 

Steps for Connecting Oracle Database: Windows

Install Oracle Client on machine. Also check your firewall settings for port 1521. Open Command Prompt & use below command to connect DB.

sqlplus username/password@host:port/service 

Where username: Your Database Master Username in Step 3

Example:

12

12

Now you can create your own database & other database objects.


 

Thanks!

Happy Learning!