Problem:
Query the two cities in STATION with the Shortest & 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.
The station table is described as follows:
STATION
| Field | Type |
|---|---|
| ID | NUMBER |
| CITY | VARCHAR2(21) |
| STATE | VARCHAR2(2) |
| LAT_N | NUMBER |
| LONG_W | NUMBER |
where LAT_N is the northern latitude and LONG_W is the western longitude.
For example, CITY has four entries: DEF, ABC, PQRS, and WXY.
Sample Output
ABC 3
PQRS 4
When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths 3, 3, 4, and 3. The longest name is PQRS, but there are 3 options for shortest named city. Choose ABC, because it comes first alphabetically.
Note
You can write two separate queries to get the desired output. It need not be a single query.
Solution 1:
select CITY, length(CITY) as len_city from STATION order by len_city, CITY limit 1;
select CITY, length(CITY) as len_city from STATION order by len_city desc, CITY limit 1;Solution 2:
(select CITY, length(CITY) as len_city from STATION order by len_city, CITY limit 1)
union
(select CITY, length(CITY) as len_city from STATION order by len_city desc, CITY limit 1);Explanation: Solution 1
Since in question given that you can use two separate queries output. that’s why I used two separate queries.
(select CITY, length(CITY)
step 1 – as per the question we have to find city names with length so I selected city and length of city with length(city).
order by len_city, CITY limit 1;
step 2 – but we want only those city names that have the Shortest and Longest lengths so we use order by clause on len_city which is an alias of length(city) and limit to 1 for only one result. (in the first query we will get the shortest city name)
order by len_city desc, CITY limit 1;
step 3 – (in the second query) we want the longest city name as well so for that I sorted len_city in descending order with DESC now we will get the longest city name first. and to get that only one so I use limit 1.
Explanation: Solution 2
there is no more difference between solution 1 and solution 2.
In Solution 2 we used a union operator. and we combine solution 1’s two queries as a union.
if we use the union operator then we have to write the first and second parts in round brackets ().
Note: order by column_name is the same as order by column_name asc