Summary

WebDor Observation Station 14 - SQL Lesson Summary

In this lesson, we focus on solving a problem from the SQL aggregation section on HackerRank. The task involves querying a station table with the following columns: ID, city, state, lat_n, and long_w.

Problem Statement

We need to retrieve the greatest value of lat_n that is less than 137.2345, truncating the result to four decimal places.

Key Considerations

Filtering Values: We need to filter for lat_n values that are less than 137.2345.
Truncation: The result should be truncated to four decimal places, which means dropping any digits beyond the fourth decimal without rounding.
Finding Maximum: We are looking for the maximum value within the filtered results.

SQL Approach

Start by exploring the data with the query:
```
SELECT * FROM station;
```
Apply the WHERE clause to filter out values that meet the criteria:
```
WHERE lat_n < 137.2345
```

Use the MAX function to get the greatest value:

SELECT MAX(lat_n) FROM station WHERE lat_n < 137.2345;

To truncate to four decimal places, incorporate the TRUNCATE function:

SELECT TRUNCATE(MAX(lat_n), 4) FROM station WHERE lat_n < 137.2345;

This query results in 137.0193, correctly truncated to four decimal places.

Conclusion

The final SQL query effectively retrieves the desired value while adhering to the requirements of filtering, truncating, and finding the maximum value. This method ensures we handle the data correctly and return the required results.

Video Transcript

Hey everybody, welcome to another lesson. I will do WebDor observation station 14 from the section aggregation from SQL in Hacker Rank. So this question we have the station table with ID city state of lat underscore n and longer underscore w columns. And we're asked to query the greatest value of lat underscore n that is less than 137.2345 truncate to four decimal places. So I got three things to keep in mind, right? So filter out meaning you only take the values for the purpose of this that's less than 137.2345 for lat underscore n. And at the end we should also truncate to four decimal places, meaning we drop everything after the fourth digit to the right of the dot. It's different from round, so we use truncate instead of round to be technically correct. And yeah, greatest value just means taking the maximum value. So there are many different ways to do this problem, I'll do with the max function. So I always like to start here, let me choose my SQL select star from station to visualize what the data looks like and have a better idea of what to do. Okay, so the purposes of this question, we care about the lat underscore n column here, I see these values. So trying to find the greatest one that's less than 137.2345. So let's go here. First let's add a where lat underscore n has to be less than 137.2345. But the value is greater than that, we don't care about it, we won't consider it. And then instead of star here, I'm going to first say lat n, but that will just take us all the rows of the lat n values, but we have to take the maximum one. So one way of doing this, it could order by the value itself and take the very first row if you are descending for the greatest, but we can also use the max function here. So if I say max parenthesis of lat n, that will take the maximum value that is the greatest that it can find within the selection range that for this where here. And because we want to truncate, meaning you want to drop all the digits to the right of the fourth one after the dot coming from the left, right? We have to say truncate, past max lat n comma four, that means only keep four decimal places. And that's different from round round around up or down truncate just drops the digits without rounding. Let's run the code. And there you go. We got 137.0193 and we got truncated. We only have four digits to the right hand side of the dots. And that's Watter observation station 14.