HackerRank SQL - Advanced Select (MySQL)

 

Solve SQL Code Challenges

 

The PADS

SELECT CONCAT(Name, '(', LEFT(Occupation, 1), ')') AS Occupation 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;

 

 

Occupations

SELECT
    MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS 'Doctor',
    MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS 'Professor',
    MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS 'Singer',
    MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS 'Actor'
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY OCCUPATION ORDER BY NAME) RN
    FROM OCCUPATIONS
    ) TEMP GROUP BY TEMP.RN;

 

 

Binary Tree Nodes

SELECT N, 
    (CASE
        WHEN P IS NULL THEN 'Root'
        WHEN N NOT IN(SELECT DISTINCT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf' 
                                                                      ELSE 'Inner' END)
     AS 'nodeType'
FROM BST ORDER BY N;

 

 

New Companies

SELECT c.company_code, c.founder,
       COUNT(DISTINCT lm.lead_manager_code) AS lead_manager_number,
       COUNT(DISTINCT sm.senior_manager_code) AS senior_manager_number,
       COUNT(DISTINCT m.manager_code) AS manager_number,
       COUNT(DISTINCT e.employee_code) AS employee_number
FROM Company c LEFT JOIN Lead_Manager lm ON c.company_code = lm.company_code
               LEFT JOIN Senior_Manager sm ON lm.lead_manager_code = sm.lead_manager_code
               LEFT JOIN Manager m ON sm.senior_manager_code = m.senior_manager_code
               LEFT JOIN Employee e ON m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code;

* JOIN으로 해도 정상적으로 원하는 값이 출력된다.

 

 

Type of Triangle

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