HackerRank
-
Population Census MySQLHackerRank/SQL 2023. 2. 16. 12:45
문제 https://www.hackerrank.com/challenges/asian-population/problem Population Census | HackerRank Query the sum of the populations of all cities on the continent 'Asia'. www.hackerrank.com 풀이 INNER JOIN과 SUM()을 이용한다. SELECT SUM(CI.POPULATION) FROM CITY CI JOIN COUNTRY CO ON CI.COUNTRYCODE = CO.CODE WHERE CO.CONTINENT = 'Asia'
-
Weather Observation Station 20 MySQLHackerRank/SQL 2023. 2. 15. 13:02
문제 https://www.hackerrank.com/challenges/weather-observation-station-20/problem Weather Observation Station 20 | HackerRank Query the median of Northern Latitudes in STATION and round to 4 decimal places. www.hackerrank.com 풀이 서브쿼리와 PERCENT_RANK()를 이용한다. SELECT ROUND(LAT_N, 4) FROM (SELECT LAT_N, PERCENT_RANK() OVER (ORDER BY LAT_N) AS P FROM STATION) AS A WHERE A.P = 0.5 참조 https://transferhwan..
-
Top Earners MySQLHackerRank/SQL 2023. 2. 13. 18:00
문제 https://www.hackerrank.com/challenges/earnings-of-employees/problem Top Earners | HackerRank Find the maximum amount of money earned by any employee, as well as the number of top earners (people who have earned this amount). www.hackerrank.com 풀이 서브쿼리를 이용해 MAX 값과 개수를 센다. SELECT SALARY * MONTHS AS A, COUNT(*) FROM EMPLOYEE GROUP BY A HAVING A = (SELECT MAX(SALARY * MONTHS) FROM EMPLOYEE) 참조 ht..
-
Binary Tree Nodes MySQLHackerRank/SQL 2023. 2. 11. 21:16
문제 https://www.hackerrank.com/challenges/binary-search-tree-1/problem Binary Tree Nodes | HackerRank Write a query to find the node type of BST ordered by the value of the node. www.hackerrank.com 풀이 CASE문과 서브쿼리를 이용한다. 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 FROM BST ORDER BY N 참조 https://jogrammer.tist..
-
Occupations MySQLHackerRank/SQL 2023. 2. 10. 17:22
문제 https://www.hackerrank.com/challenges/occupations/problem 풀이 CASE문과 ROW_NUMBER()를 이용한다. SELECT MIN(CASE WHEN OCCUPATION = 'Doctor' THEN NAME END), MIN(CASE WHEN OCCUPATION = 'Professor' THEN NAME END), MIN(CASE WHEN OCCUPATION = 'Singer' THEN NAME END), MIN(CASE WHEN OCCUPATION = 'Actor' THEN NAME END) FROM (SELECT OCCUPATION, NAME, ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) B ..
-
The PADS MySQLHackerRank/SQL 2023. 2. 10. 16:08
문제 https://www.hackerrank.com/challenges/the-pads/problem The PADS | HackerRank Query the name and abbreviated occupation for each person in OCCUPATIONS. www.hackerrank.com 풀이 문자열 연결 함수 CONCAT을 사용한다. SELECT CONCAT(NAME, '(', SUBSTR(OCCUPATION, 1, 1), ')') FROM OCCUPATIONS ORDER BY NAME, SUBSTR(OCCUPATION, 1, 1); SELECT CONCAT('There are a total of ', COUNT(OCCUPATION), ' ', LOWER(OCCUPATION), 's..
-
The Report MySQLHackerRank/SQL 2023. 2. 9. 16:53
문제 https://www.hackerrank.com/challenges/the-report/problem The Report | HackerRank Write a query to generate a report containing three columns: Name, Grade and Mark. www.hackerrank.com 풀이 먼저 출력하고자 하는 값들을 불러온다. SELECT A.NAME, B.GRADE, A.MARKS FROM STUDENTS A JOIN GRADES B JOIN 조건 ON을 추가해 학생별로 해당되는 GRADE만 가져오도록 수정한다. SELECT A.NAME, B.GRADE, A.MARKS FROM STUDENTS A JOIN GRADES B ON A.MARKS BETWEEN..
-
Weather Observation Station 6 MySQLHackerRank/SQL 2023. 2. 9. 15:07
문제 https://www.hackerrank.com/challenges/weather-observation-station-6/problem Weather Observation Station 6 | HackerRank Query a list of CITY names beginning with vowels (a, e, i, o, u). www.hackerrank.com 풀이 LIKE 조건을 여러 개 사용하고 싶을 경우 REGEXP를 사용한다. SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou]'