[MySQL] HackerRank SQL 연습 문제 정답 정리

Easy

Practice > SQL > Advanced Select > Type of Triangle

Q. Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

• Equilateral: It's a triangle with sides of equal length.
• Isosceles: It's a triangle with sides of equal length.
• Scalene: It's a triangle with sides of differing lengths.
• Not A Triangle: The given values of A, B, and C don't form a triangle.

• 나의 풀이

SELECT IF (A=B AND B=C, 'Equilateral', 
           IF (A+B <= C OR A+C <= B OR B+C <= A, 'Not A Triangle', 
               IF ((A=B OR B=C OR A=C), 'Isosceles', 'Scalene')))
FROM TRIANGLES

IF 구문을 이용하여 코드를 작성하였다. 삼각형이라 각 상황에 맞는 조건을 하나 하나 작성할 수 있었다. 강한 조건을 먼저 적용해주는 것이 중요한 문제 같았다.

• 다른 사람 풀이

SELECT CASE             
            WHEN A + B > C AND B + C > A AND A + C > B THEN
                CASE 
                    WHEN A = B AND B = C THEN 'Equilateral'
                    WHEN A = B OR B = C OR A = C THEN 'Isosceles'
                    ELSE 'Scalene'
                END
            ELSE 'Not A Triangle'
        END
FROM TRIANGLES;

CASE를 이용한 구문으로 가독성이 좋다고 느껴졌다.

Medium

Practice > SQL > Advanced Select > The PADS

Q. Generate the following two result sets:

1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).

2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

   There are a total of [occupation_count] [occupation]s.

   where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

⚡ Note: There will be at least two entries in the table for each type of occupation.

• 나의 풀이

SELECT CONCAT(NAME,'(',left(OCCUPATION,1),')')
FROM OCCUPATIONS
ORDER BY NAME;

SELECT CONCAT('There are a total of ',
            COUNT(OCCUPATION),' ',LOWER(OCCUPATION),'s.') 
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(OCCUPATION),OCCUPATION;

처음으로 Medium 난이도를 풀어보았다. 생각보다는 어렵지 않았다. 다양한 함수와 형식들을 많이 알아가고 있는 것 같아서 뿌듯하다.

• 다른 사람 풀이

select concat(name, '(', substr(occupation,1,1), ')')
from occupations
order by name asc;

select concat('There are total ', count(1), ' ' , lower(occupation), 's.')
from occupations
group by occupation
order by count(1) asc, occupation asc;

내 풀이와 거의 유사한 것 같다. count(1)을 활용하는 것이 눈에 띄었다.

Hard

Practice > SQL > Advanced Join > Interviews

Q. Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are .

⚡ Note: A specific contest can be used to screen candidates at more than one college, but each college only holds screening contest.