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 )
반응형
'코딩 문제 풀기 ( Algorithm problem solving ) > 해커랭크 ( HackerRank )' 카테고리의 다른 글
[HackerRank][SQL(Oracle)] Ollivander's Inventory (0) | 2023.03.29 |
---|---|
[HackerRank][SQL(Oracle)] SQL Project Planning (0) | 2023.03.29 |
[HackerRank][SQL(Oracle)] Symmetric Pairs (0) | 2023.03.29 |
[HackerRank][SQL(Oracle)] New Companies (0) | 2023.03.29 |
[HackerRank][SQL(Oracle)] Top Competitors (0) | 2023.03.29 |