Print Prime Numbers

Question Link

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

For example, the output for all prime numbers ≤ 10 would be:

2&3&5&7

-- 3 Methods
-- 1) MYSQL without a Procedure
WITH RECURSIVE t AS (
    SELECT 2 AS nums
    UNION
    SELECT nums + 1
    FROM t
    WHERE nums < 1000
)
SELECT GROUP_CONCAT(nums SEPARATOR '&')
FROM t
WHERE nums NOT IN (
    SELECT DISTINCT a.nums
    FROM t a
    JOIN t b
    ON b.nums <= FLOOR(SQRT(a.nums)) 
        AND a.nums % b.nums = 0
)

-- 2) MYSQL with a Procedure

DELIMITER //
CREATE PROCEDURE printprime(n INT)
BEGIN
    DECLARE iter INT DEFAULT 2;
    DECLARE last INT DEFAULT n;
    DECLARE traversal INT DEFAULT 0;
    DECLARE divisible_count INT DEFAULT 0;
    DECLARE chain TEXT DEFAULT '';
    
    WHILE iter <= last DO
        SET divisible_count = 0;
        SET traversal = 2;
        WHILE traversal <= FLOOR(SQRT(iter)) DO
            IF iter % traversal = 0 THEN
                SET divisible_count = 1;
            END IF;
            SET traversal = traversal + 1;
        END WHILE ;
        IF divisible_count = 0 THEN
            IF iter = 2 THEN
                SET chain = '2';
            ELSE 
                SET chain = CONCAT(chain, '&', iter);
            END IF;
        END IF;
        SET iter = iter + 1;
    END WHILE;
    SELECT chain;
END //
DELIMITER ;

CALL printprime(1000);


/* 3) MSSQL
Recall Prime condition: Can be devided by itself or 1, and 1 is NOT a prime number/smallest prime number is 2
1- create a table for prime numbers since there is no table to choose data from
2- populate the table with only prime numbers
    i. declare (define) variables for: 
        a. number (INT,)
        b. dividers (INT)
        c. prime check (BIT, i.e. 1 or 0)
    ii.numbers will start from 1000 (since it is the max number to be checked), divider will check up to the 'number' that is being checked minus 1, and we will assume all numbers are prime first. 
        a. nr (INT, ≤ 1000)
        b. dividers (INT, ≤ NR-1)
        c. prime (all 1 at first)
    iii. if the all the dividers for certain number yield a non-zero remainder, then that number  will be added to the table
        a. number % divider <> 0 -> still prime, add too prime table
*/

CREATE TABLE prime_numbers (numbers INT);
-- declare (define) variable
DECLARE @nr INT;
DECLARE @divider INT;
DECLARE @prime BIT;

-- the smallest prime number is 2
SELECT @nr = 2;

-- Loop Through All Numbers
WHILE @nr <= 1000
    BEGIN
    SELECT @divider = @nr - 1; -- dividers (INT, ≤ NR-1)
    SELECT @prime = 1; -- all numbers assumed to be prime at first
    -- Prime test
    WHILE @divider > 1
        BEGIN
        IF @nr % @divider = 0
            SELECT @prime = 0 -- not a prime
        SELECT @divider = @divider - 1  -- loop till
        END
    IF @prime = 1
        INSERT INTO prime_numbers (numbers) VALUES (@nr)
    SELECT @nr = @nr - 1 -- loop till
END

-- Print all prime numbers
SELECT STRING_AGG(numbers,'&') FROM prime_numbers