☑️ Weather Observation Station 9
Query the list of CITY names from STATION that do not start with vowels.
Your result cannot contain duplicates.
SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT LIKE 'A%'
AND CITY NOT LIKE 'E%'
AND CITY NOT LIKE 'I%'
AND CITY NOT LIKE 'O%'
AND CITY NOT LIKE 'U%'
[정규표현식]
select distinct city
from station
where city NOT REGEXP '^[aeiou].*' ☑️ Weather Observation Station 10
Query the list of CITY names from STATION that do not end with vowels.
Your result cannot contain duplicates.
SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT LIKE '%A'
AND CITY NOT LIKE '%E'
AND CITY NOT LIKE '%I'
AND CITY NOT LIKE '%O'
AND CITY NOT LIKE '%U'☑️ Japan Population
Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.
SELECT SUM(POPULATION)
FROM CITY
WHERE COUNTRYCODE = 'JPN'☑️ Top Competitors
1) 2개 이상의 Chanllenge(문제)에서 Full score(만점)을 받은 Hacker(참가자)들의 이름과 hacker_id 출력
2) 결과 화면을 full score를 받은 chanllenge의 개수를 기준으로 내림차순 정렬
3) 만약 full score 받은 chanllenge 개수가 동일하다면 hacker_id기준으로 오름차순 정렬
- full score(만점)을 받은 챌린지가 2개 이상의 참가자들을 찾고
- 이 때 만점이란, Submissions 테이블의 score 값과 difficulty 테이블의 score 값이 일치하는 것을 의미
- 2가지 정렬 기준 만족해서 출력하기
(https://hanawithdata.tistory.com/entry/%ED%95%B4%EC%BB%A4%EB%9E%AD%ED%81%AC-Top-Competitors)
SELECT S.HACKER_ID, H.NAME
FROM SUBMISSIONS S
INNER JOIN CHALLENGES C ON S.CHALLENGE_ID = C.CHALLENGE_ID
INNER JOIN DIFFICULTY D ON C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
INNER JOIN HACKERS H ON S.HACKER_ID = H.HACKER_ID
WHERE D.SCORE = S.SCORE
GROUP BY S.HACKER_ID, H.NAME
HAVING COUNT(S.CHALLENGE_ID) > 1
ORDER BY COUNT(S.CHALLENGE_ID) DESC , S.HACKER_ID
ㄴ 왜 S.HACKER_ID = C.HACKER_ID로 조인하면 안되는거지?☑️ Weather Observation Station 11
Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.
<내가 작성한 답>
SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) NOT IN ('A','E','I','O','U')
OR RIGHT(CITY,1)NOT IN ('A','E','I','O','U')☑️ Contest Leaderboard
The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of from your result.
- 문제 해결 포인트
total score 개념 = 같은 문제(challenge)에 대해 점수 2개가 있다면 최댓값을 더해준다.
예를 들어 이 경우, 존의 21번 문제 80점과 23번 문제중 더 높은 점수인80점을 더해서 70+80=150점이 total score 인 것이다.
ㄴ 이 부분을 서브쿼리로 만들어줬다.
SELECT S.HACKER_ID, H.NAME, SUM(S.MAX_SCORE) AS TOTAL_SCORE
FROM
( SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) AS MAX_SCORE
FROM SUBMISSIONS
GROUP BY HACKER_ID, CHALLENGE_ID) AS S
INNER JOIN HACKERS H ON S.HACKER_ID = H.HACKER_ID
GROUP BY S.HACKER_ID, H.NAME
HAVING TOTAL_SCORE > 0
ORDER BY TOTAL_SCORE DESC, S.HACKER_ID☑️ The Blunder
Query the following two values from the STATION table:
The sum of all values in LAT_N rounded to a scale of decimal places.
The sum of all values in LONG_W rounded to a scale of decimal places.
SELECT ROUND(SUM(LAT_N),2), ROUND(SUM(LONG_W),2)
FROM STATION☑️ 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☑️ Weather Observation Station 14
Query the greatest value of the Northern Latitudes (LAT_N) from STATION that is less than . Truncate your answer to decimal places.
SELECT TRUNCATE(MAX(LAT_N),4)
FROM STATION
WHERE LAT_N < 137.2345☑️ Weather Observation Station 16
Query the smallest Northern Latitude (LAT_N) from STATION that is greater than . Round your answer to decimal places.
SELECT ROUND(MIN(LAT_N),4)
FROM STATION
WHERE LAT_N > 38.7780☑️ Weather Observation Station 17
Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than . Round your answer to decimal places.
ㄴ조건1.LAT_N이 38.7780 보다 큰 것
ㄴ조건2. 조건1중에 가장 작은 LAT_N
<풀이1> 서브쿼리
SELECT ROUND(LONG_W, 4)
FROM STATION
WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780)
<풀이2> ORDER BY
SELECT ROUND(LONG_W, 4)
FROM STATION
WHERE LAT_N > 38.7880
ORDER BY LAT_N
LIMIT 1;☑️ Weather Observation Station 18
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(ABS(MIN(LAT_N)-MAX(LAT_N))+ABS(MIN(LONG_W)-MAX(LONG_W)),4)
FROM STATIONㄴ ABS 함수: 절대값
☑️ Weather Observation Station 19
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(POW(MIN(lat_n)-MAX(lat_n),2) + POW(MIN(long_w)-MAX(long_w),2)),4)
from stationㄴ 참고: https://jogrammer.tistory.com/254
☑️The Blunder
SELECT CEIL(AVG(SALARY) - AVG(REPLACE(SALARY,0,'')))
FROM EMPLOYEES함수정리
- CEIL(숫자) : 올림
- ROUND(숫자,(자릿수)) : 반올림
- TRUNCATE(숫자, 자릿수) : 내림
- FLOOR(숫자) : 소숫점 아래 버림
- ABS(숫자) : 절대값
- POW(X,Y) : X의 Y승
- MOD(X,Y) : X를 Y로 나눈 나머지
[문자열 변경 함수]
REPLACE('문자열','기존의 문자열','변경할 문자열')
