Summary

Summary of Weather Observation Station SQL Problem

In this tutorial, the speaker discusses solving a SQL problem related to the "Weather Observation Station 10" challenge on HackerRank. The task involves querying a table named station with the following columns: ID, city, state, lat_n, and lon_w. The goal is to retrieve city names that do not end with a vowel and eliminate any duplicate city names.

Steps to Solve the Problem

Initial Query: The speaker starts by selecting all data from the station table to visualize it:
```
SELECT * FROM station;
```
Identifying Vowels: The vowels to avoid at the end of city names are: a, e, i, o, u.

Constructing the Query:

Using LIKE, the condition for city names not ending with vowels is constructed:

SELECT DISTINCT city FROM station
WHERE city NOT LIKE '%a'
  AND city NOT LIKE '%e'
  AND city NOT LIKE '%i'
  AND city NOT LIKE '%o'
  AND city NOT LIKE '%u';

Using Regular Expressions:
- The speaker explains a more concise way to achieve the same result using regular expressions:
```
SELECT DISTINCT city FROM station
WHERE city NOT REGEXP '[aeiou]$';
```
Alternative Regex Approach:
- Another method using the negation inside the pattern:
```
SELECT DISTINCT city FROM station
WHERE city REGEXP '^[^aeiou].*';
```

Conclusion

The tutorial concludes by showcasing different methods to query city names that do not end with vowels, demonstrating both SQL LIKE clauses and regular expressions for brevity and efficiency.

Video Transcript

I'll do weather observation station 10 on the subsection basic select of the SQL section from hacker rank. So we're given a table station that has the columns ID city state lat underscore n loner underscore w and we're asked to query the city names that do not end with vowels and make sure to eliminate any duplicate city name. So to solve this problem I'm going to do first with like and I'm going to show you how to do regular expressions. So let me choose my SQL here. So I first like to always select everything so I choose select star from station to visualize the data and see what it looks like. As you can see here this is what the data looks like each row and for the purposes of this question we are only interested in the city name that's the second column. So how can we say do not end with vowel? Well vowels are the characters a e i o or u. So we can say it does not end in a and does not end in e and does not end in i and does not end in o and does not end in u. So five things. So let's add to the where. So where I'm going to say column the city like how can I say do not end with a well how can I say and with a so I can say quotes percent a so it could be anything followed by a at the end but this is to to and in a how can I say not and in a I can say city not like so it means does not end in a so I just copy the same pattern for every single vowel and combine them with and so it cannot and in a and city not like percent e and city not like quotes percent I and city not like quotes percent o and city not like quotes percent u. So we're going to query from station where the city does not end in a and does not end in e and does not end in I and does not end in o and does not end in u. So all of those and because we just interested in the city name here instead of star say city and since they want us to eliminate any duplicate city names that might appear again in different rows we'd say distinct before the city column here let's test it out and there you go now I want to show you a shorter version using reg X so instead of typing every single thing like this I can just say where city are e g e x p for a regular expression and space quotes and you have to say the pattern in this case that do not end in a vowel there's two ways you can do this I can use a not here so where city not so I can negate whatever this is in this case it would have to be the pattern to end in a vowel so when you say not that means does not end in a vowel to do that I can I say square brackets a e I owe you that means a single character that could be either a or e or I or or you and then if I want it at the end I get to say dollar sign meaning end of the string so this pattern would match any of these vowels at the end and because of the not it would see it would mean does not end let's test it out and there you go now one more thing I want to show you how to do without the not so instead of having not here you can also use the not within the pattern itself and to do that you you add a circumflex right after the square brackets notes is not before don't confuse this the carrot character might mean beginning of the string if you put outside but within the square brackets here it means not any of these following characters so this means not a or e or I or or you so let's run code and there you go and that was weather observation station then