# Print Prime Numbers in SQL | HackerRank Solution

Hello coders, today we are going to solve Print Prime Numbers HackerRank Solution in SQL.

## Problem

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

## Solution – Print Prime Numbers in SQL | Hacker Rank Solution

### MySQLServer

```/*
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
*/
DECLARE @Output AS VARCHAR(MAX) = '';
WITH digit(d)
AS
(
SELECT 0 AS d
UNION ALL
SELECT d+1 AS d FROM digit WHERE d < 9
)
SELECT
@Output += CAST(a.Number AS VARCHAR(3)) + '&'
FROM (
SELECT a.d * 100 + b.d*10 + c.d + 1 AS Number FROM digit a
CROSS JOIN digit b
CROSS JOIN digit c
) a
LEFT JOIN (
SELECT a.d * 100 + b.d*10 + c.d + 1 AS Number FROM digit a
CROSS JOIN digit b
CROSS JOIN digit c
) b ON SQRT(a.Number) >= b.Number AND b.Number > 1
WHERE  a.Number > 1
GROUP BY a.Number
HAVING ISNULL(SUM(CASE WHEN a.Number % b.Number = 0 THEN 1 ELSE 0 END),0) = 0
ORDER BY a.Number
PRINT SUBSTRING(@Output,1,LEN(@Output)-1)
;```

Disclaimer: The above Problem (Print Prime Numbers) is generated by Hacker Rank but the Solution is Provided by CodingBroz. This tutorial is only for Educational and Learning Purpose.

### 3 thoughts on “Print Prime Numbers in SQL | HackerRank Solution”

1. DHARA RAJASHEKHAR REDDY

this is the correct one
DELIMITER //

CREATE PROCEDURE GeneratePrimes()
BEGIN
DECLARE I INT DEFAULT 2;
DECLARE PRIME INT DEFAULT 0;

CREATE TEMPORARY TABLE IF NOT EXISTS OUTPUT (NUM INT);

prime_loop: WHILE I <= 1000 DO
SET PRIME = 1;
SET @J = 2;

prime_inner_loop: WHILE @J < I DO
IF I % @J = 0 THEN
SET PRIME = 0;
LEAVE prime_inner_loop;
END IF;
SET @J = @J + 1;
END WHILE prime_inner_loop;

IF PRIME = 1 THEN
INSERT INTO OUTPUT VALUES (I);
END IF;

SET I = I + 1;
END WHILE prime_loop;

SELECT GROUP_CONCAT(NUM SEPARATOR '&') FROM OUTPUT;
DROP TEMPORARY TABLE IF EXISTS OUTPUT;
END //

DELIMITER ;

CALL GeneratePrimes();

1. DECLARE @I INT=2
DECLARE @PRIME INT=0
DECLARE @OUTPUT TABLE (NUM INT)
WHILE @I1
BEGIN
IF @I % @J=0
BEGIN
SET @PRIME=0
END
SET @J=@J-1
END
IF @PRIME =1
BEGIN
INSERT @OUTPUT VALUES (@I)
END
SET @I=@I+1
END
SELECT string_agg(Num, ‘&’)
FROM @OUTPUT