Number of Centuries

Question: You have 2 tables as follows:

create table table1(player varchar2(10), ground_name varchar2(10), num_centuries number);

create table table2(ground_name varchar2(10), country varchar2(10));

Write a SQL to list the player names who have made centuries in every country

Solution:

select p.player

from

(select count(distinct country) cnt from table2) g,

(select player,count(distinct table2.country) cnt from table1,table2 where num_centuries>0

 and table1.ground_name=table2.ground_name

group by player) p

where g.cnt=p.cnt

Exploding Rows........

SQL Puzzle:


You have a table with 2 columns: literal and num with data as shown below:


Literal     NUM
 a                 4
 b                 3
 c                 2


Write a query that returns the output as:


Literal
----------
   a
   a
   a
   a
   b
   b
   b
   c
   c


The NUM represents the number of times the literal has to be repeated in the output




Solution:


The query will be as follows:


select a.literal
from
(select literal, num from explode) a, (select rownum rn from user_objects) b
where b.rn<=a.num

Divisions

A table contains certain columns. One of the columns is Division. The user enters a division name through a prompt. Write a query to satisfy below scenario:

If the value entered in prompt is 'a' or 'b', then all records should be displayed else, the records pertaining to that particular division should be displayed.

Solution:

The query would be written as follows:

select * from my_table
where
division = &division
OR
'^' = decode(&division,'a','^','b','^',&division)

Here any special character (like '^' in above query) can be choosed to give all results in case division value entered from prompt is either 'a' or 'b'.

'LIKE' does'nt like UNDERSCORE

Write a query to return those values from an Oracle table where the column myword contains an UNDERSCORE ( _ ) sign in its values. e.g if the column 'myword' contains following values:

myword

----------

FAC_FAC

FACFAC

_FACE

FACE

FACE_

The output should be:

myword

----------

FAC_FAC

_FACE

FACE_

Solution:

Generally, most people will write the query as follows:

select myword from mytable where myword like '%_%'.

However this query will not give expected results. It will return all the values of column myword.

The correct query will be as follows:

select myword from mytable where myword like '%\_%' escape '\'

We will have to escape the '_' character to exactly match it.

Generate a Pyramid

You have table pyramid_table with 2 columns CH and NUM. It contains 1 row. The column CH contains a single character and column NUM contains a number.

Write a SQL query to Generate a pyramid of CH characters of height NUM e.g if CH is '*' and NUM is 5, the output should be :

       *
     ***
   *****
 *******
*********

Solution:

SELECT
lpad(' ',num-rownum,' ')||substr(lpad(ch,2*num,ch),1,2*rownum-1)||lpad(' ',num-rownum,' ') as pyramid
from
pyramid_table,all_objects
where
rownum<=num

Count of a Particular Character in a string

Write a query to count a particular character in a string. e.g Return the number of times 'N' appears in string 'NITININ'. The result shoudl be 3.

Solution:

select length('NITININ') - length(replace('NITININ','N','')) from dual

Count of Individual Characters in a String

Write a query to display the count of individual characters in a given string. e.g if the given string is "mesopotamia" , the output should be :

m  2
e  1
i  1
s  1
o 2
p 1
t  1
a  2

Solution:

select ind, count(ind)
from
(
select substr(given_string,rownum,1) ind
from
(
select 'mesopotamia' given_string from all_objects
)
where
rownum<=length(given_string)
)
group by ind

Concatenate the rows

You have table named SHOW_TABLE having 2 columns SHOW_ID and EMP_NAME having data as follows:

SHOW_ID           EMP_NAME
   1                      Gaurav
   2                      Kalpana
   1                      Ashok
   2                      Manish

Write a query to return data as follows:

SHOW_ID                EMP_NAME
     1                     Gaurav,Ashok
     2                     Kalpana, Manish


Solution:

select show_id,rn, ltrim(sys_connect_by_path (emp_name,','),',') concatenated
from
(
select
show_id,
emp_name,
row_number() over (partition by show_id order by show_id )rn,
count(*) over (partition by show_id) cnt from
(
select distinct show_id, emp_name from show_table
))
where
rn=cnt
start with rn=1
CONNECT BY prior show_id=show_id and PRIOR rn = rn -1
order by show_id,length(concatenated) desc

Query to retreive every Nth row from an Oracle table

The query is as follows:

select * from my_table where (rowid,0) in (select rowid, mod(rownum,n) from my_table )

Query to retrieve Nth row from an Oracle table

The query is as follows:

select * from my_table where rownum <= n
MINUS
select * from my_table where rownum < n

Updation of a table by the values from a different table

Scenario: There are 2 oracle tables TEST1 and TEST2 containing 2 columns each : EMP_ID and EMAIL_ID. How will you update column EMAIL_ID of table TEST1 with the values of EMAIL_ID of table TEST2. The join condition to be used will be on EMP_ID.

Solution:

update test1 set test1.email_id = (select test2.email_id from test2 where test1.emp_id=test2.emp_id)
where
exists (select 1 from test2 where test1.emp_id=test2.emp_id)

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
)

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