HackerRank SQL Problem Solving Solution Challenges

A lesson that teaches you how to solve the problem Weather Observation Station 1 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-1

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 1 | SQL Basic Select | HackerRank Solution

# Weather Observation Station 1 - SQL Problem Summary

In this lesson, the instructor solves the problem of **Weather Observation Station 1** from the SQL section of HackerRank. Below is a summary of the steps taken to address the problem.

## Problem Overview
The task is to query the **state** from the **station** table, which is defined with the following columns:

- **ID**
- **city**
- **state**
- **lat_n**
- **long_w**

For this problem, only the **city** and **state** columns are of interest. The **ID**, **lat_n**, and **long_w** columns can be ignored.

## Querying the Data
To view the data structure, one can use the following command:

```sql
SELECT * FROM station;
```

This will display all the columns in the dataset.

### SQL Query
To accomplish the task, the instructor executes the following SQL query:

```sql
SELECT city, state FROM station;
```

### Important Notes
- Be cautious when modifying the query, as incorrect changes may lead to errors.
- Validate the query by running the code before submitting it.

## Conclusion
Once the query is confirmed to be correct, the next step is to submit the code. This completes the solution for **Weather Observation Station 1**.

In this lesson I will solve the problem of weather observation station 1 from the basic
select section in the SQL Category Hacker Rank.
So here is the problem, it states that we should query the setting state from the station
table.
Station table is defined as following, we have the columns ID, city, state, lat, underscore
n, long underscore w.
Actually, we don't care about ID nor lat in nor long at w for this problem, all we care
about is the city column, state column, and we're going to carry those columns from the
table station.
If you want to get a glimpse of what the table looks like, you can simply go here and right
hand side and say select star from station the table name.
And click around code to see what the data looks like.
As you can see here, this is what the data looks like.
You have the ID, it's a number, followed by the city name, followed by the state, in this
case an abbreviation, and then we have lat underscore n followed by long underscore w.
So basically all we want to do here in this problem is select, we only want to show the
name of the city and the state abbreviation.
So for that, we simply go here and instead of saying star, so we want to only select
the city comma state from station.
And I'm using DB2 as a flavor for SQL here.
The most popular would be my SQL, so you can do that as well.
And be careful because you're going to lose a thing, so you want to copy if you change.
Run code here first to make sure it's correct before you submit.
As you can see now, we selected the city and state columns.
Simply follow the syntax, select, followed by the columns you want to show the data for
from, and then the name of the table.
You can comma separate every column name.
So we got the city, and then we got the state.
Too bad this output doesn't put a like a bar separating these two, but it's actually two
separate columns being selected.
They're not one thing, so be careful with that.
So once you think everything's fine, just click submit code.
And that's it for Weather Observation Station 1.


A lesson that teaches you how to solve the problem Weather Observation Station 2 from the subcategory Aggregation from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-2

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 2 | SQL Aggregation | HackerRank Solution

# Summary of Weather Observation Station 2 Problem

In this lesson, we solve the Weather Observation Station 2 problem from HackerRank under the SQL category focusing on aggregation. The task involves summing the values in two columns: `lat_n` and `long_w`, then rounding the results to two decimal places.

## Steps to Solve the Problem

1. **Select the Table**: Start by selecting data from the table named `station`.

2. **Use the SUM Function**:
   - To compute the sum of `lat_n`, use:
     ```sql
     SUM(lat_n)
     ```
   - To compute the sum of `long_w`, use:
     ```sql
     SUM(long_w)
     ```

3. **Round the Results**:
   - The results need to be rounded to two decimal places. We use the `ROUND` function:
     ```sql
     ROUND(SUM(lat_n), 2)
     ```
     ```sql
     ROUND(SUM(long_w), 2)
     ```

4. **Final Query Structure**:
   Here's how the full SQL query should look:
   ```sql
   SELECT 
       ROUND(SUM(lat_n), 2) AS total_lat_n,
       ROUND(SUM(long_w), 2) AS total_long_w
   FROM 
       station;
   ```
5. **Execute the Query**: Run the query to get the desired results, which will display the summed values for both columns rounded to two decimal places.

By following these steps, we can efficiently obtain the required sums for the latitude and longitude columns as stipulated in the problem.

This lesson I'll solve the problem weather observation station 2 from the
subsection aggregation in the category SQL from HackerRing. So we're given this
stable station as the calls ID city state lat underscore n long underscore w.
So we're asked to sum up all the values in lat underscore n and then also show
the sum of all the values in long underscore w. And we must also round it
to two decimal places. So keep in mind those things. So first how can we get the
sum? That's going to be very simple. I'm going to use the function sum from my
SQL. Others flavors of SQL might have something similar. So you can probably
try the same thing. Call it in the select section of the query. You're going to
say sum parentheses and then you pass the column name. In this case we are
summing lat underscore n and then for the second column we do the same sum
except we change to the different column long underscore w. And that's going to
give us what is desired except it's not going to be rounded to two decimal
places. That's when round comes in. That's the next function we're going to use.
We're going to get that sum statement and place that into the round function and
then we're going to do comma the number of decimal places. In this case the
documentation here is the D as you can see from these examples. So let's go. So
I'm going to choose my SQL. So we're going to select something from name of the
table station. Now this something let's fill it out. So we have lat underscore n
and we have long underscore w. So these are the two columns. So we want to take
the sum of the lat underscore n. Simply call the sum function like so. Pass lat
underscore n as the argument here in the select statement. Do the same for long
underscore w. Call sum pass long underscore w as the argument. If you run
this code you're going to get the sum for all the lat underscore n as the first
column and then the sum for all the values on long w as the second column. As
you can see here this is the sum. If you take all the values for lat underscore
n and add them up you're going to get this. And if you take all the values for
long underscore w you're going to get that. Now we need to round it to only two
decimal places. In this case there's more than two. So that's when the round
function comes in. So you're going to go here whenever you have this sum of
whatever you think of that as one thing that is to be passed to the round
function. So you can write round parentheses past this thing as sum as
the argument comma and you need to pass the second argument to the round
function that will tell it how many decimal places you want to round it. In
this case it's two by the requirements of the problem. So we're going to pass
comma two there. Do the same for the sum of long underscore w. You're going to
see round function call that pass the sum of long underscore w comma second
argument of round is the number of decimal places, place a two there. And
then run code again and see if it works. As you can see now we only have two
decimal places that is there's two jays to the right of the dot. So click
submit code and that's how we can solve whether observation station two.


A lesson that teaches you how to solve the problem Weather Observation Station 3 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-3

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/


Weather Observation Station 3 | SQL Basic Select | HackerRank Solution

# 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

1. **Initial Query**:
   - Start by selecting all columns from the `station` table to understand the data structure:
     ```sql
     SELECT * FROM station;
     ```

2. **Filter Even IDs**:
   - Add a filter to select only rows with even ID values. This can be achieved using the modulo operator:
     ```sql
     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:
     ```sql
     SELECT * FROM station WHERE MOD(ID, 2) = 0;
     ```

3. **Select Only City Column**:
   - Instead of selecting all columns, limit the selection to the city column:
     ```sql
     SELECT city FROM station WHERE MOD(ID, 2) = 0;
     ```

4. **Exclude Duplicates**:
   - To eliminate any duplicate city names from the results, add the `DISTINCT` keyword:
     ```sql
     SELECT DISTINCT city FROM station WHERE MOD(ID, 2) = 0;
     ```

5. **Final Execution**:
   - Run the final query to check if it returns the expected results with distinct city names:
     ```sql
     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.


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.


"A lesson that teaches you how to solve the problem Weather Observation Station 3 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-3

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/
"

A lesson that teaches you how to solve the problem Weather Observation Station 4 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-4

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/


Weather Observation Station 4 | SQL Basic Select | HackerRank Solution

# SQL Problem Solving: Weather Observation Station

In this lesson, we solve a problem related to the **Weather Observation Station** for HackerRank under the **Basic Select** SQL category.

## Problem Overview

We have a table named `station` with the following columns:
- ID
- City
- State
- Ladder
- Score
- N
- Long
- Score W

The goal is to:
1. Count the total number of entries (rows) in the `station` table.
2. Subtract the number of distinct cities from the total count.

### Example

Given the data such as:
- New York
- New York
- Bengaluru

The counts would be:
- Total Rows: 3 
- Distinct Cities: 2 (New York and Bengaluru)

Thus, the final result would be:
```
3 (total rows) - 2 (distinct cities) = 1
```

## SQL Query Breakdown

1. **Count Total Rows**: 
   To count the total number of entries, use:
   ```sql
   SELECT COUNT(*) FROM station;
   ```

2. **Count Distinct Cities**: 
   To count only unique city names, modify the count operation:
   ```sql
   SELECT COUNT(DISTINCT city) FROM station;
   ```

3. **Final Query**:
   Combine the two counts in a single query:
   ```sql
   SELECT COUNT(*) - COUNT(DISTINCT city) FROM station;
   ```

## Conclusion

By running the above query, we can determine the specified count difference. The expected output for this particular problem is `13`, and upon executing the code, we confirm that our approach is correct. 

Finally, don't forget to submit the code once verified.

This lesson I will solve the problem with our observation station for
on the subcategory basic select in the category SQL from HackerRank.
So here's the problem, we have the table station, it has the columns ID, city, state,
ladder, score, n, long, and score, w.
So we're asked to count the number of entries, that is number of rows in this table.
And that's easy, right? If you want to count anything in SQL,
the most basic thing you can do is select count, parentheses, star.
And that should give us number of rows.
Now it's also asking us to subtract the number of distinct cities.
That is, we have to take the cities that might be duplicate.
For example, we have New York, New York, Bengaluru, like it's said here.
We have to take this count of three because there's three rows.
But then we have to subtract from the distinct count of cities.
In this case, there's two unique cities, New York and Bengaluru.
So that's two, so three minus two is one, so that's what the query should return.
Now, how can we do that?
Now let's start with the thing we know, right?
So I'm going to select my SQL here.
I'm going to say select count star from station.
So this is going to give us the total number of rows in this table.
Now we need to subtract something.
So here in the select, I'm going to say minus, subtracting another count, right?
We need to count city, but if we count city like so, you see it's the wrong answer.
It's going to be the same as count star.
So we have to find a way of counting only the unique city names.
To do that, it's very simple.
Before the city, the column name, you have to say distinct.
So once you say distinct before the column name, and you pass it to the count function,
it's only going to count the unique city names.
So in this case, you're going to take the total number of rows, minus the unique ones,
and that should give you what the problem is asking for.
Run the code.
And there you go, the output 13, and we got it passing.
Let's submit the code.
And that's weather observation station four.


"A lesson that teaches you how to solve the problem Weather Observation Station 4 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-4

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/
"

A lesson that teaches you how to solve the problem Weather Observation Station 5 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-5

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/


Weather Observation Station 5 | SQL Basic Select | HackerRank Solution

# SQL Lesson: Weather Observation Station 5

In this lesson, we will solve the HackerRank problem "Weather Observation Station 5" from the SQL basic select subcategory.

## Problem Overview

We need to query the `station` table to provide two rows:
1. The city with the shortest name.
2. The city with the longest name.

Both rows should include the city name and its length.

### Example

For city names:
- DEF
- ABC
- PQRS
- WXY

The expected output:
- Shortest name: ABC (3 characters).
- Longest name: PQRS (4 characters).

If two cities have the same length, select the one that comes first alphabetically.

## Query Steps

To tackle this problem, we can use two sub-queries:

1. **Shortest City Name Query**:
   - Use `ORDER BY` to order rows by the length of the city name (ascending).
   - Use `LIMIT 1` to select the first row.
   - Add a secondary `ORDER BY` clause to sort alphabetically if lengths are equal.

   ```sql
   SELECT city, LENGTH(city) 
   FROM station 
   ORDER BY LENGTH(city), city 
   LIMIT 1;
   ```

2. **Longest City Name Query**:
   - Similar to the shortest, but use descending order.

   ```sql
   SELECT city, LENGTH(city) 
   FROM station 
   ORDER BY LENGTH(city) DESC, city 
   LIMIT 1;
   ```

Both queries will yield the required city names and their lengths.

## Conclusion

After implementing the queries and running the tests, the solution was successfully passed. This completes the lesson on the Weather Observation Station 5 problem.

In this lesson I will solve the problem weather observation station 5 from the subcategory
basic select in the SQL Category Hacker Rank.
So let's get started.
So we're asked to query the table station and provide two rows.
The first row being the city with the shortest name, the second row the city with the longest
name and as the second column you also want to show the length.
So as you can see if you scroll down here's an example.
If you have four rows with the city names DEF, ABC, PQRS, WXY you have the sample output
here.
So the first row is for the shortest length.
In this case we have DEF and ABC but which one do we choose?
The problem states that we should pick.
If they have the same length we should pick the one that's first alphabetically.
So A comes first before the D so therefore should choose ABC and because there are three
characters in ABC we have to show as the second column three here.
Now for the next row that's the longest.
Now we have PQRS as the longest.
We have four characters so we have to say for the first column the name of the city
PQRS then from the second column the length of PQRS that's four characters.
So we can do two sub-queries to solve this problem as you can see in the note here.
So let's get started and if you want to choose I'm going to choose my sequel and I'm going
to go here and select star from station just so we can see what the data looks like.
And we can see the there is like this.
Now if you want to get the shortest and the longest city names there's a technique we
can use.
We can use order by to order all the rows by a certain column and then we can get just
the first column the first row and we combine order by with limit one so we can get only
one at the top.
And we can do that for the shortest and for the longest.
One we will sort the length ascending and get the first row that's the shortest.
The other one we're going to sort by length descending and we get the first row that's
the longest.
So let's get started with the first query for the shortest.
So we can go here and say order by and then we want to order by the city name like so.
And if you say limit one that will only take the first row.
So if I order by city name it's going to be alphabetically the first and then going down
but that's only the secondary thing we want to order by.
We actually want the length right so we can call length function here like so.
So that means you're going to order these records by the length of the city and by default
ascending so ASC is implicit here you don't have to say it.
So the very first row we have the shortest length while the last row will have the longest
length.
And when you say limit one you take the very first one.
So as you can see here we have this city rye three characters and all these extra columns
that we don't need.
So if you want to narrow down here you can say city but we also need to add a number
of characters so I can say comma length of city as the second column.
Let's run the code and see what we get.
We should get rye followed by the second column three.
Okay we got that but our answer is wrong because we have to think about what if there's also
another city with the same number of characters how do we pick which one is going to be.
Well if we have the same order number of characters here you have to add a secondary order by and
that's when you add a comma after the first.
So if they happen to have the same length we should also sort by this second column here
and that's going to be city.
We want to sort by the name of the city right alphabetically and that's ascending by default
when I pick the one with the very first letter in the alphabet.
If you compare them the letter has to come before so that's the default ascending.
Okay so we got ammo underscore three there and that's nine we still got the wrong answer
because we need to do longest as well.
So we're going to follow the same pattern so we're going to select we want the city
name followed by the length of the city from the table name station and we want to order
by length of city but in this case to get the longest I'm going to order by the length
but descending so that the very first column is the one of the longest so I'm going to
say DESC right after length of city so it can already descending.
You want to limit that to one so we got that very first row and also add the secondary
sorting here if they happen to have the same length you're going to order by city ascending
alphabetically right.
Okay now we got the task passing and let's click submit.
And it's a success and that's weather observation station five.


"A lesson that teaches you how to solve the problem Weather Observation Station 6 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-6

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/
"

Weather Observation Station 6 | SQL Basic Select | HackerRank Solution

# Weather Observation Station 6 - SQL Lesson Summary

In this lesson, the instructor demonstrates how to solve **HackerRank’s Weather Observation Station 6** problem, which requires querying city names from a SQL table that start with a vowel (A, E, I, O, or U). The instructor presents two approaches: using the `LIKE` operator and using regular expressions.

## SQL Table Overview
- **Table Structure**:
  - `ID`
  - `City`
  - `State`
  - `Latitude`
  - `Longitude`
  
The focus is on the `City` column, and duplicate city names must be excluded from the results.

## Solution 1: Using `LIKE`
```sql
SELECT DISTINCT city
FROM station
WHERE city LIKE 'A%'
   OR city LIKE 'E%'
   OR city LIKE 'I%'
   OR city LIKE 'O%'
   OR city LIKE 'U%'
```
- **Explanation**:
  - Retrieves distinct city names that start with a specified vowel.
  - The condition is case insensitive.

## Solution 2: Using Regular Expressions
```sql
SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(city, '^[AEIOU]', 'i')
```
- **Explanation**:
  - This approach uses a regular expression to match cities beginning with a vowel more succinctly.
  - `^` denotes the start of the string.
  - `[AEIOU]` specifies that the first character should be one of the vowels.
  - The 'i' flag makes the search case insensitive.

## Key Takeaways
- The `LIKE` approach provides a clear method but requires multiple conditions.
- The regular expression method is more concise and efficient for this task.
- Care must be taken not to confuse patterns between the two approaches, especially with symbols indicating matches.

In conclusion, both methods ultimately achieve the same goal, though regular expressions can significantly shorten the code.

Hey everybody, welcome to another lesson. In this lesson I will do weather observation
station 6 from the section SQL subsection basic select of hacker rank.
Here in this question we have the table station and then we're asked to query all the list
of city names that starts with a vowel, that is the first character has to be A, or E,
or I, or all or you. There are two ways that are approached this problem. The first solution
I'll do is with like, the second that I'll do is with regular expression. So let's get
started. Here I'll choose my SQL and I'll just visualize the data saying select star
from station so we can have a better understanding of what the table looks like.
So as you can see here, this is what the table looks like. There's an ID, city, state,
lat, and long W columns. We only care about city for this question so we're going to focus
on this second column here. So instead of star I'm going to say city but notice that
the question says your results cannot contain duplicates. So if there's a row with the same
city name, we need to eliminate that so it only appears once. So we say distinct before
the column city. Now we have to filter this so that only the city names that start with
a vowel can appear. So we're going to add a where clause here, then we're going to use
a like on the column city. So where city like and then first you're going to do the vowel
a has to start with a so we're going to say like quotes a percent so a is the first character
in the city name followed by anything so the percent sign here means they can follow a
by any character. But this is only capturing the cities that start with a and another thing
is this is case insensitive. So it doesn't matter if it's an uppercase a lowercase a
will catch the same. Now this is just for vowel a starts with a but it could also start
with e or I or O or U. So we have to do basically the same pattern. We're going to say or city
like starts with E so say E percent or city like starts with I so I percent within quotes
or city like quotes or percent so it starts with all or city like quotes you percent so
starts you so it will get all the city values where that city value either starts with a
or starts with E or starts with I or starts with O or starts with U. Let's run the code and see
and there you go. Those are all the cities that start with a vowel.
Now that's nice but this solution is pretty long. Can we make a short now teach you a second
alternative to this using regular expression. So instead of having all this massive like or like
or like like you just say where city rag X R E G E X P space and then I'm going to add some
regular expression here. So a regular expression we have to say starts with so in the beginning
of the string so I have to say circumflex and then brackets followed by any of these
characters within the brackets so it's either a or E or I or O or U. So this regular expression
saying at the beginning of the string followed by this character that could be either a or
E or I or O or U. Let's try it out. And that's it. I'm going to click submit code. As you can see
this is a much shorter solution using regular expression. Be careful when you write this
solution to not confuse if the pattern for like it's not the same thing the percent is different
when you do the regular expression there's an implicit it could be followed by whatever
whereas the like you have to explicitly say percent so it follows whatever. So that's it for
wet observation station six


