[SQL 코드카타] 150 ~ 157번 (HackerRank)

(150번) revising-aggregations-the-count-function

SELECT COUNT(DISTINCT NAME)
FROM CITY
WHERE POPULATION > 100000

 

(151번) revising-aggregations-sum

SELECT SUM(POPULATION)
FROM CITY
WHERE DISTRICT IN ('California')
GROUP BY DISTRICT

 

(152번) revising-aggregations-the-average-function

SELECT AVG(POPULATION)
FROM CITY
WHERE DISTRICT IN ('California')

 

(153번) average-population

SELECT ROUND(AVG(POPULATION),0)
FROM CITY

 

(154번) japan-population

SELECT SUM(POPULATION)
FROM CITY
WHERE COUNTRYCODE = 'JPN'

 

(155번) population-density-difference

SELECT MAX(POPULATION) - MIN(POPULATION)
FROM CITY

 

(156번) the-blunder

> Replace 활용

WITH ERROR_SALARY AS (
SELECT
    ID, NAME, REPLACE(Salary,0,'') AS Err_Salary
FROM
    EMPLOYEES
    )
SELECT ROUND(AVG(emp.Salary),0)- ROUND(AVG(err.Err_Salary),0)    
FROM
    EMPLOYEES emp
    JOIN ERROR_SALARY err
    ON emp.Id = err.id

 

(157번) earnings-of-employees

SELECT 
    MAX(salary * months) AS max_earning,
    count(1) AS num_max_earner
FROM
    Employee
WHERE 
    salary * months = (SELECT MAX(salary * months)
                      FROM Employee)