But pls, can u explain me clearly step-by-step , as I am new to SQL
1 select l prime_number
2 from (select level l from dual connect by level <= 100)
3 , (select level m from dual connect by level <= 100)
4 where m<=l
5 group by l
6 having count(case l/m when trunc(l/m) then 'Y' end) = 2
7 order by l
Sure, line 2 and 3 are both generating the numbers 1 until 100. Without a where clause they would give 10,000 rows, having all combinations of two numbers between 1 and 100.
The next step is to filter out the rows where m>l. After this step you have the pairs (1,1), (2,1), (2,2), (3,1), (3,2), (3,3), (4,1) et cetera. You will have 5,050 rows left at this stage.
Line 5 is grouping by the l column, to have 100 records again. The prime number check is in the having clause at line 6. You divide the l number by the m number and only when it can be divided ( l/m = trunc(l/m) ), you are counting. Prime numbers are the ones that have two m numbers (1 and the number l itself), so only those are displayed.
Hope this helps.
Regards,
Rob.