"A lesson that teaches you how to solve the problem Weather Observation Station 7 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-7

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/
"

Weather Observation Station 7 | SQL Basic Select | HackerRank Solution

# Summary of Weather Observation Station 7 Exercise

In this lesson, the presenter discusses the SQL exercise "Weather Observation Station 7" from HackerRank, focusing on how to select city names from a database where the names end with a vowel (A, E, I, O, U) while ensuring no duplicates in the result.

## SQL Exercise Overview
- **Table Structure**: The table includes columns: ID, city, state, lat, and long.
- **Objective**: List distinct city names that end with a vowel.

## Solutions
Two SQL solutions are demonstrated:

### 1. Using `LIKE`
- **Query Steps**:
  - Select the `city` column with the `DISTINCT` keyword to avoid duplicates.
  - Use the `WHERE` clause combined with `LIKE` to filter city names ending in vowels:
    ```sql
    SELECT DISTINCT city
    FROM station
    WHERE city LIKE '%a' 
       OR city LIKE '%e' 
       OR city LIKE '%i' 
       OR city LIKE '%o' 
       OR city LIKE '%u';
    ```

### 2. Using Regular Expressions
- A more concise solution using regular expressions is also provided:
- **Query Steps**:
  - Utilize `REGEXP` to match city names with a pattern that captures endings with vowels. The pattern defined includes uppercase and lowercase vowels:
    ```sql
    SELECT DISTINCT city
    FROM station
    WHERE city REGEXP '[aeiou]$';
    ```

## Conclusion
Both solutions effectively accomplish the task, though the regular expression method is more succinct, highlighting the power of regex for pattern matching in SQL.

The presenter concludes by submitting the final SQL code for validation, completing the exercise.

Hey everybody, welcome to another lesson.
In this lesson I will do the exercise weather observation station 7 from the subsection
basic select of the section SQL from HackerRank.
In this question we're given the stable station that has the columns ID, city, state, lat,
and long w.
For the purpose of this question we're going to care about the city column.
So we're asked to list all the city names that start, that actually end with a vowel,
that is A, E, I, O, or U character.
And the result cannot contain the duplicate, so that's a distinct city, right?
So let's get started.
I'm going to do two solutions in my SQL.
The first one using like and the second one using regexp.
So let's get started by visualizing what the data looks like.
So I'm always like to do select star from station here to see what it looks like.
So this is what the data looks like.
As you can see there's multiple columns ID, city, state, lat, and long w.
We want to care about city for the purposes of this question.
So it's going to be the second column here.
So looking to find all the city names that end in a vowel.
So obviously this first one wouldn't appear nor would this.
So basically these two would have to appear, Glencoe and Chelsea.
So how can we do that?
Well, first we need to select instead of star we want the city name.
So we're going to select the column city.
And because we don't want any duplicates as you can see here,
we're going to add distinct before the name of the column city.
Now to further these things, we're going to add a where clause here at the end.
Where?
Now where the city, now we have to say in was ends with a vowel.
So we've got to do each vowel here.
So let's say ends in a.
So I'm going to say where city like quotes a.
Percent a.
Okay, so it could be whatever followed by the ending being the a vowel.
Okay, and then you got to do the same pattern here for every other vowel.
So or use the or city like quotes.
Percent e.
So it means ends in the e vowel and then our city like percent I.
So it ends in I or the city like percent o and Zino or city like.
Percent you and Zin you.
Now let's check it out run code.
And there you go.
That's the solution using like.
Now if you notice the solution is pretty long because you got to do city like or city like
or city like or city like there's a better version if you a short version if you use
regular expression.
Let me show you what it looks like.
So we're going to say where city but instead of like we're going to say reg X R E G E X
B and then we're going to do quotes here and we have to specify the regular expression pattern.
This one is different from the pattern for like.
Okay, so here we have to explicitly.
Okay, so we have a vowel.
What is it as a character that could be either a or e or I or all or you.
So we're going to say square brackets here and you're going to say a e I o you.
What does it mean here?
It means okay, I'm going to try to find capture a single character that could be either of these characters within the square brackets.
So it's either a or I or a or e or I or or you and then we have to say it's at the end right it's ending in that.
So we have to say right here the dollar sign that means okay this is the end of the string.
So it's a character at the end and anything before that it doesn't really matter could be anything.
So that's the difference with like in regular expression.
It's implicit that it could be followed anything before here but whereas like you have to explicitly say percent to say whatever comes before.
Well, that's run code and see what this does.
And if you notice I put lower case so the case does not matter or rag X here in my sequel.
So I have both capture uppercase and lowercase vowels.
And there you go.
That's the solution.
Let's click submit code.
And that was weather observation station seven.


"A lesson that teaches you how to solve the problem Weather Observation Station 8 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-8

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/
"

Weather Observation Station 8 | SQL Basic Select | HackerRank Solution

# Summary of Weather Observation Lesson

In this lesson, an exercise from HackerRank's SQL section is explored, specifically focusing on querying city names from a table named `station`. The goal is to retrieve distinct city names that start and end with a vowel. 

## Key Points

1. **Table Structure**:
   - The `station` table has the following columns: `ID`, `city`, `state`, `lat`, and `long_w`. The focus is primarily on the `city` column.

2. **Query Requirements**:
   - The task is to find city names that:
     - Start with a vowel (A, E, I, O, U).
     - End with a vowel.

3. **Approach**:
   - The problem can be divided into two parts: checking for the first character and checking for the last character being a vowel.
   - To avoid duplicates, the query involves selecting distinct city names.

4. **SQL Query Examples**:
   - The initial approach uses `LIKE` for matching:
     ```sql
     SELECT DISTINCT city 
     FROM station 
     WHERE (city LIKE 'A%' OR city LIKE 'E%' OR city LIKE 'I%' OR city LIKE 'O%' OR city LIKE 'U%')
       AND (city LIKE '%A' OR city LIKE '%E' OR city LIKE '%I' OR city LIKE '%O' OR city LIKE '%U');
     ```

5. **Optimized Regular Expression Approach**:
   - An alternative method using regular expressions (regex) is presented, which simplifies the query significantly:
     ```sql
     SELECT DISTINCT city 
     FROM station 
     WHERE city REGEXP '^[AEIOU].*[AEIOU]$';
     ```
   - The regex checks that the city name starts with a vowel (using `^`) and ends with a vowel (using `$`).

6. **Conclusion**:
   - The final SQL code successfully retrieves the desired list of city names that both start and end with a vowel, effectively combining knowledge from previous exercises.

This structured approach efficiently solves the weather observation problem by utilizing both SQL pattern matching techniques and regular expressions to achieve the desired output.

Hey everybody, welcome to another lesson. In this lesson I will do weather observation 8
and exercise from the subsection basic select of the section sequel from hacker rank.
This question has a table station that has the columns ID city, state, lat, and long w.
Now we're asked to query the list of city names that cannot contain duplicates
which have both vowels
as the first and last character. So this is like a combination of the previous exercises.
So it has to start with a vowel and
has to end in a vowel.
So we can split this problem into two problems, right?
First you have to figure out how do we do beginning in a vowel and then you figure out how to do ending in a vowel then you combine them.
So let's get started. I'm going to choose my sequel.
So I'm going to like to visualize the data. So I always do select star from
station to see what a day looks like. I'll click run code here.
So this is what the stable looks like. We have five columns here ID, city,
state, lat, and long w. So for the purposes of this question, we only care about the city column.
So we need to figure out how can we filter out all the
city names that do not start in a vowel and
do not end in a vowel. So basically we need or do not end in a vowel need something that
starts both
with a vowel and ends in a vowel. So
for example, this one would qualify as starts with E and
in E.
So let's get started here. So I need to select city names. I
narrow down to only the city column and because we cannot contain duplicates,
we select distinct city to eliminate any possible or repetition of the same city.
Now we have to add a clause here where so there's two ways you can do this that I want to do and
one is using like the other ones using reg X, the regular expression.
So using like is pretty long, regular expression is pretty short. So let me show you first with like.
So we're going to say, okay, we've got to figure out there's two problems, right? First start a vowel and then ends.
Let's do starting. So starting would be where city like
starts at A. So A percent or
city like
E percent or starts at E or city like
I percent starts at I or city like
O percent
starts at O or city like
U
percent starts with U. So
city can start A or city starts with E or
city starts with I or city starts with O or city starts with U.
Then you've got to grab all these things. I'm going to add parentheses. So it's obvious here too and
right, so it has to start
with a vowel and has to end now right here. How can we write the same equivalent thing but to end in a vowel?
So we're going to say city like
quotes percent A. So that means it and the city could
end in A or city like
quotes percent E could add in E or city like
quotes percent I could add in I or city like quotes percent O could add in O or city like
percent U could add in U. So select the distinct city from station where so we have either
starts has to start with a vowel and don't forget and has to end. So let's check it out.
And if you notice this is like a combination of the solutions from the previous questions where asked us to
find out all the city that starts with a vowel find out all the cities that end in a vowel.
So we just combine them
with an end clause here. Oh, there you go. Okay. Now this is very long.
So how can we do better? We can do better regular expression. So here
we're going to say instead of all this stuff city like whatever I'm going to replace that with city reg X
and we're going to write an expression here to start in a vowel. So start is circumflex
and then followed by a character that could be a vowel. So I'm going to use square brackets
A E I O U. So this means starting out the string followed by a single character that could be any of these
characters either A or E or I or O or U. And this means starts with a vowel.
Now do the same kind of pattern
in the end here. So instead of all this stuff, we're going to say city reg X
quotes. Now we're going to do ending. Now ending is the dollar sign at the end.
And then before that we got to say square brackets A E I O U. So this means
you're going to match a single character that could be any
of these could be either A or E or I or O or U followed right at the end of the string.
And this is kind of different from the pattern of like where we have to explicitly say percent to
say whatever but regular expression, you don't have to say it's implicit that this is there could be
anything before this pattern. So let's run code and see what we get.
Okay, so that's it list of city names that both starts and ends in a vowel. Click submit code.
And that was weather observation station eight.


A lesson that teaches you how to solve the problem Weather Observation Station 9 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-9

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 9 | SQL Basic Select | HackerRank Solution

```markdown
# 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;
   ```
2. Construct the SQL query:
   ```sql
   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:
   ```sql
   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:
   ```sql
   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.
```

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.


A lesson that teaches you how to solve the problem Weather Observation Station 10 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-10

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 10 | SQL Basic Select | HackerRank Solution

# Summary of Weather Observation Station SQL Problem

In this tutorial, the speaker discusses solving a SQL problem related to the "Weather Observation Station 10" challenge on HackerRank. The task involves querying a table named `station` with the following columns: `ID`, `city`, `state`, `lat_n`, and `lon_w`. The goal is to retrieve city names that do not end with a vowel and eliminate any duplicate city names.

## Steps to Solve the Problem

1. **Initial Query**: The speaker starts by selecting all data from the `station` table to visualize it:
   ```sql
   SELECT * FROM station;
   ```
   
2. **Identifying Vowels**: The vowels to avoid at the end of city names are: `a`, `e`, `i`, `o`, `u`.

3. **Constructing the Query**:
   - Using `LIKE`, the condition for city names not ending with vowels is constructed:
     ```sql
     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';
     ```

4. **Using Regular Expressions**:
   - The speaker explains a more concise way to achieve the same result using regular expressions:
     ```sql
     SELECT DISTINCT city FROM station
     WHERE city NOT REGEXP '[aeiou]$';
     ```

5. **Alternative Regex Approach**: 
   - Another method using the negation inside the pattern:
     ```sql
     SELECT DISTINCT city FROM station
     WHERE city REGEXP '^[^aeiou].*';
     ```

## Conclusion
The tutorial concludes by showcasing different methods to query city names that do not end with vowels, demonstrating both SQL `LIKE` clauses and regular expressions for brevity and efficiency.

I'll do weather observation station 10 on the subsection basic select of the SQL section
from hacker rank.
So we're given a table station that has the columns ID city state lat underscore n loner
underscore w and we're asked to query the city names that do not end with vowels and
make sure to eliminate any duplicate city name.
So to solve this problem I'm going to do first with like and I'm going to show you how to
do regular expressions.
So let me choose my SQL here.
So I first like to always select everything so I choose select star from station to visualize
the data and see what it looks like.
As you can see here this is what the data looks like each row and for the purposes of
this question we are only interested in the city name that's the second column.
So how can we say do not end with vowel?
Well vowels are the characters a e i o or u.
So we can say it does not end in a and does not end in e and does not end in i and does
not end in o and does not end in u.
So five things.
So let's add to the where.
So where I'm going to say column the city like how can I say do not end with a well how can
I say and with a so I can say quotes percent a so it could be anything followed by a at
the end but this is to to and in a how can I say not and in a I can say city not like
so it means does not end in a so I just copy the same pattern for every single vowel and
combine them with and so it cannot and in a 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 we're going to query from station where the city does not end in a and does not end
in e and does not end in I and does not end in o and does not end in u.
So all of those and because we just interested in the city name here instead of star say
city and since they want us to eliminate any duplicate city names that might appear again
in different rows we'd say distinct before the city column here let's test it out
and there you go now I want to show you a shorter version using reg X so instead of
typing every single thing like this I can just say where city are e g e x p for a regular
expression and space quotes and you have to say the pattern in this case that do not
end in a vowel there's two ways you can do this I can use a not here so where city not
so I can negate whatever this is in this case it would have to be the pattern to end in a
vowel so when you say not that means does not end in a vowel to do that I can I say square
brackets a e I owe you that means a single character that could be either a or e or I
or or you and then if I want it at the end I get to say dollar sign meaning end of the
string so this pattern would match any of these vowels at the end and because of the
not it would see it would mean does not end let's test it out and there you go now one
more thing I want to show you how to do without the not so instead of having not here you
can also use the not within the pattern itself and to do that you you add a circumflex right
after the square brackets notes is not before don't confuse this the carrot character might
mean beginning of the string if you put outside but within the square brackets here it means
not any of these following characters so this means not a or e or I or or you so let's run
code and there you go
and that was weather observation station then


A lesson that teaches you how to solve the problem Weather Observation Station 11 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-11

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 11 | SQL Basic Select | HackerRank Solution

```markdown
# 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.
```

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


A lesson that teaches you how to solve the problem Weather Observation Station 12 from the subcategory Basic Select from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-12

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 12 | SQL Basic Select | HackerRank Solution

# 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
1. **Requirement**: Select city names that **do not start** or **end** with a vowel, specifically 'A', 'E', 'I', 'O', 'U'.
2. **Distinct Results**: Use `DISTINCT` to avoid duplicate names.

## SQL Query Breakdown
1. **Selecting Data**:
   - Start by selecting data to visualize the structure: 
     ```sql
     SELECT * FROM station;
     ```
   - Focus on the `city` column.

2. **Building the Query**:
   - Begin constructing the query to select distinct city names:
     ```sql
     SELECT DISTINCT city
     ```
   - To filter out cities that start or end with a vowel, use the `WHERE` clause with `LIKE`:
     ```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%'
     ```

3. **Filter Conditions**:
   - For the end of the string, use the reverse:
     ```sql
     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:
     ```sql
     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';
     ```

4. **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:
       ```sql
       city NOT REGEXP '^[AEIOU]'
       ```
     - To ensure it does not end with a vowel:
       ```sql
       city NOT REGEXP '[AEIOU]$'
       ```

5. **Final Version with Negation in Brackets**:
   - An even more streamlined version can include the negation inside brackets:
     ```sql
     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.

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.


A lesson that teaches you how to solve the problem Weather Observation Station 13 from the subcategory Aggregation from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-13

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 13 | SQL Aggregation | HackerRank Solution

# Summary of Weather Observation Station 13

In this transcript, the speaker discusses how to approach the SQL task for the Weather Observation Station 13 challenge on HackerRank. The task involves querying a table named `station`, which consists of five columns: `ID`, `city`, `state`, `lat_n`, and `long_w`. The goal is to calculate the sum of all northern latitudes (`lat_n`) that lie between two specific values: 38.788 and 137.2345, and then truncate the result to four decimal places.

## Key Points Discussed

1. **Understanding the Task**:
   - The primary focus is on the `lat_n` column from the `station` table.
   - The values to be summed must be greater than 38.788 and less than 137.2345.

2. **Truncation vs. Rounding**:
   - **Truncation**: This process involves dropping any digits after the specified decimal place without rounding.
   - **Rounding**: Unlike truncation, rounding adjusts the number based on the value of the digit following the last retained digit.
   - An example highlights the difference between `round` and `truncate`.

3. **SQL Query Construction**:
   - The speaker demonstrates how to construct the SQL query:
     ```sql
     SELECT TRUNCATE(SUM(lat_n), 4) 
     FROM station 
     WHERE lat_n > 38.788 AND lat_n < 137.2345;
     ```
   - This query sums the valid `lat_n` values and truncates the result to four decimal places.

4. **Final Thoughts**:
   - The result of the query provides the required truncated sum of latitudes meeting the criteria.

By the end of the task, the speaker successfully formulates the query and completes the weather observation station challenge.

Hey everybody, I will do weather observation station 13 from the aggregation subsection
of SQL in hacker rank.
So we're given this table station with five columns ID, city, state, lat, underscore,
n, and long, underscore, w.
And we're asked to query the sum of all the northern latitudes, lat, underscore, n from
station.
So having values greater than 38.788 and 137.2345 truncate your answer to four decimal places.
Okay, so let's break this down.
So we only care about lat underscore and for this question, that's the only call let me
care about.
And we have values greater than whatever this means just where lat underscore and is greater
than this number and less than that number.
Now truncate that means you drop the decimal places, meaning if there's more than four
decimal places, you drop everything after the fourth one to the right.
So this is kind of different from round where round we round up or down truncate just drops
the digits without rounding up and down.
So technically truncate is different from round.
Yeah, so let's get started.
I always like to visualize the data first with a select star from station.
And here's the data we only care about lat underscore and that's the this these values
here and we first has make sure the values that are within the range that we're given
here 38.788 and 137.2345, then we take all those values within that range and add them
up and get a sum and then finally we drop all the digits if there's more than four decimal
places to the right of those four.
Yeah, so let's go.
So here I'm going to say first let's do the where so where lat underscore and has to be
greater than 38.788 and lat underscore and has to be less than 137.2345.
Now that's fine.
What do we select?
We select well, first it's lat n right but we got to do add them up.
So to add them all up we call the sum function.
And that's nice it's going to sum but if you run the code
this is what you get you see the value point and then we got four digits followed by four
more digits.
So because it's asking us to truncate your answer to four decimal places that means everything
after this fourth after the dot here to the right has to be dropped and that's different
from rounding.
I'll show you the difference right now.
If you comment this so it doesn't get executed with dash dash space we can select for example
this number here I want to copy it.
First I want to show you what round does to four decimal places.
Now this one is going to always round or truncate to 8135 because it's closer to that but I
want to change this value here to say 59 here.
So if you round it to four it would be what 81 36 but if you truncate meaning just drop
the nine it would be 81 35.
So that's what you're going to see here when I show you what truncate does instead of round.
As you can see round the first column rounds up to point 81 36 the five became six whereas
the truncate it just dropped all the digits to the right of the five and didn't round
anything so it still capped 81 35.
So we want this second one truncate not round to be technically correct for this problem.
So I'm going to remove this uncomment and then I'm going to say truncate parentheses
past this whole thing think of it as one thing past to the function and second argument
comma truncate to four decimal places comma four closed parentheses and I'm going to submit
this.
Okay so we got there truncate it to four so 81 35 here after the decimal point and that's
a ladder observation station 13.


