Week 2

Well, it’s been a long week & I’m supposed to be posting this yesterday but hey, here we go.

I have been going through concepts like JOIN which is used to combine tables in order to get the desired output.

Since I’m using RDBMS, using JOIN command is essential. Here are the types of JOIN.

INNER JOIN

Here you can use this command by using both ON and USING.

SELECT *
FROM ARTIST AS ART
INNER JOIN ALBUM AS ALB
ON ART.ARTIST_ID = ALB.ARTIST_ID;

SELECT *
FROM ARTIST AS ART
INNER JOIN ALBUM AS ALB
USING (ARTIST_ID);

It will only return records between two tables where a joining field such as a primary key finds a match in both tables.

Another example would be in a dating scenario When both people swipe right. You only get matches where both tables (people) have a connection.

SELECT *
FROM singles AS s
INNER JOIN matches AS m ON s.id = m.single_id;

Sarah (as “s”) and Michael (as “m”) both swipe right, so they show up in each other's match list. Others who didn't mutually match? They're filtered out.

LEFT JOIN

Person A swiped right, but Person B ghosted. Includes all from the left table, even if there’s no match on the right.

SELECT *
FROM singles AS s
LEFT JOIN matches AS m ON s.id = m.single_id;

You’ll still see Sarah on the list even if Bob never responded. Unrequited interest shows up with a NULL match.

RIGHT JOIN

It’s just like LEFT JOIN but in vice versa.

SELECT *
FROM matches AS m
RIGHT JOIN singles AS s ON s.id = m.single_id;

Michael swiped, but Sarah never showed up. You still get Michael in the results.

FULL JOIN

It combines all rows from both tables including:

• Matches between the two tables where ON condition is true.

• Rows from the left table that have no match in the right table (will show NULLs for right-side columns)

• Rows from the right table that have no match in the left table (will show NULLs for left-side columns)

Let’s say we have two tables:

• Profiles: all active users looking for a match

• MatchHistory: past matches logged, if any

You want to list everyone—whether they’ve matched before or not.

SELECT p.name, m.last_matched_with
FROM Profiles AS p
FULL JOIN MatchHistory AS m ON p.id = m.profile_id;

This shows the list of every single person from the two tables whether they have matched or not.

SELF JOIN

A SELF JOIN is when a table joins with itself. You treat it like two copies and compare rows from each side using aliases.

Eg: You're trying to match singles based on shared interests. So you compare each person with every other person (except themselves) to see if there’s chemistry.

SELECT a.name AS person1, b.name AS person2
FROM profiles AS a
JOIN profiles AS b
  ON a.hobby = b.hobby AND a.id <> b.id;

The above code shows pairs with matching hobbies (or) shared interests.

CROSS JOIN

A CROSS JOIN returns all possible combinations of rows from two tables. No condition needed.

Let’s take the dating scenario for example.

You have:

• A list of singles

• A list of available date locations

You want to see every possible pairing of a person and a spot—just like speed dating.

SELECT s.name, d.location
FROM singles AS s
CROSS JOIN date_spots AS d;

This shows every person matched with every spot—whether the chemistry is real or not 😄

Well, it’s time to focus practicing via HackerRank.

It is the key to get good at SQL.

Instead of just practicing HackerRank problems, I have divided my time for 2 things - practicing HackerRank problems & learning Excel as it’s an important tool for a Business Analyst.

Hence mornings before office will be dedicated towards HackerRank problems as mornings (6:30am to 9am) are the most productive time & Excel is dedicated after office hours (>7pm) as I’m familiar with the tool.

Here’s the important thing I’ve learned this week - setting goals alone isn’t enough.

Setting systems is important cause goals are based on emotions & systems are based on execution which gets you results.

I learned about setting systems via this video from Leila Hormozi where she explains it in detail.

Looking forward for the next week with a much stronger update 💪🏻💪🏻.