Oracle SQL Scenario Questions – Part 3

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


 

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

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

length (‘TEST      ‘) = 10

Output:

rpad

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

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

lpad

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

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

nullif

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;

nullif

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;

vnl2

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.

unpv

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

unpv2

Scenario 6: Draw this pattern.

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

star

Scenario 7: Draw this pattern.

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

star

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

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

translate

Scenario 8: Extract the state code from these addresses.

insttr

Option 1: Using instr & substr

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

instr2

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

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

subsst

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

kerala

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

kk


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle SQL Scenario Questions – Part 2

Oracle SQL Scenario Questions  – Part 2

In this blog we will explore some more latest Oracle SQL scenario questions. Here we will cover analytical functions, list aggregate, date functions,  union all, pivot & prime numbers.


Scenario 1. In below example remove the duplicate. Example: Records 1 & 2 are identical.

1Src

CREATE TABLE "DISTANCE"
  (
    "SOURCE_NAME" VARCHAR2(20 BYTE),
    "DEST_NAME"   VARCHAR2(20 BYTE),
    "DISTANCE"    NUMBER
  );

Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','JAIPUR',281);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('JAIPUR','DELHI',281);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','PUNE',1427);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('PUNE','DELHI',1427);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','SPITI',731);

COMMIT;

Solution:  You can use the LEAST/GREATEST function to find out the dupes.

SELECT d.*,
row_number() over (partition BY LEAST(SOURCE_NAME, DEST_NAME),
GREATEST(SOURCE_NAME, DEST_NAME), distance order by distance )AS RNUM
FROM DISTANCE d;

2

From this data set you can filter the rows using the RNUM column to fetch distinct rows.

WITH data_set AS
(SELECT d.*,
row_number() over (partition BY LEAST(SOURCE_NAME, DEST_NAME), GREATEST(SOURCE_NAME, DEST_NAME), distance order by distance ) AS RNUM
FROM DISTANCE d
)
SELECT * FROM data_set WHERE RNUM =1;

3

Scenario 2. In below example customer data is present for product with dates. Fetch the customer who came in all months of quarter at least once. Like Jan, Feb, Mar or Apr, May, June etc. Notice in below data-set C1 & C3 came in all months of quarter Q1 & Q2 respectively.

11

Solution: You can use the date functions to find out the months & quarter.

SELECT CUST_ID,
listagg (TO_CHAR(BILL_DATE,'MM'),'||') within GROUP (
ORDER BY BILL_DATE) months_name,
TO_CHAR(BILL_DATE,'q')
FROM SALES
GROUP BY CUST_ID,
TO_CHAR(BILL_DATE,'YYYY'),
TO_CHAR(BILL_DATE,'q');

111

WITH dataset AS
  (SELECT CUST_ID,
    listagg (TO_CHAR(BILL_DATE,'MM'),'||') within GROUP (
  ORDER BY BILL_DATE) months_name,
    TO_CHAR(BILL_DATE,'YYYY') year_,
    TO_CHAR(BILL_DATE,'q') quarter_
  FROM SALES
  GROUP BY CUST_ID,
    TO_CHAR(BILL_DATE,'YYYY'),
    TO_CHAR(BILL_DATE,'q')
  )
SELECT *
FROM dataset
WHERE months_name IN ('01||02||03','04||05||06','07||02||09','10||11||12') ;
 

12

Scenario 3.  Output of this SQL: Select count(1), SUM(1) from dual where 1=2;

Select count(1), SUM(1) from dual where 1=2;

333

Scenario 4.  Print 1st day of Month, Last Day, Mid, Week, Quarter for given date.

WITH dataset AS
  (SELECT to_date('10-JAN-2019','dd-mon-yyyy') date_ FROM dual
  union
  SELECT to_date('20-FEB-2019','dd-mon-yyyy') date_ FROM dual
  union
  SELECT to_date('05-JUN-2019','dd-mon-yyyy') date_ FROM dual
  )
