Shortest and longest city name and alphabetical order Problem
  
  Query the two cities in STATION with the shortest and 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.
  
  Sample Input
  
  Let's say that CITY only has four entries: DEF, ABC, PQRS and WXY
  
  Sample Output
  
  ABC 3 PQRS 4


Can I get shorter code in MySQL and also an optimal Oracle query? There's no way to have only one sub-query for both min and max, is there?

select city, char_length(city) from STATION 
 where city = (select min(city) from STATION
     where char_length(city) = (select min(char_length(city)) from STATION)) 

 or city = (select min(city) from STATION
     where char_length(city) = (select max(char_length(city)) from STATION));
Formatting

Your SQL statement formatting is not very good. I would encourage you to use a free tool like sql-format.com (or one of the many others) to format your SQL queries in a more readable way. This is your original query, with better formatting (I indented the subqueries by hand because the tool didn't).

SELECT
    city,
    CHAR_LENGTH(city)
FROM STATION
WHERE city = (
    SELECT
        MIN(city)
    FROM STATION
    WHERE CHAR_LENGTH(city) = (
        SELECT
            MIN(CHAR_LENGTH(city))
        FROM STATION
    )
)
OR city = (
    SELECT
        MIN(city)
    FROM STATION
    WHERE CHAR_LENGTH(city) = (
        SELECT
            MAX(CHAR_LENGTH(city))
        FROM STATION
    )
);


Now we can see much more easily how deeply nested queries are.



Variables

You could make the code simpler by using a few user-defined variables:

SET @MinCityLen = (SELECT MIN(CHAR_LENGTH(city)) FROM STATION);
SET @MaxCityLen = (SELECT MAX(CHAR_LENGTH(city)) FROM STATION);
/* Query below only to demonstrate the variables */
SELECT 
  '@MinCityLen' AS `VariableName`, 
  @MinCityLen AS `Value`
UNION
SELECT 
  '@MaxCityLen', 
  @MaxCityLen;


Which returns this:


VariableName  Value
@MinCityLen   3
@MaxCityLen   4



This will abstract away one level of nesting and make the query simpler to understand. Note that the @ symbol is just a convention, it is not needed as far as MySQL syntax goes. Some database systems do require the @ symbol though, the most famous being Microsoft SQL Server.



Shorter code != Better code

Sometimes, better code is longer rather than shorter. This is often the case with SQL. Longer code can be better formatted, better documented, better abstracted, better structured. In my personal SQL experience, short queries, unless they are very simple, are often pretty bad. Yours is not a case of a very simple query. 

This is what I came up with. I also have a link on sqlfiddle

# find shortest city name
SET @MinCityLen = (SELECT MIN(CHAR_LENGTH(city)) FROM STATION);
# find longest city name
SET @MaxCityLen = (SELECT MAX(CHAR_LENGTH(city)) FROM STATION);

SELECT
    city,
    CHAR_LENGTH(city)
FROM 
    STATION
WHERE 
    # find shortest city name sorted alphabetically
    city = (
        SELECT
            city
        FROM STATION
        WHERE CHAR_LENGTH(city) = @MinCityLen
        ORDER BY city ASC
        LIMIT 1
    )
    # find longest city name sorted alphabetically
    OR city = (
        SELECT
            MIN(city)
        FROM STATION
        WHERE CHAR_LENGTH(city) = @MaxCityLen
        ORDER BY city ASC
        LIMIT 1
    );