A lesson that teaches you how to solve the problem Weather Observation Station 14 from the subcategory Aggregation from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-14

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 14 | SQL Aggregation | HackerRank Solution

# WebDor Observation Station 14 - SQL Lesson Summary

In this lesson, we focus on solving a problem from the SQL aggregation section on HackerRank. The task involves querying a `station` table with the following columns: `ID`, `city`, `state`, `lat_n`, and `long_w`.

### Problem Statement
We need to retrieve the greatest value of `lat_n` that is less than `137.2345`, truncating the result to four decimal places.

### Key Considerations
1. **Filtering Values**: We need to filter for `lat_n` values that are less than `137.2345`.
2. **Truncation**: The result should be truncated to four decimal places, which means dropping any digits beyond the fourth decimal without rounding.
3. **Finding Maximum**: We are looking for the maximum value within the filtered results.

### SQL Approach
1. Start by exploring the data with the query:
   ```sql
   SELECT * FROM station;
   ```

2. Apply the `WHERE` clause to filter out values that meet the criteria:
   ```sql
   WHERE lat_n < 137.2345
   ```

3. Use the `MAX` function to get the greatest value:
   ```sql
   SELECT MAX(lat_n) FROM station WHERE lat_n < 137.2345;
   ```

4. To truncate to four decimal places, incorporate the `TRUNCATE` function:
   ```sql
   SELECT TRUNCATE(MAX(lat_n), 4) FROM station WHERE lat_n < 137.2345;
   ```

5. This query results in `137.0193`, correctly truncated to four decimal places.

### Conclusion
The final SQL query effectively retrieves the desired value while adhering to the requirements of filtering, truncating, and finding the maximum value. This method ensures we handle the data correctly and return the required results.

Hey everybody, welcome to another lesson.
I will do WebDor observation station 14 from the section aggregation from SQL in Hacker
Rank.
So this question we have the station table with ID city state of lat underscore n and
longer underscore w columns.
And we're asked to query the greatest value of lat underscore n that is less than 137.2345
truncate to four decimal places.
So I got three things to keep in mind, right?
So filter out meaning you only take the values for the purpose of this that's less than 137.2345
for lat underscore n.
And at the end we should also truncate to four decimal places, meaning we drop everything
after the fourth digit to the right of the dot.
It's different from round, so we use truncate instead of round to be technically correct.
And yeah, greatest value just means taking the maximum value.
So there are many different ways to do this problem, I'll do with the max function.
So I always like to start here, let me choose my SQL select star from station to visualize
what the data looks like and have a better idea of what to do.
Okay, so the purposes of this question, we care about the lat underscore n column here,
I see these values.
So trying to find the greatest one that's less than 137.2345.
So let's go here.
First let's add a where lat underscore n has to be less than 137.2345.
But the value is greater than that, we don't care about it, we won't consider it.
And then instead of star here, I'm going to first say lat n, but that will just take us
all the rows of the lat n values, but we have to take the maximum one.
So one way of doing this, it could order by the value itself and take the very first row
if you are descending for the greatest, but we can also use the max function here.
So if I say max parenthesis of lat n, that will take the maximum value that is the greatest
that it can find within the selection range that for this where here.
And because we want to truncate, meaning you want to drop all the digits to the right of
the fourth one after the dot coming from the left, right?
We have to say truncate, past max lat n comma four, that means only keep four decimal places.
And that's different from round round around up or down truncate just drops the digits
without rounding.
Let's run the code.
And there you go.
We got 137.0193 and we got truncated.
We only have four digits to the right hand side of the dots.
And that's Watter observation station 14.


A lesson that teaches you how to solve the problem Weather Observation Station 15 from the subcategory Aggregation from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-15

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 15 | SQL Aggregation | HackerRank Solution

# Weather Observation 15 - SQL Query Guide

## Overview
In this lesson, we'll cover Weather Observation 15 from the SQL aggregation section on Hackerrank. We will be working with a stable station dataset containing the following columns:
- **ID**
- **City**
- **State**
- **Lat (latitude)**
- **Long (longitude)**

## Task
We need to query the western longitude (`long_w`) for the largest latitude (`lat_n`) that is less than 137.2 and greater than 345. Additionally, the result must be rounded to four decimal places.

## Process
1. **Visualize the Data**: Start with the following query to understand the data structure:
   ```sql
   SELECT * FROM station;
   ```

2. **Filter Rows**: We need to filter the rows to keep only those where the latitude (`lat_n`) is within the specified range:
   ```sql
   WHERE lat_n < 137.2 AND lat_n > 345;
   ```

3. **Order by Latitude**: To find the largest latitude, order the results in descending order:
   ```sql
   ORDER BY lat_n DESC;
   ```

4. **Limit to One Result**: Select only the first row to get the highest latitude within the range:
   ```sql
   LIMIT 1;
   ```

5. **Select the Longitude**: Finally, retrieve the longitude (`long_w`) from this row. Since the result needs to be rounded to four decimal places, use the `ROUND` function:
   ```sql
   SELECT ROUND(long_w, 4);
   ```

## Complete SQL Query
Putting it all together, the complete SQL query will look like this:
```sql
SELECT ROUND(long_w, 4)
FROM station
WHERE lat_n < 137.2 AND lat_n > 345
ORDER BY lat_n DESC
LIMIT 1;
```

## Final Result
The rounded longitude value will be output as, for example, `0.2465`. 

This concludes the lesson for Weather Observation Station 15. Remember to always round your final results to the specified number of decimal places. Happy querying!

Hey everybody, welcome to another lesson. I will do weather observation 15 for the subsection
aggregation of the SQL section from Hackadrank. In this question we're given the stable station
that has five columns ID, city, state, lat, underscore, n, and long underscore, w. And
we're asked to query the western longitude that's the long underscore, w value for the
largest nor the latitude, lat, underscore, n. That's less than 137.2 and is 345. And make
sure to round not truncate, keep that in mind, your answer to four decimal places. So the
thing with this question is we're asked to find a row that has the largest lat n that's
less than 137.2 and 345. And from that row, we have to take the value long underscore,
w. And to do that question, I'm going to do an order by and I'll order by the and take
the very first row as the greatest lat n score n. Let me show you what I mean. First,
I will choose my SQL and I always like to visualize the data. So I'll select star from
station to see what it looks like. Okay, so we have all these values for lat underscore
n here, we have to find out what's the greatest, the row of the greatest and from that row,
we take this long underscore w value. So what I'm going to do here, let's do where let's
make sure the lat underscore n is within the range that's required by the question. So
it has to be less than 137.2 and 345. That's fine. And then from that, let's do the following.
So I want to order by the lat underscore n value, but descending meaning the very first
row is going to have the greatest value. So I'm going to say order by lat underscore n
and the ESC to say descending. Now this is going to give us multiple roles, right? But
I only want the first row that has the greatest value for lat underscore n within this range.
So I'm going to say limit one to only take the very first row. And then from that, we
have to select what we have to take the value from long underscore w. So we're going to
say long underscore w here. And this is the answer, but because it's not rounded, you
can see there's like eight digits after to the right of the decimal point. So we'll have
to make sure to round your answer to four decimal places, meaning this one would actually
become 0.2465. So to do that, you go here where we have long w and you say round parentheses,
and then comma has to be four decimal places, close parentheses, this will take that value
and just round to four digits to the right of the decimal place. Okay, so now we have
0.2465. Let me submit. And that's whether observation station 15.


A lesson that teaches you how to solve the problem Weather Observation Station 16 from the subcategory Aggregation from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-16

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 16 | SQL Aggregation | HackerRank Solution

# Weather Observation Lesson Summary

In this lesson, we're focusing on querying a database for weather observation data. Here's a concise breakdown of the key points discussed:

## Database Table
- **Table Name:** `station`
- **Columns:** 
  - `ID`
  - `city`
  - `state`
  - `lat_n`
  - `long_w`

## Objective
- **Task:** Query the smallest `lat_n` value that is greater than `38.778`.
- **Output Requirement:** Round the result to four decimal places.

## Steps to Achieve the Query
1. **Visualize the Data:** 
   - Execute the command: `SELECT * FROM station;`
   - Focus on the `lat_n` column values.

2. **Filter and Find Minimum:**
   - Use a `WHERE` clause to filter `lat_n` for values greater than `38.778`.
   - Retrieve the smallest value:
     - Use `MIN(lat_n)` to find the minimum latitude after filtering.

3. **Round the Result:**
   - Apply the `ROUND` function to format the result to four decimal places.

## Final Result
- The query yields: `38.8526` (rounded to four decimal places).

This method effectively identifies the smallest latitude from the selected observations over the specified threshold.

Hey everybody, welcome back to another lesson.
I will do weather observation, station 16 on the section aggregation from SQL Hacker
Rink.
We're given the table station that has five columns ID, city, state, lat, underscore,
n, long underscore, w.
We're asked to query the smallest lat underscore, n that is greater than 38.778.
So around the answer to four decimal places.
Okay, so let's get started.
So I always like to visualize the data.
So I say select star from station, run code.
Okay, so here for the purposes of this question, we only care about the column,
lat, underscore, n, these values here.
So for the range that's greater than 38.778, what value is the lowest, right?
You have to find the lowest out of all these numbers.
So let's go.
We here have to say, aware.
First, lat underscore n has to be greater than 38.778.
And then we have to take, okay, so we have the lat underscore n values.
And we have to take the smallest.
And for that, we could do an order by and take only the row of the smallest.
The first one if you do ascending, or you could use the function min.
So I'm gonna use min of lat underscore n.
So out of all the values that were found, it takes the smallest one.
And because we want to round the answer to four decimal places,
if you think this is one thing that's the actual value of the min,
you have to say round for the function call.
And two decimal places has to be four here for four decimal places.
And there you go, we have 38.8526, exactly four digits to the right-hand side of
the decimal point, and it was being rounded.
And that's weather observation station 16.


A lesson that teaches you how to solve the problem Weather Observation Station 17 from the subcategory Aggregation from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-17

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 17 | SQL Aggregation | HackerRank Solution

# Summary of Weather Observation Station 17

In this lesson, we focus on querying data from the "station" table as part of HackerRank's weather observation station task.

## Task Overview
- **Objective**: Retrieve the `long_W` value for the row that has the smallest `lat_n` greater than 38.778.
- **Columns in the Station Table**:
  - `ID`
  - `city`
  - `state`
  - `lat_n`
  - `long_W`

## Steps to Solve the Problem
1. **Initial Query**: Begin by visualizing the data with a simple SQL query:
   ```sql
   SELECT * FROM station;
   ```
2. **Filtering Data**: Use a `WHERE` clause to filter out rows where `lat_n` is greater than 38.778.
3. **Ordering Results**: Use `ORDER BY lat_n ASC` to sort the results from smallest to largest.
4. **Limiting Output**: Use `LIMIT 1` to return only the first row, which would have the smallest `lat_n` greater than 38.778.
5. **Selecting the Desired Value**: Finally, select the `long_W` value and round it to four decimal places using the `ROUND` function:
   ```sql
   SELECT ROUND(long_W, 4)
   FROM station
   WHERE lat_n > 38.778
   ORDER BY lat_n ASC
   LIMIT 1;
   ```
6. **Final Output**: After running the query, the output displays the rounded value of `long_W` (70.1378) for the required row.

## Conclusion
This walkthrough demonstrates how to efficiently query specific data from a table using SQL by applying filtering, ordering, and rounding functions. This completes the requirement for Weather Observation Station 17.

Hey everybody, welcome to another lesson. I will do weather observation station 17 from the subsection aggregation
Section sequel from hacker rank. So we're given the table station that has five columns ID city state lat underscore n and long underscore
W so we're asked to query the long underscore W value for the row that has the smallest lat underscore n
That's greater than 38778 that is the lat underscore n has with it be within that range of
Greater than 38.778 and make sure to round to four decimal places
So let's get started. I choose my sequel and I like to visualize the data initially. So I select star from station
Click run code
Okay, so if you see these values here for the lat underscore n if you have greater than
38.778 that means this value here wouldn't be included
but you for example, you would have to take the lowest and
Then from that row you have to take the value from
Long underscore W. So to do this. I'm gonna use order by
the value of
lat underscore and I'm gonna find a row that has the
Smallest and from that row I'm gonna select the along W. So here. I'm gonna say first
Let's add a where the lat underscore w is greater than 38.778 and
Then I'm gonna do order by
The value of lat underscore in and in this case it has to be from the lowest to the highest
So I can take the very first row that's gonna be the smallest. So this is by default ASC
I it's implicit. You don't have to say it
but if I want to be explicit as right here ASC ascending and
I limit one to only take the very first row out of all this these rows because I know the first one is gonna have the
smallest value and
Then from that we have to select the value of long and short W. So that's what we put in select
But because we want to round it to four decimal places
We have to call the round function past the long W value here comma four decimal places close parentheses
run code now
And there you go have seventy point thirteen seventy eight has been routed to four decimal places submit code
And
That's weather observation station 17


A lesson that teaches you how to solve the problem Weather Observation Station 18 from the subcategory Aggregation from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-18

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 18 | SQL Aggregation | HackerRank Solution

# Summary of Weather Observation Station 18

In this lesson, we are working on a SQL problem from HackerRank involving a table named `station` with columns ID, city, state, lat_n, and long_w. The goal is to calculate the Manhattan distance between two points, defined as:

- $ p1(a, b) $: where $ a $ is the minimum value of `lat_n` and $ b $ is the minimum value of `long_w`
- $ p2(c, d) $: where $ c $ is the maximum value of `lat_n` and $ d $ is the maximum value of `long_w`

## Manhattan Distance Formula
To compute the Manhattan distance, we apply the following formula:

\[
\text{Distance} = |x_1 - x_2| + |y_1 - y_2|
\]

Where:
- $ x_1 = a $ (minimum of `lat_n`)
- $ x_2 = c $ (maximum of `lat_n`)
- $ y_1 = b $ (minimum of `long_w`)
- $ y_2 = d $ (maximum of `long_w`)

### SQL Query Steps
1. Use the `SELECT` statement on the `station` table to retrieve the necessary values.
2. Apply the formula to calculate the distance, ensuring to use the `ABS()` function for absolute values.
3. Round the result to four decimal places using the `ROUND()` function.

### Example Query Structure
```sql
SELECT ROUND(
    ABS(MIN(lat_n) - MAX(lat_n)) + 
    ABS(MIN(long_w) - MAX(long_w)), 
    4
) AS Distance
FROM station;
```

### Result
Upon executing the query, the resulting Manhattan distance is calculated to be **259.6859**.

This concludes the lesson on Weather Observation Station 18.

Hey everybody, welcome to another lesson, Weather Observation Station 18, from the subsection
aggregation of the SQL section from HackerRank.
So we're given the table station that has the columns ID, city, state, lat, underscore
n and long underscore w.
So it's telling us to consider p1 of a comma b and p2 of cd to be two points in a 2D plane.
It's asking us to query the Manhattan distance.
It's basically a formula.
We're going to take the x values, subtract them, take the absolute value, add to the
subtraction of the y values, absolute value of that, and then you add them all up.
And if you want to find out more, click this link.
And basically, we care about is this part here.
So we have p1 at x1, y1, right, the points on the plane, and p2 at x2, y2, another point,
and we do this formula here, where we subtract the x values, take the absolute value, and
we subtract the y values, take the absolute value, and then add them up.
Now we have to map these values x1, x2, y1, y2 to whatever is in the problem here.
So they give us the mapping here.
You can see the p1, a b, and p2, cd.
So it specifies what a is, b is, cd, and so on.
Basically, a is the minimum value of lat underscore n, b is the minimum value of long
underscore w, c is the maximum of lat underscore n, and d is the maximum of long underscore
w.
So you can use those in the formula.
So let me copy the formula here.
This so you can better understand the mapping here.
Make sure to use my SQL, because otherwise it might not work in the other one.
So I'm going to comment this with a dash dash space so you can have it here as reference.
So basically, we have to figure out what's x1, what's x2, to figure out the subtraction
and the absolute value here.
So x1 here would be p1's a value, a happens to be the minimum of lat n.
So we're going to say minimum of lat n, that's the x1 here.
Now x2 is the p2's c, so c happens to be the maximum value of lat underscore n.
So we're going to minus max of lat underscore n.
And we take these two, and after subtraction, you have to take the absolute value.
So I'm going to say a, b, s, short for absolute value of everything here.
And basically, you do the same for the other one, the other part here.
So that one would be what?
Y1.
Y1 happens to be, in this case, b.
And b happens to be the minimum of long w.
So basically, min of long w.
And then minus y2, which happens to be maximum of long w.
And you take absolute value of everything here, right?
Think of this thing as one thing.
And now you have the second part.
Now you have to take these two and some, and that's the actual thing we want to calculate
here.
Now to do this, we have to do select, rom, and then the table station.
Now they want us to scale around it to four decimal places.
So you have to take this whole thing here, think of it as one thing.
I'm going to cut it for the time being.
And I'll say round parentheses, close parentheses, and I'm going to paste it inside.
And I'm going to say comma and then four decimal places.
So we round the result of this expression here.
Okay, let's run the code and see what we get.
And there you go, 259.6859.
And that's weather observation station 18.


A lesson that teaches you how to solve the problem Weather Observation Station 19 from the subcategory Aggregation from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-19

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 19 | SQL Aggregation | HackerRank Solution

