Hackerrank SQL 全問解答 | 難易度 Medium【問題翻訳付き】

{"filename":"Binary Tree Node","code":"SELECT\n  N,\n  CASE\n    WHEN P is NULL THEN 'Root'\n    WHEN EXISTS(\n      SELECT\n        *\n      FROM\n        BST BST2\n     WHERE\n        BST2.P = BST1.N\n     ) THEN 'Inner'\n    ELSE 'Leaf'\n  END\nFROM\n  BST BST1\nORDER BY\n  BST1.N;","language":"sql","id":18}

{"filename":"THE PADS","code":"SELECT\n  Name || '(' || CASE\n    WHEN Occupation = 'Professor' THEN 'P'\n    WHEN Occupation = 'Actor' THEN 'A'\n    WHEN Occupation = 'Doctor' THEN 'D'\n    WHEN Occupation = 'Singer' THEN 'S'\n    ELSE NULL\n  END || ')'\nFROM\n  OCCUPATIONS\nORDER BY\n  Name;\nSELECT\n  'There are a total of ' || COUNT() || CASE\n  WHEN Occupation = 'Professor' THEN ' professors.'\n  WHEN Occupation = 'Actor' THEN ' actors.'\n  WHEN Occupation = 'Doctor' THEN ' doctors.'\n  WHEN Occupation = 'Singer' THEN ' singers.'\n  ELSE NULL\n  END\nFROM\n  OCCUPATIONS\nGROUP BY\n  Occupation\nORDER BY\n  COUNT(),\n  Occupation;","language":"sql","id":18}

{"filename":"Occupations","code":"SELECT\n  MAX(\n    CASE\n      WHEN Occupation = 'Doctor' THEN Name\n      ELSE NULL\n    END\n  ),\n  MAX(\n    CASE\n      WHEN Occupation = 'Professor' THEN Name\n      ELSE NULL\n    END\n  ),\n  MAX(\n    CASE\n      WHEN Occupation = 'Singer' THEN Name\n      ELSE NULL\n    END\n  ),\n  MAX(\n    CASE\n      WHEN Occupation = 'Actor' THEN Name\n      ELSE NULL\n    END\n  )\nFROM(\n  SELECT\n    ROW_NUMBER() OVER(\n      PARTITION BY Occupation\n      ORDER BY\n        Name\n    ) RN,\n    Name,\n    Occupation\n  FROM\n    OCCUPATIONS\n )\nGROUP BY\n  RN\nORDER BY\n  RN;","language":"sql","id":18}

{"filename":"Weather Observation Station 18","code":"SELECT\n  ROUND(\n    ABS(MAX(LAT_N) - MIN(LAT_N)) + ABS(MAX(LONG_W) - MIN(LONG_W)),\n    4\n  )\nFROM\n  STATION;","language":"sql","id":18}

{"filename":"Weather Observation Station 19","code":"SELECT\n  ROUND(\n    SQRT(\n      POWER(MAX(LAT_N) - MIN(LAT_N), 2) + POWER(MAX(LONG_W) - MIN(LONG_W), 2)\n    ),\n    4\n  )\nFROM\n  STATION;","language":"sql","id":18}

{"filename":"Weather Observation Station 20\uff08Oracle\uff09","code":"SELECT\n  ROUND(MEDIAN(LAT_N), 5)\nFROM\n  STATION;","language":"sql","id":18}

{"filename":"The Report","code":"SELECT\n  CASE\n    WHEN Marks >= 70 THEN Name ELSE NULL\n  END,\n  (\n    SELECT\n      Grade\n    FROM\n      Grades\n    WHERE\n      Marks >= Grades.Min_Mark\n      AND Marks = Grades.Max_Mark\n  ) AS G,\n    Marks\n  FROM\n    Students\n  ORDER BY\n    G DESC,\n    Name,\n    Marks;","language":"sql","id":18}

