SQL도 공부합시다(1)

이번엔 SQL입니다.
HackerRank로 할 예정.

SQL → Basic Select

Day 1
조인의 정석과 조건문의 활용

문제 List
The Report
Top Competitors
Ollivander's Inventory

Today 문법
INNER JOIN vs LEFT JOIN
CASE WHEN ... THEN ... ELSE ... END
GROUP BY와 HAVING

• 추가로 서술용 대비

---

01.The Report

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Grades contains the following data:

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

Sample Input

Sample Output

Maria 10 99
Jane 9 81
Julia 9 88
Scarlet 8 78
NULL 7 63
NULL 7 68

Note

Print "NULL" as the name if the grade is less than 8.

Explanation

Consider the following table with the grades assigned to the students:

So, the following students got 8, 9 or 10 grades:

Maria (grade 10)
Jane (grade 9)
Julia (grade 9)
Scarlet (grade 8)

-> Grade에 따라 정렬 기준이 달라진다, 8점 미만은 이름을 NULL로 표시한다가 핵심

JOIN
두 테이블 사이에 공통 컬럼이 없어도 BETWEEN을 사용해 조인
이름 처리
IF / CASE WHEN을 사용하여 Grade가 8 미만이면 NULL 출력
정렬(Order By)
콤마(,)를 이용해 1순위, 2순위, 3순위 정렬 조건 나열

SELECT 
    CASE 
        WHEN G.Grade >= 8 THEN S.Name 
        ELSE NULL 
    END AS Name, 
    G.Grade, 
    S.Marks
FROM Students S
JOIN Grades G ON S.Marks BETWEEN G.Min_Mark AND G.Max_Mark
ORDER BY 
    G.Grade DESC, 
    Name ASC, 
    S.Marks ASC;

---

2.Top Competitors

여기서부터 갑자기 문제가 너무 길어져서 생략
문제 보고 싶으신 분들은 여기 가서 봐주셔요.

그래서 여기서 핵심은 뭐냐면

1. 테이블 연결 고리 찾기

Submissions (S): 누가 어떤 문제를 풀었는지 기록.

Challenges (C): 그 문제가 어떤 난이도(Difficulty_level)인지 확인. (S.challenge_id = C.challenge_id)

Difficulty (D): 그 난이도의 만점(Score)이 몇 점인지 확인. (C.difficulty_level = D.difficulty_level)

Hackers (H): 최종적으로 출력할 해커의 이름(Name)을 가져옴. (S.hacker_id = H.hacker_id)

2. 필터링 조건 (만점자 찾기)
조인된 테이블에서 S.score (해커가 제출한 점수)와 D.score (그 난이도의 만점)가 일치하는 행만 남기기.
WHERE S.score = D.score

3. 그룹화 및 정렬

만점을 2번 이상 받은 사람만 골라야 하므로 GROUP BY 후 HAVING COUNT(*) > 1 정렬은 만점 횟수 내림차순, 같으면 hacker_id 오름차순으로 합니다.

SELECT 
    H.hacker_id, 
    H.name
FROM Submissions S  
JOIN Hackers H ON S.hacker_id = H.hacker_id 
JOIN Challenges C ON S.challenge_id = C.challenge_id 
JOIN Difficulty D ON C.difficulty_level = D.difficulty_level
WHERE S.score = D.score 
GROUP BY H.hacker_id, H.name
HAVING COUNT(S.submission_id) > 1
ORDER BY COUNT(S.submission_id) DESC, H.hacker_id ASC;

---

3.Ollivander's Inventory

역시나 문제는 여기 링크로 두겠습니다.

1. 문제의 핵심 조건
   Non-evil 지팡이여야 함 (is_evil = 0).

Age와 Power가 같은 지팡이들 중에서, 가장 적은 비용(coins_needed)이 드는 지팡이의 id, age, coins_needed, power를 출력해야함.

2. 해결 방법: 상관 서브쿼리 (Correlated Subquery)
   메인 쿼리에서 지팡이 정보를 뿌려줄 때, WHERE 절 안에서 서브쿼리를 사용하여 "현재 행의 나이와 파워가 같은 것들 중 최소 가격이 맞는지" 확인해야함.

SELECT
    W.id,
    P.age,
    W.coins_needed,
    W.power
FROM Wands W
JOIN Wands_Property P ON W.code = P.code
WHERE P.is_evil = 0
    AND W.coins_needed = (
        SELECT MIN(W1.coins_needed)
        FROM Wands W1
        JOIN Wands_Property P1 ON W1.code = P1.code
        WHERE P1.is_evil = 0
        AND P1.age = P.age
        AND W1.power = W.power
    )
ORDER BY 
    W.power DESC, 
    P.age DESC;

---

이번 기회에 HackerRank 같이 도전해보실 분
영어여도 조건은 똑같습니다.
영어도 공부하고 코딩도 하고 일석이조.

제 깃허브 입니다...잔시 안 심은지 백만년
인턴 할 때는 회사 깃허브 써야되가지고 회사 깃허브 잔디 심어주느라 개인 깃허브에 좀 소홀했습니다....