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

---

Thanks!

Happy Learning! Your feedback would be appreciated!

Follow @shobhitsinghIN

Oracle SQL – Scenario Questions (Interview)

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!

Follow @shobhitsinghIN