previousnext
Lesson 09
Courses / HackerRank SQL Problem Solving Solution Challenges
Weather Observation Station 9 | SQL Basic Select | HackerRank Solution

Summary

# Lesson Summary: Weather Observation Station 9

In this lesson, we focused on solving exercise "Weather Observation Station 9" from HackerRank's SQL section. The main task was to retrieve distinct city names from the `station` table that do not start with a vowel (a, e, i, o, u).

## Key Takeaways

- **Table Structure**: The `station` table contains the following columns:
  - `ID`
  - `city`
  - `state`
  - `lat_n`
  - `long_w`

- **Objective**: 
  - Query distinct city names.
  - Filter out cities starting with vowels.

## Approach

### Method 1: Using `LIKE`
1. Visualize the data using:
   ```sql
   SELECT * FROM station;
  1. Construct the SQL 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%';

Method 2: Using Regular Expressions (REGEXP)

  1. Use REGEXP to simplify the condition:

    SELECT DISTINCT city
FROM station
WHERE city NOT REGEXP '^[AEIOU]';
    • ^ denotes the beginning of the string.
    • [AEIOU] represents any vowel.

  2. Alternatively, to avoid using NOT, a different pattern can be used:

    SELECT DISTINCT city 
FROM station 
WHERE city REGEXP '^[^AEIOU]';
    • This searches for characters at the start of the string that are not vowels.

Conclusion

Using LEFT JOIN and REGEXP greatly simplifies the query process and improves readability. The final solution efficiently retrieves the desired city names without any vowels at the start.

The code submission for "Weather Observation Station 9" concluded the lesson.

Scroll down above for more

Video Transcript

Hey everybody, welcome to another lesson. In this lesson, I will do the exercise weather observation station 9 from the subsection basic select of the section sequel from hacker ring. So in this question, we have the table called station has the columns ID, city, state, lat underscore n and long underscore w. The purposes of this question, we only care about the columns city. Now we're asked to query list of city names that cannot contain duplicates. So distinct city that do not start with balls that it does not start with the characters a, e, i, o or u. So let's get started. I'll do two solutions. One using like the other one using reg X. Let's start with the like. So I always like to visualize the data. So I do select star from station to see what the data looks like. So as you can see, this was the data looks like we have all this five columns. But the purposes of this question, we only care about the city names. And we got to check that the city names that do not start with a vowel. So it would be this one, this one, this one, they have to appear this one line five cannot appear because it starts with a that's a vowel. So let's get started. My sequel here, first we got a narrow down. Okay, I want only the city name. So the column that will select the city. And because I don't want duplicate cities that is the same city naming appearing twice or more than once. I'm going to say distinct city. Now we got to apply a filter. So going to use where now let's do this problem very simple. How can we do just for one ball a. So so I'm going to use like where city the column in question like so how can we make it that starts with a. So a percent right so this means where the city would start with a how can make it do not start with a so we can say where city not like. Okay, and then this pattern just copy for all the others. But it cannot be starting of a and okay it cannot be starting with e and city not like I percent starting with I and city not like starting with all percent and city not like starting with you so you percent. Let's run code and see what we get. So there you go other cities that do not start with a vowel. That's nice but very long how can I make a short using rag X. So instead of like here I'm going to say where city rag X. So not rag X so you have you can keep the not here and just write a regular expression that means start with a vowel so we will negate it or you could just do with city rag X and use the not within the rag X itself that means the circumflex thing but keep it with not so not rag X. So how can we say starts with a ball. So beginning of a string is circumflex here and we have rack is a E I O U so that means beginning of the string followed by a single character that could be either a E I O or you and because it's not that means does not start a vowel so let's run code here. Now about that thing of not using not here I'll just show you what I meant. So if you don't want to use not here you can make a pattern that explicitly says not any of these so you got to add the circumflex here. Yeah it's kind of confusing because the circumflex can mean beginning of the string but it could also mean not if it's within the square brackets here so that means this not any of these characters. So run code. And there you go. Basically where city does not start a ball so beginning of the string starts with some character that's not right the circumflex after the square brackets mean not any of these characters not a ball. So I'm going to submit code. And that was weather observation station nine.
See more
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work:
See donation links