{"filename":"Top Competitors","code":"CREATE VIEW PerfectScorer(hacker_id, name) AS\nSELECT\nCASE WHEN SUB1.score =\n  (SELECT score\n  FROM Challenges INNER JOIN Difficulty\n  ON Challenges.difficulty_level = Difficulty.difficulty_level\n  WHERE SUB1.challenge_id = challenge_id)\nTHEN SUB1.hacker_id ELSE NULL END AS full_score_hacker_id,\n  (SELECT name FROM Hackers WHERE Hackers.hacker_id = SUB1.hacker_id)\nFROM Submissions SUB1;\nSELECT hacker_id, MAX(name) FROM PerfectScorer\nWHERE (hacker_id IS NOT NULL)\nGROUP BY hacker_id\nHAVING COUNT(hacker_id) > 1\nORDER BY COUNT(hacker_id) DESC, hacker_id;","language":"sql","id":18}

{"filename":"Contest Leaderboard","code":"CREATE VIEW Max_Scores(hacker_id, name, max_score)\nAS\nSELECT\n  MAX(hacker_id),\n  MAX((SELECT\n      name\n    FROM Hackers\n    WHERE Hackers.hacker_id = Submissions.hacker_id)),\n  MAX(score)\nFROM Submissions\nWHERE score > 0\nGROUP BY hacker_id, challenge_id;\nSELECT\n  hacker_id,\n  MAX(name),\n  SUM(max_score)\nFROM Max_Scores\nGROUP BY hacker_id\nORDER BY SUM(max_score) DESC, hacker_id;","language":"sql","id":18}

{"filename":"Challenges","code":"CREATE VIEW Count_Each_Student_Posts(hacker_id, name, Cnt) AS\nSELECT Hackers.hacker_id,\n  MAX(name),\n  COUNT(challenge_id)\nFROM\n  Hackers INNER JOIN Challenges\n  ON Hackers.hacker_id = Challenges.hacker_id\nGROUP BY Hackers.hacker_id\nORDER BY COUNT(challenge_id) DESC, Hackers.hacker_id;\nSELECT *\nFROM\n  (SELECT\n    CASE\n      WHEN (SELECT COUNT(*)\n        FROM Count_Each_Student_Posts C2\n        WHERE C1.Cnt = C2.Cnt\n        GROUP BY Cnt) = 1\n     THEN hacker_id\n     ELSE\n      CASE\n        WHEN Cnt = (SELECT MAX(Cnt) FROM Count_Each_Student_Posts)\n        THEN hacker_id\n        ELSE NULL END\n      END hacker_id_2,\n     name,\n     Cnt\nFROM Count_Each_Student_Posts C1)\nWHERE hacker_id_2 IS NOT NULL\nORDER BY Cnt DESC, hacker_id_2;","language":"sql","id":18}

{"filename":"Olivander's Inventory","code":"CREATE VIEW W(id, age, coins_needed, power, is_evil) AS\nSELECT\n  id,\n  age,\n  coins_needed,\n  power,\n  is_evil\nFROM\n  Wands INNER JOIN Wands_property\n  ON Wands.code = Wands_property.code;\nSELECT\n  id,\n  age,\n  coins_needed,\n  power\nFROM\n  W\nWHERE is_evil = 0\n  AND coins_needed =\n  (\n    SELECT\n      MIN(coins_needed)\n    FROM W W2\n    WHERE W.age = W2.age\n      AND W.power = W2.power\n  )\nORDER BY\n  power DESC,\n  age DESC;","language":"sql","id":18}

{"filename":"Placements","code":"SELECT Name FROM Students Std1\nWHERE\n  (SELECT Salary\n    FROM Packages P1\n    WHERE P1.ID = Std1.ID)\n    <\n  (SELECT Salary\n    FROM Packages P2\n    WHERE P2.ID\n    = (SELECT Friend_ID\n  FROM Friends\n  WHERE Std1.ID = Friends.ID))\nORDER BY\n  (SELECT Salary\n    FROM Packages P2\n    WHERE P2.ID\n  = (SELECT Friend_ID\n       FROM Friends\n      WHERE Std1.ID = Friends.ID));","language":"sql","id":18}

{"filename":"New Company","code":"SELECT\n  company_code,\n  founder,\n  (SELECT\n    COUNT(DISTINCT lead_manager_code)\n  FROM Lead_Manager\n  WHERE Lead_Manager.company_code = Company.company_code\n  GROUP BY company_code),\n  (SELECT\n    COUNT(DISTINCT senior_manager_code)\n  FROM Senior_Manager\n  WHERE Senior_Manager.company_code = Company.company_code\n  GROUP BY company_code),\n  (SELECT\n    COUNT(DISTINCT manager_code)\n  FROM Manager\n  WHERE Manager.company_code = Company.company_code\n  GROUP BY company_code),\n  (SELECT\n    COUNT(DISTINCT employee_code)\n  FROM Employee\n  WHERE Employee.company_code = Company.company_code\nGROUP BY company_code)\nFROM Company\nORDER BY company_code;","language":"sql","id":18}

{"filename":"Symmetric Pairs (Oracle)","code":"CREATE VIEW Functions_with_Rownumber(R, X, Y)\nAS\nSELECT\n  ROW_NUMBER() OVER(ORDER BY X),\n  X,\n  Y\nFROM Functions;\nSELECT\n  X,\n  Y\nFROM Functions_with_Rownumber F1\nWHERE\n  EXISTS(\n    SELECT\n      *\n    FROM\n      Functions_with_Rownumber F2\n    WHERE\n      F1.X = F2.Y AND F1.Y = F2.X\n    AND\n      F1.R < F2.R\n  )\nORDER BY R;","language":"sql","id":18}

{"filename":"SQL Project Planning (Oracle)","code":"CREATE VIEW V(Start_Date, End_Date, period) AS\nSELECT\n  Start_Date,  \n  End_Date,\n  SUM(not_consecutive_flg) OVER(ORDER BY Start_Date RANGE UNBOUNDED PRECEDING)\nFROM\n(SELECT\n  Start_Date,\n  End_Date,\n  CASE WHEN\n    MIN(End_Date)\n    OVER(ORDER BY Start_Date\n      ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) IS NULL\n  THEN 1\n  ELSE\n    CASE WHEN\n      Start_Date -\n      MIN(End_Date)\n      OVER(ORDER BY Start_Date\n        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) > 0\n    THEN 1 ELSE 0 END\n  END not_consecutive_flg\nFROM Projects);\nSELECT\n  MIN(Start_Date),\n  MAX(End_Date)\nFROM V\nGROUP BY period\nORDER BY COUNT(*), MIN(Start_Date);","language":"sql","id":18}

{"filename":"Print Prime Numbers (Oracle)","code":"CREATE TABLE Digits(\n  digit INTEGER NOT NULL\n);\nINSERT INTO Digits VALUES\n(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);\nCREATE VIEW Sequence(seq)\nAS\nSELECT D1.digit + 10(D2.digit) + 100\/em>(D3.digit)\nFROM Digits D1\n  CROSS JOIN Digits D2\n  CROSS JOIN Digits D3;\nCREATE VIEW PrimeNums(R,p)\nAS\nSELECT\n  ROW_NUMBER() OVER(ORDER BY seq),\n  seq\nFROM Sequence S1\nWHERE (\n  NOT EXISTS(\n    SELECT\n      *\n    FROM Sequence S2\n    WHERE S1.seq > S2.seq\n      AND S1.seq % S2.seq = 0\n      AND S2.seq > 1\n  )\n  AND S1.seq BETWEEN 2 AND 1000\n)\nOR S1.seq = 2;\nSELECT\n  GROUP_CONCAT(p separator '&')\nFROM\n  PrimeNums;","language":"sql","id":18}