HackerRank: SQL 풀이(21~30)

21. Type of Triangle

• 틀린 답

  SELECT
     CASE
         WHEN A>=B+C or B>=A+C or C>=A+B THEN "Not A Triangle"
         WHEN A=B=C THEN "Equilateral"
         WHEN A=B or B=C or A=C THEN "Isosceles"
         WHEN A=!B AND A=!C AND B=!C THEN "Scalence"
     END AS Triangle_type
  FROM TRIANGLES;

  왜냐하면

1. MS SQL Server에서는 A=B=C 이런거 지원 안함
2. =!은 SQL에서 지원되지 않는 연산자임 대신해서 <> 이거 써야함.

• 수정

SELECT
    CASE
        WHEN A>=B+C or B>=A+C or C>=A+B THEN "Not A Triangle"
        WHEN A=B AND B=C THEN "Equilateral"
        WHEN A=B or B=C or A=C THEN "Isosceles"
        WHEN A<>B AND A<>C AND B<>C THEN "Scalene"
    END AS Triangle_type
FROM TRIANGLES;

CASE 문법 개념

CASE는 조건문을 사용하여 값 또는 결과를 반환할 때 사용하는 제어 흐름 함수.
주로 SELECT, UPDATE, ORDER BY, WHERE 절 등에서 조건에 따라 다른 값을 반환하거나 연산을 수행할 때 활용

형식:
1. Simple CASE:
특정 값에 따라 조건을 비교.

CASE input_expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

2. Searched CASE:
   조건식(Boolean Expression)을 사용.

   CASE
       WHEN condition1 THEN result1
       WHEN condition2 THEN result2
       ...
       ELSE default_result
   END

---

1. Simple CASE 예제

학생의 성적에 따라 등급을 매기는 경우:

SELECT 
    StudentName,
    Score,
    CASE Score
        WHEN 90 THEN 'A'
        WHEN 80 THEN 'B'
        WHEN 70 THEN 'C'
        ELSE 'F'
    END AS Grade
FROM Students;

설명:

• Score 값이 90이면 'A', 80이면 'B', 70이면 'C', 그 외에는 'F'를 반환.

---

2. Searched CASE 예제

조건식으로 값을 판별:

SELECT 
    EmployeeID,
    Salary,
    CASE
        WHEN Salary > 5000 THEN 'High'
        WHEN Salary BETWEEN 3000 AND 5000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryCategory
FROM Employees;

설명:

• Salary 값이 5000보다 크면 'High', 3000~5000 사이라면 'Medium', 그 외는 'Low'를 반환.

---

3. WHERE 절에서 CASE 사용

상품 가격에 따라 검색 조건 변경:

SELECT ProductName, Price
FROM Products
WHERE 
    CASE 
        WHEN Price > 100 THEN 'Expensive'
        ELSE 'Affordable'
    END = 'Expensive';

설명:

• 가격이 100보다 큰 상품만 반환.

---

4. ORDER BY에서 CASE 사용

직원 데이터를 부서에 따라 정렬:

SELECT EmployeeID, Department
FROM Employees
ORDER BY 
    CASE Department
        WHEN 'HR' THEN 1
        WHEN 'Sales' THEN 2
        ELSE 3
    END;

설명:

• 부서가 'HR'이면 1, 'Sales'이면 2, 그 외는 3으로 정렬.

---

요약

• CASE는 조건별로 반환 값을 정의하며, WHEN, THEN, ELSE, END로 구성.
• Simple CASE는 특정 값 비교, Searched CASE는 조건식 사용.
• SELECT, WHERE, ORDER BY 등 다양한 구문에 적용 가능.

---

22. The PADS

• 오답

SELECT NAME+"("+LEFT(Occupation, 1)+")"
FROM OCCUPATIONS;

SELECT "There are a total of "+COUNT(Occupation)+" "+lower(Occupation)+"s."
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation) ASC;

