Nsryjdtyk

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

    );

It took me a while to think about the answer and how to present it in a presentable manner. The following works just fine:

SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY ASC LIMIT 1;
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC LIMIT 1;

I think, presentation matters. My solution shows each parts separately in a human readable manner. The steps show how to break the problem before we begin to solve it

Idea : Find all cities that have the smallest length.

STEPS:

• Find the minimum length of city .


• Get all cities that have the MIN Length.


• Sort by CITY .


• Output the first result only.

Repeat same for MAX

select CITY, length(CITY) from STATION where length(CITY) = (

    select MIN(length(CITY)) from STATION 

    ) order by CITY limit 1;



select CITY, length(CITY) from STATION where length(CITY) = (

    select MAX(length(CITY)) from STATION 

    ) order by CITY limit 1;

I spent a while on this one and here is what I came up with:

    SELECT CITY, LENGTH(CITY) 

    FROM STATION 

    WHERE LENGTH(CITY)=(SELECT MIN(LENGTH(CITY)) FROM STATION) 

    ORDER BY CITY ASC LIMIT 1;



    SELECT CITY, LENGTH(CITY) 

    FROM STATION 

    WHERE LENGTH(CITY)=(SELECT MAX(LENGTH(CITY)) FROM STATION);

The reason I tried this is because I was reading on the MySQL Reference Manual about nesting WHERE subqueries, then I looked into it further and I found an article that digs into it further https://community.modeanalytics.com/sql/tutorial/sql-subqueries/ I drew inspiration under the subheading "Subqueries in Conditional Logic". I hope this helps someone.