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 Data Integrator 11g (ODI) Repositories | Master & Work Repository | Connection & Setup

Folks,

In this blog we will learn about Oracle Data Integrator Repositories.

You can refer this blog for ODI 11g Installation & Setup on Windows OS.


Suppose you are just done with the ODI installation on your machine:

Now you are going to use the ODI Studio to develops, operate & administrate some ELT project work. But you need some space/location to start working & to save your all work. Basically you need ODI repository to store the information you are going to develop, configure or used by the ODI.

There are two types of ODI repositories: Master & Work Repository

  • Objects developed, configured, operated/used by the ODI stored in one of these two types of repositories.
  • Repositories are accessed in client/server mode by various components of the ODI architecture.
  • Master Repository is usually associated with multiple Work Repositories.
  • Each Work repository can associate with only one Master Repository.
  • Work repository can either be Development Work Repository or Execution Work repository. (We will discuss this point later in this blog)

repo1

Refer this YouTube video to setup Master & Work Repositories in ODI 11g using Oracle.


Master Repository:

Master Repositories are used to store:

  • ODI Connection Information: JDBC URLs, LDAP information, user names and passwords for source/target connections.
  • ODI Security Information: ODI users names and passwords, ODI users privileges and profiles information, if security is handled by ODI.
  • Version Information: When a new version of an object is created in the ODI, that information is saved in the Master Repository.

Information contained in the Master Repository is maintained with the Topology Navigator and the Security Navigator in ODI Studio.

  • Data contained in master repository is mostly static data and will be going through minimum changes, whenever any topology or security information added/updated.
  • ODI components access the Master Repository, whenever they need the topology and security information data stored in it.

Work Repository:

Work Repositories are used to store:

 

  • Project Information: Folders/Sub-folders, packages, interface, procedure, variables, sequences, functions,  knowledge module etc.
  • Models: Data Store structures and metadata, fields, columns, constraints etc.
  • Load Plans & Scenarios: Load plans information & scenarios.
  • Operations:  Execution details, scheduling information, and logs.

The contents of a Work Repository are managed by using Designer and Operator. They are also accessed by the agent at run time.

When a Work Repository is used only to store execution, operations logs information (typically for production environment), it is called an execution work repository. Designer Navigator is disabled in this execution repository.

Work repository consists of two sub-types: Development Work RepositoryExecution Work repository.

Development Work Repository:

  • Repository which contains all the objects which are used for developing the development project i.e. packages, interface, procedure, variables, sequences, load plans, data stores etc.
  • Designer Navigator & Operator Navigator both are available for Development Work Repository.
  • Usually, used for development projects.

2.PNG

Execution Work Repository:

  • Repository which stores only the scenarios (executable files) & execution information, no source code & designer information available here.
  • Usually, used for production and test environments to make sure that source code will not be modified in a live environment.
  • Operator Navigator is available, but Designer Navigator is restricted for Execution Work Repository.

3

Checkout this blog for understanding of relationship between ODI Master & Work Repository.

Refer this YouTube video to Install ODI 11g on Windows OS.


Thanks!

Happy Learning! Your feedback would be appreciated!

 

 

Inner Join: SAS Merge & Proc SQL

Folks,

In this blog we will explore the basic concept of Inner Join using SAS Merge & Proc SQL.


An inner join retrieve only the matched rows from the data-sets/tables.

Suppose we have two data-sets/tables Customer & Sales.

So an inner join of Customer and Sales gives the result of Customer intersect Sales, i.e. the inner part of a Venn diagram intersection. (See below image)

innerjoinn.png

In SQL terminology, this is inner join. Equivalent to a merge in a DATA step in SAS.

To achieve the inner join using SAS merge, first we have to understand the concept of IN= option, which is especially useful when merging and concatenating data sets.

IN= option

IN= option tells SAS to create an “indicator variable” having the value 0 or 1 depending on whether or not the current observation comes from the input data set.

  • If the observation does come from the input data-set, then the indicator will be 1.
  • If the observation does not come from the input data set, then the indicator variable value will be 0.

Lets have a example – Here is the Data-sets Customer & Sales.

inner

DATA TEMP;
MERGE CUSTOMER (IN=IN_CUSTOMER ) SALES(IN=IN_SALES);
BY CUSTOMER_ID ;

/* Creating new variables using indicator variables */
CUSTOMER = IN_CUSTOMER ;
SALES = IN_SALES;

RUN;
Proc print data=temp;

See below output. Indicator Sales= 1 When observation coming from the input data-set Sales  else indicator is 0, same applied for customer data set.

full

 

SAS Merge (Inner Join):

Lets go for SAS Merge (Inner Join) using IN= Options.

Prerequisites for a SAS Merge

  • Input data-sets must have at least one common variable to merge with same name (In our case we have CUSTOMER_ID). If not same then use rename.
  • Input data sets must be sorted by the common variable(s) that will be used to merge.

Let see how we can use IN= options to get the common data using Merge.

Select only those observation where IN variables Sales=1 & Customer=1 i.e. common observation from both datasets.joins.png

IN VAR

SAS Merge (Inner Join) –

DATA INNER_JOIN;
MERGE CUSTOMER (IN=IN_CUSTOMER ) SALES(IN=IN_SALES);
BY CUSTOMER_ID ;
IF IN_CUSTOMER = 1 & IN_SALES = 1;
RUN;

Output

output inner join

Inner Join using Proc SQL in SAS:

  • Joining is possible on columns with differing names. (No need to rename)
  • Input data sets doesn’t required sorting by the common variable(s)

1st way using Proc SQL-

 PROC SQL;
 SELECT C.CUSTOMER_ID,C.COUNTRY, S.SALES FROM 
 CUSTOMER AS C, SALES AS S
 WHERE C.CUSTOMER_ID=S.CUSTOMER_ID;
 QUIT;

2nd way using Proc SQl-

PROC SQL;
 SELECT C.CUSTOMER_ID,C.COUNTRY, S.SALES FROM 
 CUSTOMER AS C JOIN SALES AS S
 ON C.CUSTOMER_ID=S.CUSTOMER_ID;
 QUIT;

Proc SQL Output

proc sql


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Database 11g Installation & Setup| Windows OS

Oracle Database 11g  Installation & Setup| Windows OS

Folks,

In this vlog we will learn how to download, install & setup Oracle Database 11g on Windows 10 OS.

Step by step installation guide:

You can download the Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (x64) using this Link

Refer this blog setup your first schema/user: Oracle Database 11g | Create User | Check Instance Name| Host Name | Services | Parameters |


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!