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.
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!!
Very useful!! Only thing i didnt get is trunc(l/m) , could you please explain ?
ReplyDeleteA good programmer rarely leaves junk code. They are competent in judging whether a piece of code is useless or not. If this piece of code is useful, best monitor for software developers they will keep it. If it is useless, they will get rid of it.
ReplyDeletecontdeuPdia_ru Tim Sitton link
ReplyDeletegaregali
kayseri evden eve nakliyat
ReplyDeleteaydın evden eve nakliyat
kütahya evden eve nakliyat
gümüşhane evden eve nakliyat
balıkesir evden eve nakliyat
VZJ
hatay evden eve nakliyat
ReplyDeleteısparta evden eve nakliyat
erzincan evden eve nakliyat
muÄŸla evden eve nakliyat
karaman evden eve nakliyat
FVZ
urfa evden eve nakliyat
ReplyDeletemalatya evden eve nakliyat
burdur evden eve nakliyat
kırıkkale evden eve nakliyat
kars evden eve nakliyat
ZO2
düzce evden eve nakliyat
ReplyDeletedenizli evden eve nakliyat
kırşehir evden eve nakliyat
çorum evden eve nakliyat
afyon evden eve nakliyat
D1Y6
5B5FC
ReplyDeleteMalatya Şehirler Arası Nakliyat
Adana Lojistik
Okex Güvenilir mi
MuÅŸ Evden Eve Nakliyat
Niğde Şehirler Arası Nakliyat
Siirt Parça Eşya Taşıma
Bilecik Şehirler Arası Nakliyat
Aksaray Evden Eve Nakliyat
Ünye Çatı Ustası
B37AA
ReplyDeleteÃœnye Koltuk Kaplama
Yalova Evden Eve Nakliyat
Çerkezköy Oto Lastik
Karabük Parça Eşya Taşıma
Gümüşhane Lojistik
Diyarbakır Lojistik
Çanakkale Evden Eve Nakliyat
Burdur Parça Eşya Taşıma
Kastamonu Şehir İçi Nakliyat
FE965
ReplyDeleteKocaeli Evden Eve Nakliyat
buy testosterone propionat
Mamak Boya Ustası
Uşak Parça Eşya Taşıma
Isparta Lojistik
sms onay
Kırklareli Parça Eşya Taşıma
Isparta Parça Eşya Taşıma
steroids for sale
97907
ReplyDeleteMuÄŸla Evden Eve Nakliyat
Denizli Evden Eve Nakliyat
Eryaman Parke Ustası
Pursaklar Fayans Ustası
Gate io Güvenilir mi
Ãœnye Petek Temizleme
Eryaman Alkollü Mekanlar
Çerkezköy Çatı Ustası
Afyon Evden Eve Nakliyat
D3C31
ReplyDeleteÇankaya Parke Ustası
Ünye Halı Yıkama
Mexc Güvenilir mi
Coinex Güvenilir mi
Karapürçek Fayans Ustası
Trabzon Evden Eve Nakliyat
Çerkezköy Fayans Ustası
Kırıkkale Evden Eve Nakliyat
Ãœnye Petek Temizleme
84063
ReplyDeletebinance referans kodu
binance referans kodu
binance referans kodu
binance referans kodu
referans kimliÄŸi nedir
resimli magnet
resimli magnet
resimli magnet
referans kimliÄŸi nedir