Hacker rank | SQL(Advanced)| Print Prime Numbers | By Kiruthicka GP

Problem

Write a query to print all prime numbers less than or equal to 1,000. Print your result on a single line, and use the ampersand (&) character as your separator (instead of a space).

Solution

WITH RECURSIVE Numbers AS (
    SELECT 2 AS L 
    UNION
    SELECT L + 1
    FROM Numbers
    WHERE L < 1000
)

SELECT GROUP_CONCAT(L SEPARATOR '&') AS PrimeNumbers
FROM Numbers
WHERE NOT EXISTS (
    SELECT 1
    FROM Numbers AS N2
    WHERE N2.L > 1 AND N2.L < Numbers.L AND Numbers.L % N2.L = 0
) AND L <= 1000;

Explantion of the Syntax

1.Recursive CTE Definition:

WITH RECURSIVE Numbers AS (
    SELECT 2 AS L 
    UNION
    SELECT L + 1
    FROM Numbers
    WHERE L < 1000
)

• WITH RECURSIVE Numbers: This declares a recursive common table expression named "Numbers."
• SELECT 2 AS L UNION SELECT L + 1 FROM Numbers WHERE L < 1000: This is the recursive part of the CTE. It starts with the base case (SELECT 2 AS L) and then repeatedly adds 1 to the previous value (SELECT L + 1) as long as the value is less than 1000.

2.Main Query:

SELECT GROUP_CONCAT(L SEPARATOR '&') AS PrimeNumbers
FROM Numbers
WHERE NOT EXISTS (
    SELECT 1
    FROM Numbers AS N2
    WHERE N2.L > 1 AND N2.L < Numbers.L AND Numbers.L % N2.L = 0
) AND L <= 1000;

• SELECT GROUP_CONCAT(L SEPARATOR '&') AS PrimeNumbers FROM Numbers: This selects the prime numbers generated in the "Numbers" CTE and concatenates them into a single string using the GROUP_CONCAT function. The SEPARATOR '&' specifies that an ampersand should be used to separate the numbers in the resulting string.
• WHERE NOT EXISTS (SELECT 1 FROM Numbers AS N2 WHERE N2.L > 1 AND N2.L < Numbers.L AND Numbers.L % N2.L = 0): This condition filters out non-prime numbers. It checks for the absence of any other number (N2.L) within the range (greater than 1 and less than Numbers.L) that evenly divides (%) the current number (Numbers.L).
• AND L <= 1000: This ensures that only prime numbers up to 1000 are considered.

In summary, the query generates prime numbers up to 1000 using a recursive CTE and then selects and concatenates them into a single string while filtering out non-prime numbers using the NOT EXISTS condition.

OUTPUT

I want to express my gratitude for dedicating your time to read my blog.

For additional updates,Follow my GitHub and LinkedIn accounts