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&7Solution – Print Prime Numbers in SQL | Hacker Rank Solution
MySQL Server
/* Enter your query here. 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.
answer showing incorrect
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();
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