[SQL코드카타] 130 ~ 137번 (HackerRank)

<HackerRank>

(130번) select-by-id

SELECT *
FROM CITY
WHERE ID = 1661 ;

 

(131번) japanese-cities-attributes

> in과 like 관하여

SELECT *
FROM CITY
WHERE COUNTRYCODE LIKE 'JPN%' ;

 

(132번) japanese-cities-name

SELECT NAME
FROM CITY
WHERE COUNTRYCODE LIKE 'JPN%' ;

 

(133번) weather-observation-station-1

SELECT CITY, STATE
FROM STATION ;

 

(134번) weather-observation-station-2

SELECT ROUND(SUM(LAT_N),2) LAT, 
       ROUND(SUM(LONG_W),2) LON
FROM STATION ;

 

(135번) weather-observation-station-3

> mod(a,b) = a/b의 나머지

SELECT DISTINCT CITY
FROM STATION
WHERE MOD(ID,2) = 0;

 

(136번) weather-observation-station-4

SELECT COUNT(city) - COUNT(DISTINCT city)
FROM STATION ;

 

(137번) weather-observation-station-5

SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE 1=1
      AND LENGTH(CITY) = (SELECT MIN(LENGTH(CITY))
                          FROM STATION)
      AND CITY LIKE 'A%'
      OR LENGTH(CITY) = (SELECT MAX(LENGTH(CITY))
                         FROM STATION);
-- 또 다른 답 (ROWNUM = 1 활용)
SELECT CITY, LENGTH(CITY)
FROM (SELECT CITY, LENGTH(CITY)
      FROM STATION
      ORDER BY LENGTH(CITY), CITY ASC)
WHERE ROWNUM = 1 ;
SELECT CITY, LENGTH(CITY)
FROM (SELECT CITY, LENGTH(CITY)
      FROM STATION
      ORDER BY LENGTH(CITY) DESC, CITY ASC)
WHERE ROWNUM = 1 ;