Summary

Summary of Weather Observation Lesson

In this lesson, an exercise from HackerRank's SQL section is explored, specifically focusing on querying city names from a table named station. The goal is to retrieve distinct city names that start and end with a vowel.

Key Points

Table Structure:
- The station table has the following columns: ID, city, state, lat, and long_w. The focus is primarily on the city column.
Query Requirements:
- The task is to find city names that:
  - Start with a vowel (A, E, I, O, U).
  - End with a vowel.
Approach:
- The problem can be divided into two parts: checking for the first character and checking for the last character being a vowel.
- To avoid duplicates, the query involves selecting distinct city names.

SQL Query Examples:

The initial approach uses LIKE for matching:

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%')
  AND (city LIKE '%A' OR city LIKE '%E' OR city LIKE '%I' OR city LIKE '%O' OR city LIKE '%U');

Optimized Regular Expression Approach:
- An alternative method using regular expressions (regex) is presented, which simplifies the query significantly:
```
SELECT DISTINCT city 
FROM station 
WHERE city REGEXP '^[AEIOU].*[AEIOU]$';
```
- The regex checks that the city name starts with a vowel (using ^) and ends with a vowel (using $).
Conclusion:
- The final SQL code successfully retrieves the desired list of city names that both start and end with a vowel, effectively combining knowledge from previous exercises.

This structured approach efficiently solves the weather observation problem by utilizing both SQL pattern matching techniques and regular expressions to achieve the desired output.

Video Transcript

Hey everybody, welcome to another lesson. In this lesson I will do weather observation 8 and exercise from the subsection basic select of the section sequel from hacker rank. This question has a table station that has the columns ID city, state, lat, and long w. Now we're asked to query the list of city names that cannot contain duplicates which have both vowels as the first and last character. So this is like a combination of the previous exercises. So it has to start with a vowel and has to end in a vowel. So we can split this problem into two problems, right? First you have to figure out how do we do beginning in a vowel and then you figure out how to do ending in a vowel then you combine them. So let's get started. I'm going to choose my sequel. So I'm going to like to visualize the data. So I always do select star from station to see what a day looks like. I'll click run code here. So this is what the stable looks like. We have five columns here ID, city, state, lat, and long w. So for the purposes of this question, we only care about the city column. So we need to figure out how can we filter out all the city names that do not start in a vowel and do not end in a vowel. So basically we need or do not end in a vowel need something that starts both with a vowel and ends in a vowel. So for example, this one would qualify as starts with E and in E. So let's get started here. So I need to select city names. I narrow down to only the city column and because we cannot contain duplicates, we select distinct city to eliminate any possible or repetition of the same city. Now we have to add a clause here where so there's two ways you can do this that I want to do and one is using like the other ones using reg X, the regular expression. So using like is pretty long, regular expression is pretty short. So let me show you first with like. So we're going to say, okay, we've got to figure out there's two problems, right? First start a vowel and then ends. Let's do starting. So starting would be where city like starts at A. So A percent or city like E percent or starts at E or city like I percent starts at I or city like O percent starts at O or city like U percent starts with U. So city can start A or city starts with E or city starts with I or city starts with O or city starts with U. Then you've got to grab all these things. I'm going to add parentheses. So it's obvious here too and right, so it has to start with a vowel and has to end now right here. How can we write the same equivalent thing but to end in a vowel? So we're going to say city like quotes percent A. So that means it and the city could end in A or city like quotes percent E could add in E or city like quotes percent I could add in I or city like quotes percent O could add in O or city like percent U could add in U. So select the distinct city from station where so we have either starts has to start with a vowel and don't forget and has to end. So let's check it out. And if you notice this is like a combination of the solutions from the previous questions where asked us to find out all the city that starts with a vowel find out all the cities that end in a vowel. So we just combine them with an end clause here. Oh, there you go. Okay. Now this is very long. So how can we do better? We can do better regular expression. So here we're going to say instead of all this stuff city like whatever I'm going to replace that with city reg X and we're going to write an expression here to start in a vowel. So start is circumflex and then followed by a character that could be a vowel. So I'm going to use square brackets A E I O U. So this means starting out the string followed by a single character that could be any of these characters either A or E or I or O or U. And this means starts with a vowel. Now do the same kind of pattern in the end here. So instead of all this stuff, we're going to say city reg X quotes. Now we're going to do ending. Now ending is the dollar sign at the end. And then before that we got to say square brackets A E I O U. So this means you're going to match a single character that could be any of these could be either A or E or I or O or U followed right at the end of the string. And this is kind of different from the pattern of like where we have to explicitly say percent to say whatever but regular expression, you don't have to say it's implicit that this is there could be anything before this pattern. So let's run code and see what we get. Okay, so that's it list of city names that both starts and ends in a vowel. Click submit code. And that was weather observation station eight.