Saturday 24 October 2015

'Shortest' and 'Longest' cities name

Given a table STATION that holds data for five fields namely ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.
+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------+
 Write a query to print shortest and longest cities name. If there are more than one cities print lexicographical smallest name.

>>(select CITY, char_length(CITY) as len_city from STATION order by len_city limit 1) union all (select CITY, char_length(CITY) as len_city from STATION order by len_city desc limit 1) order by len_city;

Thanks.

2 comments: