쉬운 문제부터 스스로! 빨리! 푸는거 오늘 남은 시간을 알차게 보내보자!!
시작~~~
Weather Observation Station 1 (DIFFICULTY : Easy, SKILL : Basic)
Query a list of CITY and STATE from the STATION table. The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. 문제해석 1) STATION 테이블에서 CITY, STATE를 조회하기 |
SELECT CITY, STATE
FROM STATION
;
Weather Observation Station 2 (DIFFICULTY : Easy, SKILL : Basic)
Query the following two values from the STATION table: The sum of all values in LAT_N rounded to a scale of 2 decimal places. The sum of all values in LONG_W rounded to a scale of 2 decimal places. Input Format The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. Output Format Your results must be in the form:  where LAT is the sum of all values in LAT_N and LON is the sum of all values in LONG_W. Both results must be rounded to a scale of 2 decimal places. 문제해석 1) LAT : LAT_N의 총 합계를 소수점 2자리 수로 표현하기 2) LON : LONG_W의 총 합계를 소수점 2자리 수로 표현하기 |
SELECT ROUND(SUM(LAT_N), 2) AS LAT, ROUND(SUM(LONG_W), 2) AS LON
FROM STATION
;
Weather Observation Station 3 (DIFFICULTY : Easy, SKILL : Basic)
Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer. The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. 문제해석 1) ID 번호가 짝수인 도시에 대해 STATION에서 CITY 목록을 조회하기 2) 결과를 순서에 상관없이 출력하기 3) 중복 제거하기 |
SELECT DISTINCT CITY
FROM STATION
WHERE MOD(ID, 2) = 0
;
Weather Observation Station 4 (DIFFICULTY : Easy, SKILL : Basic)
Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table. The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. For example, if there are three records in the table with CITY values 'New York', 'New York', 'Bengalaru', there are 2 different city names: 'New York' and 'Bengalaru'. The query returns 1, because total number of records - number of unique city names = 3 - 2 = 1. 문제해석 1) 'CITY의 총 개수'에서 'CITY의 DISTINCT 개수'를 뺀 값 구하기 |
SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
FROM STATION
;
Weather Observation Station 5 (DIFFICULTY : Easy, SKILL : Intermediate)
Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically. The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. Sample Input For example, CITY has four entries: DEF, ABC, PQRS and WXY. Sample Output  Explanation
When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths 3, 3, 4 and 3. The longest name is PQRS, but there are 3 options for shortest named city. Choose ABC, because it comes first alphabetically. Note You can write two separate queries to get the desired output. It need not be a single query. 문제해석 1) STATION 테이블에서 CITY 이름이 가장 짧은 곳, 가장 긴 곳 조회하기 2) 가장 작거나 큰 도시 이름이 두 개 이상이면 알파벳 순서에서 가장 앞에 오는 도시를 선택하기 3) 원하는 출력값을 도출하기 위해서 쿼리 두 개를 작성해도 괜찮음 |
SELECT CITY, LEN_CITY
FROM (
SELECT CITY, LENGTH(CITY) AS LEN_CITY
, ROW_NUMBER() OVER (ORDER BY CITY ASC) AS ROW_NUM
FROM STATION
WHERE LENGTH(CITY) IN (SELECT MIN(LENGTH(CITY)) FROM STATION)
) A
WHERE ROW_NUM = 1
;
SELECT CITY, LEN_CITY
FROM (
SELECT CITY, LENGTH(CITY) AS LEN_CITY
, ROW_NUMBER() OVER (ORDER BY CITY ASC) AS ROW_NUM
FROM STATION
WHERE LENGTH(CITY) IN (SELECT MAX(LENGTH(CITY)) FROM STATION)
) A
WHERE ROW_NUM = 1
;
Weather Observation Station 6 (DIFFICULTY : Easy, SKILL : Basic)
Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates. Input Format The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. 문제해석 1) STATION 테이블에서 CITY 이름이 모음(a, e, i, o, u)로 시작하는 목록 구하기 2) 중복 제거하기 |
SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTRING(CITY, 1, 1) IN ('a', 'e', 'i', 'o', 'u')
;
Weather Observation Station 7 (DIFFICULTY : Easy, SKILL : Basic)
Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates. Input Format The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. 문제해석 1) STATION 테이블에서 CITY 이름이 모음(a, e, i, o, u)로 끝나는 목록 구하기 2) 중복 제거하기 |
SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u')
;
Weather Observation Station 8 (DIFFICULTY : Easy, SKILL : Basic)
Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates. Input Format The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. 문제해석 1) STATION 테이블에서 CITY 이름이 모음(a, e, i, o, u)이 첫문자이면서 끝문자인 목록 구하기 2) 중복 제거하기 |
SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u')
AND RIGHT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u')
;
Weather Observation Station 9 (DIFFICULTY : Easy, SKILL : Basic)
Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates. Input Format The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. 문제해석 1) STATION 테이블에서 CITY 이름이 모음(a, e, i, o, u)로 시작하지 않는 목록 구하기 2) 중복 제거하기 |
SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u')
;
Weather Observation Station 10 (DIFFICULTY : Easy, SKILL : Basic)
Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates. Input Format The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. 문제해석 1) STATION 테이블에서 CITY 이름이 모음(a, e, i, o, u)로 끝나지 않는 목록 구하기 2) 중복 제거하기 |
SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u')
;
Weather Observation Station 11 (DIFFICULTY : Easy, SKILL : Basic)
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. Input Format The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. 문제해석 1) STATION 테이블에서 CITY 이름이 모음(a, e, i, o, u)로 시작하지도 않거나 또는 끝나지 않는 목록 구하기 2) 중복 제거하기 |
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')
;
Weather Observation Station 12 (DIFFICULTY : Easy, SKILL : Basic)
Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates. Input Format The STATION table is described as follows:  where LAT_N is the northern latitude and LONG_W is the western longitude. 문제해석 1) STATION 테이블에서 CITY 이름이 모음(a, e, i, o, u)로 시작하지도 않고 끝나지도 않는 목록 구하기 2) 중복 제거하기 |
SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u')
AND RIGHT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u')
;
느낀점/개선점
| 추가 조사 | - |
| 느낀점 | DIFFICULTY - Medium, SKILL - Intermediate 단계 문제는 술술 풀리지가 않는다. DIFFICULTY - Easy, SKILL - Basic 단계를 술술 풀면서 자신감을 좀 찾자. |
| 개선점 | - |