```markdown
# Weather Observation Station 19 - SQL Exercise Summary

In this lesson, we explore the SQL exercise "Weather Observation Station 19," part of the aggregation subsection on HackerRank. The exercise involves querying a table called `station` with columns for ID, city, state, latitude (`lat_n`), and longitude (`long_w`). The primary focus is on calculating the distance between two geographical points defined by their latitudes and longitudes.

## Problem Breakdown

### Input
- We need to work with the columns `lat_n` and `long_w` to determine two points:
  - Point 1 ($p1$): Minimum latitude and longitude.
  - Point 2 ($p2$): Maximum latitude and longitude.

### Distance Calculation
The distance $d$ between two points $(x_1, y_1)$ and $(x_2, y_2)$ is calculated using the formula:
\[ 
d = \sqrt{(x_2 - x_1)^2 + (y_2 - y_1)^2} 
\]
Where:
- $x_1$ is the minimum `lat_n`
- $x_2$ is the maximum `lat_n`
- $y_1$ is the minimum `long_w`
- $y_2$ is the maximum `long_w`

### SQL Translation
Using SQL functions:
- Use `MIN()` and `MAX()` functions to obtain the minimum and maximum values for `lat_n` and `long_w`.
- Use `POW()` to square the differences.
- Use `SQRT()` to calculate the square root.
- Finally, use `TRUNCATE()` or `ROUND()` to format the output to four decimal digits.

### Final SQL Query
The final SQL query will look like this:
```sql
SELECT TRUNCATE(SQRT(POW(MAX(lat_n) - MIN(lat_n), 2) + POW(MAX(long_w) - MIN(long_w), 2)), 4) AS distance
FROM station;
```

### Output
This query calculates the distance based on the given data and formats it to four decimal points.

## Conclusion
The exercise allows for flexibility in how we approach the rounding/truncation of the output format. Using mathematical functions in SQL effectively aids in solving the problem accurately. 

This completes the task of the Weather Observation Station 19 exercise.
```

Hey everybody, in this lesson I will do weather observation station 19, an
exercise from the aggregation subsection of the SQL section in hacker
rank. Let's get started. We're given this question that has the station table
with five columns ID, city, state, lat, underscore n and long underscore w. For the
purposes of this question we only care about lat underscore n and long
underscore w. So we consider p1 of a comma c and p2 of b comma d to be two
points on a 2d plane where a, b are the respective minimum maximum. So basically
the x of p1 is the minimum lat underscore n and the x of p2 is the
maximum lat underscore n and then similarly c is going to be the minimum
long w and d, that's the y for p2, is going to be the maximum long w. So it's
asking us to query the collision distance between the two points and format it
to display four decimal digits. It doesn't say if it's truncate or round so you
can use either one. So if you want to find out more about this, basically the
formula is this, you got p and q two points on a plane, you want to find out
the distance between them. The formula is like this, you got, if you take the x of
the first point, subtract the x of the other one, right? You have q1 minus p1
basically the x's here on the x-axis and then you subtract them square. Then you
take the y values, right, from q and p, right, q2 minus p2, square that and you
add them up and then finally if you take the square root of everything you
should get the distance. Let's get back to that. So basically the formula is going
to be some x1, let's say x2 minus x1, square that, add the y2 minus y1, square
that and then the whole thing we take the square root. Okay now let's translate
this to SQL. I'm going to use my SQL by the way, let me choose my SQL here.
And basically what's x2, what's x1? Here x2 is going to be max of lat underscore
n, x1 is going to be the minimum. So minimum lat underscore n. So we got x2
minus minimum lat underscore n, it's x1. So you take these two and we have to
square. If you want to square this multiply by itself, right, so you could do this
times itself or you could leverage some of the mathematical functions from
SQL. If you go to my SQL, the docs, there's some mathematical functions you can
use. The one I'm interested in is pow or power. This one returns the value of x,
raised to the power of y. So the first argument is whatever you want to raise
and y is the power. In this case would be 2, right? So going back there, instead of
repeating myself, I'll just use pow. So here take this whole thing, pass it to the
pow function. Now the second argument here inside the outer parentheses, you
have to say 2 because we want to take this and square it. So that's fine for
this part here, the x2 minus x1 square. Now let's do the other one, it's the y's.
The y's would be this d minus c and if you can see what's d and c. C is the
minimum and d is the maximum of long-eater-score w. So basically you do
max of long-eater-score w, that's going to be d minus the minimum of long-eater-score
w, that's going to be c, right? The y1 value here from p1. So we do the same thing,
you have to square this. So I'll just use pow of the whole thing, put parentheses
around and inside as the second argument 2 to say square. Now that we have these
two, we've got to add them up. So I'm going to add them up. Now we have to take the
square root of this whole thing. Now to do that there's this sqrt function if
you look at the docs. This one here, sqrt of x takes the square root of the value.
So I can do that. Takes this whole thing, think of it as one thing, pass it to sqrt
parentheses around it and that will take the square root. Now with that in mind I
can just select that whole thing from station. But there's this detail about
displaying four decimal digits. So I can either truncate or round. I'll just use
truncate. It doesn't say which one it wants. So truncate this whole thing and
add the parentheses around it. Don't forget. Now let's say inside the outer
parentheses comma or to say we want to drop everything after the fourth digit
to the right of the decimal. So we only get in effect after the dot four
digits. And there you go, 184.1616, only four after the dot.
And I'll submit.
And that's Watter Observation Station 19.


A lesson that teaches you how to solve the problem Weather Observation Station 20 from the subcategory Aggregation from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/weather-observation-station-20

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Weather Observation Station 20 | SQL Aggregation | HackerRank Solution

# Summary of SQL Lesson on Hacker Rank's Observation Station 20

In this lesson, we explored a SQL task from Hacker Rank focusing on calculating the median latitude value (lat_n) from a table named `station`, which consists of five columns: ID, city, state, lat_n, and long_w. The primary goal is to determine the median of the lat_n values by sorting them and identifying the midpoint value. 

## Steps to Find the Median

1. **Count Rows**: First, determine the total number of rows in the `station` table using:
   ```sql
   SELECT COUNT(*) FROM station;
   ```
   
2. **Calculate Midpoint**: To find the median, calculate the midpoint based on the total row count. If the number of rows is odd, the median is the middle value. If even, it’s the lower of the two middle values. To achieve this, use the `FLOOR` function to avoid decimal results when dividing by two.

3. **Use Offset**: Leverage the SQL `OFFSET` function to skip all rows before the calculated midpoint. 

4. **Limit to One Row**: After offsetting, use `LIMIT 1` to get just the median value.

5. **Prepare Statement**: Since SQL does not allow direct use of variables for the offset in some cases, a prepared statement is used. This involves:
   - Preparing the query with `PREPARE`.
   - Using placeholders (`?`) for variables.
   - Executing the prepared statement with the actual variable.

6. **Casting to Integer**: Cast the row count to an integer to ensure SQL understands it properly for the OFFSET parameter.

### Example of Final Query
Here’s a consolidated example of the SQL query:
```sql
SET @row_count = (SELECT COUNT(*) FROM station);
SET @mid_index = FLOOR(@row_count / 2);

PREPARE stmt FROM 'SELECT ROUND(lat_n, 4) FROM station ORDER BY lat_n LIMIT 1 OFFSET ?';
EXECUTE stmt USING @mid_index;
```

### Result
After executing the code, the final result returned is the median latitude value, specifically rounded to four decimal places, yielding an answer of `83.8913`.

This lesson effectively demonstrates how to calculate the median using SQL functions and techniques involving counting, offsets, and preparation of statements. 

---

Thus concludes our lesson on the weather observation station problem.

Hey everybody, welcome to another lesson on whether Observation Station 20 from the subsection
aggregation of the SQL section from Hacker Rank.
So let's get started.
But given this question, it has the table station with five columns ID, city, state,
lat, underscore, and longer underscore w.
So basically for the purposes of this question, we only care about lat underscore n.
So we want to find out what the median value is for all the lat underscore n values.
Basically you take all the values for lat underscore n, sort them, ascending, and you
got to take the midpoint.
If it's odd, for example, if you had like three values, right, the midpoint would be
the second one.
Okay.
So if it's even two values, that's a little bit weird, but we can take the lower one,
which for example, if you have two elements, you can take the first one.
Okay.
So how can we do this?
Well, I don't think there is a medium function we can leverage conveniently to use.
So we got to do some a little bit more complicated stuff to get this working.
So what my line of thinking about this is I'm going to first count how many rows there
are.
If I know how many rows, I can get the midpoint, right, the mid number.
What's the index, right, of the mid row?
If I order them by lat underscore n.
And then what I do is I leverage a SQL offset function so I can offset all the records before
that midpoint so I can eliminate them, right?
So instead of querying all these first records before the midpoint, I eliminate them all with
the offset parameter.
And then after that, I use limit one to just take that very first row after eliminating
all the previous ones because I don't want anything after the midpoints, after the midrow,
right?
So how can we accomplish that?
Well, to do that dynamic number for the offset, I cannot just do it in plain SQL.
I got to do a prepared statement and put a variable there that references the number
of rows, the total number of rows in this table.
So let's go one by one.
I'm going to use my SQL here.
Now first I want to find out how many rows there are.
So that's easy, right?
You can just do select count star from station, right?
Now, that's nice.
Is it possible if I could get like the midpoint of that row?
Well, if I divide by two, right?
If I take that and divide by two, I can get kind of a midpoint.
But if it's a not number, it's going to be a decimal, right?
So I don't want that.
I can either take the floor, which is the number down, or I can round it up, right?
With the ceiling.
So for the purposes of this, because I'm using offset, and on offset everything before the
midpoint, I'm going to use the floor, okay?
So it's a little detail.
So you got to, if you do it, you would have to play around to see if it's floor or ceiling
as you do the problem.
But I already know the offset, but I need to take the floor, okay?
So that way, for example, if I have three elements, and if I divide three, divide by
two is 1.5.
If I do floor, that means I drop the five.
If it were ceiling, it would go from 1.5 to two.
I don't want that in this case, okay?
Because I want to offset only one in this case.
Because if I offset one, and there's three elements, right?
A, B, C.
I offset one, meaning I remove only the ones before the B.
There's just one, right?
And I get B and C, and then it can limit to remove C.
Okay?
So that's it for that part.
Now that's nice.
How can we place that in a variable?
So we can do set, add, and the name of the variable, I'll call it row underscore count
equals this.
But because syntax, I have to add a parenthesis around the select, okay?
And if I run the code, that should be fine.
If the rank answer, we don't get any errors.
That means the syntax is correct.
Now we got the row count there, okay?
We still got to do some stuff in that row count line, but I'll leave that for later,
because you can see it's going to error out.
Now we got the row count.
Now we got to use that.
So for the query, the query I want to do is this, select.
Let's just start for now from station, order by, lat, underscore, and so I'll take all the
rows, order by lat, underscore, and, okay?
Now I want to offset, so remove everything from the midpoint upwards, right?
I'm going to offset by the row count here.
Obviously I cannot use it like this, okay?
Because of SQL syntax, but I'm just showing you what my thought process is.
Now I want to limit, before I say that I want to limit just one, that means I remove all
the rows after the mid row, because I want to only get one, that one with the median
value.
And finally, I want to get the value for lat, underscore, and, but because it's rounded
to four decimal places, I got to do round, lat, underscore, and here, comma four.
So that would be the query, but if I do this query, it's not going to work.
We got an error in syntax.
Now to make this work, you got to do what's called a prepare statement.
So to do prepare statement, we do like this.
We call prepare SDMT, and then from any quotes, and you got to put this whole query there
inside the quotes, and remove the semicolon put outside the quote.
Now the, where you see the variable here, you got to take that out, replace it with
a question mark, so that in the next line, you're going to say execute SDMT, okay, using
that variable.
So what this is doing is, okay, I want to execute SDMT.
Which is this prepare statement, this query, and using these values.
So this first value will replace the very first question mark that appears in the query
there.
Okay, so that's the value of row count is going to go into the question mark there.
That's from the code.
It's still going to get an error.
Okay, so it's saying there's incorrect arguments to execute line three.
So the problem here is the variable value, it's not properly being understood when you
put it in the offset here.
So you have to explicitly tell my SQL, this is integer value, right?
So we have to cast the value, meaning convert it to another type.
So where you have the row count definition here, you have to go select whatever this
part here, you got to cast it to an integer.
So I'll use the cast, whatever this is, as, okay, and sign sign means an integer that
has a sign.
So I'm going to run code now.
And now you can see we got the answer 83.8913.
That's the mid the point where you sort all the roles by Latin score and take the very
mid row, take the value of the Latin score and round into four, that's what you get.
I'm going to submit.
And that's weather observation station 20.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/japanese-cities-attributes

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Japanese Cities' Attributes | SQL Basic Select | HackerRank Solution

# Summary of Japanese Cities Attributes Exercise

In this exercise, the focus is on querying attributes of Japanese cities from a database table named `city`. The table contains the following columns: ID, name, country code, district, and population.

## Steps Taken

1. **Selecting All Attributes**: 
   - The initial SQL query is designed to retrieve all columns from the `city` table using `SELECT *`.

2. **Filtering for Japanese Cities**:
   - To filter the results specifically for Japanese cities, a `WHERE` clause is added:
     ```sql
     SELECT * FROM city WHERE country_code = 'JP';
     ```
   - The identifier 'JP' is used to specify that only cities from Japan should be included in the results.

3. **Visualizing Data**:
   - Upon executing the query, only rows corresponding to Japanese cities are displayed, demonstrating successful filtering based on the country code.

By following these steps, the exercise effectively retrieves all attributes of cities located in Japan from the dataset.

Hey everybody, I will do the exercise Japanese cities attributes from the
subsection basic select of the section sequel from hacker rink. So let's get
started. So we're given this table city that has these columns ID name, country
code, district, population and then we're asked to query all attributes of
average Japanese city in a city table and the country code to Japan's JP in. So
basically want to query to Japanese cities and to do that let's get started
with a select star from the name of the table that city. I want to visualize what
the data looks like. So as you can see here each row has information about the
cities and it includes there's different cities from different countries and I
think the district is like the state equivalent here and we're interested here
in all the attributes, attributes meaning columns. Okay so we're just going to leave
the star there select star all columns of every Japanese city. Now to get a
Japanese city there's many cities from around the world here we only want to
get the one from Japan so we have to add a where here a filter to filter only
the Japanese ones and we can do that through the column country code country
code equal to JP N because the Japanese cities the roles Japanese cities will
have a JP N for the country code. Okay so let's run the code
and there you go we get only the rows there are from Japan. I'm gonna click
submit code and that's Japanese cities attributes


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/revising-the-select-query

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Revising the Select Query I | SQL Basic Select | HackerRank Solution

# SQL Lesson Summary: Select Query for American Cities

In this lesson, the focus is on revising the first select query from the HackerRank SQL section, specifically concerning the `city` table. The key elements of the task include:

- **Table Structure**: The `city` table contains the following columns:
  - ID
  - Name
  - Country Code
  - District
  - Population

- **Query Requirements**:
  - Select all columns for entries where:
    - The country code is 'USA'.
    - The population is greater than 100,000.

## SQL Query Breakdown

1. **Selecting All Columns**: Use `SELECT *` to get all the columns.
   
   ```sql
   SELECT * FROM city
   ```

2. **Filtering Conditions**:
   - To filter for American cities:
     ```sql
     WHERE country_code = 'USA'
     ```
   - To filter for populations greater than 100,000:
     ```sql
     AND population > 100000
     ```

3. **Complete SQL Query**:
   Combining these, the final SQL query looks like this:

   ```sql
   SELECT * FROM city
   WHERE country_code = 'USA' AND population > 100000;
   ```

## Output
Running the code yields a list of cities in the USA with populations exceeding 100,000. The code is then submitted for evaluation.

---

This lesson outlines the steps and considerations needed to effectively filter data in a SQL query based on specific criteria.

Hey everybody, welcome to another lesson I will do, revising the select query number
one from basic select subsection of the SQL section of Hacker Rank.
So we're given this stable city that has the columns ID, name, country, codes, district
and population.
We're asked to query all columns that is the star through all American cities.
So we have to filter by only USA.
And the population is larger than 100,000.
We also have to filter by population greater than 100,000.
So let's get started.
So I'm just going to select since it wants all the columns, right?
We say star from what's the name of the table, city.
Now that's great, but we need to get only the American city.
So we have to say where and we have to say country, code is equal to and usna.
But because we also have the population constraint, we also have to say and population,
greater than 100,000.
Okay, let's see what we get when I click run code.
And this is what we get.
We got the list of rows with city entries that are only for new USA instead of all the countries.
I'm going to click submit code.
And that was revising the select query number one.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/revising-the-select-query-2

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Revising the Select Query II | SQL Basic Select | HackerRank Solution

# SQL Lesson Summary

## Topic: Revising Select Query

In this lesson, the focus is on revising the **SELECT** query from the SQL section of HackerRank, specifically Query Number Two.

### Objective
- Query the **name** column from the **city** table.
- Filter results for:
  - Cities located in the **USA**.
  - Cities with a **population** greater than **120,000**.

### SQL Query Breakdown
1. **SELECT Statement:**
   - To select only the **name** column:
     ```sql
     SELECT name
     ```
   
2. **FROM Clause:**
   - Indicating the **city** table:
     ```sql
     FROM city
     ```

3. **WHERE Clause:**
   - Filtering conditions:
     - Country code should be **USA**:
       ```sql
       WHERE country_code = 'USA'
       ```
     - Population must be greater than **120,000**:
       ```sql
       AND population > 120000
       ```

### Final SQL Query
```sql
SELECT name
FROM city
WHERE country_code = 'USA' AND population > 120000;
```

### Conclusion
After executing the query, it retrieves a list of city names in the USA with a population exceeding 120,000. This exercise emphasizes filtering data based on multiple criteria in SQL.

--- 

This concludes the summary of revising the SELECT query from HackerRank.

Hey everybody, welcome to another lesson. I will do revising the select query
number two from basic select of the SQL section of HackerRank. So we're asked to
query the name field, that means just the name column instead of all the columns
of the table city, and we want to filter only by American cities, that is the
country code has to be equal to USA. But also the population has to be greater
than 120,000, so we have these two things to filter on. Okay, so let's get
started. I'm going to say select. Now I could be start if it's all columns, I
only want the name column, so name from the name of the table city. Now we need
some filters so I'll add where. Now the first one it has to be from the USA, so
country code is equal to USA. Now and because we have to filter by population
as well, we have to say and population has to be greater than 120,000. Okay,
I'm going to click submit code. And as you can see here, these are the list of
city names from USA with the population greater than 120,000 according to this
data. And that's revising the select query two from HackerRank.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/select-all-sql

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Select All | SQL Basic Select | HackerRank Solution

# Lesson Summary: SQL - Select All

In this lesson, we explored how to execute a basic SQL query using HackerRank. 

## Task
We were given a table named **city** with the following columns:
- ID
- Name
- Country Code
- District
- Population

## Query
We were instructed to query all columns from the city table. The SQL command used for this task was:

```sql
SELECT * FROM city;
```

## Outcome
After running the query, we were able to view all rows and their associated information about cities from around the world. 

This concludes the lesson on using the **SELECT ALL** statement in SQL.

Hey everybody, welcome to another lesson.
I will do select all from the basic select subsection of the SQL section in HackerRank.
So we're given this stable city with the columns ID name, country code, district and population.
We're asked to query all columns, that is select star, from every row in city table.
So pretty much that's it.
Select star from city.
Semicolon and submit the code.
As you can see you got all the list of rows with information about cities from around
the world.
And that's select all.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/select-by-id

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Select By Id | SQL Basic Select | HackerRank Solution

# SQL Lesson Summary: Selecting by ID

In this lesson, we explored the exercise of selecting a city by its ID from the SQL section on HackerRank.

## Overview
- **Table**: `city`
- **Columns**: ID, name, country code, district, population
- **Task**: Query all columns for the city with ID `1661`.

## Steps Taken
1. **Visualize Data**: 
   - Run the query `SELECT * FROM city;` to view the dataset and identify the structure of the data.
   
