Multiply the strings! (SQL Puzzle 20)

Problem:
The result of the multiplication of a given string by a given number is defined as follows:
1. A string multiplied by 0 is empty.
2. A string multiplied by a positive number x , is concatenation of string x number of times:
e.g Gaurav multiplied by 2 results GauravGaurav
3. A string multiplied by a negative number x , is concatenation of reverse og string x number of times:
e.g Gaurav multiplied by -2 results varuaGvaruaG

Write an SQL query to achieve this.

Solution:

select final_word
from
(
select
case when given_number>0 then replace(sys_connect_by_path(given_word,'\'),'\','') else reverse(replace(sys_connect_by_path(given_word,'\'),'\','')) end final_word,
given_word,
given_number
from
(
select
given_word,
given_number,
rownum rn
from
(
select 'gaurav' given_word, -2 given_number from all_objects
)
where
rownum<=abs(given_number)
)
connect by rn=rn
and rownum<=rn
)
where length(final_word)=length(given_word)*abs(given_number)

Change Case (SQL Puzzle 19)

Problem:
The values in a column "Location" of your table are as follows:
Location
---------------
asw-qwer-sdf
bnm-sdr
cbn-hyt-opu

Write a query to retun the values as :
Location
-------------
aswQwerSdf
bnmSdr
cbnHytOpu

Solution:

select
location,
substr(location,1,instr(location,'-',1,1)-1)^^replace(initcap(substr(location,instr(location,'-',1,1)+1)),'-','')
from
sales

* replace ^^ by pipes...due to some printing issues pipes are not being displayed...

Count the Characters (SQL Puzzle 18)

Problem:
Given a string e.g 'gaurav goel' , return the individual character of the string along with its count in the whole string, with order kept intact.
The result should be:
g 2
a 2
u 1
r 1
v 1
NULL 1
0 1
e 1
l 1

Solution:
select ind,cn
from
(
select ind,cn,row_number() over (partition by ind order by ind) rownumber from
(
select
substr(given_input,rownum,1) ind,
rownum rn,
count(substr(given_input,rownum,1)) over (partition by substr(given_input,rownum,1)order by substr(given_input,rownum,1)) cn
from
(
select 'gaurav goel' given_input from all_objects
)
where
rownum<=length(given_input)
)
order by rn
)
where
rownumber=1

Club the columns!!! (SQL Puzzle 17)

You have 2 columns in a table :

create table tab_sub_code (Subject_Codes char(3), subject_name varchar2(30));
with data as.....
insert into tab_sub_code (subject_codes, subject_name) values ('001', 'sub_1Name');
insert into tab_sub_code (subject_codes, subject_name) values ('002', 'sub_2Name');
insert into tab_sub_code (subject_codes, subject_name) values ('003', 'sub_3Name');
insert into tab_sub_code (subject_codes, subject_name) values ('004', 'sub_4Name');
insert into tab_sub_code (subject_codes, subject_name) values ('005', 'sub_5Name');
insert into tab_sub_code (subject_codes, subject_name) values ('006', 'sub_6Name');
insert into tab_sub_code (subject_codes, subject_name) values ('007', 'sub_7Name');
insert into tab_sub_code (subject_codes, subject_name) values ('008', 'sub_8Name');
Problem:
Write an SQL to fetch both columns in 1 column with values placed alternatively and separated by a NULL. The result should look like:
001
sub_1Name
NULL
002
sub_2Name
NULL
003
sub_3Name
NULL
.
.
.
.
sub_8Name
NULL

Solution:
select sn single_column
from
(
select subject_name sn,rn
from
(
select subject_codes,subject_name, rownum rn from tab_sub_code
)
union
select subject_codes sn,rn
from
(
select subject_codes,subject_name, rownum rn from tab_sub_code
)
union all
(
select NULL sn,rownum rn from tab_sub_code
)
order by rn,sn
)

Split the String (SQL Puzzle 16)

Problem:
Given a string 'a,bb,ccc,dddd,ee' . Split it on the basis of commas by an SQL. The query should return :
a
bb
ccc
dddd
ee

Solution:

select
given_input,
substr(given_input,start_index+1,(end_index-start_index)-1)
from
(
select
given_input,
(case when rownum=1 then 0 else instr(given_input,',',1,rownum-1) end) start_index,
(case when instr(given_input,',',1,rownum)=0 then length(given_input)+1 else instr(given_input,',',1,rownum) end) end_index
from
(
select 'a,bb,ccc,dddd,eeeee' given_input from all_objects
)
where
rownum<=(length(given_input)- length(replace(given_input,',','')))+1
)

The basics of "ROWNUM"

ROWNUM is a function in Oracle, that returns a number for each row returned by a query. It returns an increasing value starting from 1.

For example, if we write :

select * from emp where rownum<=3. The oracle will do the following things:

1. Oracle executes the query and fetches the first row. It assigns it number 1.
2. Oracle checks whether it has reached to number 3. If no, it returns that row(since the rownum is less than 3) and if yes, it does not return the row.
3. Oracle then, fetches the next row and advances the rownum.
4. Oracle goes to step 2.

It is to be noted that Oracle's ROWNUM are assigned after each row is fetched.

If a user writes :

select * from emp where rownum=3.
No rows will be returned. The reason is that after fetching the first row, oracle will assign it number 1. It now checks whether the number is 3. Since its not, it will discard the row. It will then fetch next row. It will again assign it number 1 but this time also it will not return any row.
It will , hence, not return any row since the number will never be 3 in this case.

However, the query 'select * from emp where rownum=1', will return the first row since after fetching the first row, oracle assigns number 1 to that row which matches the condition.

This clarifies that the equality operator is not generally used with rownum.