SELECT to_char(date_,'DD-MM-YYYY'), to_char(date_,'Q') Quarter ,to_char(date_,'W') week,
trunc((date_),'MM') FIRST_DATE_OF_MONTH, last_day(date_) LAST_DATE_OF_MONTH
FROM dataset;

4444

Print Day of the date:

day

Scenario 5.  In below example you have to print date twice. See sample date.

Input:  555  Output Required: 6666

Solution:

WITH data_set AS
  ( SELECT id_ FROM dataset
  UNION ALL
  SELECT id_ FROM dataset
  )
SELECT * FROM data_set ORDER BY 1;

555

Scenario 6.  In below example you have ~ tilde separated date in column VAL. You have to break the string using SQL like below. Refer required output screenshot.

Input: 7777 Required Output: 7777

CREATE TABLE DATASET ( ID_ NUMBER, VAL VARCHAR2(100));

INSERT INTO  DATASET VALUES (1, 'A~B~C~D');
INSERT INTO  DATASET VALUES (2, 'X~Y~Z');
INSERT INTO  DATASET VALUES (5, 'K');
COMMIT;

Solution: 

WITH data_set AS
  ( SELECT id_,val, regexp_count(val,'~') sep_cnt FROM dataset
  ),
  numseries AS
  (SELECT rownum rnum FROM DUAL CONNECT BY rownum =numseries.rnum
ORDER BY 1;

7777

Scenario 7.  Similar to Scenario 6, In below example you have data in VAL column & you have bring it in one row separated by double pipe ||.

Input : 7777 Required Output: 7777

DROP TABLE DATASET;
CREATE TABLE DATASET ( ID_ NUMBER, VAL VARCHAR2(100));
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'A');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'B');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'C');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'D');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'Y');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'Z');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'X');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('5', 'K');
COMMIT;

Solution: 

SELECT ID_,
  listagg (VAL, '||') within GROUP (
ORDER BY VAL) NEW_VAL
FROM dataset
GROUP BY ID_;

7777

Scenario 8. Rotating rows to column, see below screenshot.

Input: 7777 Output: 77

Solution : Using Pivot Clause

SELECT *
FROM
  (SELECT prod_id, month_, sales FROM sales_data
  ) pivot( MAX(sales) FOR month_ IN ('Jan' AS JAN, 'Feb' AS FEB, 'Mar' AS MAR, 'Apr' AS APR) );

 

7777

Scenario 9 : Print prime numbers using SQL

WITH a AS
  ( SELECT rownum rn FROM dual CONNECT BY rownum <=20
  ),
  b AS
  (SELECT rownum rn FROM dual CONNECT BY rownum =b.rn
  ),
  prime_num AS
  (SELECT rn FROM div WHERE mod_=0 GROUP BY rn HAVING COUNT(rn1)=2 ORDER BY 1
  )
SELECT * FROM prime_num;

prime


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Range Partition

Range partitioning is useful when data has some sort of logical range. Example dates or numbers. See below example where monthly range partitions created based on date column in account table.

CREATE TABLE T_ACCOUNT
  (
    V_ACCOUNT_NUM  VARCHAR2(100 CHAR),
    V_ACCOUNT_NAME VARCHAR2(200 CHAR),
    ACC_DATE DATE
  )
  PARTITION BY RANGE
  (
    ACC_DATE
  )
  (
    PARTITION P_DEC2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')),
    PARTITION P_JAN2019 VALUES LESS THAN (TO_DATE('01-FEB-2019','DD-MON-YYYY')),
    PARTITION P_FEB2019 VALUES LESS THAN (TO_DATE('01-MAR-2019','DD-MON-YYYY')),
    PARTITION P_MAR2019 VALUES LESS THAN (TO_DATE('01-APR-2019','DD-MON-YYYY'))
  );

 

Fetching the data from particular partition : In below example you will able to see only match partition data.

SELECT * FROM T_ACCOUNT PARTITION(P_MAR2019);

 

Check partitions metadata in user_tab_partitions & subpartitions.

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_ACCOUNT';

1

To check the Partition KEY of the table, use below query:

