SQL 오답노트 : FROM 에 alias, mod

https://www.hackerrank.com/challenges/weather-observation-station-3/problem?isFullScreen=true

Weather Observation Station 3 | HackerRank

 

1.FROM ~ AS 

 

FROM STATION AS ST (X)

 

FROM STATION ST(O)

 

2. MODE, MOD & ==, =

 

MODE(ID, 2) == 0 (X)

MOD(ID, 2) = 0 (O)

 

 

3. SELECT문에 함수 넣기

 

SELECT COUNT(DISTINCT(CITY))

 

4. rownum & WHERE( 서브쿼리 )

 

 

 

SELECT CITY, LENGTH(CITY) AS CITY_LENGTH
FROM (
    SELECT CITY, LENGTH(CITY) AS CITY_LENGTH
    FROM STATION
    WHERE LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION)
    ORDER BY CITY
) WHERE ROWNUM = 1


UNION ALL


SELECT CITY, LENGTH(CITY) AS CITY_LENGTH
FROM (
    SELECT CITY, LENGTH(CITY) AS CITY_LENGTH
    FROM STATION
    WHERE LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION)
    ORDER BY CITY
) WHERE ROWNUM = 1;

 

5. select case when

 

SELECT 
    addr AS 원래주소,

    CASE 
        WHEN addr LIKE '평택%' AND addr LIKE '%문곡리%' THEN REPLACE(addr, '문곡리', '문가리')
        ELSE addr
    END AS 변경주소

FROM food_orders fo;