Revising Aggregations - Averages,Average Population,Japan Population,Population Density Difference,The Blunder
Revising Aggregations - Averages📝
➡️ city 테이블
- population 평균 구하기 -> AVG 함수 활용
- district 이 'california'
Query the average population of all cities in CITY where District is California.
Input Format
The CITY table is described as follows:
SELECT AVG(population)
FROM city
WHERE district='california';
Average Population📝
https://www.hackerrank.com/challenges/average-population/problem?isFullScreen=true
➡️ city 테이블
- population 평균 구하기( 정수형태로 표현) -> AVG 함수, ROUND 함수 활용
Query the average population for all cities in CITY, rounded down to the nearest integer.
Input Format
The CITY table is described as follows:
SELECT ROUND(AVG(population))
FROM city;
Japan Population📝
https://www.hackerrank.com/challenges/japan-population/problem?isFullScreen=true
➡️ city 테이블
- population 합계 구하기 -> SUM 함수
- countrycode='JPN'
Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.
Input Format
The CITY table is described as follows:
SELECT SUM(population)
FROM city
WHERE countrycode='JPN';
Population Density Difference📝
➡️ city 테이블
- population 최댓값과 최솟값의 차이 구하기 -> MAX,MIN 함수 사용
Query the difference between the maximum and minimum populations in CITY.
Input Format
The CITY table is described as follows:
SELECT MAX(population)-MIN(population)
FROM city;
The Blunder📝
https://www.hackerrank.com/challenges/the-blunder/problem?isFullScreen=true
➡️ employees 테이블
- (salary의 평균값)과 (salary 0글자를 없애고 난 후에 평균값)의 차 -> salary의 0글자를 REPLACE 함수로 대체
- 그 다음 큰 integer 값을 구하라고 함 ( 가우스 함수 ) -> CELING 함수를 사용하여 소수점이 있을 경우 올림하기
Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.
Write a query calculating the amount of error (i.e.: average monthly salaries), and round it up to the next integer.
Input Format
The EMPLOYEES table is described as follows:
Note: Salary is per month.
Constraints
.
Sample Input
Sample Output
2061
Explanation
The table below shows the salaries without zeros as they were entered by Samantha:
Samantha computes an average salary of . The actual average salary is .
The resulting error between the two calculations is . Since it is equal to the integer , it does not get rounded up.
SELECT CEILING(AVG(salary)-AVG(REPLACE(salary,'0','')))
FROM employees;