공부하는 떡볶이

https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true Interviews | HackerRank find total number of view, total number of unique views, total number of submissions and total number of accepted submissions. www.hackerrank.com hard라 그런지 어려웠다... 일단 영어 해석하는 것부터 난관..ㅎ^^ 단순 조인, 필터링이라고 생각했는데 한 번 더 생각해야하는 문제! 테이블 구조는 위와 같다. 파란색 점선은 실제로 select 해야되는 컬럼이다. Contests, Colleges, Challenges..

https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true Ollivander's Inventory | HackerRank Help pick out Ron's new wand. www.hackerrank.com 해리포터 어쩌구.. 하는 문제 나 해리포터 안봐서 이름이 뭐라 뭐라 나오니까 괜히 더 헷갈렸다 ㅋㅋㅋㅋ select distinct w1.id, p1.age, w1.coins_needed, w1.power from wands w1 join wands_property p1 on w1.code = p1.code where w1.coins_needed in (select min(w.coins_needed) ..

https://www.hackerrank.com/challenges/the-report/problem?isFullScreen=true The Report | HackerRank Write a query to generate a report containing three columns: Name, Grade and Mark. www.hackerrank.com 이 문제의 특이한 점은 조인할 때 on의 방식이 약간 다르다는 것! select (case when g.grade < 8 then null else s.name end), g.grade, s.marks from students s join grades g on s.marks between g.min_mark and g.max_mark order by ..

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true New Companies | HackerRank Find total number of employees. www.hackerrank.com 일단 내가 처음 풀어봤던 방법은 무작정 테이블 4개를 냅다 조인하는 것이다. 근데 나는 where로 조인하는 걸 생각을 못해서... 테이블 4개를 서브쿼리로(...) 조인하면서 엄청난 양의 비효율적인 쿼리를 맛보았다... 그래서 일단 내가 하려고 했던 조인으로.. 좀 더 효율적으로 문제를 푸는 방법을 먼저 소개해보겠다. select c.company_code, c.founder, count(distinct l.lead_manager_..

https://www.hackerrank.com/challenges/binary-search-tree-1/problem?isFullScreen=true Binary Tree Nodes | HackerRank Write a query to find the node type of BST ordered by the value of the node. www.hackerrank.com 흑... 열심히 고민했는데 생각보다 쉬웠던 문제...! 셀프조인해서 풀려고 했지만.... 그거슨 올바른 길이 아니었다 ㅠㅠ ㅋㅋㅋ 일단 정답 코드는 다음과 같다. SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT P FROM BST) THEN 'Inner' ELSE 'Leaf..

https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true Occupations | HackerRank Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. www.hackerrank.com set @d=0, @p=0, @s=0, @a=0; select min(doctor), min(professor), min(singer), min(actor) from(select case when occupation="Doctor" then name end as Doctor, ..

https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true SQL Project Planning | HackerRank Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. www.hackerrank.com select start_date, min(end_date) from(select start_date from projects where start_date not in (select end_date from projects)) ..

https://www.hackerrank.com/challenges/the-blunder/problem?isFullScreen=true The Blunder | HackerRank Query the amount of error in Sam's result, rounded up to the next integer. www.hackerrank.com 어떤 여자의 키보드의 0 버튼이 고장나서 벌어진 실수... 간단히 replace()를 쓰면 되지만, 다른 문자열 관련 함수도 공부할 수 있었다. 일단 내가 풀었던 방법은 with temp as(select *, replace(salary, 0, '')as miscalculated from EMPLOYEES) select ceil(avg(salary) - avg(..

https://www.hackerrank.com/challenges/weather-observation-station-17/problem?isFullScreen=true Weather Observation Station 17 | HackerRank Query the Western Longitude for the smallest value of the Northern Latitudes greater than 38.7780 in STATION and round to 4 decimal places. www.hackerrank.com 내가 풀었던 방법 ▼ select round(long_w, 4) from station where lat_n = (select min(lat_n) as min from statio..

https://www.hackerrank.com/challenges/draw-the-triangle-2/problem?isFullScreen=true Draw The Triangle 2 | HackerRank Draw the triangle pattern using asterisks. www.hackerrank.com 1) 첫 번째 풀이 set @row := 0; select repeat('* ', @row := @row + 1) from information_schema.tables where @row < 20 2) 두 번째 풀이 declare @row int = 1 while (@row < 21) begin print replicate('* ', @row) set @row = @row + 1 end ..