Lesson 11
Weather Observation Station 11 | SQL Basic Select | HackerRank Solution
Summary
# Summary of Weather Observation Station 11 Lesson
In this lesson, the instructor demonstrates how to solve the SQL query from HackerRank for "Weather Observation Station 11." The objective is to query a list of city names that either do not start with a vowel or do not end with a vowel from a given `station` table with the columns: ID, city, state, lat_n, and long_w.
## Key Steps
1. **Initial Query**:
- The instructor starts with the command:
```sql
SELECT DISTINCT city FROM station
```
- This command retrieves the city names without duplicates.
2. **Filtering Criteria**:
- The query needs to be split into two main conditions:
- **Cities that do not start with a vowel**:
- The syntax used is:
```sql
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%'
```
- **OR cities that do not end with a vowel**:
- The syntax for this condition is:
```sql
OR city NOT LIKE '%A' AND city NOT LIKE '%E' AND city NOT LIKE '%I' AND city NOT LIKE '%O' AND city NOT LIKE '%U'
```
3. **Combining the Conditions**:
- The final condition combines both parts into one query that filters out cities based on the specified criteria.
4. **Using Regular Expressions**:
- To simplify the query, the instructor shows how to use regular expressions:
- For cities not starting with a vowel:
```sql
WHERE city REGEXP '^[^AEIOU]'
```
- For cities not ending with a vowel:
```sql
OR city REGEXP '[^AEIOU]$'
```
5. **Further Simplification**:
- The instructor offers another way to write the regular expression by placing the negation within the pattern. This results in:
- For starts:
```sql
WHERE city REGEXP '^[^AEIOU]'
```
- For ends:
```sql
OR city REGEXP '^[^AEIOU]$'
```
## Conclusion
The instructor finishes the lesson by running the code and confirming the completion of the task. The solution effectively retrieves cities that either do not start with vowels or do not end with vowels.
This concise approach not only answers the problem but also demonstrates SQL query optimization using regular expressions, making the solution more efficient.
Video Transcript
Hey, welcome to another lesson. I will do weather observation station 11 for the
subsection basic select of the SQL section from hacker rank. In this
question we're given this table station that has five columns ID city state
lat n and long w and we're asked to query the list of city names that either
do not start with vowel or do not end with vowel. So notice the or right keep
that in line result cannot contain duplicates that means distinct city. So
let's get started I'll do my SQL and I always like to start with select star
from station to see what the data looks like.
And there you go we have this five columns for the purposes of this question.
We only care about the city column. So let's go here. So we want only city and
because we want eliminate duplicates we say distinct city here. Now we have to
say where to filter out the undesired one so we have to split into two parts.
Let's first start do not start with vowels how can we do that. So I'm first
going to do like that later I can show you regular expressions. So do not start
a volume means does not start with an A and does not start with an E and does
not start with an I and does not start with an O and does not start with a U.
So city not like
a percent. So a at the beginning followed by anything so starts with a and this is
not case sensitive so it doesn't matter of your case lower case and since it's
not here that means does not start with an A. So we have to say and also does not
start with an E. So not city not like E percent and then for the other vowels and
city not like quotes I percent and city not like quotes O percent and city not
like quotes U percent. So city does not start with a does not start with E and
does not start with I and does not start with O and does not start with U. Now
they say either and then they say or do not end now we got to figure out a
pattern do not end. So we got all this pattern here but we have to say or the
other pattern that's for not ending with a vowel. So it's going to be like this
but instead of the letter followed by percent it's percent followed by the
letter because it means whatever followed by the letter at the end in this
case the vowel. So I'm going to say city not like percent A meaning does not end
in the vowel A and then continue for all the others 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 all this within the parentheses means it it
cannot end in A and cannot end in E and it cannot end in I and it cannot end in
O and it cannot end in U in effect it cannot end in a vowel. So this reads
where it cannot start with a vowel or it cannot end in a vowel which it
translates what what the problem is asking here. Let's run code. And there
you go that's a solution using like if you notice really long so can we make a
shorter yes we can use regular expression so let me show you. So the first
pattern here for does not start with a vowel we can say city reg exp not oops
not as before the regular expression and then you put the pattern for start
with a vowel here beginning of the string followed by square brackets A E I O U
this means followed by any of these characters in fact start with a vowel
and because it would not does not start in a vowel and then for the other one is
does not end so we're gonna say city not reg exp quotes now you have to see the
pattern for ending in a vowel and that would be square brackets A E I O U and
dollar sign meaning the end of the string so this at the end and the not
would mean does not end
and that's the solution with regular expression if you want to shorter it
even more and place the not within the pattern for the reg X you can do that
remove the not here and inside the square bracket at the carrot or circumflex
that means not any of these following characters different from the circumflex
at the beginning that says beginning of string be careful and you the same here
for the second pattern remove the not inside the square brackets at the
carrot or circumflex and this will mean not any of these characters let's run
code and see and that's it I'm gonna click submit code and that's whether
observation station 11
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work: