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;
```
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;
```
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;
```
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;
```
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
```

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.