Search code examples
sqloracle-databaseprimes

How to print prime numbers within given range | Oracle 19c |

I have been trying to write a logic to print all prime numbers.

And it works as expected till range of 1 to 100.

But when I increase the range it dose not work from 1 to 500

Can some explain in details what is wrong in my below code and how it can be improved.

Any easy or different way to print prime numbers will be much appreciated

Code :

SELECT
    result
FROM
    (
        SELECT
            CASE
                WHEN ROWNUM = 2 THEN
                    ROWNUM
                WHEN ROWNUM = 3 THEN
                    ROWNUM
                WHEN ROWNUM = 5 THEN
                    ROWNUM
            END AS result
        FROM
            dual
        CONNECT BY
            ROWNUM <= 5
    )
WHERE
    result IS NOT NULL
UNION ALL
SELECT
    result
FROM
    (
        SELECT
            decr1 AS result
        FROM
            (
                SELECT
                    rn AS decr1
                FROM
                    (
                        SELECT
                            ROWNUM AS rn
                        FROM
                            dual
                        CONNECT BY
                            ROWNUM < 1000
                    )
            )
        WHERE
            decr1 > 5
        MINUS
        SELECT
            decr AS result
        FROM
            (
                SELECT
                    t.rn AS decr
                FROM
                    (
                        SELECT
                            ROWNUM AS rn
                        FROM
                            dual
                        CONNECT BY
                            ROWNUM < 1000
                    ) t
                WHERE
                    rn >= 6
            )
        WHERE
            mod(decr, 2) = 0
            OR mod(decr, 3) = 0
            OR mod(decr, 4) = 0
            OR mod(decr, 5) = 0
            OR mod(decr, 6) = 0
            OR mod(decr, 7) = 0
            OR mod(decr, 8) = 0
            OR mod(decr, 9) = 0
            OR mod(decr, 10) = 0
    );

My output for range of 1 to 100

output

Solution

  • You've thrown out processing efficiency when you decided to do this in SQL, so you might as well go for something that is clear. Here is a SQL that is pretty much straight along the lines of the definition of a prime number:

    with nat as (SELECT rownum n FROM DUAL CONNECT BY ROWNUM <= 100 )
SELECT n   -- give me any number...
from   nat n1  -- ... from the set of natural numbers ...
where   not exists ( SELECT n FROM nat n2 where mod(n1.n,n2.n)=0 AND n2.n between 2 and n1.n-1)  -- ... that cannot be evenly divided by a smaller natural number
and n > 1  -- ... and 1 is not prime