구름 아카이브

African Cities https://www.hackerrank.com/challenges/african-cities/problem SELECT city.name FROM city INNER JOIN COUNTRY ON CITY.CountryCode = COUNTRY.CODE WHERE continent = 'Africa' Population Census https://www.hackerrank.com/challenges/asian-population/problem SELECT SUM(city.population) FROM city INNER JOIN country ON city.CountryCode = Country.Code WHERE CONTINENT = 'Asia' New Companies - ..

* CEIL - 소수점 첫째 자리에서 올림 * FLOOR - 소수점 첫째 자리에서 내림 * ROUND - 숫자를 각 자리에서 반올림 * TRUNCATE(숫자, 버릴 자릿수) - 숫자를 버릴 자릿수 아래로 버림 * ABS - 절대값 * POWER - 제곱 * SQRT - 양수 x 에 대한 제곱근을 반환 Average Population https://www.hackerrank.com/challenges/average-population/problem SELECT Floor(AVG(population)) FROM CITY Weather Observation Station 2 https://www.hackerrank.com/challenges/weather-observation-station-2/submiss..

Revising Aggregations - Averages https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem SELECT AVG(population) FROM CITY WHERE District = "California" Revising Aggregations - The Sum Function https://www.hackerrank.com/challenges/revising-aggregations-sum/problem SELECT SUM(population) FROM CITY WHERE District = "California" Revising Aggregations - The Count Fun..

https://www.hackerrank.com/challenges/the-report/problem?h_r=internal-search - SELECT 구문에서 CASE 활용해서 구분점 만들기 - 정렬기준 여러개 - INNER 조인 조건에 BETWEEN 사용할 수 있음.hacker_id SELECT CASE WHEN g.grade

https://www.hackerrank.com/challenges/challenges/submissions/database/331359810 SELECT h.hacker_id , h.name , count(*) Challenges_created FROM Challenges c INNER JOIN Hackers h ON h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name HAVING Challenges_created = (SELECT MAX(Challenges_created) --Challeges_created MAX 값이 50인 것 FROM( SELECT hacker_id , count(*) Challenges_created FROM Challenges G..

Top earners - 서브쿼리 https://www.hackerrank.com/challenges/earnings-of-employees/problem SELECT months*salary as earnings , count(*) FROM employee WHERE months*salary = (SELECT MAX(months*salary) FROM employee) GROUP BY earnings -- Having >>> GROUP BY 결과물을 다시 한 번 필터링 해줄 때 SELECT months*salary AS earnings , count(*) FROM employee GROUP BY earnings HAVING earnings = (SELECT MAX(months*salary) FROM e..

*** 다시 풀어볼 것 184. Department Highest Salary https://leetcode.com/problems/Department-Highest-Salary/ # 각각의 부서에서 누가 가장 고소득자인지 # sel 부서, 직원, 급여 # 순서 상관 x SELECT d.name AS Department , e.name AS Employee , e.salary AS Salary FROM employee as e INNER JOIN ( SELECT departmentID, MAX(salary) AS max_salary FROM Employee GROUP BY departmentID ) AS dh ON e.departmentID = dh.departmentID AND e.salary = dh..

Consecutive Numbers https://leetcode.com/problems/consecutive-numbers/ # 최소 3번이상 연속적으로 나타나는 숫자 찾기 # 순서는 상관없음 SELECT DISTINCT l.num AS ConsecutiveNums -- 중복제거 FROM logs AS l INNER JOIN logs AS l_next ON l.id + 1 = l_next.id INNER JOIN logs AS l_next2 ON l.id + 2 = l_next2.id # three times consecutively WHERE l.num = l_next.num AND l_next.num = l_next2.num

Reformat Department Table https://leetcode.com/problems/reformat-department-table/ SELECT id, SUM(CASE WHEN month='Jan' THEN revenue ELSE NULL END) AS JAN_Revenue, SUM(CASE WHEN month='Feb' THEN revenue ELSE NULL END) AS Feb_Revenue, SUM(CASE WHEN month='Mar' THEN revenue ELSE NULL END) AS Mar_Revenue, SUM(CASE WHEN month='Apr' THEN revenue ELSE NULL END) AS Apr_Revenue, SUM(CASE WHEN month='May..