SELECT partition_name,
  column_name,
  high_value,
  partition_position
FROM USER_TAB_PARTITIONS PART ,
  USER_PART_KEY_COLUMNS PART_KEY
WHERE table_name ='T_ACCOUNT'
AND PART.table_name = PART_KEY.name;

In the above example any record having acc_date less than ’01-JAN-2019′ will go to P_DEC2018 partition. See below example:

INSERT
INTO "T_ACCOUNT"
  (
    V_ACCOUNT_NUM,
    V_ACCOUNT_NAME,
    ACC_DATE
  )
  VALUES
  (
    '1234567890',
    'ABC',
    TO_DATE('20-OCT-2010', 'DD-MON-YYYY')
  );
COMMIT;
SELECT * FROM T_ACCOUNT PARTITION (P_DEC2018) ;

In the this example any record having acc_date greater than ’31-MAR-2019′ will error out, as no partition exists for that date.

Example:

INSERT
INTO "T_ACCOUNT"
  (
    V_ACCOUNT_NUM,
    V_ACCOUNT_NAME,
    ACC_DATE
  )
  VALUES
  (
    '1234567890',
    'ABC',
    TO_DATE('20-OCT-2019', 'DD-MON-YYYY')
  );

Error report:
SQL Error: ORA-14400: inserted partition key does not map to any partition
14400. 00000 – “inserted partition key does not map to any partition”
*Cause: An attempt was made to insert a record into, a Range or Composite
Range object, with a concatenated partition key that is beyond
the concatenated partition bound list of the last partition -OR-
An attempt was made to insert a record into a List object with
a partition key that did not match the literal values specified
for any of the partitions.
*Action: Do not insert the key. Or, add a partition capable of accepting
the key, Or add values matching the key to a partition specification

1

Here in this example we can see the partition key is defined maximum upto 2019-04-01 (Non-inclusive) . But we are trying to insert 20-OCT-2019, which is not getting mapped to any partition, that’s why this error occured as date value which we are trying to insert is T_ACCOUNT not satisfying the partition key range criteria.

Solution: To fix it, you may have to add new partitions or add a Maxvalue partition.

ALTER TABLE T_ACCOUNT ADD partition P_OCT2019 VALUES less than (TO_DATE('01-NOV-2019', 'DD-MON-YYYY'));
ALTER TABLE T_ACCOUNT ADD partition P_MAXVALUE VALUES less than (MAXVALUE);

You can also go with : Oracle Interval Partitioning to resolve this issue, but this not preferable.

Drop Partition:

Once historical data is no longer required for business analysis, after retention period the whole partition can be dropped.

DROP PARTITION P_DEC2018;

Updating Key:

When you are trying to update the partition key column for any record.

UPDATE T_ACCOUNT SET ACC_DATE='20-JAN-2019' WHERE v_account_num='1234567890';

Error report:
SQL Error: ORA-14402: updating partition key column would cause a partition change
14402. 00000 – “updating partition key column would cause a partition change”
*Cause: An UPDATE statement attempted to change the value of a partition
key column causing migration of the row to another partition
*Action: Do not attempt to update a partition key column or make sure that
the new partition key is within the range containing the old
partition key.

You can’t move a row with an update: Row movement disabled, which is the default option.

Solution:  We can enable Row movement to partition table T_ACCOUNT. It allows rows to be moved across partitions.

ALTER TABLE T_ACCOUNT ENABLE ROW MOVEMENT;
UPDATE T_ACCOUNT SET ACC_DATE='20-JAN-2019' WHERE v_account_num='1234567890';
COMMIT;

 


Thanks!

Happy Learning! Your feedback would be appreciated!

Dataserver, Physical Schema, Logical Schema & Context | Oracle Data Integrator 11g

Dataserver, Physical Schema, Logical Schema & Context | Oracle Data Integrator 11g

Folks,

In this blog we will explore some areas of ODI Topology Navigaor.

  • We will explore Dataserver, Physical Schema, Logical Schema & Context.
  • Relationship between Physical, Logical Schema & Context.

