728x90
※ The STATION table is described as follows:
( where LAT_N is the northern latitude and LONG_W is the western longitude. )
■ [Basic Select] Weather Observation Station 1
https://www.hackerrank.com/challenges/select-by-id/problem?isFullScreen=tru
Q.
Query a list of CITY and STATE from the STATION table.
A.
SELECT CITY, STATE FROM STATION;
■ [Aggregation] Weather Observation Station 2
https://www.hackerrank.com/challenges/weather-observation-station-2/problem?isFullScreen=true
Q.
Query the following two values from the STATION table:
1. The sum of all values in LAT_N rounded to a scale of decimal places.
2. The sum of all values in LONG_W rounded to a scale of decimal places.
Your results must be in the form:
lat lon
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 decimal places.
A.
SELECT ROUND(SUM(LAT_N),2), ROUND(SUM(LONG_W),2) FROM STATION;
■ [Basic Select] Weather Observation Station 3
https://www.hackerrank.com/challenges/weather-observation-station-3/problem?isFullScreen=true
Q.
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.
A.
SELECT DISTINCT CITY FROM STATION WHERE MOD(ID,2) = 0;
■ [Basic Select] Weather Observation Station 4
https://www.hackerrank.com/challenges/weather-observation-station-4/problem?isFullScreen=true
Q.
Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
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
A.
SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION;
■ [Basic Select] Weather Observation Station 5
https://www.hackerrank.com/challenges/weather-observation-station-5/problem?isFullScreen=true
Q.
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.
A.
/*
SELECT
MIN(CITY) AS NM
,LENGTH(CITY) AS LEN
FROM
STATION
WHERE
LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION)
GROUP BY
LENGTH(CITY)
*/
----------------------------------------------------------
SELECT MIN(CITY) AS NM, LENGTH(CITY) AS LEN FROM STATION WHERE LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION) GROUP BY LENGTH(CITY)
UNION
SELECT MIN(CITY) AS NM, LENGTH(CITY) AS LEN FROM STATION WHERE LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION) GROUP BY LENGTH(CITY);/*
SELECT
CITY_NM_MIN
,CITY_LEN
FROM
(
SELECT
MIN(CITY) AS CITY_NM_MIN
,LENGTH(CITY) AS CITY_LEN
FROM
STATION
GROUP BY
LENGTH(CITY)
ORDER BY
LENGTH(CITY)
)
WHERE ROWNUM = 1
*/
----------------------------------------------------------
SELECT CITY_NM_MIN, CITY_LEN FROM (SELECT MIN(CITY) AS CITY_NM_MIN, LENGTH(CITY) AS CITY_LEN FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY)) WHERE ROWNUM = 1
UNION
SELECT CITY_NM_MIN, CITY_LEN FROM (SELECT MIN(CITY) AS CITY_NM_MIN, LENGTH(CITY) AS CITY_LEN FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY) DESC) WHERE ROWNUM = 1;/*
SELECT
CITY_NM_MIN
,CITY_LEN
FROM
(
SELECT
MIN(CITY_NM) AS CITY_NM_MIN
,CITY_LEN
FROM
(
SELECT
CITY AS CITY_NM
,LENGTH(CITY) AS CITY_LEN
FROM STATION
)
GROUP BY CITY_LEN
ORDER BY CITY_LEN
)
WHERE ROWNUM = 1
*/
----------------------------------------------------------
SELECT CITY_NM_MIN ,CITY_LEN FROM (SELECT MIN(CITY_NM) AS CITY_NM_MIN, CITY_LEN FROM (SELECT CITY AS CITY_NM, LENGTH(CITY) AS CITY_LEN FROM STATION) GROUP BY CITY_LEN ORDER BY CITY_LEN) WHERE ROWNUM = 1
UNION
SELECT CITY_NM_MIN, CITY_LEN FROM (SELECT MIN(CITY_NM) AS CITY_NM_MIN, CITY_LEN FROM (SELECT CITY AS CITY_NM, LENGTH(CITY) AS CITY_LEN FROM STATION) GROUP BY CITY_LEN ORDER BY CITY_LEN DESC) WHERE ROWNUM = 1;
■ [Basic Select] Weather Observation Station 6
https://www.hackerrank.com/challenges/weather-observation-station-6/problem?isFullScreen=true
Q.
Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.
A.
SELECT
DISTINCT CITY
FROM
STATION
WHERE
UPPER(CITY) LIKE 'A%'
OR UPPER(CITY) LIKE 'E%'
OR UPPER(CITY) LIKE 'I%'
OR UPPER(CITY) LIKE 'O%'
OR UPPER(CITY) LIKE 'U%';SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(UPPER(CITY),1,1) IN ('A','E','I','O','U');SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(UPPER(CITY),'^[AEIOU]');SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'^[AEIOU]','i');
■ [Basic Select] Weather Observation Station 7
https://www.hackerrank.com/challenges/weather-observation-station-7/problem?isFullScreen=true
Q.
Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.
A.
SELECT
DISTINCT CITY
FROM
STATION
WHERE
UPPER(CITY) LIKE '%A'
OR UPPER(CITY) LIKE '%E'
OR UPPER(CITY) LIKE '%I'
OR UPPER(CITY) LIKE '%O'
OR UPPER(CITY) LIKE '%U';SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(UPPER(CITY),-1,1) IN ('A','E','I','O','U');SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(UPPER(CITY),'[AEIOU]$');SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'[AEIOU]$','i');
■ [Basic Select] Weather Observation Station 8
https://www.hackerrank.com/challenges/weather-observation-station-8/problem?isFullScreen=true
Q.
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.
A.
SELECT
DISTINCT CITY
FROM
STATION
WHERE
(UPPER(CITY) LIKE 'A%'
OR UPPER(CITY) LIKE 'E%'
OR UPPER(CITY) LIKE 'I%'
OR UPPER(CITY) LIKE 'O%'
OR UPPER(CITY) LIKE 'U%')
AND
(UPPER(CITY) LIKE '%A'
OR UPPER(CITY) LIKE '%E'
OR UPPER(CITY) LIKE '%I'
OR UPPER(CITY) LIKE '%O'
OR UPPER(CITY) LIKE '%U');SELECT
DISTINCT CITY
FROM
STATION
WHERE
SUBSTR(UPPER(CITY),1,1) IN ('A','E','I','O','U')
AND SUBSTR(UPPER(CITY),-1,1) IN ('A','E','I','O','U');SELECT DISTINCT CITY
FROM STATION
WHERE REGEXP_LIKE(CITY,'^[AEIOU].*[AEIOU]$','i'); -- '^[AEIOU](.)*[AEIOU]$'
-- ( 정규 표현식 쓸 때 간과한 게 있었음 '^[AEIOU][A-Z]*[AEIOU]$' 에서 가운데 글자가 꼭 알파벳이 아닐 수도 있음.
-- 공백 문자 등 특수문자일 수도 있다는 의미 )
■ [Basic Select] Weather Observation Station 9
https://www.hackerrank.com/challenges/weather-observation-station-9/problem?isFullScreen=true
Q.
Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.
A.
SELECT
DISTINCT CITY
FROM
STATION
WHERE
UPPER(CITY) NOT LIKE 'A%'
AND UPPER(CITY) NOT LIKE 'E%'
AND UPPER(CITY) NOT LIKE 'I%'
AND UPPER(CITY) NOT LIKE 'O%'
AND UPPER(CITY) NOT LIKE 'U%';SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(UPPER(CITY),1,1) NOT IN ('A','E','I','O','U');SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'^[^AEIOU]','i');
■ [Basic Select] Weather Observation Station 10
https://www.hackerrank.com/challenges/weather-observation-station-10/problem?isFullScreen=true
Q.
Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.
A.
SELECT
DISTINCT CITY
FROM
STATION
WHERE
UPPER(CITY) NOT LIKE '%A'
AND UPPER(CITY) NOT LIKE '%E'
AND UPPER(CITY) NOT LIKE '%I'
AND UPPER(CITY) NOT LIKE '%O'
AND UPPER(CITY) NOT LIKE '%U';SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(UPPER(CITY),-1,1) NOT IN ('A','E','I','O','U');SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'[^AEIOU]$','i');
■ [Basic Select] Weather Observation Station 11
https://www.hackerrank.com/challenges/weather-observation-station-10/problem?isFullScreen=true
Q.
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.
A.
SELECT
DISTINCT CITY
FROM
STATION
WHERE
(UPPER(CITY) NOT LIKE 'A%'
AND UPPER(CITY) NOT LIKE 'E%'
AND UPPER(CITY) NOT LIKE 'I%'
AND UPPER(CITY) NOT LIKE 'O%'
AND UPPER(CITY) NOT LIKE 'U%')
OR
(UPPER(CITY) NOT LIKE '%A'
AND UPPER(CITY) NOT LIKE '%E'
AND UPPER(CITY) NOT LIKE '%I'
AND UPPER(CITY) NOT LIKE '%O'
AND UPPER(CITY) NOT LIKE '%U');SELECT
DISTINCT CITY
FROM
STATION
WHERE
SUBSTR(UPPER(CITY),1,1) NOT IN ('A','E','I','O','U')
OR SUBSTR(UPPER(CITY),-1,1) NOT IN ('A','E','I','O','U');SELECT DISTINCT CITY
FROM STATION
WHERE REGEXP_LIKE(CITY,'(^[^AEIOU].*|.*[^AEIOU]$)','i'); -- '((^[^AEIOU].*)|(.*[^AEIOU]$))'
■ [Basic Select] Weather Observation Station 12
https://www.hackerrank.com/challenges/weather-observation-station-12/problem?isFullScreen=true
Q.
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.
A.
SELECT
DISTINCT CITY
FROM
STATION
WHERE
(UPPER(CITY) NOT LIKE 'A%'
AND UPPER(CITY) NOT LIKE 'E%'
AND UPPER(CITY) NOT LIKE 'I%'
AND UPPER(CITY) NOT LIKE 'O%'
AND UPPER(CITY) NOT LIKE 'U%')
AND
(UPPER(CITY) NOT LIKE '%A'
AND UPPER(CITY) NOT LIKE '%E'
AND UPPER(CITY) NOT LIKE '%I'
AND UPPER(CITY) NOT LIKE '%O'
AND UPPER(CITY) NOT LIKE '%U');SELECT
DISTINCT CITY
FROM
STATION
WHERE
SUBSTR(UPPER(CITY),1,1) NOT IN ('A','E','I','O','U')
AND SUBSTR(UPPER(CITY),-1,1) NOT IN ('A','E','I','O','U');SELECT DISTINCT CITY
FROM STATION
WHERE REGEXP_LIKE(CITY,'^[^AEIOU].*[^AEIOU]$','i');
반응형