> For the complete documentation index, see [llms.txt](https://dshub.gitbook.io/ds-hub/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dshub.gitbook.io/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/hackerrank/sql-advanced/print-prime-numbers.md).

# Print Prime Numbers

[Question Link](https://www.hackerrank.com/challenges/print-prime-numbers/problem?isFullScreen=false)

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
```

<pre class="language-sql"><code class="lang-sql"><strong>-- 3 Methods
</strong><strong>-- 1) MYSQL without a Procedure
</strong>WITH RECURSIVE t AS (
    SELECT 2 AS nums
    UNION
    SELECT nums + 1
    FROM t
    WHERE nums &#x3C; 1000
)
SELECT GROUP_CONCAT(nums SEPARATOR '&#x26;')
FROM t
WHERE nums NOT IN (
    SELECT DISTINCT a.nums
    FROM t a
    JOIN t b
    ON b.nums &#x3C;= 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 &#x3C;= last DO
        SET divisible_count = 0;
        SET traversal = 2;
        WHILE traversal &#x3C;= 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, '&#x26;', 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 &#x3C;> 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 &#x3C;= 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,'&#x26;') FROM prime_numbers


</code></pre>


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://dshub.gitbook.io/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/hackerrank/sql-advanced/print-prime-numbers.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.