HR-SQL Occupations

1 개요

HR-SQL Occupations

해커랭크 SQL

문제	DB2	MS SQL	MySQL	Oracle
HR-SQL Advanced Select e
HR-SQL Type of Triangle
HR-SQL The PADS
HR-SQL Occupations
HR-SQL Binary Tree Nodes
HR-SQL New Companies

2 DB2

sql

3 MS SQL

sql

4 MySQL

MySQL

Copy

SET @c1=0, @c2=0, @c3=0, @c4=0;
SELECT MIN(Name1), MIN(Name2), MIN(Name3), MIN(Name4)
FROM (
SELECT CASE Occupation
    WHEN 'Doctor' THEN @c1:=@c1+1
    WHEN 'Professor' THEN @c2:=@c2+1
    WHEN 'Singer' THEN @c3:=@c3+1
    WHEN 'Actor' THEN @c4:=@c4+1
END AS RowNumber,
IF(Occupation='Doctor',Name,NULL) AS Name1,
IF(Occupation='Professor',Name,NULL) AS Name2,
IF(Occupation='Singer',Name,NULL) AS Name3,
IF(Occupation='Actor',Name,NULL) AS Name4
FROM OCCUPATIONS
ORDER BY Name
) AS t GROUP BY RowNumber;

MySQL

Copy

SET @c1=0, @c2=0, @c3=0, @c4=0;
SELECT MIN(Name1), MIN(Name2), MIN(Name3), MIN(Name4)
FROM (
SELECT CASE Occupation
    WHEN 'Doctor' THEN @c1:=@c1+1
    WHEN 'Professor' THEN @c2:=@c2+1
    WHEN 'Singer' THEN @c3:=@c3+1
    WHEN 'Actor' THEN @c4:=@c4+1
END AS RowNumber,
CASE WHEN Occupation='Doctor' THEN Name END AS Name1,
CASE WHEN Occupation='Professor' THEN Name END AS Name2,
CASE WHEN Occupation='Singer' THEN Name END AS Name3,
CASE WHEN Occupation='Actor' THEN Name END AS Name4
FROM OCCUPATIONS
ORDER BY Name
) AS t GROUP BY RowNumber;

MySQL

Copy

SET @c1=0, @c2=0, @c3=0, @c4=0;
SELECT MIN(Name1), MIN(Name2), MIN(Name3), MIN(Name4)
FROM (
SELECT CASE
    WHEN Occupation='Doctor' THEN @c1:=@c1+1
    WHEN Occupation='Professor' THEN @c2:=@c2+1
    WHEN Occupation='Singer' THEN @c3:=@c3+1
    WHEN Occupation='Actor' THEN @c4:=@c4+1
END AS RowNumber,
CASE WHEN Occupation='Doctor' THEN Name END AS Name1,
CASE WHEN Occupation='Professor' THEN Name END AS Name2,
CASE WHEN Occupation='Singer' THEN Name END AS Name3,
CASE WHEN Occupation='Actor' THEN Name END AS Name4
FROM OCCUPATIONS
ORDER BY Name
) AS t GROUP BY RowNumber;

5 Oracle

sql