[코드카타 연습하기]코드카타 SQL 156-164

156. 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.

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

*REPLACE 함수는 지정한 컬럼에서 특정 문자열을 다른 문자열로 바꾸어 주는 문자열 치환 함수.

select replace(컬럼이름A, '문자X', '문자Y')
from 테이블이름;

#SELECT 구문에서는 REPLACE 함수를 적용하여도 원본 테이블이 변경되지 않는다.

select first_name as 성명,
       replace(first_name, 'e', '*') as REPLACE
from employees;

*한 칼럼의 데이터 타입을 다른 형식으로 변환시켜 보여주도록 도와주는 타입 변환 함수

CAST( 칼럼명 AS 변환하고자 하는 타입)

->
SELECT *
  FROM test
 ORDER BY CAST(count AS NUMBER) DESC;

CONVERT(데이터 타입, 칼럼명)
->SELECT *
  FROM test
 ORDER BY CONVERT(NUMBER, count) ASC;

*올림 : CEILING(값)

SELECT CEILING(940.16)		--941
SELECT CEILING(942.16)		--943

*버림 : FLOOR(값)

SELECT FLOOR(940.16)		--940
SELECT FLOOR(942.16)		--942

157.We define an employee's total earnings to be their monthly 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)earnings, COUNT(*)
FROM Employee
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1;

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

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

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

SELECT TRUNCATE(LAT_N,4)
FROM STATION
WHERE LAT_N <= 137.2345
ORDER BY 1 DESC
LIMIT 1;

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

SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N <= 137.2345
ORDER BY LAT_N DESC
LIMIT 1;

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

SELECT ROUND(LAT_N,4)
FROM STATION
WHERE LAT_N >= 38.7780
ORDER BY 1
LIMIT 1

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

SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N >= 38.7780
ORDER BY LAT_N
LIMIT 1

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

     happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
     happens to equal the minimum value in Western Longitude (LONG_W in STATION).
     happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
     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 decimal places.

SELECT ROUND((MAX(LAT_N)-MIN(LAT_N))+(MAX(LONG_W)-MIN(LONG_W)),4)MAN_D
FROM STATION;

164. Consider and to be two points on a 2D plane where are the respective minimum and maximum values of Northern Latitude (LAT_N) and are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION.

Query the Euclidean Distance between points and and format your answer to display decimal digits.

SELECT ROUND(SQRT(POWER(MAX(LAT_N)-MIN(LAT_N),2)+POWER(MAX(LONG_W)-MIN(LONG_W),2)),4)EUC_D
FROM STATION;

SQRT() : 제곱근
POW()=POWER() : 거듭 제곱 ex) POW(2.4) : 2의 4제곱 =16

인사이트

• cast()
• replace()
• sqrt()
• power()
  이번 회차의 코드카타는 문제의 난이도가 높기보다는 처음보거나 사용해본적없는 함수를 활용해 해결해야하는 문제들이었다.