[10주차]해커랭크: 26 ~ 35

26. Aggregation > Population Density Difference: MIN(), MAX()

Query the difference between the maximum and minimum populations in CITY.

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

 

 

 

27. Aggregation >  The Blunder: CAST(), REPLACE(), AVG(), CEIL()

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.:  'actual - miscalculated'average monthly salaries), and round it up to the next integer.

SELECT CEIL(AVG(Salary) - AVG(REPLACE(CAST(Salary AS CHAR), '0', '')))
FROM EMPLOYEES

• CAST(): 데이터 타입 바꾸는 함수
  • CAST( COLUMN AS CHAR): 문자형으로 바꾸는 경우
• REPLACE(바꾸야할 문자, 바꾸고싶은문자, 어떻게 바꿀지)
• CEIL(): 무조건 숫자 올릴때

 

 

 

28. Aggregation > Top Earners

We define an employee's total earnings to be their monthly 'salary * months' worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as  space-separated integers.

SELECT salary * months, COUNT(DISTINCT employee_id)
FROM Employee
where salary * months = (
SELECT salary * months
FROM Employee
order by 1 desc
limit 1
)
group by 1

가장 큰 salary * months를 구하고, 이를 조건절에 넣어 찾는 식으로 쿼리를 작성해야한다.

 

 

 

29. Aggregation > Weather Observation Station 2

Query the following two values from the STATION table:

1. The sum of all values in LAT_N rounded to a scale of  2 decimal places.
2. The sum of all values in LONG_W rounded to a scale of  2 decimal places

SELECT ROUND(SUM(LAT_N),2), ROUND(SUM(LONG_W),2)
FROM STATION

 

 

 

30. Aggregation > Weather Observation Station 13

Query the sum of Northern Latitudes (LAT_N) from STATION having values greater than 38.7880  and less than 137.2345. Truncate your answer to  decimal places.

SELECT TRUNCATE(SUM(LAT_N),4)
FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345

• TRUNCATE(숫자, 버릴자리수): 숫자 버릴때

 

 

31. Aggregation > Weather Observation Station 14

Query the greatest value of the Northern Latitudes (LAT_N) from STATION that is less than 137.2345 . Truncate your answer to  4 decimal places.

SELECT TRUNCATE(MAX(LAT_N),4)
FROM STATION
WHERE LAT_N < 137.2345

 

 

 

32. Aggregation > Weather Observation Station 15

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345 . Round your answer to 4 decimal places.

SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345)

 

 

 

33. Aggregation > Weather Observation Station 16

Query the smallest Northern Latitude (LAT_N) from STATION that is greater than 38.7780 . Round your answer to 4 decimal places.

SELECT ROUND(MIN(LAT_N),4)
FROM STATION
WHERE LAT_N > 38.7780

 

 

 

34. Aggregation > Weather Observation Station 17

Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780 . Round your answer to 4 decimal places.

SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780)

 

 

 

35. Aggregation > Weather Observation Station 18

Consider  and  to be two points on a 2D plane.

•  a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
•  b happens to equal the minimum value in Western Longitude (LONG_W in STATION).
•  c happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
•  d happens to equal the maximum value in Western Longitude (LONG_W in STATION).

Query the Manhattan Distance between points  and  and round it to a scale of  4 decimal places.

SELECT ROUND(ABS(DB.c-DB.a) + ABS(DB.d-DB.b),4)
FROM (
SELECT MIN(LAT_N) AS a,
       MIN(LONG_W) AS b,
       MAX(LAT_N) AS c,
       MAX(LONG_W) AS d
FROM STATION
) DB

• ABS(): 절댓값함수