2. **Filtering Data**: 
   - To filter the data, use the SQL query:
     ```sql
     SELECT * FROM city WHERE ID = 1661;
     ```
   - Since `ID` is a numeric type, quotes are not required.

3. **Execution**: 
   - Run the code to retrieve the row corresponding to the city with ID `1661`, displaying all relevant information.

## Conclusion
The final output successfully shows the city information for ID `1661`. The exercise demonstrates how to use the `SELECT` statement to filter data based on a specific ID.

Hey everybody, welcome to another lesson. I will do the exercise select by ID from the
basic select subsection of the SQL section from HackerRank. Let's get started. We have
the stable city with the columns ID name, country code, district, and population. And
we're asked to query all columns that is going to use star for the select statement. For
a city with the ID 1661 that we have to filter by the ID column with the value 1661. Okay,
if you want to visualize the data you can always do select star from city to see what
it looks like and click run code. And as you can see this is what the data looks like each
row has information about the city and as you can see the first column is the ID and
that's what we want to filter by. There is one of them that has the ID 1661 that's the
row we want to filter. So we're going to go here we want all columns so star and after
from city we say where and we have to say ID is equal to 1661 and ID is a number so
I don't need quotes here. Let's run the code. And there you go this is the row with the
city of ID 1661 and that's the information for the other columns. I'm going to click
submit and there you go that's select by ID.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/more-than-75-marks

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Higher Than 75 Marks | SQL Basic Select | HackerRank Solution

```markdown
# SQL Lesson Summary: Querying Students with Marks Higher than 75

In this lesson, we are tasked with querying a table of students to find those who have scored more than 75 marks. The query must return only the names of these students, ordered by the last three characters of their names and then by their IDs in ascending order in case of ties.

## Steps Covered:

1. **Understanding the Data**: 
   - The `students` table contains columns: `ID`, `Name`, and `Marks`.

2. **Filtering the Data**:
   - We need to filter the records for students with marks greater than 75 using a `WHERE` clause.

3. **Selecting the Required Columns**:
   - Only the `Name` column will be queried.

4. **Ordering the Results**:
   - Use the `RIGHT()` function to get the last three characters of the name for sorting:
     - Syntax: `RIGHT(name, 3)` 
   - Add a secondary sort based on the `ID` column to ensure a consistent order for entries with the same last three characters.

## SQL Query Example:

Here’s a sample SQL query based on the steps outlined:

```sql
SELECT Name
FROM students
WHERE Marks > 75
ORDER BY RIGHT(Name, 3), ID;
```

5. **Execution**: 
   - After writing the SQL query, execute it and verify the output conforms to the requirements.

The fulfilled query successfully filters and orders the student names, achieving the objective.
```

Hey everybody, welcome to another lesson. I will do higher than 75 marks from the
basic select subsection of SQL and HackerRank. Here we're given the stable
of students with the column's ID name and marks and we're asked to query the name
of any student that is we're going to query only the value from the name column
who will score higher than 75 marks that is we have to filter by the marks being
greater than 75. Then we got an order you output by the last three characters
each name. So for that we have to take the name, get only the last three
characters and use that to order and if we have the same last three
characters for the same for different names then we have to order
secondarily by ascending ID. So that's the second clause of the order by. So
let's get started. Let's first see what the data looks like. I'll do select star
from students and now click run code. So there you go we have ID, the name and
marks. So let's first filter out all the ones that have marks less than or equal
to 75 so we're going to add a where and the name of the call is marks. We have
to have it greater than 75 and that's going to take care of eliminating for
example this line here 5. Now we have to query only the name so the name of the
column for the select is only name we don't care about the other columns. Now
we have to order by and the last three characters of a name. How can we get the
last three characters of name? There's a function you can use that's right here's
the docs for DB2. This function will do the job just pass the column in this
case name comma the number of characters you want from the right hand side of
that string. In this case would be three and if you're using my SQL it's the same
function and same arguments here. Going back there I'm going to do write of name
comma since we want only the last three I have to say three and that's going to
take only the last three characters of the string and that's going to be
ordered alphabetically by default right ASC is the default implicit so I don't
have to write it. Now if it happens that the two names or more will have the same
last three characters we have to find another way of sorting those and that's
by ascending ID as required by the question. So here I have to say comma
and I'm going to say ID that's the name of the column ID and it's by default
ASC so you can either write it or not. So let's run the code and see what we get
and there you go that's the solution let me submit the code and that's higher
than 75 marks.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/name-of-employees

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Employee Names | SQL Basic Select | HackerRank Solution

# SQL Lesson: Querying Employee Names

In this lesson, we will focus on querying employee names from the "employee" table. The table includes the following columns: 
- `employee_ID`
- `name`
- `months`
- `salary`

## Objective
We need to write a SQL query that retrieves the list of employee names in alphabetical order.

## SQL Query Structure
To achieve this, we will:
1. **Select** the `name` column.
2. **Source** the data from the `employee` table.
3. **Order** the results by the `name` column in ascending order.

### SQL Query
```sql
SELECT name 
FROM employee 
ORDER BY name ASC;
```

### Explanation
- `SELECT name` indicates we want to fetch the `name` column.
- `FROM employee` specifies the table from which to retrieve the data.
- `ORDER BY name ASC` sorts the names in alphabetical (ascending) order.

## Result
Executing this query will provide a list of employee names sorted alphabetically, as desired.

Hey everybody, welcome to another lesson. I will do employee names from the subsection
basic select of the SQL section from Hackerrank. So we're given this stable employee that has
the columns employee underscore ID, name, months, and salary. And we're asked to write
a query to bring the list of employee names. That is, we're only going to query for the
name column and alphabetical order. That is, we have to order by name. So let's get to
it. I can do select. And I only want the column name. So I'm going to say name from what's
the name of this table, it's employee. And because I want to order by alphabetic order
name, so I have to say name here and it's simply ASC, which is ascending. So it's going
to be alphabetic ascending, if you don't have to write it. So let's do it. That's it. As
you can see, the name is order of alphabetically. And that's employee names.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/salary-of-employees

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Employee Salaries | SQL Basic Select | HackerRank Solution

```markdown
# Lesson Summary: Employee Salaries Query

In this lesson, we focus on querying employee salaries using SQL from HackerRank. The task involves an employee table with the following columns:

- **employee ID**
- **name**
- **months**
- **salary**

## Objective
Write a SQL query to retrieve a list of employee names with the following conditions:
1. Salary greater than 2000 per month.
2. Employed for less than 10 months.

## Query Explanation
- The query needs to filter results based on the **salary** and **months** conditions.
- We order the results by **employee ID** in ascending order.

### SQL Query Structure
```sql
SELECT name
FROM employee
WHERE salary > 2000
  AND months < 10
ORDER BY employee_id;
```

## Conclusion
After executing the query, the output lists employees who meet the criteria of having a salary greater than 2000 and being employed for less than 10 months, sorted by employee ID in ascending order.
```

Welcome to another lesson. I will do employee salaries from the basic select subsection
of the SQL section hacker rank. So we're given the stable employee that has the columns
employee ID, name, months, and salary. Write a query that prints the list of employee names,
so we only care about employee names column, and having a salary greater than 2k per month
and been employed for less than 10 months. So we have two conditions here for the where,
right? We have the salary has to be greater than 2000 and the months has to be less than 10.
So the result of ascending is sending employee ID. So we do an order buy for that on the employee
ID column. So let's get to it. We're going to select and we want to care about name column
from what's the table here employee where and we have two conditions, right? So the salary column
that's an integer has to be greater than 2000 and we have another column for months has to be
less than 10 months in the job here. And because we need to order by we do an order buy. And what's
the column we want to sort by is the employee ID. And it's ascending so ASC, but ASC is default.
So you can write it or not. So I can just eliminate it. It's implicitly ASC. Let's click submit code.
And there you go. According to this data, those are the employees that make a salary greater than
2000 per month and been there on the job for less than 10 months. And we ordered by the ascending
employee ID. And that's employee salaries.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/what-type-of-triangle

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Type of Triangle | SQL Advanced Select | HackerRank Solution

# Summary of Triangle Type Determination Lesson 

In this lesson, the instructor discusses the SQL challenge "Type of Triangle" from HackerRank, which involves determining the type of triangle based on the lengths of its sides, labeled A, B, and C. The key steps and concepts are summarized below:

## Problem Overview
- The task is to assess each row of a table containing triangle side lengths and classify each triangle as:
  - **Equilateral**: All sides equal
  - **Isosceles**: Two sides equal
  - **Scalene**: All sides different
  - **Not a Triangle**: Fails the triangle inequality theorem

## Triangle Inequality Theorem
To determine if three lengths can form a triangle, the following conditions (inequalities) must hold:
1. A + B > C
2. A + C > B
3. B + C > A

If any of these conditions do not hold, the lengths do not form a triangle.

## SQL Implementation
The instructor explains how to use a `CASE` statement in SQL to output the triangle type based on the conditions outlined. The implementation involves:
1. Checking if the lengths form a triangle using the triangle inequality theorem.
2. Classifying the triangle based on the equality of side lengths:
   - If all sides are equal, classify as **Equilateral**.
   - If two sides are equal, classify as **Isosceles**.
   - If no sides are equal, classify as **Scalene**.

### SQL Code Outline
```sql
SELECT
  CASE
    WHEN (A + B <= C OR A + C <= B OR B + C <= A) THEN 'Not a triangle'
    WHEN (A = B AND B = C) THEN 'Equilateral'
    WHEN (A = B OR A = C OR B = C) THEN 'Isosceles'
    ELSE 'Scalene'
  END AS TriangleType
FROM triangles;
```

## Conclusion
The lesson concludes by running the code, which correctly classifies the type of triangle for each set of side lengths provided in the table. The instructor confirms that the output aligns with the expected results for each row.

--- 

This concise summary captures the essence of the lesson while providing necessary details about the concepts involved and how to apply them in SQL.

Hey everybody welcome to another lesson I will do type of triangle from the
advanced select subsection of the sequel section hack rank. Here we're given
this table here of triangles and each column is the size of one of the size
of the triangle we have ABC and here's an example on the right hand left hand
side and we're asked to pretty much tell for each row we have to say what kind of
triangle it is and if it's not a triangle say it's not your triangle and
here's an example output so basically we're going to use a case for the select
so case according to some conditions we're going to output isosceles case
occurrence of conditions equilateral scalene or not a triangle so we have four
conditions here to check and that will determine what appears for each row and
to understand what we should do about these things we have to understand some
mathematics about the triangle inequality theorem and here's a page from my
boyfriend you can get you can read about this but what matters is the sum of
the lengths of any two sides of the triangle is greater than the length of
the remaining side so we're going to keep that in mind as we work with the
conditions for our case statement in the select and here's another website
math warehouse.com that has a nice diagram and we have a triangle with the
sides A B and C and this is what the inequality theorem says so these
conditions three conditions must be met at all times for it to be a triangle
so the sides A plus B has to be greater than the length of C and the sides B plus
C has to be greater than the length of A and the A plus C has to be greater than
the length of B so keep that in mind as we work through our case statement for
the select okay and finally I want to show you this diagram for what kind of
triangles so if the triangle has all the sides equal it's an equilateral and if
one of the sides is not equal that's isosceles that is have two sides that
are equal but the third one isn't and if the sides are all different in length
it's a scalene like this one so keep that in mind visualize what we're doing in
this problem so let's get back to it so here I'm going to use my sequel okay so
we're going to select something that I'll write later from triangle stable now
this something we have to output for each row the type of triangle or or not a
triangle so I'm gonna use a case statement here okay case and I'm gonna add
the end there to start and then we're gonna add when something and then okay
when something then it's gonna be something when something there's gonna
be something when something there's gonna be something okay and then finally I
would do an else for whatever that what if it doesn't match in every cases okay
so these cases will determine if it's a collateral isosceles, scalene, not a
triangle so I want to start off by right off the bat seeing if it's a triangle or
not so for this case I'm gonna do a then not a triangle so if this condition in
the when matches what's gonna be output for that column value is this not a
triangle so how can we determine what when it's not a triangle when it doesn't
obey the inequality theorem if you remember this thing here so I'm gonna
see if any of the sum of the sides is less right if you take a side if it's
equal or less then the other side because the theorem says it has to be
greater if it's not greater that means it's not a triangle so here I'm gonna
say we take the side a add to the side b now if that is less than or equal to side
c that's definitely not a triangle and we got to do for the other combination
right or a plus c less than or equal to b or right b plus c less than or equal to
a so this is like not a triangle if any of these is true now if this case
condition here if this doesn't match that means it's a triangle so it could be
either all the sides are equal equilateral or only two are equal
the societies or all of them are different length scaleen so let's do the
equilateral one then equilateral what's the condition for a collateral so we
have to have all of the side the lengths equal so a has to be equal to b and a
has to be equal to c and b has to be equal to c right so all of them are
equal all the sides equal equilateral now if this case is not true it goes to the
next case the when here so that means it's either two sides equal or no side
length equal so in this case how can we say for the is Saucer least Saucer list
case that's also this case we have to check if one combination of sides is
equal so if a is equal to b or a is equal to c or b is equal to c because we
already checked before previously and we are sure that's not a collateral that's
when we can do this we have we can say for sure that if at least one of them two
sides are equal then it's going to be I saw Salis now finally for the else case
we're going to say okay we checked everything is not a collateral not as
Saucer this has to be scalene all of the sides have different length so that's
the outskirts there let's check that's it out I'll click run code and there you go
let me click submit code
okay and you can see for each row it outputs what kind of a triangle it is or
not a triangle if it's not a triangle for that combination of column values for
the size of the triangle and this was type of triangle


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/revising-aggregations-the-count-function

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Revising Aggregations - The Count Function | SQL Aggregation | HackerRank Solution

# Lesson Summary: SQL Aggregation and Count Function

In this lesson, we revisited the aggregation functions in SQL, particularly the `COUNT` function, focusing on a practical example using the city table.

## Objective
We aimed to count the number of cities in the `city` table that have a population greater than 100,000.

## Steps Undertaken
1. **Initial Query**: We started by retrieving all rows and columns from the `city` table using:
   ```sql
   SELECT * FROM city;
   ```

2. **Filtering Data**: To focus only on cities with populations greater than 100,000, we added a `WHERE` clause:
   ```sql
   WHERE population > 100000;
   ```

3. **Counting Rows**: To count the number of qualifying cities, we used the `COUNT` function:
   ```sql
   SELECT COUNT(*) FROM city WHERE population > 100000;
   ```

4. **Outcome**: Upon executing the query, we determined that there are **six cities** in the database with populations exceeding 100,000.

## Conclusion
This lesson illustrated the application of the `COUNT` function in SQL while performing a simple aggregation task based on population criteria.

Hey everybody, welcome to another lesson.
I will be doing revising aggregations to count function from the subsection aggregation of
SQL in Hack her ring.
We're asked to count the number of cities in the table city that has a population larger
than 100,000.
Now for the purposes of this question, we're going to treat each row where considered
to be one city, so we don't care about being distinct here.
We can do DB2 of my SQL is going to be the same solution.
Let me get started here.
I'll just start off with select star from city.
So this would take all the rows and all the columns from the table.
Okay, so we have the last column is a population.
We have to eliminate everything that's under or equal to 100,000.
So we're going to go here and we add a where and then the column is going to be population
greater than 100,000.
Now for the select, we need to give account of how many cities there are.
How many cities that's simply the count of rows.
If you want to count the number of rows, simply say count, parentheses star.
It doesn't matter which column in this case.
So I just leave a star.
So let's submit the code.
And there you go.
There are six cities.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/revising-aggregations-sum

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Revising Aggregations - The Sum Function | SQL Aggregation | HackerRank Solution

# Summary of SQL Aggregation Lesson

## Overview
In this lesson, we are revising the `SUM` function in SQL to calculate the total population of cities within the district of California from a table called `city`. The table consists of columns: `ID`, `country code`, `district`, and `population`.

## SQL Query Steps
1. **Select Data**: Start by selecting data from the `city` table.
   ```sql
   SELECT * FROM city
   ```

2. **Filter District**: Add a `WHERE` clause to filter the results for the district of California.
   ```sql
   WHERE district = 'California'
   ```

3. **Calculate Total Population**: Use the `SUM` function to aggregate the `population` values for the filtered results.
   ```sql
   SELECT SUM(population) FROM city WHERE district = 'California'
   ```

## Conclusion
The final SQL query will sum the population values only for cities in California, allowing us to effectively get the total population for that district. This method works for both DB2 and MySQL databases. 

That's it for revising aggregations with the `SUM` function!

Hey everybody, welcome to another lesson. I'll be doing revising aggregations, the sum function from the aggregation
subsection of SQL and hacker rank. So we're given the stable city has the column ID named country code district and population.
We're asked to pair with the total population of all cities where a district is California.
So basically we have a bunch of rows and those rows have
population column that has a number. So we have to take all those numbers and add them up except we can only take the rows that have the district
value California. So this solution will work for DB2 and my SQL.
Let's go. So we're gonna first do I just select star from city and then we're gonna add on to this.
So we want to query the population where district is. Let's do where district is California.
So we're gonna filter, we're gonna add a where and
district is the column and that value has to be equal to California like this.
Now what column do we query here? Well, we query the total population. So we have to sum all the values for the population
column. So we got to say sum of and
then you put population. That's the column name here.
So this is gonna take the value of each population column, add them up and bring you the sum and
we have a filter here for only the rows that are from California.
And there you go. You're gonna collect submit code and that's revising aggregations the sum function.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/revising-aggregations-the-average-function

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Revising Aggregations - Averages | SQL Aggregation | HackerRank Solution

```markdown
# Lesson Summary: SQL Aggregations - Average Population Calculation

In this lesson, we revisited SQL aggregations, focusing on how to calculate averages, specifically for cities in California. The dataset we are working with includes the following columns: ID, name, country code, district, and population.

## Task
We need to query the average population of cities where the district is "California".

### Steps to Achieve the Query
1. **Filter by District**: We will filter the cities by ensuring the district value is "California".
2. **Calculate Average Population**: We will use the average function (AVG) to compute the mean of the population for the filtered cities.

### Understanding Average
- The average is calculated by summing all the elements and dividing by the total number of elements $(n)$:
  
  \[
  \text{Average} = \frac{\sum_{k=1}^{n} a_k}{n}
  \]

- For example, if you have three values $a, b, c$, the average would be:
  
  \[
  \text{Average} = \frac{a + b + c}{3}
  \]

### SQL Query Execution
To formulate our query:
```sql
SELECT AVG(population) 
FROM city 
WHERE district = 'California';
```

- This query selects the average of the population column from the city table where the district equals "California".

### Result
After executing the query, the calculated average population for cities in California is approximately **113,000.67**.

## Conclusion
This lesson provided a concise overview of how to filter data and calculate averages using SQL. We reaffirmed the basic concept of averaging and demonstrated it with a specific example.
```

