4월 2주

Weather Observation Station5

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

문제풀이 & 해석

1. STATION의 두 도시에 각각의 길이(즉, 이름에 있는 문자 수)
2. 가장 짧고 긴 CITY 이름을 쿼리합니다.
3. 두 개 이상의 가장 작거나 큰 도시가 있는 경우,
4. 알파벳 순으로 정렬할 때 가장 먼저 오는 도시를 선택합니다.

(SELECT CITY, LENGTH(CITY)) FROM STATION
➡STATION의 두 도시에 각각의 길이(즉, 이름에 있는 문자 수
WHERE LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION)
➡가장 짧은 CITY 이름을 쿼리
ORDER BY CITY LIMIT 1) ➡ 가장 먼저 오는 도시 선택을 위해 
UNION ALL  ➡ 짧고 긴 CITY의 이름을 합치기 위해 
(SELECT CITY, LENGTH(CITY)) FROM STATION
WHERE LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION)
 ➡ 가장 긴 CITY 이름을 쿼리
ORDER BY CITY LIMIT 1) ➡ 가장 먼저 오는 도시 선택을 위해 

---

Binary Tree Nodes

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

Root: If node is root node.
Leaf: If node is leaf node.
Inner: If node is neither root nor leaf node.

문제풀이 & 해석

• N : 노드값
• P : 부모값
• N과 P라는 두 개의 열이 포함된 테이블 BST가 제공됩니다.

1. N과 CASE문에 따라 노드의 역할을 분류하는 열을 선택합니다.
2. 노드가 루트인 경우 'Root'
3. 내부 노드인 경우 'Inner'
4. 리프 노드인 경우 'Leaf'로 분류합니다.
5. 결과를 N 열을 기준으로 오름차순으로 정렬합니다.
   

✅루트 노드를 기점으로 찾고자 하는 값의 크기에 따라 왼쪽 서브 트리나 오른쪽 서브 트리로 이동하여 노드를 탐색한다.

SELECT N, ➡ N을 선택하여
CASE WHEN P IS NULL THEN 'Root' ➡ CASE문을 이용해 부모가 없으면 Root 노드
WHEN N IN (SELECT P FROM BST) THEN 'Inner'
➡ N이 부모노드를 가진다면 'Inner'
ELSE 'Leaf' END AS TREE FROM BST ➡ 제일 마지막 노드
ORDER BY 1

---

⭐New Companies

In company C1, the only lead manager is LM1. There are two senior managers, SM1 and SM2, under LM1. There is one manager, M1, under senior manager SM1. There are two employees, E1 and E2, under manager M1.

In company C2, the only lead manager is LM2. There is one senior manager, SM3, under LM2. There are two managers, M2 and M3, under senior manager SM3. There is one employee, E3, under manager M2, and another employee, E4, under manager, M3.

Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy:

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

문제풀이 & 해석

C1사의 리드 매니저는 LM1뿐입니다.
LM1 산하에 SM1과 SM2 두 명의 시니어 매니저가 있습니다.
SM1 산하에 M1이라는 매니저가 한 명 있습니다.
M1 산하에 E1과 E2라는 두 명의 직원이 있습니다.

C2사의 리드 매니저는 LM2뿐이며, LM2 산하에 SM3라는 선임 매니저가 한 명 있습니다.
SM3 부장 밑에는 M2 부장과 M3 부장 두 명이 있으며,
M2 부장 밑에는 E3 사원 한 명과 M3 부장 밑에 E4 사원 한 명이 있습니다.

select c.company_code, c.founder, 
    count(distinct lm.lead_manager_code),
    count(distinct sm.senior_manager_code),
    count(distinct mg.manager_code),
    count(distinct e.employee_code)
from Company c
left join Lead_Manager lm on c.company_code = lm.company_code
left join Senior_Manager sm on lm.company_code = sm.company_code
left join Manager mg on sm.company_code = mg.company_code
left join Employee e on mg.company_code = e.company_code
group by c.company_code, c.founder
order by c.company_code

---

Weather Observation Station 20

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to decimal places.

Input Format

The STATION table is described as follows:

문제해석 & 풀이

STATION에서 북위도 중앙값(LAT_N)를 쿼리하고 소수점으로 답을 반올림합니다.

SELECT ROUND(MEDIAN(LAT_N), 4) FROM STATION;

---

⭐The Report

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

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.

문제풀이 & 해석

1. Students 테이블은 학생 정보, Grades 테이블은 점수 별 등급과 최소 점수, 최대 점수
2. 8등급 미만의 학생들 이름 출력 X
3. 등급 별로 내림차순
4. 같은 등급의 학생이 2명 이상이면 학생들의 이름을 알파벳 순으로 출력
5. 이름이 null일 때는 점수 순으로 출력
6. 마지막으로, 8 미만의 등급인 학생들은 그들의 이름을 null 처리
7. 출력할 항목은 이름 / 등급 / 점수 순으로

SELECT CASE WHEN G.GRADE < 8 THEN NULL ➡ 8등급 미만 
ELSE S.NAME END, G.GRADE, S.MARKS ➡ 
FROM STUDENTS AS S ➡ STUDENTS 테이블을 S로 별칭부여
JOIN GRADES AS G ➡ GRADE 테이블을 G로 별칭부여
ON S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK ➡ 
ORDER BY G.GRADE DESC, S.NAME, S.MARKS ➡ 알파벳 순으로 출력 

---

⭐Top Competitors

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

문제풀이 & 해석

줄리아는 코딩 대회를 막 마쳤고, 그녀는 리더보드를 조립하는 데 당신의 도움이 필요합니다! 쿼리를 작성하여
두 번 이상의 도전에 대해 만점을 획득한 각 hacker_id 및 해커의 이름을 출력합니다.
해커가 전체 점수를 획득한 총 도전 수에 따라 출력을 내림차순으로 정렬합니다.
둘 이상의 해커가 동일한 수의 도전에서 만점을 받은 경우
hacker_id를 오름차순으로 정렬합니다.

1. 두 번 이상의 도전에 대해 만점을 획득한 각 hacker_id 및 해커의 이름을 출력
   ➡Full Score를 받은 챌린지가 2개 이상인 참가자 찾기
2. 전체 점수를 획득한 총 도전 수에 따라 출력을 내림차순으로 정렬 + 둘 이상의 해커가 동일한 수의 도전에서 만점을 받은 경우 hacker_id를 오름차순
   ➡ Submissions 테이블의 점수와 Difficulty 테이블의 score값과 일치하는 것 의미
3. 2가지 정렬기준 만족

SELECT s.hacker_id, h.name
FROM Submissions s
    INNER JOIN Challenges c ON s.challenge_id = c.challenge_id
    INNER JOIN Difficulty d ON c.difficulty_level = d.difficulty_level
    INNER JOIN Hackers h ON s.hacker_id = h.hacker_id
WHERE d.score = s.score
GROUP BY s.hacker_id, h.name
HAVING count(s.challenge_id) > 1
ORDER BY count(s.challenge_id) DESC, s.hacker_id