The Easy City 1 [NCTU Floor 22]

# 7) Using regex to select city starts with all letters and ends with vowels
SELECT COUNT(city) - COUNT(DISTINCT city)
    FROM citytable WHERE city RLIKE '^[nz].*[^aiueo]$'
        AND lat > 121.5 AND lon < 25.3;

# 8) Update lat to p*lat, lon to q*lon using parity checking
UPDATE citytable
    SET lat = lat*2, lon = lon*5
    WHERE CASE
        WHEN 'od' = 'ev' THEN (id % 2) = 0
        ELSE id % 2 = 1 END;

# 9) Find sum of lat, lon, and difference between biggest and smallest city
SELECT ROUND(SUM(lat), 3), ROUND(SUM(lon), 3), MAX(LENGTH(city)) - MIN(LENGTH(city))
    FROM (SELECT*FROM citytable
            WHERE CASE
                WHEN 'even' = 'even' THEN (id % 2) = 0
                ELSE id % 2 = 1 END
            ORDER BY LENGTH(city) DESC, city DESC LIMIT 5) AS newTable;