Hey everybody, welcome to another lesson.
I will do revising aggregations, averages, for the self-section aggregation of the SQL
section in HackerRank.
We have this stable city with the column ID name, country code, district and population.
And we're asked to query the average population in all the cities where district is California.
So for this, we're going to filter first by district having the value of California.
And then for those rows, we're going to take all those values and take the average, the
values for population.
And if you don't remember what the average is, here's some brief reminder.
You're just going to take the sum of the elements and divide by the number of elements.
In this case, if you have a sum of ak with k being from 1 to n, and then n has to be
greater than or equal to 1, so you take the sum and divide by n.
So if you want a more expanded version without the sum notation, this is pretty much you
have a value plus an order plus an order up to n values and then divided by n.
And here's a concrete example.
If you have three values, you just sum them and divide by the total number of values.
In this case, a plus b plus c, take that sum and then divide by 3.
In any case, this will work for dv2 and my SQL at least.
So let's get started.
We're going to do select star from city.
And then from this query, we're going to change it so it becomes what the solution wants.
So first, let's make sure it's just the rows for Californians.
So we're going to add a where.
The district column value is equal to California.
And then we have to take the average of population.
So we're going to work with the column select population here.
And then we're going to use the average function AVG and take that and pass population as
the argument.
And that will take all the values for all these rows from California and take the population
values add them up and then divide by the total number of rows or values.
Let's click submit code and see what we get.
And here's the answer.
We got 113,000.667.
And that's revising aggregations averages.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/average-population

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Average Population | SQL Aggregation | HackerRank Solution

# Summary of SQL Query to Calculate Average Population

In this session, the speaker explains how to calculate the average population of all cities from a database table named `city` using SQL. The table contains columns: `ID`, `country code`, `district`, and `population`.

## Steps to Calculate Average Population

1. **Understand the Requirement**: 
   - We need to query the average population and round it down to the nearest integer.

2. **Select the Population Column**: 
   - Instead of selecting all columns with `SELECT * FROM city`, we focus on the `population` column.

3. **Use AVG Function**: 
   - The average is calculated using the `AVG()` function which sums up all the population values and divides by the count of cities.

4. **Rounding Down the Result**: 
   - To round down the average to the nearest integer, the `ROUND()` function is used, passing the result of the `AVG()` function.

5. **Final Query**: 
   - Since there are no specific filters required, the query is straightforward.

## Example SQL Query

```sql
SELECT ROUND(AVG(population)) FROM city;
```

## Conclusion
The speaker successfully formulates the SQL query to obtain the average population for all cities and confirms that the query works as intended.

Hey, I will do average population from the subsection aggregation of SQL in hacker rank
We're given the table city with the Collins ID country code district and population
We're asked to query the average population for all cities in a table city
Rounded down to the nearest integer
So I'm gonna use my SQL for this. Let's get started with select star from city
Now we're asked to get the average population. So instead of star, we're gonna work with population
That's the column right number of people in that city
So we have to take the average of all the cities that is we take all the values for a population
Add them up and then we divide that by the number of
Cities that we used in this case. There's a function AVG that we can use
So AVG of population will do that for us
Now round it down to the nearest integer if you want to ensure that
Rounded you can call the round function passing this AVG of population to round
parentheses AVG of population then you close the parentheses
Okay, since it's all the cities we don't have any filter with where and that's just like that. Oh, I had a
Additional parentheses there. Let me click submit code again
And
There you go. This is the average population for all cities


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/japan-population

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Japan Population | SQL Aggregation | HackerRank Solution

# Summary of Japan Population Calculation

In this tutorial, we are tasked with calculating the total population of Japanese cities from a SQL database. The relevant table is named `city` and includes the following columns:

- `ID`
- `country code`
- `district`
- `population`

## Steps to Query Population

1. **Filtering Cities**: We need to filter the cities to only include those from Japan, which is indicated by the country code `JPN`.
  
2. **Using the SUM Function**: To calculate the total population, we will utilize the SQL `SUM` function on the `population` column.

3. **Writing the Query**:
   - Start with the base query: `SELECT * FROM city`
   - Modify it to filter Japanese cities:
     ```sql
     SELECT population 
     FROM city 
     WHERE country code = 'JPN';
     ```
   - Finally, implement the sum:
     ```sql
     SELECT SUM(population) 
     FROM city 
     WHERE country code = 'JPN';
     ```

## Submitting Code

After constructing the query, the code can be submitted to output the total sum of the population for Japanese cities based on the provided data.

Hey everybody, I will do Japan population from the aggregation subsection of SQL and
hacker rank.
So we're given the table city has the columns ID, country code district and population.
We're asked to query the sum of populations for all Japanese cities.
So if you want to get a Japanese city that is a row for a city, you have to filter by
the country code having the value JPN.
And then we take all the populations and we're going to use the sum function.
So let's get started.
We can use DB2 or MySQL.
So I'm going to do select star from city and from this we're going to modify this query
to have the correct answer.
So first we have to do where.
So where the country code column has the value is equal to JPN.
That way we filter all the rows to only having Japanese cities.
And we have to take the column population so select population.
Matt, we need to add them up, right?
So we're going to do the function sum of population like so.
And that's it.
I'm going to go submit code.
And here's the sum of the population according to this data.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/population-density-difference

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Population Density Difference | SQL Aggregation | HackerRank Solution

# SQL Exercise Summary: Population Density Difference

In this exercise from HackerRank, the task is to calculate the difference between the maximum and minimum populations from a given city table, which includes the following columns: ID, name, country code, district, and population.

## Steps to Solve the Problem

1. **Identify the Target Column**: The focus is on the `population` column.
2. **Calculate Maximum and Minimum**:
   - Use the SQL function `MAX()` to find the maximum population.
   - Use the SQL function `MIN()` to find the minimum population.
3. **Compute the Difference**: Subtract the minimum population from the maximum population to get the desired difference.

## SQL Query

To perform the calculation, the SQL query structure is as follows:

```sql
SELECT MAX(population) - MIN(population) AS population_difference
FROM city;
```

## Conclusion

After running the query, the output will display the difference between the maximum and minimum populations in the city table.

Hey everybody, I'll be doing the exercise population density difference from the subsection
aggregation of SQL in hacker rank.
So we're given the city table has the columns ID name, country code, district and population.
We're asked to query the difference between the maximum and minimum populations in city.
So this question is all about the column population.
So we take value population, that's the lowest, and we take what's the greatest.
We have to subtract greatest minus lowest, and that's going to give us the difference.
Now SQL has the functions min for to take the minimum value and max to get the maximum.
So we're going to use those and just subtract them.
So let's get started here.
I'm going to do select star from city just to get started here.
And now we want to care about the population.
So select population.
Now I need first the maximum value.
How can I get the maximum value, the greatest value I can find in all the rows for this
population.
So we're going to use max parenthesis population.
That's going to take the maximum value that it can find for population.
Now we have to subtract, I'm going to say minus.
Now I need to take the minimum.
Can you guess which function is going to be?
M, I, N, min of population.
So we take the maximum value minus the minimum value, and that's going to give us the difference.
I'm going to click submit code.
And here's the difference.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/the-blunder

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

The Blunder | SQL Aggregation | HackerRank Solution

# Summary of SQL Exercise: The Blunder

## Overview
In this exercise from Hackeray's SQL aggregation section, the task involves calculating the difference in average salaries due to a key error. An employee, Samantha, was typing salaries without zeros due to a broken keyboard.

## Task
1. **Data Source**: The exercise uses a table named `employees`, with columns: `id`, `name`, and `salary`.
2. **Objective**: 
   - Calculate the average of the actual salary values.
   - Calculate the average of modified salary values with all zeros removed.
   - Find the difference between these two averages and round it up to the nearest integer.

## Steps
1. **Calculate Actual Average**:
   - Use the `AVG()` function directly on the `salary` column.
   
2. **Calculate Modified Average**:
   - Use the `REPLACE()` function to remove zeros from each salary value.
   - Calculate the average of these modified values.

3. **Compute the Difference**:
   - Subtract the modified average from the actual average.
   - Apply the `CEIL()` function to round up the difference to the next integer.

## Example SQL Code
```sql
SELECT
  CEIL(AVG(salary) - AVG(REPLACE(salary, '0', ''))) AS error_difference
FROM
  employees;
```

## Conclusion
The exercise effectively illustrates how a simple data entry error can significantly impact numerical calculations and provides practice in using SQL functions for aggregation and string manipulation.

Hey everybody, I'll be doing the Blunder, an exercise from the aggregation subsection
of the section SQL in Hackeray.
So we're given the stable employees has the columns id, name and salary.
We'll be interested in the columns salary for the purposes of this exercise.
And there's lots going on here, but actually it's a very simple exercise.
So you have the salary values, right?
Each row has a salary.
If we add them up and divide by the number of salaries, you get the average.
Now this person here, Samantha in the exercise, for some reason, she had a keyboard with a
key zero broken.
So whenever she typed numbers, it would type the numbers without the zeros.
So we have to figure out what's the error.
Like if we take the average of the original salaries as they are, and then we take the
average of the salary values as they were typed without the zeros, and what's the difference?
Okay?
So to do this, we're first going to take the average normally, like we do the average
of salary values.
And then we have to take the average of the salary values with the zeros from each number
removed.
So we have to replace for each value of salary.
If there's a zero, you take that out.
And we're going to use the replace function to do that.
And we take those two averages and we subtract them.
And then we have to round up to the next integer.
And then once you round up, you use the ceiling function, seal.
So if you have like 1.5, you take ceiling of that or go to two, because that's the next
integer up.
So let's get started.
I will use my SQL.
So if you want to visualize the data, you can do select star from employees.
Oops, I forgot from.
And here's what the data looks like.
We'll be interested in the third column here.
Okay, you see this one has a zero, nine, zero, zero, nine.
So for the first average, you're going to do as they are like this, sum them all up,
okay, divide by the number of rows.
Now for the second average, we're going to take this nine, zero, zero, nine, take out
the zero zeros in between so it becomes 99.
And then do that for every single row.
For example, this other row, nine, nine, nine, followed by zero, remove the zero, only take
99.9 and then add them all up with the other ones and take this average.
And then at the end, we just subtract from the original average with the zeros included.
So let's get started here.
Let's first do the one with the zeros already there.
So it's just average of salary column.
Okay, now what's the other average here?
I'm going to do minus, okay, and this is going to be average of salary, but I need to remove
the zeros.
How can I do that?
Well, we're going to use the replace function.
So replace something, what do I want to replace from the salary column, comma, take any zeros
that you find, replace that with nothing.
So this is an empty string.
It's a single quote, single quote without anything in between.
So that will take care to remove all the zeros.
So remember that nine, zero, zero, nine becomes just 99.
And then once you get this average, and we got the other average, subtract them, but
you had to round it up to the next integer.
So you take this whole thing and you pass it to the seal ceil function.
Okay, let's submit the code.
All right, so this is the error, right?
The difference between the actual and the miscalculated average.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/earnings-of-employees

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Top Earners | SQL Aggregation | HackerRank Solution

# Summary of SQL Top Earners Transcript

In this transcript, the speaker discusses solving a SQL problem related to employee earnings using a table called `employee`. The objective is to find the maximum total earnings for all employees and count how many employees have that maximum earning.

## Steps Involved

1. **Understanding the Data**:
   - The `employee` table contains the following fields:
     - `employee_id`
     - `name`
     - `months`
     - `salary`

2. **Calculating Total Earnings**:
   - The total earnings for each employee are calculated by multiplying `salary` by `months`.

3. **Query Implementation**:
   - The speaker starts with a basic query to display all employee information:
     ```sql
     SELECT * FROM employee;
     ```

4. **Finding Maximum Earnings**:
   - After running the initial query, the speaker runs a calculation for total earnings:
     ```sql
     SELECT salary * months AS total_earnings FROM employee;
     ```
   - The results are ordered in descending order to identify the highest total earnings.

5. **Grouping and Counting**:
   - To count how many employees share the same maximum earnings, the speaker uses the `GROUP BY` clause:
     ```sql
     SELECT salary * months AS total_earnings, COUNT(*) 
     FROM employee 
     GROUP BY total_earnings;
     ```

6. **Limit Results**:
   - Finally, to get only the maximum earnings and the count of employees, the speaker applies a `LIMIT` to the query:
     ```sql
     SELECT salary * months AS total_earnings, COUNT(*) 
     FROM employee 
     GROUP BY total_earnings 
     ORDER BY total_earnings DESC 
     LIMIT 1;
     ```

This process effectively identifies the highest total earnings and counts the number of employees earning that amount.

Hey everybody, I will do top earners from the aggregation section of SQL from Hacker
Rank.
So we're giving this table employee that has the Collins employee ID name, months and salary
and we're asked to query the maximum total earnings for all employees as well as the
total number of employees who have maximum total earnings.
So we're going to be interested in the Collins months and salary.
We're going to multiply them together to get the total earning.
And then we have to find out which one is the highest and then we have to count how
many people has that highest value.
Let's do my SQL and I'll start off with select star from employee.
And I want to see what it did.
It looks like first I'll click run code.
So here you are.
We have this number of months and we have the salary.
So we're going to take these two and multiply and let's find out what values we get for
each employee.
So let's go here and say salary times months and let's run code and see what we get.
So as you can see, you got all these values.
So what I'm going to do is I'm going to order them by salary times months, which is pretty
much the same thing that I wrote here.
I write it here, but I want to order this descending so that the very first row is the
highest value.
Let's see what we get.
So as you can see, you got all these values and the highest ones are at the top.
As you can see, there's one, two, three, four, five, six, seven people with the same maximum
total earnings.
But how can we group these together so we had a count of these people?
We can use the group by and we have to count by the number of people who have the same
total earnings.
So go here.
In addition to the salary times months, we're going to say count of salary times months.
Pretty much the same thing that you typed here.
You type it here so we can count how many people has that same value.
And we get that as the second column.
Now let's go after from employee group by salary times months.
So the same thing here is what I typed for the first column here.
If you want to not repeat yourself, you could create an alias for this thing and use it
here.
But I'll keep it simple.
Just use without an alias.
So let's see what we get.
So as you can see it groups every maximum earnings and the number of employees that
have that.
That's precisely the answer we want except we only want the very first line, the very
first row.
And to limit that we can use limit one at the end here.
So limit one to make sure we just take the very first row and all the subsequent rows
are discarded.
And there you go.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/asian-population

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Population Census | SQL Basic Join | HackerRank Solution

# SQL Population Census - Summary

In this tutorial, we will perform a population census using SQL by joining the `city` and `country` tables available on HackerRank. The two tables are linked through the `country code`, where `city.country_code` matches `country.code`. Our goal is to query the sum of populations for all cities located in the continent of Asia.

## Steps to Create the Query

1. **Identify Tables**: The `city` table contains population data, but does not have continent information. We will need to join it with the `country` table, which has the continent data.

2. **Join Tables**: We will use an **inner join**, which selects records that have matching values in both tables. The inner join will allow us to filter the cities based on their corresponding countries.

3. **Filter Records**: To focus only on the cities in Asia, we will filter the records from the `country` table by where the continent is 'Asia'.

4. **Select and Sum**: We will use the `SUM` function to calculate the total population from the `city` table, ensuring we specify `city.population` to avoid ambiguity with any similar column names in the `country` table.

5. **Final Query**: The completed SQL query will look something like this:

```sql
SELECT SUM(city.population)
FROM city
INNER JOIN country ON country.code = city.country_code
WHERE country.continent = 'Asia';
```

After executing the query, we will obtain the total population of all cities located in Asia.

Hey everyone, I will do population census on the section basic join of SQL in hacker
rank. We're given the table city and country as you can see here. So these two tables are
linked by the country code as you can see city dot country code will have the matching
value in country dot code. Okay, so that's what we're going to use to join these tables.
So it asks query the sum of all the populations all cities where continent is Asia. So we
have the city stable but we do not know where the continent for that city is. So that's
why you have to take the matching record from city with the country table. And for the country
table we can filter by the continent that's Asia so that we can exclude all the other
cities. So let's get started. First I'll do select and star from city. Now we need to
get the population and then the sum. So here I'm going to say population. And then I'm
going to take the sum of that. But notice if I do it this way, that's just going to
take the sum of all the population for all the cities. But I need to filter so I'm going
to add a where. Right. So the continent has to be Asia so continent equals Asia. But
city table does not have that column. So we have to take city and match with the records
in the other table country. Now if you want to do what kind of join we're going to do.
We're going to do the very first one in the middle of this Venn diagram here that I like
to reference a lot. And that's called the inner join where we take the intersection of the
two tables that is the records both appear and table A and table B. So let's go back
and I'm going to do inner join country. That's the name of the other table on now country
dot code remember has to match so equals the city table dot country code column. So this
is the condition to match the lines. Now because of that I know that continent is a column
that only appears in country that's not ambiguous name. However, the population is a name that
appears for the city dot population column and country dot population column. So to disambiguate
that I have to say explicitly city dot population here. So we are referring to the column population
from the city table not the country one. Okay. So let's submit that. And there it is.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/african-cities

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

African Cities | SQL Basic Join | HackerRank Solution

# Summary of SQL Querying in HackerRank: African Cities

In this session, the speaker discusses how to query African cities using SQL within HackerRank. Here’s a concise breakdown of the process:

1. **Objective**: 
   - Query the names of all cities located in Africa.

2. **Tables Involved**:
   - Two tables: `city` and `country`.
   - They are linked via `city.countrycode` and `country.code`.

3. **Potential Ambiguity**:
   - Both the `city` and `country` tables contain a column named `name`, so it's important to specify `city.name` to avoid ambiguity.

4. **Filtering for Africa**:
   - Since there's no continent column in the `city` table, the query must reference the `country` table to filter by the continent.

5. **Joining Tables**:
   - An **INNER JOIN** is used to combine the two tables based on matching `country.code` with `city.countrycode`.

6. **SQL Query Structure**:
   - Start with the SELECT statement to get the city names.
   - Use an INNER JOIN followed by the ON clause to specify the condition for joining.
   - The SQL query can be summarized as:
     ```sql
     SELECT city.name 
     FROM city 
     INNER JOIN country 
     ON country.code = city.countrycode 
     WHERE country.continent = 'Africa';
     ```

7. **Reference Tool**:
   - The speaker recommends using a Venn diagram to visualize different types of SQL joins when necessary.

8. **Execution**:
   - After constructing the query, the speaker plans to submit the code for evaluation.

This session provides valuable insights into SQL operations, particularly focusing on joins and handling ambiguous table columns.

