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.

Separate the words (SQL Puzzle 15)

Problem: Given any sentence, break it into separate words. (The words are separated by spaces).

Solution:
select doc.EXTRACT('/l/text()').getstringVal()
from
(select 'I am a bad man' given from dual ) T ,
TABLE(xmlSequence(EXTRACT(XMLTYPE(''REPLACE(T.given,' ','')''),'/doc/l'))) doc

('I am a bad man' is an example)

Order the Names (SQL Puzzle 14)

Problem: A table 'NAMES' has a column 'NAME'. The create and insert scripts are as follows:

CREATE TABLE NAMES
(
NAME VARCHAR2(25)
)

Insert into NAMES (NAME) Values ('DON ');
Insert into NAMES (NAME) Values ('EAGER ');
Insert into NAMES (NAME) Values ('BJ ');
Insert into NAMES (NAME) Values ('BJ ');
Insert into NAMES (NAME) Values ('BJ ');
Insert into NAMES (NAME) Values ('DON ');
Insert into NAMES (NAME) Values ('EAGER ');
Insert into NAMES (NAME) Values ('Gaurav ');
Insert into NAMES (NAME) Values ('Gaurav ');
Insert into NAMES (NAME) Values ('Gaurav ');
Insert into NAMES (NAME) Values ('Nishi ');
Insert into NAMES (NAME) Values ('Gaurav ');
Insert into NAMES (NAME) Values ('Nishi ');
Insert into NAMES (NAME) Values ('XYZ');
Insert into NAMES (NAME) Values ('ABC');
Insert into NAMES (NAME) Values ('Sanjeev');
Insert into NAMES (NAME) Values ('Sanjeev');
Insert into NAMES (NAME) Values ('Sanjeev');
Insert into NAMES (NAME) Values ('PQR');
COMMIT;

Write an SQL to fetch the names in the same order but changed as follows: If a name is unique in the given list, leave the name unchanged. Otherwise, add a single space followed by the names chronological number among all same names. For example if there are 2 Sanjeev's return Sanjeev1 Sanjeev 2. If there is single 'Gaurav', return only Gaurav. The order of the names should be the same as initially given.
In the given case, the result should be:

RESULT
=========
DON 1
EAGER 1
BJ 1
BJ 2
BJ 3
DON 2
EAGER 2
Gaurav 1
Gaurav 2
Gaurav 4
Nishi 1
Gaurav 3
Nishi 2
XYZ
ABC
Sanjeev 1
Sanjeev 2
Sanjeev 3
PQR

Solution:


select
decode(cn,1,name,name' 'row_number() over(partition by name order by name)) result
from
(
select
rn,
name,
count(name) over(partition by name order by name) cn
from
(
select name,rownum rn from names
)
)
order by rn

Binary to Decimal (SQL Puzzle 13)

Problem: Write an SQL query to convert a binary number to its Decimal equivalent.
Solution:
select sum(substr(&binary_number,rownum,1)*power(2,length(&binary_number)-rownum)) decimal_number from all_objects
where rownum<=length(&binary_number)

Decimal to Binary (SQL Puzzle 12)

Problem: Write an SQL query to convert a given decimal number to its binary equivqlent.
Solution:
select
reverse(max(replace(sys_connect_by_path(sign(bitand(&num,power(2,level-1))),','),','))) from dual
connect by power(2,level-1)<=&num

Concatenate the rows (SQL Puzzle 11)

Problem: There is a table with one column. Concatenate the rows of this column.
create table characters(ch varchar2(1));
insert into characters values ('A');
insert into characters values ('B');
insert into characters values ('C');
insert into characters values ('D');
Solution:

select concatenated
from
(
select concatenated
from
(
select replace(sys_connect_by_path (ch,' '),' ','') concatenated
from
(
select ch,rownum rn from characters
)
CONNECT BY PRIOR rn = rn -1
)
order by length(concatenated) desc
)
where rownum=1