1. MS SQL Server의 경우 자동으로 숫자를 문자열로 변환한다든가 하지 않는다.
   CAST 또는 CONVERT 함수를 사용해야 한다.
2. UNION ALL로 엮어주고 ORDER BY는 마지막에 한번만 써줘라

• 정답

SELECT 
    Name + '(' + LEFT(Occupation, 1) + ')' AS Result
FROM 
    OCCUPATIONS

UNION ALL

SELECT 
    'There are a total of ' + CAST(COUNT(*) AS VARCHAR) + ' ' + LOWER(Occupation) + 's.'
FROM 
    OCCUPATIONS
GROUP BY 
    Occupation

ORDER BY 
    Result;

23. Revising Aggregations - The Count Function

SELECT COUNT(*)
FROM CITY
WHERE POPULATION > 100000;

24. Revising Aggregations - The Sum Function

SELECT SUM(POPULATION)
FROM CITY
WHERE DISTRICT = 'California';

25. Revising Aggregations - Averages

SELECT AVG(POPULATION)
FROM CITY
WHERE DISTRICT='California';

26. Average Population

SELECT AVG(POPULATION)
FROM CITY;

27. Japan Population

SELECT SUM(POPULATION)
FROM CITY
WHERE COUNTRYCODE='JPN';

28. Population Density Difference

SELECT MAX(POPULATION) - MIN(POPULATION)
FROM CITY;

29. The Blunder

1. RPLACE함수
   문자열 내에서 지정된 문자를 다른 문자로 대체하거나, 제거하는 역할
   REPLACE(string, old_char, new_char)
2. ROUND(): 반올림
   CEILING(): 올림
   FLOOR(): 버림

• 잘못된 풀이 (0 제거후 '' 인 경우의 수 고려 X)

SELECT CEILING(
    AVG(Salary) - AVG(
        CAST(REPLACE(CAST(Salary AS VARCHAR), '0', '') AS INT)
    )
) AS error
FROM EMPLOYEES;

• 정답 풀이

SELECT CEILING(
    AVG(Salary * 1.0) - AVG(
        CASE 
            WHEN REPLACE(CAST(Salary AS VARCHAR), '0', '') = '' THEN 0
            ELSE CAST(REPLACE(CAST(Salary AS VARCHAR), '0', '') AS INT)
        END * 1.0
    )
) AS error
FROM EMPLOYEES;

갑자기 왜 1.0 을 곱해주나요?
만약 1.0을 곱하지 않아서 실수형으로 변환되지 않는다면, AVG 함수는 정수형 값을 입력받아 계산하므로, 중간 계산에서 발생하는 소수점은 무시됨.
최종적으로 AVG 결과가 실수형으로 반환되지만, 내부적으로는 정수형 연산이 이루어 짐.
그래서 약간의 오차가 발생할 수 있고 실제 1.0을 곱하지 않으면(실수형 중간계산이 누락되면) 정답이 1 만큼 달라져서 오답됨 ㅋㅋ ;;; 실화냐

30. Top Earners

• 오답

WITH Earning_table AS (
    SELECT employee_id, name, months, salary, (months*salary) AS total_earnings
    FROM Employee
)
SELECT MAX(total_earnings), COUNT(*)
FROM Earning_table
WHERE total_earnings=MAX(total_earnings);

MAX()는 집계 함수라서 SELECT 절이나 HAVING 절에서만 사용가능합니다 ~ WHERE 절에서 집계함수인 MAX()를 사용하는건 틀려쓰요~

• 정답

WITH TotalEarnings AS (
    SELECT 
        employee_id,
        name,
        (months * salary) AS total_earnings
    FROM Employee
)
SELECT 
    MAX(total_earnings) AS max_total_earnings,
    COUNT(*) AS max_earners_count
FROM TotalEarnings
WHERE total_earnings = (SELECT MAX(total_earnings) FROM TotalEarnings);

서브쿼리를 넣어서 MAX() 값을 구하고 WHERE 절에서 그 값을 기준으로 직원들을 필터링하는 방식!