Loading
Lesson 07
Courses / HackerRank SQL Problem Solving Solution Challenges
Weather Observation Station 7 | SQL Basic Select | HackerRank Solution

Summary

Summary of Weather Observation Station 7 Exercise

In this lesson, the presenter discusses the SQL exercise "Weather Observation Station 7" from HackerRank, focusing on how to select city names from a database where the names end with a vowel (A, E, I, O, U) while ensuring no duplicates in the result.

SQL Exercise Overview

  • Table Structure: The table includes columns: ID, city, state, lat, and long.
  • Objective: List distinct city names that end with a vowel.

Solutions

Two SQL solutions are demonstrated:

1. Using LIKE

  • Query Steps:
    • Select the city column with the DISTINCT keyword to avoid duplicates.
    • Use the WHERE clause combined with LIKE to filter city names ending in vowels:
      SELECT DISTINCT city
      FROM station
      WHERE city LIKE '%a' 
         OR city LIKE '%e' 
         OR city LIKE '%i' 
         OR city LIKE '%o' 
         OR city LIKE '%u';
      

2. Using Regular Expressions

  • A more concise solution using regular expressions is also provided:
  • Query Steps:
    • Utilize REGEXP to match city names with a pattern that captures endings with vowels. The pattern defined includes uppercase and lowercase vowels:
      SELECT DISTINCT city
      FROM station
      WHERE city REGEXP '[aeiou]$';
      

Conclusion

Both solutions effectively accomplish the task, though the regular expression method is more succinct, highlighting the power of regex for pattern matching in SQL.

The presenter concludes by submitting the final SQL code for validation, completing the exercise.

Video Transcript

Hey everybody, welcome to another lesson. In this lesson I will do the exercise weather observation station 7 from the subsection basic select of the section SQL from HackerRank. In this question we're given the stable station that has the columns ID, city, state, lat, and long w. For the purpose of this question we're going to care about the city column. So we're asked to list all the city names that start, that actually end with a vowel, that is A, E, I, O, or U character. And the result cannot contain the duplicate, so that's a distinct city, right? So let's get started. I'm going to do two solutions in my SQL. The first one using like and the second one using regexp. So let's get started by visualizing what the data looks like. So I'm always like to do select star from station here to see what it looks like. So this is what the data looks like. As you can see there's multiple columns ID, city, state, lat, and long w. We want to care about city for the purposes of this question. So it's going to be the second column here. So looking to find all the city names that end in a vowel. So obviously this first one wouldn't appear nor would this. So basically these two would have to appear, Glencoe and Chelsea. So how can we do that? Well, first we need to select instead of star we want the city name. So we're going to select the column city. And because we don't want any duplicates as you can see here, we're going to add distinct before the name of the column city. Now to further these things, we're going to add a where clause here at the end. Where? Now where the city, now we have to say in was ends with a vowel. So we've got to do each vowel here. So let's say ends in a. So I'm going to say where city like quotes a. Percent a. Okay, so it could be whatever followed by the ending being the a vowel. Okay, and then you got to do the same pattern here for every other vowel. So or use the or city like quotes. Percent e. So it means ends in the e vowel and then our city like percent I. So it ends in I or the city like percent o and Zino or city like. Percent you and Zin you. Now let's check it out run code. And there you go. That's the solution using like. Now if you notice the solution is pretty long because you got to do city like or city like or city like or city like there's a better version if you a short version if you use regular expression. Let me show you what it looks like. So we're going to say where city but instead of like we're going to say reg X R E G E X B and then we're going to do quotes here and we have to specify the regular expression pattern. This one is different from the pattern for like. Okay, so here we have to explicitly. Okay, so we have a vowel. What is it as a character that could be either a or e or I or all or you. So we're going to say square brackets here and you're going to say a e I o you. What does it mean here? It means okay, I'm going to try to find capture a single character that could be either of these characters within the square brackets. So it's either a or I or a or e or I or or you and then we have to say it's at the end right it's ending in that. So we have to say right here the dollar sign that means okay this is the end of the string. So it's a character at the end and anything before that it doesn't really matter could be anything. So that's the difference with like in regular expression. It's implicit that it could be followed anything before here but whereas like you have to explicitly say percent to say whatever comes before. Well, that's run code and see what this does. And if you notice I put lower case so the case does not matter or rag X here in my sequel. So I have both capture uppercase and lowercase vowels. And there you go. That's the solution. Let's click submit code. And that was weather observation station seven.
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work: