Retrieve first 100 prime numbers using SQL Query

On an interview , one of our colleague asked participant the following question:

“How can you generate prime numbers using just query?”

Answering to this question using PL/SQL is simple using loop. But using SELECT 🙂 not so simple 🙂

I found the answer and want to share with you.

select l prime_number
from (select level l from dual connect by level <= 100)
, (select level m from dual connect by level <= 100)
where m<=l
group by l
having count(case l/m when trunc(l/m) then ‘Y’ end) = 2
order by l