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)