Hey guys, Hope you all are doing great. I am back with a new domain SQL. So the problem goes like, you need to print prime numbers less than 1000.

Seems easy right!! Just use a for loop check for the list of natural numbers for prime condition whether they are only divisible by 1 and itself.

But if you are asked to do it only using plain SQL, i will be using Oracle in my case, how would you do that.

In PL/SQL its possible with straight approach, but in SQL its bit tricky.Lets start....

Write a query to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the (,) character as your separator (instead of a space).
For example, the output for all prime numbers  would be:
2,3,5,7

Algorithm:


1. First we will generate the numbers from 1 to 1000. This can be achieved by using level connect in SQL i.e. Hierarchical SQL. A condition that identifies the relationship between parent rows and child rows of the hierarchy. So we will get number 1, 2, 3.....1000.
This is how it will look on Oracle SQL format....

select level l from dual connect by level  <= 1000



2. Now we can use above query to generate another set of numbers and proceed with our logic.
So for checking the prime condition there should be a number which is when divided return 0 as remainder only 2 times, that is divided by 1 and divided by num (number itself).

So we have a condition a SQL where we have Count = 2


This is how we can implement it:



select l prime_number
from (select level l from dual connect by level <= 1000)
, (select level m from dual connect by level < = 1000)
where m<=l
group by l
having count(case l/m when trunc(l/m) then 'Y' end) = 2;

Output of above looks like:

PRIME_NUMBER
43
83
151
167
173
179
227
269

 

3. Now for the final step we need to connect the output is separate lines to single line output separated by comma (,). For this purpose we will use LISTAGG function in SQL

Program/Script:
So our final script looks like...

SELECT LISTAGG(prime_number,',') WITHIN GROUP (ORDER BY prime_number) AS NUMBERS
FROM(    
    select l prime_number
from (select level l from dual connect by level <= 1000)
, (select level m from dual connect by level <= 1000)
where m<=l
group by l
having count(case l/m when trunc(l/m) then 'Y' end) = 2);

Output will be


2,3,5,7,11,13,17,19,23,29,31,37,41,43,47,53,59,61,67,71,73,79,83,89,97,101,103,107,109,113,127,131,137,139,149,151,157,163,167,173,179,181,191,193,197,199,211,223,227,229,233,239,241,251,257,263,269,271,277,281,283,293,307,311,313,317,331,337,347,349,353,359,367,373,379,383,389,397,401,409,419,421,431,433,439,443,449,457,461,463,467,479,487,491,499,503,509,521,523,541,547,557,563,569,571,577,587,593,599,601,607,613,617,619,631,641,643,647,653,659,661,673,677,683,691,701,709,719,727,733,739,743,751,757,761,769,773,787,797,809,811,821,823,827,829,839,853,857,859,863,877,881,883,887,907,911,919,929,937,941,947,953,967,971,977,983,991,997 

Hope you guys find my post useful, let me know how i can improve....
Till then. Take care!!




13

View comments

Popular Posts
Popular Posts
print ("About Me")
print ("About Me")
My Photo
HYDERABAD, Telangana, India
Blog.History(All)
Labels
Total Pageviews
Total Pageviews
21466
About Me
About Me
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.