Summary

SQL Lesson: Weather Observation Station 5

In this lesson, we will solve the HackerRank problem "Weather Observation Station 5" from the SQL basic select subcategory.

Problem Overview

We need to query the station table to provide two rows:

The city with the shortest name.
The city with the longest name.

Both rows should include the city name and its length.

Example

For city names:

DEF
ABC
PQRS
WXY

The expected output:

Shortest name: ABC (3 characters).
Longest name: PQRS (4 characters).

If two cities have the same length, select the one that comes first alphabetically.

Query Steps

To tackle this problem, we can use two sub-queries:

Shortest City Name Query:
- Use ORDER BY to order rows by the length of the city name (ascending).
- Use LIMIT 1 to select the first row.
- Add a secondary ORDER BY clause to sort alphabetically if lengths are equal.
```
SELECT city, LENGTH(city) 
FROM station 
ORDER BY LENGTH(city), city 
LIMIT 1;
```

Longest City Name Query:

Similar to the shortest, but use descending order.

SELECT city, LENGTH(city) 
FROM station 
ORDER BY LENGTH(city) DESC, city 
LIMIT 1;

Both queries will yield the required city names and their lengths.

Conclusion

After implementing the queries and running the tests, the solution was successfully passed. This completes the lesson on the Weather Observation Station 5 problem.

Video Transcript

In this lesson I will solve the problem weather observation station 5 from the subcategory basic select in the SQL Category Hacker Rank. So let's get started. So we're asked to query the table station and provide two rows. The first row being the city with the shortest name, the second row the city with the longest name and as the second column you also want to show the length. So as you can see if you scroll down here's an example. If you have four rows with the city names DEF, ABC, PQRS, WXY you have the sample output here. So the first row is for the shortest length. In this case we have DEF and ABC but which one do we choose? The problem states that we should pick. If they have the same length we should pick the one that's first alphabetically. So A comes first before the D so therefore should choose ABC and because there are three characters in ABC we have to show as the second column three here. Now for the next row that's the longest. Now we have PQRS as the longest. We have four characters so we have to say for the first column the name of the city PQRS then from the second column the length of PQRS that's four characters. So we can do two sub-queries to solve this problem as you can see in the note here. So let's get started and if you want to choose I'm going to choose my sequel and I'm going to go here and select star from station just so we can see what the data looks like. And we can see the there is like this. Now if you want to get the shortest and the longest city names there's a technique we can use. We can use order by to order all the rows by a certain column and then we can get just the first column the first row and we combine order by with limit one so we can get only one at the top. And we can do that for the shortest and for the longest. One we will sort the length ascending and get the first row that's the shortest. The other one we're going to sort by length descending and we get the first row that's the longest. So let's get started with the first query for the shortest. So we can go here and say order by and then we want to order by the city name like so. And if you say limit one that will only take the first row. So if I order by city name it's going to be alphabetically the first and then going down but that's only the secondary thing we want to order by. We actually want the length right so we can call length function here like so. So that means you're going to order these records by the length of the city and by default ascending so ASC is implicit here you don't have to say it. So the very first row we have the shortest length while the last row will have the longest length. And when you say limit one you take the very first one. So as you can see here we have this city rye three characters and all these extra columns that we don't need. So if you want to narrow down here you can say city but we also need to add a number of characters so I can say comma length of city as the second column. Let's run the code and see what we get. We should get rye followed by the second column three. Okay we got that but our answer is wrong because we have to think about what if there's also another city with the same number of characters how do we pick which one is going to be. Well if we have the same order number of characters here you have to add a secondary order by and that's when you add a comma after the first. So if they happen to have the same length we should also sort by this second column here and that's going to be city. We want to sort by the name of the city right alphabetically and that's ascending by default when I pick the one with the very first letter in the alphabet. If you compare them the letter has to come before so that's the default ascending. Okay so we got ammo underscore three there and that's nine we still got the wrong answer because we need to do longest as well. So we're going to follow the same pattern so we're going to select we want the city name followed by the length of the city from the table name station and we want to order by length of city but in this case to get the longest I'm going to order by the length but descending so that the very first column is the one of the longest so I'm going to say DESC right after length of city so it can already descending. You want to limit that to one so we got that very first row and also add the secondary sorting here if they happen to have the same length you're going to order by city ascending alphabetically right. Okay now we got the task passing and let's click submit. And it's a success and that's weather observation station five.