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,7Algorithm:
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.
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 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!!
Till then. Take care!!
View comments