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

Esperanto Spelling(SQL Puzzle 10)

Problem: In Esperanto language, numbers are represented as "unu" for 1, "du" for 2, "tri" for 3, "kvar" for 4, "kvin" for 5, "ses" for 6, "sep" for 7, "ok" for 8, "nau" for 9, "dek" for 10. Numbers 11 to 19 are represented as "dek unu", "dek du","dek tri"......up to "dek nau". Numbers 20 to 29 are "dudek","dudek unu", "dudek tri".....up to "dudek nau". Similarly for all other numbers from 30 to 99 where 30 is "tridek", 40 is "kavrdek".....so on.. We have a table "esperanto_spell" having 2 columns as:

CREATE TABLE ESPERANTO_SPELL
(
NUMBERS NUMBER,
SPELL VARCHAR2(10 BYTE)
)

with values as:
Insert into ESPERANTO_SPELL (NUMBERS, SPELL) Values (1, 'unu');
Insert into ESPERANTO_SPELL (NUMBERS, SPELL) Values (2, 'du');
Insert into ESPERANTO_SPELL (NUMBERS, SPELL) Values (3, 'tri');
Insert into ESPERANTO_SPELL (NUMBERS, SPELL) Values (4, 'kvar');
Insert into ESPERANTO_SPELL (NUMBERS, SPELL) Values (5, 'kvin');
Insert into ESPERANTO_SPELL (NUMBERS, SPELL) Values (6, 'ses');
Insert into ESPERANTO_SPELL (NUMBERS, SPELL) Values (7, 'sep');
Insert into ESPERANTO_SPELL (NUMBERS, SPELL) Values (8, 'ok');
Insert into ESPERANTO_SPELL (NUMBERS, SPELL) Values (9, 'nau');
Insert into ESPERANTO_SPELL (NUMBERS, SPELL) Values (10, 'dek');

Write an SQL query to return the Esperanto Spelling of a given number.
(The given number will be between 1 and 99).

Solution :


select
(select
decode(spell^^'dek ','unudek ','dek ',spell^^'dek ')
from esperanto_spell
where numbers=trunc(&given_number/10) )^^(select spell from esperanto_spell where numbers=mod(&given_number,10)) as converted
from dual

(^^ represents concatenation operator i.e pipes. Due to some print format the pipes are not being reflected. Replace ^^ with pipes while running)

Palindromes (SQL Puzzle 9)

Problem: Given a string, find whether its a palindrome or not by SQL query.
Solution:
select decode(count(distinct ascii(straight.ch)-ascii(revers.ch)),1,'YES','NO') is_palindrome
from
(select ch,rownum rownumber
from
(
select substr(name,level,1) ch,rownum rn from
(
select &palindrome name from dual
)
connect by prior name=name
and level<=length(name)
and prior dbms_random.string('p',10) is not null
)) straight,
(select ch,rownum rownumber
from
(
select ch
from
(
select substr(name,level,1) ch,rownum rn from
(
select &palindrome name from dual
)
connect by prior name=name
and level<=length(name)
and prior dbms_random.string('p',10) is not null
)
order by rn desc
)
) revers
where straight.rownumber=revers.rownumber

Break the values of a column into individual characters (SQL Puzzle 8)

Problem: A column 'Names' contains following values:
'Nitin'
'Ashok'
'Sanjeev'
Write a SQL to break these values into individual characters.

Solution:
select names,
substr(names,level,1) ch
from
(
select 'Nitin' names from dual
union all
select 'Ashok' names from dual
union all
select 'Sanjeev' names from dual
)
connect by prior names=names
and level<=length(names)
AND prior DBMS_RANDOM.STRING ('p',10) IS NOT NULL

Write Your Number (SQL Puzzle 7)

Problem: There are few sets of digits. Each set contains exactly ten digits - 0 to 9. You are given a number. Write an SQL logic to return the number of sets to write the given number. Note that 6 can be used as 9 and 9 can be used as 6.
e.g if the given number is 1234 , the answer will be 1 since we require 1 set to select each digit.
if the given number is 4444, the answer will be 4 since we require 4 sets to select each digit.
if the given number is 255, the answer will be 2.
if the given number is 6666, the answer will be 2 (Each set contains one 6 and one 9 and hence 2 sets are enough).

Solution:
select count(rn)
from
(
select distinct row_number() over (partition by room_no2 order by room_no2) rn from
(
select (case when room_no=9 and mod(rn,2)=0 then '6' else room_no end) room_no2
from
(
select substr(&room_no,rownum,1) room_no,row_number() over (partition by substr(&room_no,rownum,1) order by substr(&room_no,rownum,1)) rn
from all_objects
where
rownum<=length(&room_no)
)
)
)

Sort the String (SQL Puzzle 6)

Problem: You are given a string say 'dgfgkanb'. Give a SQL or PL/SQL logic to sort it.

PL/SQL Solution:
create a function as follows:
=============================================
create or replace function sort_string(original_string varchar2)
return varchar2 is
sorted_string varchar2(20):='';
cursor individual_string is
select ind from
(
select substr(original_string,rownum,1) ind from all_objects
where
rownum<=length(original_string) order by substr(original_string,rownum,1) );
begin
for rec2 in individual_string loop
sorted_string := sorted_string (concat) rec2.ind;
end loop;
return sorted_string;
commit;
end;
=============================
select sort_string('dgfgkanb') from dual
result : abdfggkn

I am not able to write a single SQL query to do this. Any help will be greatly appreciated....

A Perfect String (SQL Puzzle 5)

Problem : A 'perfect' string is a string where the index of the first occurence of each letter is equal to the number of occurences of that letter in the string. For example 'ABB' is perfect since letter 'A' occurs at position 1 and number of 'As' in string is also 1. Letter 'B' occurs at position 2 and total number of Bs is also 2. Similary string 'ABCBCC' is also perfect but string 'ABDDD' is not perfect.
Given a string, write an SQL query to determine whether its perfect or not.

One of the solutions:


select
decode(count (distinct (case when rn=count_individual then 'Perfect' else 'Not Perfect' end)),1,max((case when rn=count_individual then 'Perfect' else 'Not Perfect' end)),'Not Perfect') result from
(
select
rownum rn,
substr(&mystring,rownum,1) individual,
count(substr(&mystring,rownum,1)) over (partition by substr(&mystring,rownum,1)) count_individual ,
row_number() over (partition by substr(&mystring,rownum,1) order by substr(&mystring,rownum,1) ) row_number
from
(
select &mystring from all_objects
)
where
rownum<=length(&mystring)
)
where row_number=1

Divide the string into individual Characters (SQL Puzzle 4)

Problem : Divide a given string into individual characters and display these characters into separate rows through a SQL query. For example , the given string is 'Paper', display it as :

Given_String
--------
P
a
p
e
r

The query is :
select substr(&string,rownum,1) given_string
from all_objects
where rownum<=length(&string)