[HackerRank][SQL(Oracle)] Print Prime Numbers

728x90

 

■  [Alternative Queries]  Print Prime Numbers

https://www.hackerrank.com/challenges/print-prime-numbers/problem?isFullScreen=true 

Q.

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

A.

(+)

WITH TMP AS (
	SELECT 			LEVEL LV 
	FROM 			DUAL
	CONNECT BY 	LEVEL <= 1000
)
SELECT 	LISTAGG(LV, '&') WITHIN GROUP (ORDER BY LV) AS RES
FROM 	(
			SELECT 	LV
			FROM 	TMP T1
			WHERE 	NOT EXISTS	(
									SELECT 	NULL
									FROM 	TMP T2
									WHERE 	T1.LV > T2.LV AND
											MOD(T1.LV, T2.LV) = 0 AND
											T2.LV > 1 AND T2.LV <> T1.LV
								)
		)
WHERE LV > 1;
SELECT 	LISTAGG(LV, '&') WITHIN GROUP (ORDER BY LV) AS RES
FROM 	(
			SELECT 	LV
			FROM 	(SELECT LEVEL + 1 LV FROM DUAL CONNECT BY LEVEL < 1000) T1
			WHERE 	NOT EXISTS	(
									SELECT 	LV
									FROM 	(SELECT LEVEL + 1 LV FROM DUAL CONNECT BY LEVEL < 1000) T2
									WHERE 	T1.LV > T2.LV AND
											MOD(T1.LV, T2.LV) = 0 AND
											T2.LV <> T1.LV
								)
		);
SELECT 	LISTAGG(LV, '&') WITHIN GROUP (ORDER BY LV) AS RES
FROM 	(
			SELECT 		LV
			FROM 		(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 1000)
						,(SELECT LEVEL LV2 FROM DUAL CONNECT BY LEVEL <= 1000)
			WHERE 		LV >= LV2
			GROUP BY 	LV
			HAVING 		COUNT(CASE LV/LV2 WHEN TRUNC(LV/LV2) THEN 1 END) = 2
		);
SELECT	LISTAGG(N, '&') WITHIN GROUP (ORDER BY N) AS RES
FROM 	(
			SELECT	ROWNUM + 1 N FROM DUAL CONNECT BY ROWNUM < 1000
			MINUS
			SELECT	T1.N1 AS N
			FROM
					(SELECT ROWNUM + 1 N1 FROM DUAL CONNECT BY ROWNUM < 1000) T1 JOIN
					(SELECT ROWNUM + 1 N2 FROM DUAL CONNECT BY ROWNUM < 1000) T2
			    ON	T1.N1 > T2.N2 AND MOD(T1.N1, T2.N2) = 0
		);

( https://stackoverflow.com/questions/9915375/get-prime-number-records-in-oracle-using-rownum )

 

 

반응형