Hey everyone, I will do African cities from the basic join section of SQL in HackerRank.
So we're given these two tables city and country and they match by the city.country
code with the country.code columns.
And we're asked to query the name of all cities where the continent is Africa.
Now I can do here first if we start select and we're going to do name from city, but
we got to be careful.
Does name appear in the other column?
It does.
So country.name appears.
So that's ambiguous.
Two columns with the same name, right?
Two different tables.
So we have to say city.name.
That's the name of the table to differentiate with the country.name.
Now with that out of the way, we have to do a filter here because we want only the ones
whose continent is Africa.
Now we don't have any column about continent in the city table.
So we have to refer to the country table.
So we have to combine these two tables and match the records based on the country code.
So here the where would be the continent, the Africa.
Now continent is a column that only appears in the country table.
Therefore we don't have to explicitly disambiguate with a country.continent there.
Now select city.name from city.
That's fine, but we need to get continent from the other tables.
So we have to do a join here.
What kind of join are we going to do?
Let me show you the Venn diagram that I like to reference a lot for SQL joins.
Whenever you need to do it and you don't know which one, refer to this diagram.
Now we're going to do the one in the center here called an inner join where we have two
tables A and B and we're going to have records that both appear in A and B that is the intersection
between these two tables.
So we're going to match these two according to city.countrycode and country.code.
So here we're going to say inner join, the other table is country, okay?
On, you have to say on, what's the condition to match the records from one table with the
other?
We're going to say country.code, that's the column, has to be equal to the city.countrycode.
So these two values have to match so that we have the two combined together into a single
row.
Now let's submit the code and see what we get.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/average-population-of-each-continent

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Average Population of Each Continent | SQL Basic Join | HackerRank Solution

```markdown
## Summary

In this exercise, the presenter demonstrates how to calculate the average population of cities for each continent using SQL.

### Steps Taken:

1. **Combine Tables**: The presenter discusses combining the `city` and `country` tables to access continent names.
2. **Select Statement**: They start writing a SQL query using `SELECT` and define the `INNER JOIN` between `city` and `country` tables based on matching country codes.
3. **Select Columns**: They specify `country.continent` to get the continent's name and `city.population` for city populations.
4. **Initial Query Result**: The initial query results in multiple rows for each continent due to different cities.
5. **Group By Clause**: To aggregate the data, a `GROUP BY` clause is added to group the results by continent.
6. **Calculate Average**: The presenter uses the `AVG` function to calculate average city populations and applies a flooring function to round down the results.
7. **Final Output**: The final output displays the average city population for each continent.

### Key Points:

- Use `INNER JOIN` to combine tables based on matching columns.
- Group results by continent to avoid duplicate entries.
- Use `AVG()` function and round down the result with `FLOOR()` to meet the requirements.
```


Hey everybody, I will do average population of each continent, an exercise of basic
joint section of Sequel and Hacker Rank. We're given these tables city and country
and we're asked to query the names of all the continents and their respective average city
populations, rounding down to the nearest integer, rounding down the integer meaning floor. Okay,
now first we're going to combine these two tables because we need the name of the continent. The
name of the continent is in country.continent and then we have to group them by the continent so
everything that's all the rows for a certain continent will be collapsed to a single row
and then we'll get the average of the population of each city of every city. Right,
mind you we're going to use city.population not country.population. Okay, so don't confuse these
so let's get started here. We're going to say select. Let me just start with star from city. Now
we need to combine these two tables so we need to do a join. Which join I'm going to use? Here's
the diagram. We're going to do this one in the center called the inner join where we have table A
and table B. We're going to find the records that are in the intersection that is they both
are in A and in B based on the country code. So let's get back. We're going to do from city
inner join country that's the name of the other table on country.code that's the column has to
match equals the city.country.code column. Okay, that's nice. Now we need to get the name of all
the continents. Now name is in the column continent in the country table. So here instead of star I'm
going to say country.continent. So we got a list of all the continent names. Okay, and then we need
to get the average city population. Now let's just do comma population here but we have to say
population from where? City not from country city.population. Let's see what we get just with this
query. So as you can see we have multiple rows for the same continent and this is because each of
these is a different city. So what we have to do is take these three and combine them group them by
the name of the continent and then we got to take the average of these values for the population.
So to do that let's go here and add group by at the end group by country.continent.
Okay now for population I want to take the average so I'm going to use the AVG function.
Now it wants us to round down to the nearest integer that if you do down that means floor.
Right? If you have 2.5 it becomes 2 for example. Now we got that let's run the code
and there you go the average city population by continent.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/the-pads

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

The PADS | SQL Advanced Select | HackerRank Solution

# SQL HackerRank Exercises Summary

## Overview
The presenter tackles a set of SQL exercises from HackerRank, emphasizing the importance of breaking down complex problems into smaller parts. The session covers two main queries related to a table named `occupations`.

## Query 1: Listing Names with Occupation Initials
- **Objective**: Retrieve a list of all names in the `occupations` table, accompanied by the first letter of each person's occupation in parentheses.
- **Steps**:
  1. Select the `name` column from the `occupations` table.
  2. Use the `LEFT` function to extract the first letter of the `occupation` column.
  3. Combine the `name` and the first letter of the `occupation` using the `CONCAT` function, placing the letter in parentheses.
  4. Sort the results alphabetically by name.

### SQL Code Example
```sql
SELECT CONCAT(name, ' (', LEFT(occupation, 1), ')') 
FROM occupations 
ORDER BY name;
```

## Query 2: Count of Each Occupation
- **Objective**: Count the occurrences of each occupation and display a formatted message.
- **Steps**:
  1. Select the `occupation` column and use `COUNT` to get the occurrences of each occupation.
  2. Use `GROUP BY` to group the results by occupation.
  3. Format the output message with the `CONCAT` function: "There are a total of X occupations".
  4. Sort the results first by the count of occurrences in ascending order, then alphabetically by occupation if counts are the same.

### SQL Code Example
```sql
SELECT CONCAT('There are a total of ', COUNT(occupation), ' ', LOWER(occupation), 's') 
FROM occupations 
GROUP BY occupation 
ORDER BY COUNT(occupation), occupation;
```

## Conclusion
The presenter successfully demonstrates how to approach SQL problems methodically, resolving each query step by step and ensuring proper syntax for accurate results.

Hey everybody, I will do the pads and exercise from the section I've been selected of SQL
HackerRank.
So this problem is actually very simple, but there's a lot of things going on, a lot of
parts.
So because it's a big problem, with many things we have to solve, whenever we have a big problem,
you got to break it down to smaller parts.
So let's do it little by little until we get to the final solution.
So we have two queries.
So query number one and query number two.
Let's start with the first one, query number one.
So we have the table occupations, it has the column's name and occupation, for example,
the name of a person and what they do as their vocation.
So query, unophobetically, or the list of all names in occupations.
Okay, how can we do this?
Let's start with just this sentence.
I'm going to choose my SQL.
So we need the list of all names in occupations.
So I'm going to say select the name column from occupations table.
Okay, we got that down.
Now immediately followed by the first letter of each profession as a parenthetical in closed
parentheses.
What does that mean?
Okay, so if you have like a name John and he is an architect, we're going to take the
letter A, that's the first letter of the profession.
We're going to put that in parentheses like this, and the rest with the scarred.
Okay, and we just add it to the right of the name.
That's what it means.
Okay, how can we do that?
Well, we can use combine the name with the occupation column value with just the first
letter in parentheses.
So let's just do the occupation, right?
So we had occupation.
How can we just extract the first letter of occupation?
We can say use a function left comma one here.
Left means you're going to take starting from the left, take one character of the occupation,
in this case, the very first letter.
That's fine, but we need to put that in parentheses.
So how can we do that?
So I'm going to combine the that with the name, I'm going to use concat function.
So that way it takes the name.
And then immediately after it's going to put the very first letter, but we need the open
parentheses and the closing one.
So in between, I'm going to add open parentheses as a string comma, and then after the left
occupation commas one, close parenthesis there, I'm going to add a string with the quotes,
single quotes, and then close parentheses like so.
Okay.
And let's run the code and see what we have.
Okay, so that's what it looks like.
Let's sort alphabetically.
So alphabetically, by the names, we're going to do order by the name.
Okay.
Now, that solves the very first query.
Now let's go on to the next query.
So the next query here query the number of occurrences of each occupation occupations.
You're going to select from occupations table.
Now the number of occurrences of each occupation.
So if you just select the occupation itself, you're going to get a list of names of occupations.
Some people might have the same occupation, right?
So what we have to do is group by, okay, group by that occupation.
And then we need to get the count of each.
So I'm going to do your count of occupation.
And at the end here, I'm going to just group by occupation.
Let's see what we get.
And let me just comment out with dash dash space for the time being temporarily.
So we just see the second query.
Okay.
So as you can see, we come, we grouped all the rows so that we can get account for each
of the professions.
But it doesn't want us to do that.
It wants us to display this message here for each of these rows.
How can we do that?
Well, we're going to go to the select part here, and we're going to make this message
with the concat function we learned before.
So let's just start with concat here.
Enclose everything in concat.
Now I'm going to start with a string.
I'm going to add single quotes, single quotes, comma, I'm going to say there are a total
of space.
That's the first part of this thing.
And then it follows by the count of occupation.
We got that here.
So I'm going to cut and place it right after the comma so that when we do the concatination,
it's going to say the count right after the space, the of space.
And then after that, there's another space.
So after the count, comma, space, comma, space under quotes, and then occupation s.
So we have occupation there.
Now we've got to add to the end s.
Now, occupation is in lowercase.
How can we make occupation lowercase?
We call the function lower on the occupation.
Like that.
Now it asks us to sort the occurrences in ascending order.
Okay.
So I'm going to add a sort here, order by, right?
Let me add an order by before the group by here.
Order by.
Now I'm going to order by the count of occupation.
Okay?
Now they say if more than one occupation has the same name, the same count, right?
This should be ordered alphabetically.
So that means if the count is the same, I add a new second set of order by here.
That's going to be by occupation itself by the name.
So that if you have like architect three, programmer three, because they have the same
count, we're going to order them by the name, which architect comes first and then programmer
afterwards because a comes before p.
Okay.
And we have an error in the syntax because let me add the order by after group by.
Maybe that's the problem.
There you go.
So there are total three doctors for actors for singers, some professors, as you can see,
you got everything the count space and then the name and then followed by s dot.
Now let's uncomment the very first query.
And I'm going to run the code.
And there you go.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/draw-the-triangle-1

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Draw The Triangle 1 | SQL Alternative Queries | HackerRank Solution

# Summary of SQL Triangle Problem Solution

## Overview
The task involves printing a triangle of asterisks where the number of asterisks decreases with each line. For example, for an input of 20, the output should be:

```
************
***********
**********
*********
********
*******
******
*****
****
***
**
*
```
The last line contains only a single asterisk without an additional space.

## Methodology
1. **Repeat Function**: Utilize the `REPEAT` function to print asterisks followed by spaces. For instance, `REPEAT('* ', 20)` would yield the first line.

2. **Looping**: Instead of writing multiple lines for each decreasing count, a loop is implemented:

   - Initialize a variable `i` to 20.
   - Use a `WHILE` loop that runs while `i` is greater than zero.
   - On each iteration, print `i` asterisks followed by spaces. 
   - Decrement `i` after each iteration with `SET i = i - 1`.

3. **Stored Procedure**: The solution must be encapsulated within a stored procedure. Here’s how to define it:

   ```sql
   DELIMITER $$
   CREATE PROCEDURE while_proc()
   BEGIN
       DECLARE i INT DEFAULT 20;
       WHILE i > 0 DO
           SELECT REPEAT('* ', i);
           SET i = i - 1;
       END WHILE;
   END$$
   DELIMITER ;
   ```

4. **Delimiter Management**: Change the statement delimiter temporarily to avoid confusion with the semicolon used in SQL.

5. **Final Touch**: Ensure there’s no trailing space for the last line:
   - Use an `IF` condition to check if `i` equals 1. If so, just print `'*'` without the trailing space.

## Execution
To run the procedure:

```sql
CALL while_proc();
```

This will effectively print the desired triangle of asterisks as per the problem's requirements.

Hey everyone, I will draw the triangle 1 from alternative queries from the SQL section of HackerRync.
This problem asks us to print an asterisk space a number of times.
For example, if you say p of 5, that means the first line will have to print asterisk space 5 times.
Then the next line would be 4 times and so on until we reach only one asterisk.
The problem asks us to do p of 20, that is 20 asterisks separated by a space, followed by 19 asterisks, 18 and so on up until the last line that's going to be only one asterisk.
So how can we do that?
Well, first, teach you how to do just the very first one.
If you do select and use the function repeat with a star space and you pass the number 20 here.
Let's see what we get.
So the result is asterisk space repeated 20 times.
That would satisfy the very first line.
But then we got to do it for 19 and then 18, 17 up until one, right?
And I would even argue if I look at this last line, it doesn't seem to have a space after the asterisk.
So to be more technically correct, the last one you wouldn't have to repeat at all.
It would be just the asterisk without any space.
But obviously you could write these one by one.
You can write 20 copy this, do 19 and so on.
But that's not the smart way.
We don't want to do it that way.
We want to do it programmatically.
So we're going to write a program that will do that automatically.
So we don't have to write it.
And I'm going to use a loop for that.
And it's the while loop.
To do the while loop, I have to do it inside a star procedure.
So I cannot just do it right away.
So let's get going here.
So instead of 20, I'm going to put a placeholder variable i.
And that i will change.
So it will start from 20, and then it will become 19, and then it will become 18,
and so on up until one.
Okay, so I'm going to define that variable here.
So I'm going to say declare i, that's the name of the variable,
type int an integer, and the initial value that is the default
is going to be in this case 20.
And then what we're going to do here is a loop.
Now this loop, we're going to have to do the star procedure later.
So just wait a little bit.
So I'm going to do while i is greater than zero do.
We're going to do something inside.
And then at the end, I'm going to say and while let me go in here.
And because we're doing a loop, what is a loop?
I is initially 20.
So we go here and check is 20 greater than zero true because it's true.
It executes whatever's in the block here.
In this case, it's going to do repeat star space with 20 as the value.
Now after that, if we didn't have anything to change the value of i,
it will keep repeating that forever.
It will check 20 greater than zero again.
Yeah, do the same thing.
Do the same thing and so on.
So we have to prevent that from happening, that infinite loop.
So I'm going to set i to be i minus one.
Okay, so 20 becomes 19.
And then on next iteration, 19 becomes 18 and so on.
So say set the value of i is going to be i minus one semicolon.
So that is initially 20.
Once we do the first iteration, it's going to take 20 minus one 19 and assign that to i.
So the next time we check i greater than zero, i is going to be 19.
So 19 greater than zero true.
We're going to do the select repeat star space with 19 as the value.
And so on is going to do with 18 17 so on up until when i is one, right?
I one greater than zero true do only one asterisk and then i minus one would be zero.
So finally it checks again zero greater than zero.
That's false.
Therefore we end this loop.
We don't do anything.
We go and move forward after this block.
Okay, but to make this work, we need to make it a store procedure.
So we're going to close this whole thing in a star procedure.
So we're going to say the following.
So create procedure, give it any name you want.
I can call it a while underscore proc.
Then give parentheses and then I'm going to say begin in a new line and after the whole thing the block of code that I want to execute the statements.
I'm going to say end.
Now to make this work.
This is just the declaration of how the procedure would go.
You have to actually execute the procedure.
So you have to call it.
So I'm going to call space the name that you give here while underscore proc and parentheses semicolon.
Now there's a little problem that we're going to run if we run it like that the semicolon here.
And that's because if you look at the documentation, here's for defined store programs.
This is how you do it.
There's this problem if you use my SQL client to define a store program, containing semicolon characters, a problem arises by default.
My SQL itself recognizes semicolon as a statement delimiter.
Okay, so every time at the end of the line we add a semicolon, right?
But we're going to get a problem if we create a start procedure.
So what we have to do is we must redefine the delimiter temporarily to cause my SQL to pass the entire start program definition to the server.
So basically you have to say delimiter and give it some character, for example, dollar dollar or slash slash.
And then this is going to be the delimiter instead of the semicolon at the end of the line.
And then you do your procedure, definition and so on.
And then at the end you use that delimiter.
And then finally to bring it back to the original delimiter that semicolon, you do the limiter space semicolon.
So we're going to need that otherwise we'll get a problem.
Okay, so instead of the semicolon here, I'm going to use a dollar dollar as the delimiter.
So at the end of the statement of the procedure, I use dollar dollar.
Now to make that work before the procedure, I have to say delimiter space dollar dollar.
Okay, and then after the procedure is done, I can say delimiter semicolon again to bring it back to its original value.
So the next line we use semicolon for the delimiter as we have it here.
And there you go. We got the first line with 20 asterisks followed by space and then 18 and then 19 and then 18 and so on up until the last line that's asterisks followed by space.
So I see here asterisks at the end this one works fine.
If you want to be more technical correct and not have a space after the asterisk when there's only one, you can add here in the repeat.
You can have an if the I is equal to one that is only one asterisk.
We want to only show one asterisk without a space afterward. Otherwise else we do the repeat as usual.
So that way the last one, the very last one, you only got a asterisk and no space after that.
That one works the same way for the purposes of the solution to this problem.
So we want to click submit code.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/draw-the-triangle-2

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Draw The Triangle 2 | SQL Alternative Queries | HackerRank Solution

```markdown
# Triangle Drawing in SQL

## Overview
This guide explains how to create a stored procedure in SQL to draw a triangle pattern using asterisks. The goal is to print a series of lines, where each line contains asterisks followed by a space, increasing from 1 to 20 asterisks.

## Steps

1. **Understanding the Requirement**:
   - Each line in the triangle requires an increasing number of asterisks followed by a space.
   - Example output:
     ```
     *
     **
     ***
     ...
     ********************
     ```

2. **Using Repeat Function**:
   - Initially, you can use the `REPEAT` function to produce the asterisk line.
   - For example, `SELECT REPEAT('* ', n);` where `n` is the number of asterisks to print.

3. **Avoiding Manual Repetition**:
   - Instead of manually writing 20 lines for each number, use a loop.
   - We'll create a loop that runs from 1 to 20, incrementing the count each time.

4. **Setting Up the Loop**:
   - Declare an integer variable `i` initialized to 1.
   - Use a `WHILE` loop that checks if `i <= 20`.
   - Inside the loop, execute the `REPEAT` function to print the asterisks.

5. **Creating the Procedure**:
   - Wrap the logic in a stored procedure.
   - Define the procedure using a custom delimiter to avoid conflicts with semicolons inside the procedure.

6. **Changing the Delimiter**:
   - Temporarily change the delimiter before creating the procedure to avoid SQL errors.
   - Example: `delimiter $$` before the procedure and `delimiter ;` after.

7. **Example Code**:
   ```sql
   DELIMITER $$

   CREATE PROCEDURE while_proc()
   BEGIN
       DECLARE i INT DEFAULT 1;
       WHILE i <= 20 DO
           SELECT REPEAT('* ', i);
           SET i = i + 1;
       END WHILE;
   END$$

   DELIMITER ;

   CALL while_proc();
   ```

8. **Output**:
   - Executing the procedure will print lines from 1 to 20 asterisks, with spaces in between.

## Conclusion
With the above procedure, you can efficiently generate the triangle pattern using SQL, demonstrating the capability of loops and stored procedures in SQL.
```


