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!
Follow @shobhitsinghIN
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
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.
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:
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
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 ;
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');
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 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 ;
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
SELECT '2 * ' || rownum || ' = ' || rownum *2 as t FROM DUAL CONNECT BY rownum <= 10
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
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 –
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;
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');
-- 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)
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;
Input:- See binary tree below
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.
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.
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 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 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;
/
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!
Follow @shobhitsinghIN