개발 공부기록

https://www.hackerrank.com/challenges/african-cities/problem?isFullScreen=true

SELECT city.name
FROM city
    INNER JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Africa'

https://leetcode.com/problems/reformat-department-table/description/

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' THEN revenue ELSE NULL END) AS May_Revenue
        ,SUM(CASE WHEN month = 'Jun' THEN revenue ELSE NULL END) AS Jun_Revenue
        ,SUM(CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) AS Jul_Revenue
        ,SUM(CASE WHEN month = 'Aug' THEN revenue ELSE NULL END) AS Aug_Revenue
        ,SUM(CASE WHEN month = 'Sep' THEN revenue ELSE NULL END) AS Sep_Revenue
        ,SUM(CASE WHEN month = 'Oct' THEN revenue ELSE NULL END) AS Oct_Revenue
        ,SUM(CASE WHEN month = 'Nov' THEN revenue ELSE NULL END) AS Nov_Revenue
        ,SUM(CASE WHEN month = 'Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
FROM department
GROUP BY id

SELECT CASE
    WHEN A = B AND B = C THEN 'Equilateral'
    WHEN A + B <= C OR B + C <= A OR C + A <= B THEN 'Not A Triangle'
    WHEN A = B OR B = C OR A = C THEN 'Isosceles'
    ELSE 'Scalene'
    END
FROM triangles

SELECT salary * months as earnings
    ,COUNT(*)
FROM employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1

SELECT AVG(population)
FROM city
WHERE district = 'California'

https://leetcode.com/problems/duplicate-emails/description/

👇 정답 코드

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(id) > 1

https://leetcode.com/problems/big-countries/description/

SELECT name, population, area
FROM world
WHERE area >= 3000000
OR population >= 25000000

https://leetcode.com/problems/not-boring-movies/description/

SELECT *
FROM cinema
WHERE description <> 'boring'
AND MOD(id, 2) = 1
ORDER BY rating DESC