Hey everyone, I will do draw the triangle 2 from the section SQL alternative queries in hacker rank.
So the question asks us to print an asterisk followed by space.
A certain number of times in the case for P5 as an example,
that means the very first one is going to be an asterisk followed by space.
Then the second one is going to be two asterisks followed by space.
And so on up until the five, that means five asterisk space.
So to do this, I'll teach you first how you can repeat asterisks.
Then we're going to put that in a loop, while loop, so that we can do multiple times instead of us having to write
20 times as the question wants us.
And then finally, we have to place that in the star procedure to be able to run.
So let's go here. If you do select and the function is repeat star,
and 20, for example, that will let me put a space after the asterisks.
That's going to repeat this string, the asterisk space 20 times.
So if I run code, that's what we get.
So this would be the very last row in this problem.
If I do 19, it's going to be asterisk space 19 times concatenated.
And if I do 18 and so on up until one,
which is basically essentially the same thing as without the repeat.
You could do that to solve the problem and cut and write these things 20 times.
But that's not the smart way to do this. You should not do it that way.
As programmers, I should do it with a loop.
I'm going to call this I. So I'm going to add a variable there that's going to change its value
and every iteration that is a starts from one and goes up to 20.
So the first time is going to be one repetition of the asterisk space.
The next time I is going to become I plus one, which is two.
So that's going to be asterisk space, asterisk space.
So two times, right?
And so on for three, four, five up until 20.
So to do that, we must declare I as a variable.
So the name is I, the type is going to be int an integer.
And the initial value in this case is going to be one.
So I'm going to say default space one semicolon.
Now I'm going to have to place this inside a loop so it can repeat the same thing over and over
as long as some condition is true.
So I'm going to say while I last then or equal to 20 do.
And then I'm going to do whatever I want here inside.
And then I'm going to say and while at the end, semicolon.
Now this is nice.
The first time I is one, one is less than or equal to 20 true because that is true.
That executes everything between the do and the end while.
So in this case, it would print repeat asterisk space with I being one,
which is pretty much just asterisk space for the first one.
And then the next time I have to do for I two, but to make that happen,
I have to set the value of I.
So set I to be I plus one.
That is I initially one becomes two.
Then at the end of the block for the while, we have to check the condition again is two less
than or equal to 20 true because it's true.
We execute everything between the do and end while again.
So that would repeat the asterisk star two times.
And then I equals I plus three, meaning I is becoming two plus one.
That's three, three less than or equal to 20.
True, repeat the same thing again.
And so on up until I is going to be 20.
So 20 less than or equal to 20 true.
So it's going to be the repeat 20 times and then 20 plus one is 21, 21 less than or equal to 20.
That's false because it's false.
It's finally stops executing the statement for the while and keeps going in the next line after line five.
Okay, but to make that work, we need to create a star procedure and then execute it.
So we're going to take this whole thing place inside a begin and then end here.
Now I have to say create procedure and give it any name you want.
I would say while underscore proc parentheses, and then I have to call the procedure.
So call with the name that you give here in this case while
proc parentheses, and we cool.
Now we are not done yet because there's a slight issue with my sequel that if you read the docs
for defining stored programs, here's how they define the store procedure, you're going to run
it through this problem. If you use the my sequel client program to define a star program containing
semicolon characters, problem arises by default my sequel itself recognizes the semicolon as
state and the limiter. So the semicolon at the end of every statement in my sequel,
it's called a delimiter. But we're going to have a problem when you define the star procedure.
So in order to get around the problem, we must redefine the delimiter temporarily
to be something other than the semicolon. For example, we're going to use dollar dollar or slash
slash. And then after defining the procedure, we revert the delimiter back to a semicolon.
Here's what they do them the delimiter slash slash do the procedure slash slash to finish
the procedure because that's like the semicolon that we used to have. And then finally, they bring
it back to the limit to be semicolon and then call the procedure. So we're going to do exactly that.
So going back here, instead of the semicolon at the end of the create procedure are going to say,
I'm going to use dollar dollar. And then before the create the seizure line, I have to say delimiter
space dollar dollar. So I can change from semicolon to dollar dollar. And then once I do my procedure,
I can revert back to the limiter semicolon. And then I can continue all my statements with the
semicolon at the end. Okay, let's run the code. And there you go. That's the solution.
You can see one star two stars, three with a space between up until 20.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/occupations

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Occupations | SQL Advanced Select | HackerRank Solution

# SQL Occupations Challenge Summary

The challenge involves processing an `occupations` table with `name` and `occupation` columns to output a table where each column lists the names of individuals sharing the same profession. Here’s how we can achieve that:

### Step-by-Step Breakdown

1. **Understanding the Input Table**:
   - We have a table `occupations` that contains names associated with their respective occupations (e.g., doctors, professors, singers, and actors).

2. **Desired Output Structure**:
   - Each column in the output table represents a different occupation, with names listed under the appropriate occupation. 
   - For example, the first column might show all names with the occupation "Doctor".

3. **SQL Techniques Used**:
   - **Window Functions**: To count occupations and facilitate ordered outputs.
   - **Derived Tables**: To manage intermediate results.
   - **Aggregate Functions**: To consolidate results into the final table.

4. **Using Window Functions**:
   - Start with basic queries to understand the number of rows and specific counts for each occupation.
   - Utilize the `COUNT` function alongside `OVER` and `PARTITION BY` clauses to compute counts within each occupation.

5. **Creating Columns for Each Occupation**:
   - Use `IF` statements or `CASE` expressions to create individual columns for each occupation, displaying names where the occupation matches, or "no" otherwise.

6. **Removing Unnecessary Rows**:
   - After creating columns for each occupation, filter the output to remove "no" entries and consolidate results into a clearer table.

7. **Finalizing the Output Table**:
   - Use a derived table to group by the generated occupation-specific columns and use aggregate functions (like `MIN`) to consolidate rows properly.

### Example SQL Code Structure
```sql
SELECT
    MIN(CASE WHEN occupation = 'Doctor' THEN name END) AS Doctor,
    MIN(CASE WHEN occupation = 'Professor' THEN name END) AS Professor,
    MIN(CASE WHEN occupation = 'Singer' THEN name END) AS Singer,
    MIN(CASE WHEN occupation = 'Actor' THEN name END) AS Actor
FROM
    (SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) as rn
     FROM occupations) AS derived_table
GROUP BY rn
```

### Conclusion
- The final output table successfully consolidates names into the respective occupation columns, formatted as required. This process illustrates the powerful use of SQL functions to manipulate and present data efficiently.

Hey, I will do the challenge occupations from the subsection advanced select of SQL and
hacker rank.
So we're given this table occupations that has the columns name and occupation and we're
asked to produce the following.
The simple output should be a table.
Each column is a list of names that follow the same profession.
For example, in this case here, we see that we got Jenny and Samantha in the output for
the first column.
What is that column about?
Now, if you look at the input, Samantha is a doctor and Jenny is also a doctor.
So we take all the names whose occupation is doctor and place them as the first column
here.
And then we do the same for every other occupation.
For Ashley, that's a professor, Christine and Kedi, we place them in the second column
because they're all with the occupation professor.
Do the same for the actor and the singer one.
I will solve this problem using window functions, derived tables and aggregate functions.
And now we use my SQL.
So to help you understand how much solution is going to go, I want to first talk about
window function.
Let's say we're going to just select name occupation from occupations first so we can
understand what the data looks like.
So as you can see, we got the name there corresponding occupation.
Now let's say I want to count how many rows I have.
So I can do select count star from occupations, right?
And click run code.
As you can see, there are 18 rows.
But what happened here was the 18 rows collapsed into a single row with a number in the output.
What if I wanted to have that 18 for every single row and I wanted to print the original
rows but with the 18 as the third column?
Now to do that, I can do a window function like this.
If I do select count star over like so, and I can even add the name and occupation back
here so you can better visualize it.
So when I say the over there with the parentheses, I can now get that count for every row so
you can see 18 is in every row.
So it's important we understand this concept because we're going to use it to produce the
output for this problem.
And we can go even further.
What if I want to count on a partition discount?
Say I want to count just a number of people in a certain profession.
So I can go here inside the parentheses for over and I can say partition by and I'm going
to give occupation.
So if I do that, it's going to count by occupation.
So we can see Samantha is an actor and now it's no longer 18 rows.
It's four because in total, it found four rows that all have occupation actor.
So we can see every actor now have four.
For doctor, they'll have three because they're in total three doctors and for professor seven.
Okay.
Now we can go further.
What if we want to take these and order by name?
Say I want Eve to come before Samantha.
I can go inside partition by occupation and say order by and I can say name.
So as you can see, it orders by name.
Eve comes before Jennifer, Ked and Samantha.
So we're going to keep these concepts in mind because you're going to use for the solution.
So now I want to create a column for each of these professions.
So I can let's start with just the one, the first one that's going to be doctor.
So I'm going to go here.
We have one, two, three, and then going to create the fourth column.
And for this column, I want to display the value for the name only if it matches the
profession or occupation that I want.
In this case, I'm going to create the column for doctor.
So it should match occupation equals doctor.
So to do that, I'm going to use an if statement.
You can also use case or whatever other thing.
So if the occupation is equal to doctor, what we're going to do, we're going to display
the name here.
Otherwise just going to say no.
Okay.
So what's going to happen if I run this code?
So as you can see, we have the last column that's before and I have Amina, Julia and
Priya.
And these are actually what's going to be in the final results.
We just need to get rid of all these unnecessary nodes before.
Okay.
So there will be some nodes that will appear in case that one of the columns has more rows
than the others.
For example, this one, Jenny, Samantha, no, and then Ashley, Christine and Katie.
So but we don't have to have so many no.
So we're going to get rid of them later.
But for now, this column is for doctor.
Then you repeat the same pattern for all the three other professions.
Okay.
So the same pattern if you can do with one, you can do with all of them.
So we're going to do here, I'm going to create another column.
And this one, if the occupation is equal to professor, we're going to display the name.
Otherwise, we're going to say no.
If I click round code and verify what it looks like, as you can see, it now appeared right
here.
And this is because you can see Ashley is selected because that's the row for Ashley.
Velvet selected, that's because the S row for Velvet.
And the Darault professors, as you can see.
So I left the name and the occupation here, just so we can visualize what's happening,
how the data is being shaped.
But in the end, the final result, you don't need to display or have these two in your
query.
Okay, the name and the occupation.
Let's do the other ones.
So after professor, you're going to say if occupation is equal to singer, we're going
to display the name.
Otherwise, else case, we're going to do no.
Okay.
So the argument is the if condition.
The second is what happens if that is true.
And the third argument is the else case, what happens if that condition is false?
So we return no.
Comma, let's do if occupation is equal to actor.
Then we're going to name.
Otherwise, else we do no.
Okay.
So we got all four of those.
Okay.
So let's run the code and see what we get.
As you can see, now we have the columns for actor here in those rows for doctor in those
rows for professor in those rows and for singer on in those rows.
Now we got a shape this so it's better to the final result here, right?
Got rid of all these no's and remove the first three columns.
So we can get rid of name occupation here is not necessary.
And what I'm going to do is I have to do a derived table here.
So I'm going to select something.
Okay.
I'll do it in a second from this whole result, the stable that we created.
Okay.
Now, because my SQL when you do derive tables as for an A list, we have to say as some name,
whatever I'm going to say derived table.
Now what I'm going to do here is I want to select an any an aggregate function.
It could be mean, for example, I just need an aggregate.
That's the thing because I'm going to do here group by and I need to group by this value
here value of the remember that number the 123 4 based on the actor or 123 based on doctor,
I need that number here.
So I need I'm going to create an alias for this so I can reference in this group by outside.
So I'm going to say here, we can say maybe row numbers and like that.
Okay.
And here outside me is the same name row number.
So I'm going to group by them.
That means I when I have the one here, all the ones you can see one one, I'm going to
group them meaning I collapse them into a single row.
So this one will be combined with this one will be combined with that one will be combined
with that one.
Right.
So if I take them here so you can better visualize and I'll paste them here so you can see this
one.
Do I do actually?
Yeah.
So we're going to take all these combined based on this number because the same number
they all become one.
So you're going to have let me remove those because we don't need them.
So essentially you're going to have a Mina here.
We got Christine here.
We got Ashley here.
So something like this.
Okay.
So that they are each in their proper column without all those extra stuff.
Okay.
So that's what it's doing when you do a group by that.
So to do this I need to do a select here and I'll select using an aggregate.
So I'll use min.
Okay.
I need to reference the value of the column for the doctors.
I need a name is here to make it easy for me to reference there.
So I can see as whatever name you want, you can get colleges doctor and you can say doctor
here and then you do the same thing for every other column.
So I'm going to do a common min professor and at this name professor has to be the alias
for this one as professor.
And then I'm going to do min of singer and this name singer has to match the alias for
this one right here as singer.
And finally I have to do min of actor and this name actor here has to match the alias
for this column here.
So I'm going to say as actor.
Now let's run the code.
Now we get an error and that's because I used row number here and that's probably a keyword.
So I have to say another name that's not exactly row number.
I remove the E and just number.
Let's see if that works.
And there you go.
Now we can see we got those columns consolidated into one and you can see the first column
for doctor, the second for professor and the third for singer, the fourth for actor.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/japanese-cities-name

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Japanese Cities' Names | SQL Basic Select | HackerRank Solution

# Summary of Transcript

The speaker discusses a programming challenge related to querying Japanese city names from a database table called `city`. This table contains the columns: ID, name, country code, district, and population. 

## Key Points:
- **Objective**: To query the names of all Japanese cities.
- **Filtering Criteria**: Use the country code 'JPN' to identify Japanese cities.
- **SQL Query**:
  ```sql
  SELECT name FROM city WHERE country_code = 'JPN';
  ```
- The speaker mentions compatibility with DB2 and other SQL flavors and proceeds to submit the query code.

The talk focuses on performing a simple SQL selection based on given criteria.

Hey everybody, I will do the challenge Japanese cities names from the sections basic select of sequel and hacker rank.
So let's get started. We have this table called city with the columns ID name country code district and population.
We are asked to query the names of all Japanese cities.
To do that, we have to filter by the country code being JPN.
So we can use DB2 and the other flavors. So we're going to do select.
Now the column we want to select is name from the table is city.
Now we have to add a filter. So where the country code that's the column, the value has to be equal to JPN so that we capture only the rows that have Japanese cities.
Now I'm going to submit the code.


A lesson that teaches you how to solve the following problem from the SQL section in HackerRank. 
https://www.hackerrank.com/challenges/binary-search-tree-1

Learn: Building a News Blog Web App with Next.js and Express
https://www.udemy.com/course/building-a-news-blog-web-app-with-nextjs-and-express/

Binary Tree Nodes | SQL Advanced Select | HackerRank Solution

# Summary of Binary Tree Nodes SQL Problem

## Overview
The task involves analyzing a binary tree represented by a dataset containing two columns: `n` (node values) and `p` (parent node values). The goal is to categorize each node as either a "root," "leaf," or "inner" node based on its relationship with parent nodes.

## Node Definitions
- **Root Node**: The top node which has no parent (i.e., `p` is null).
- **Leaf Node**: A node with no children (not a parent to any other node).
- **Inner Node**: A node that has at least one child (i.e., is a parent to another node).

## Procedure to Identify Node Types
1. **Identify Root Nodes**:
   - Check if `p` is null. If true, classify the node as a root.

2. **Identify Leaf Nodes**:
   - Create a list of parent values (`p`) from the dataset excluding null values (i.e., non-parent nodes).
   - For each node value (`n`), check if it exists in the list of parents:
     - If it does not exist, classify the node as a leaf.
     - If it does exist, classify it as an inner node.

3. **SQL Implementation**:
   - Use a `CASE` statement to streamline the classification based on the described conditions.
   - Final output should be ordered by node value (`n`).

## SQL Code Outline
```sql
SELECT 
    n,
    CASE 
        WHEN p IS NULL THEN 'root'
        WHEN n IN (SELECT DISTINCT p FROM BST WHERE p IS NOT NULL) THEN 'inner'
        ELSE 'leaf'
    END AS description
FROM BST
ORDER BY n;
```

## Conclusion
This SQL query compiles a necessary description of each node in a binary tree from the provided dataset, enabling effective understanding and manipulation of the tree structure.

Hey everybody, I would do binary tree nodes from the advanced select section of SQL and HackerRank.
So we're given the stable of the columns n and p. n is the value that's an integer, for example,
one, two, or three. And p is the parent of that value. If we scroll down and see the diagram,
this is what it looks like. So we have nodes, those circles, and they're connected. So that means
a node can have children. In this case, five has two and eight as children. And then at the end,
we have nodes that have no children. There's a call to leave nodes. And the very first node is
called the root node at the top here in this diagram. So we're asked to compile this list
of descriptions for each value for each node. For example, the value one, node of the value one,
is it a leaf, a neater or root? In this case, it is a leaf. Because if you look at the diagram, one
has no children. Okay, so let me show you side by side.
These are the rows. So first row, n one has the parent two. It's better that you look at the parent
first to make sense of what it looks like visually. So let's look at all the values for p. So we have
two, we have eight, and we have five. So that means that two is a parent to one, and two is also
parent to three. In the graph here on the right hand side, that means that two is a parent to one,
you can see here. And two is a parent to three. So two has the children one and three. And if you go
on eight is a parent to six, and eight is a parent to nine. So that means eight has the children six
and nine. And then finally, five is a parent to two, and a parent to eight. So five is the parent
of two, and the parent of eight. So that's what it looks like visually. So we are asked to describe
each of these nodes, whether they are root, the very first of the top, whether they are leaves,
the in this diagram, they're at the bottom usually, and they have no children or their inner,
meaning they have both a parent and a child. Now getting back, let's choose my sequel. I'm going
to do a select star from VST. Let's look at the very first case root. So we want to display the
value that's and followed by the description. Now the description, I'm going to use a case here.
Let me split this into multiple lines so you can better see. So after that, I'm going to use a case.
And for root, that's the easy one because what is a root has no parent. If you find the example
here, the very last row is five, the Pb no. So if the root has no parents, the value of P is null.
So here we're going to say when P is null, then root. So if it's not a root, that means it's either in
between or someone at the bottom that isn't a parent to anyone. So I'm going to focus on the P value
here. So if I do a select for the P values in a compile list of values for P that are non-null,
we can see two, eight and five. And then if I take the value of N and check, hey, is this value of N
contained in this list of parents, if it's not in the list of parents, that means it's a leaf,
right? It's not apparent to any node. Otherwise, if it's apparent to any of these nodes,
it's got to be inner. So let's do a here when the value of N in some list, I want to take the values
of P and group them. If I do a select P from BST here, I'm going to get the values of P,
but they can be repeated. So what I want to do is I want to group by P so that I eliminate any
duplicates. So I get all the parents. I also want to get rid of null. So if I want to get rid of null,
I can add a where here. P is not null. Now this will give us a list of parents, right? In this case,
for this example, two, eight and five. So we're going to check is N in this list. If that is the
case, then that means it's inner because N is apparent, right? It was in the list of parents.
Otherwise else case, we did not find N in the list of parents. Therefore, it does not have any
children. It is a leaf node. We're going to say leaf. Okay, so first we check if it's root,
if that's the case, the parent is no. Okay, if it's not rude, that means, okay, I'm going to check
the list of parents to see if that value is there. If that is the case, that means that value is
apparent to someone, therefore it's inner. But if it's not apparent to anyone, it's got to be a leaf,
the else case. Okay, just to finalize, I have to order by the value of the node, that is the
value of N. So here at the end, I'm going to add order by N. Let's go submit code.
