Week 3

It’s week 3. Now I was supposed to start with Excel this week parallelly alongside practicing SQL but instead got sidetracked by my own mind🧠.

Man, need to control the mind these days.

Anyways, here’s my progress for this week.

Started with the Weather Observation Station 5 problem from HackerRank.

As soon as I read the statement, my mind went blank, as if I never knew anything about SQL. I had to search on google, use AI tools in order to understand the problem & the syntax that should be used. I’m glad that I was able to pass this one.

The most important query I learned is the TOP query which is supported by MS SQL Server.

It’s 8:28am. Solved 4 queries.

Here’s the code for my recent problem statement:

SELECT DISTINCT CITY FROM STATION
WHERE CITY LIKE '[AEIOU]%'
    AND CITY LIKE '%[AEIOU]';

I had a hard time solving the problem of Weather Observation Station 8 where the question was “Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.”

I was scratching my head, trying to figure out the problem

While I was searching, I came across this syntax from Microsoft’s website which helped me a lot in answering this query. Since I learned Microsoft SQL Server, it was better to search for it directly from Microsoft’s website.

Completed 2 SQL problems & earned my 2nd star.

I made a small error in this code

SELECT DISTINCT CITY FROM STATION
WHERE CITY NOT LIKE '[AEIOU]%' 
    OR NOT LIKE '%[AEIOU]'

In this case, every NOT LIKE must be paired with a column name so that SQL can go ahead & fulfill the condition else SQL is confused, wondering,

“Hmm, what does OR NOTE LIKE ‘%[AEIOU]’ mean?”

Hence, it’s important to add the column name before NOT LIKE which comes out like this

SELECT DISTINCT CITY FROM STATION
WHERE CITY NOT LIKE '[AEIOU]%' 
    OR CITY NOT LIKE '%[AEIOU]'

Query the Name of any student in STUDENTS who scored higher than Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

SELECT * FROM STUDENTS
WHERE Marks > 75
ORDER BY RIGHT(Name, 3), ID ASC;

I was confused, like I did everything right but what went wrong. Turns out the intended sample output was different from my output.

Here’s the sample output:

<aside>

Ashley

Julia

Belvet

</aside>

Notice that the output only shows names ending with similarly last 3 characters.

Also, secondary sort is an interesting concept I learned, which means “If two rows are equal based on the first sorting rule, use a second rule to break the tie”

So SQL looks at ORDER BY RIGHT(Name, 3) first & sorts them by the last 3 characters from the column “Name”. Now SQL goes to ID & breaks the duplicates hence secondary sort is helpful.

Hence the final answer is something like this:

SELECT Name FROM STUDENTS
WHERE Marks > 75
ORDER BY RIGHT(Name, 3), ID ASC;

Next problem

SELECT name FROM Employee
WHERE salary>2000 AND months<10
ORDER BY employee_id ASC

This was my entire progress until last Friday (25th July, 2025) which is bad :(

This needs improvement.

So, next week, I’ll be back with more improvements.