Lesson 03
Weather Observation Station 3 | SQL Basic Select | HackerRank Solution
Summary
Summary of SQL Problem Solution: Weather Observation Station 3
In this lesson, we explore a problem from the HackerRank SQL category that involves querying city names from the station
table based on specific conditions.
Problem Statement
The objective is to:
- Query city names where the city ID is even.
- Exclude duplicate city names from the results.
Steps to Solve the Problem
-
Initial Query:
- Start by selecting all columns from the
station
table to understand the data structure:SELECT * FROM station;
- Start by selecting all columns from the
-
Filter Even IDs:
- Add a filter to select only rows with even ID values. This can be achieved using the modulo operator:
SELECT * FROM station WHERE ID % 2 = 0;
- Note: If using a SQL flavor that does not support the modulo operator (like DB2), use the
MOD
function instead:SELECT * FROM station WHERE MOD(ID, 2) = 0;
- Add a filter to select only rows with even ID values. This can be achieved using the modulo operator:
-
Select Only City Column:
- Instead of selecting all columns, limit the selection to the city column:
SELECT city FROM station WHERE MOD(ID, 2) = 0;
- Instead of selecting all columns, limit the selection to the city column:
-
Exclude Duplicates:
- To eliminate any duplicate city names from the results, add the
DISTINCT
keyword:SELECT DISTINCT city FROM station WHERE MOD(ID, 2) = 0;
- To eliminate any duplicate city names from the results, add the
-
Final Execution:
- Run the final query to check if it returns the expected results with distinct city names:
SELECT DISTINCT city FROM station WHERE ID % 2 = 0; -- or use MOD function if necessary
- Run the final query to check if it returns the expected results with distinct city names:
Conclusion
The result will be a list of unique city names where the city ID is even, and upon running the query, it successfully meets the problem's requirements.
And that's the solution for Weather Observation Station 3.
Video Transcript
In this lesson I will solve the problem with other observation station 3 from the subcategory
basic select in the category SQL from HackerRank.
So basically you have the problem here that wants us to query the list of city names from
the table station.
The catch is the ID of the city has to be an even number.
And then in addition to that you have to exclude duplicates.
Okay to do that let's just start selecting everything so we can see what the data looks
like.
I'm going to select my SQL here in the top right.
Select and I'm going to want all columns so I say star from name of the table station
semicolon.
I'm going to run the code so you can see what the data looks like.
And then we're going to proceed to add the filter for the ID and then we're going to
narrow down to only the city column so you can see it working visualize it.
So you can see we have everything here have ID this one is even even this one is odd so
we want to eliminate the ones with ID that's not even.
How can we do that?
So here select from station I'm going to add it where and then I'm going to say the name
of the column ID and then I'm going to take the following.
I'm going to take the remainder of the ID division by two.
So if you say ID percent to the modules operator and if you take that and divide by two and
there's no remainder which is equal to zero that's when it's even.
Okay so as you can see we exclude all the odd numbers for the ID.
One thing to notice here this module operator might not work in other flavors of SQL if
I do DB2 here.
As you can see here we got an error saying that unexpected token percent sign and that's
because DB2 doesn't really support that so if you are using a SQL flavor that does not
support the module operator you have to do it as a function like this you call it mod
parenthesis and pass the ID to the function and this will take the remainder of the division
of ID column two in this case divide ID by two so you have to pass the two arguments
to mod like so.
Try to run the code.
And as you can see there's no error about the operator or whatever so the module still
worked and we eliminated all the entries whose ID is odd.
Now let's narrow it down to only selecting the city so instead of star we're going to
say just the city column and the last thing we have to do is exclude duplicates let's
just see what it looks like.
Okay so as you can see it's still the wrong answer because it's a duplicate thing so if
you scroll down here somewhere you might find some duplicate so in order to get around
that one simple way of doing that is just simply adding distinct keyword before the column
city here so select distinct city run the code again and see if it works.
And now because you can see it worked with the distinct keyword before the column name.
And let's submit.
And that's it for weather observation station three.
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work: