Summary

Weather Observation Station 12 Lesson Summary

In this lesson, the instructor explains how to query city names from the weather observation station (table name: station) that do not start or end with a vowel. The objective is to write a SQL query ensuring that the result contains distinct city names.

Table Structure

The station table consists of five columns:

ID
city
state
lat (latitude)
long (longitude)

Key Points

Requirement: Select city names that do not start or end with a vowel, specifically 'A', 'E', 'I', 'O', 'U'.
Distinct Results: Use DISTINCT to avoid duplicate names.

SQL Query Breakdown

Selecting Data:
- Start by selecting data to visualize the structure:
```
SELECT * FROM station;
```
- Focus on the city column.
Building the Query:
- Begin constructing the query to select distinct city names:
```
SELECT DISTINCT city
```
- To filter out cities that start or end with a vowel, use the WHERE clause with LIKE:
```
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%'
```

Filter Conditions:

For the end of the string, use the reverse:

AND city NOT LIKE '%A' AND city NOT LIKE '%E' AND city NOT LIKE '%I' AND city NOT LIKE '%O' AND city NOT LIKE '%U'

Combine both filters in one query:

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%'
AND 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:
- For a more compact query, regular expressions can be employed using REGEXP:
  - To ensure a city does not start with a vowel:
```
city NOT REGEXP '^[AEIOU]'
```
  - To ensure it does not end with a vowel:
```
city NOT REGEXP '[AEIOU]$'
```
Final Version with Negation in Brackets:
- An even more streamlined version can include the negation inside brackets:
```
city REGEXP '^[^AEIOU]' AND city REGEXP '[^AEIOU]$'
```

Conclusion

The lesson outlines the method of querying city names that do not start or end with vowels using both traditional SQL with LIKE and regular expressions for more efficient code. The key takeaway is the understanding of string patterns and the effective use of DISTINCT in SQL queries.

Video Transcript

Hey everybody, welcome to another lesson. I will do weather observation station 12 with a subsection basic select of the cycle section from HackerRing. In this question we're asked to query the latest city names that do not start with vowel and do not end with vowel. Basically is the previous question but with instead of or you do and. Result cannot contain duplicates so it's distinct city. So we're given this table station with five columns ID city state lat underscore in long underscore w. So let's get started. I'll choose my sequel here and I always like to visualize the data so I'll say select star from station to see what it looks like. So here's the data for the purposes of this question. We only care about the second column. That's the city name. So let's get started. So we want to list city names. So set of stars say city and to eliminate all duplicate cities. I'm going to say distinct before city. Now we have to filter out the undesired ones to add where now at the first pattern. It does not start of vowels and then and do not end with all. So we have to do two separate things. So I'm going to add the first pattern with parenthesis here. Do not start of vowels and then and the second pattern with imparences here to say does not involve so vowels are the letters. A I O U. So we have to say I'm going to start doing with like later I show you how to do a regular expression. So what the like would be like it does not start of a and it does not start with. He and it does not start I and it does not start of all and it does not start of you. And for the other patterns basically the same thing except it does not end right. So let's do it here for the first pattern does not start of a vowel. So we're going to say city not like and start with a so a percent so a at the beginning followed by anything. And then we repeat the same pattern for every other letter but we have to combine them with and and city not like. He percent and city not like I percent and city not like. Oh percent and city not like you percent so let's check it out city not like a meaning does not start of a and it does not start of E and city not like I percent does not start of I and city not like over said does not start of O and city not like you percent does not start of you effective believe it means does not start with a vowel. Now do the other pattern that means does not end so we have does not start and we have to say does not end so here we're going to do city not like percent a it's like the opposite of the previous pattern. So here we say anything and then followed by a at the end meaning does not end with a because the not. So let's do it for every other letter and city not like percent E and city not like percent I and city not like percent O and city not like percent you so all this is doing and saying does not end in vowel and now let's run code. And there you go that's the solution with like now I'm show you a shorter one with a regular expression. So for the first pattern start with a vowel I'm going to say city not rag EXP and the pattern for starts with a vowel so beginning of the string is carrot follow square brackets and any of these characters. A E I O U so this means not whatever this is this pattern is beginning of the string followed by any of these characters either a or E or I or O or U so does this means starts with a vowel but because of the not it becomes does not start with a vowel. Now we do the pretty much the same kind of pattern for the second parentheses here after the end I'm going to say city not rag EXP and I'm going to say the regular expression for ends in a vowel so square brackets A E I O U and then as outside square brackets dollar sign to mean at the end of the string and because of the not this means does not end in any of these letters. If you want an even shorter version I will show you remove the not and place that not inside the pattern here when you say carrot inside the square brackets that means not any of the following characters that's different from the carrot outside at the beginning which means beginning of the string so be careful they are the same character they have different meaning. Now do the same for the second pattern instead of having not here I put the not inside within the square brackets as a character so not any of these characters not a or E or I or O or U. Let's run code. And there you go that's the solution for weather observation station 12.