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

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.