Regarding setup & configuration of Dataserver, Physical Schema, Logical Schema in ODI using Oracle Technology please refer below video.


Let’s explore Topology Navigator in detail.

topology section
Topology Navigator

In Topology Navigator we have ‘Physical Architecture‘ section where we define the physical connection details of any Instance ( either Oracle DB, SQL Server,  IBM DB2, PostgreSQL, MySQL etc.) or any file system.

What is Dataserver?

A ODI object that defines the physical connection to any database instance or any file system. It basically store the host, username & password related details of an instance.

To define any physical connection in ‘Physical Architecture‘, you have to create a Data Server. Just click on your desired technology in Physical Architecture & click on ‘ New Data Server’.

NEWDATASERVER
Physical Architecture

After that you have to provide instance details. In case of any Database instance provide host, user/password & JDBC details.

Database server new
Data Server: DS_ORACLE_DEV

JDBC Details in case of oracle database as instance:

JDBC details
JDBC Details Data Sever: Localhost

In case of any file. Just provide the folder path from where you pick raw files in interface.

file1
Data Server File Type

JDBC Details in case of file:

file2
JDBC Details File Type

Now after adding the Data-server, next step is to add the ‘Physical Schema‘ in Data-server.

What is Physical Schema?

Physical Schema represents the actual connection to the data source (any database instance or any file system).

ODI Physical Schema corresponds to a pair of schema:

  • Main Schema (Data) – In which ODI look for the source and target objects for the interfaces.
  • Work Schema – Generally used by ODI for staging purpose, here ODI create temporary data objects associated to the sources and targets. Example C$, I$, E$ tables. It is always preferred to create & use separate work schema.

To define any physical schema, go to Data Server you created and then just click on new ‘New Physical Schema’.

PS.png
New Physical Schema

After that you have to provide Schema Name & Work Schema name using drop down. It will show you all the schema present in selected data-server instance.

PS02
Physical Schema Configuration
  • RAW_SCHEMA – schema where my source/target tables lies.
  • ODI_WORK_SCHEMA – Created for ODI staging purpose.
ps03.PNG
Physical Schema – RAW_SCHEMA present in Dataserver DS_ORACLE_DEV

Now after adding the Physical Schema, next step is to create Logical Schema & Context.

What is Logical Schema & Context?

  • Logical schema represents the logical name associated to that source/target objext.
  • One logical schema can be associated with multiple physical schema along with context, i.e. one logical schema is associated with different physical schema using different context.

Create Context:

To add any new context, go to Context Section and then just click on new ‘New Context’.

contextContext 02

Create Logical Schema:

To define any logical schema in ‘Logical Architecture‘ . Just click on your desired technology & click on ‘New Logical Schema’.

LA

LS02
Add New Logical Schema

After that you have to provide the logical schema name & Physical Schema mapping with context.

LS03
Logical Schema

So in Dev Context, Logical Schema LS_RAW_SCHEMA is pointing to Physical Schema DS_ORACLE_DEV.RAW_SCHEMA.

It may be possible that in Test Context, Logical Schema LS_RAW_SCHEMA is pointing to different Physical Schema DS_ORACLE_TEST.RAW_SCHEMA.

lS_Context.PNG

Refer this video clip for better understanding of Logical Schema & Context relationship.


Thanks!

Happy Learning! Your feedback would be appreciated!

 

Oracle SQL Scenario Questions – Part 1

Oracle SQL Scenario Questions – Part 1

Folks,

In this blog we will explore some latest Oracle SQL scenario questions for interview. Here we will cover topics like regex, pivot, binary tree and other Oops related PL/SQL questions.

For more question visit


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

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.


PL/SQL Scenario Questions

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;

proc

Question 13: 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;

1

2

Question 14: 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;
/

func

For more scenarios question visit:

Oracle SQL – Scenario Questions Part 2

Oracle SQL – Scenario Questions – Part 3


If you have optimized answers for these scenarios, then please comment.

Thanks! Happy Learning! Your feedback would be appreciated!