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.