Summary

Weather Observation Station 6 - SQL Lesson Summary

In this lesson, the instructor demonstrates how to solve HackerRank’s Weather Observation Station 6 problem, which requires querying city names from a SQL table that start with a vowel (A, E, I, O, or U). The instructor presents two approaches: using the LIKE operator and using regular expressions.

SQL Table Overview

Table Structure:
- ID
- City
- State
- Latitude
- Longitude

The focus is on the City column, and duplicate city names must be excluded from the results.

Solution 1: Using `LIKE`

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%'

Explanation:
- Retrieves distinct city names that start with a specified vowel.
- The condition is case insensitive.

Solution 2: Using Regular Expressions

SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(city, '^[AEIOU]', 'i')

Explanation:
- This approach uses a regular expression to match cities beginning with a vowel more succinctly.
- ^ denotes the start of the string.
- [AEIOU] specifies that the first character should be one of the vowels.
- The 'i' flag makes the search case insensitive.

Key Takeaways

The LIKE approach provides a clear method but requires multiple conditions.
The regular expression method is more concise and efficient for this task.
Care must be taken not to confuse patterns between the two approaches, especially with symbols indicating matches.

In conclusion, both methods ultimately achieve the same goal, though regular expressions can significantly shorten the code.

Video Transcript

Hey everybody, welcome to another lesson. In this lesson I will do weather observation station 6 from the section SQL subsection basic select of hacker rank. Here in this question we have the table station and then we're asked to query all the list of city names that starts with a vowel, that is the first character has to be A, or E, or I, or all or you. There are two ways that are approached this problem. The first solution I'll do is with like, the second that I'll do is with regular expression. So let's get started. Here I'll choose my SQL and I'll just visualize the data saying select star from station so we can have a better understanding of what the table looks like. So as you can see here, this is what the table looks like. There's an ID, city, state, lat, and long W columns. We only care about city for this question so we're going to focus on this second column here. So instead of star I'm going to say city but notice that the question says your results cannot contain duplicates. So if there's a row with the same city name, we need to eliminate that so it only appears once. So we say distinct before the column city. Now we have to filter this so that only the city names that start with a vowel can appear. So we're going to add a where clause here, then we're going to use a like on the column city. So where city like and then first you're going to do the vowel a has to start with a so we're going to say like quotes a percent so a is the first character in the city name followed by anything so the percent sign here means they can follow a by any character. But this is only capturing the cities that start with a and another thing is this is case insensitive. So it doesn't matter if it's an uppercase a lowercase a will catch the same. Now this is just for vowel a starts with a but it could also start with e or I or O or U. So we have to do basically the same pattern. We're going to say or city like starts with E so say E percent or city like starts with I so I percent within quotes or city like quotes or percent so it starts with all or city like quotes you percent so starts you so it will get all the city values where that city value either starts with a or starts with E or starts with I or starts with O or starts with U. Let's run the code and see and there you go. Those are all the cities that start with a vowel. Now that's nice but this solution is pretty long. Can we make a short now teach you a second alternative to this using regular expression. So instead of having all this massive like or like or like like you just say where city rag X R E G E X P space and then I'm going to add some regular expression here. So a regular expression we have to say starts with so in the beginning of the string so I have to say circumflex and then brackets followed by any of these characters within the brackets so it's either a or E or I or O or U. So this regular expression saying at the beginning of the string followed by this character that could be either a or E or I or O or U. Let's try it out. And that's it. I'm going to click submit code. As you can see this is a much shorter solution using regular expression. Be careful when you write this solution to not confuse if the pattern for like it's not the same thing the percent is different when you do the regular expression there's an implicit it could be followed by whatever whereas the like you have to explicitly say percent so it follows whatever. So that's it for wet observation station six