[HackerRank/SQL] Alternative Queries 문제풀이

[HackerRank/SQL] Alternative Queries

---

Draw The Triangle 1

Problem

P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):

* * * * * 
* * * * 
* * * 
* * 
*

Write a query to print the pattern P(20).

Problem explanation

SET @number = 21을 하는 이유는 21-1로 select구문이 시작되어 첫번째 별들이 20개가 나오도록 하기 위함
repeat구문을 통해 21에서 1씩 반복해서 빼며 출력해줌
문제에서 P(20)까지 구하라고 했으므로, LIMIT 20사용
'@set = ' 활용
Repeat
대입연산자 ':='

solution

SET @number = 21;
SELECT REPEAT('* ', @number := @number - 1) 
FROM information_schema.
tables LIMIT 20;

---

Draw The Triangle 2

Problem

P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):

* 
* * 
* * * 
* * * * 
* * * * *

Write a query to print the pattern P(20).

Problem explanation

repeat('* ', @NUMB := @NUMB + 1)을 통해 NUMB를 반복하며 1씩 증가
'* '사용하기 - 뒤에 빈칸도 함께 기재

solution

set @NUMB = 0;
select repeat('* ', @NUMB := @NUMB + 1) from information_schema.tables 
LIMIT 20;

Print Prime Numbers

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

Problem explanation

1.문제는 1000 보다 작은 숫자 중 소수를 찾는 것이다.
=>소수를 구한 후, &를 이용해 출력

소수:1과 자기 자신으로만 나누어지는 수 (1은 아님,2부터)

solution

SELECT GROUP_CONCAT(NUMB SEPARATOR '&')
FROM (
    SELECT @num:=@num+1 as NUMB FROM
    information_schema.tables t1,
    information_schema.tables t2,
    (SELECT @num:=1) tmp
) tempNum
WHERE NUMB<=1000 AND NOT EXISTS(
    SELECT * FROM (
        SELECT @nu:=@nu+1 as NUMA FROM
            information_schema.tables t1,
            information_schema.tables t2,
            (SELECT @nu:=1) tmp1
            LIMIT 1000
        ) tatata
    WHERE FLOOR(NUMB/NUMA)=(NUMB/NUMA) AND NUMA<NUMB AND NUMA>1
);