SQL Roadmap ✅

Commit and master 𝗦𝗤𝗟 in just 𝟯𝟬 𝗗𝗮𝘆𝘀!

I've outlined a simple, actionable plan for you to follow…

𝗪𝗲𝗲𝗸 𝟭: 𝗕𝗮𝘀𝗶𝗰𝘀 𝗼𝗳 𝗦𝗤𝗟

➛ Day 1-2: Introduction to SQL, setting up your environment (MySQL/PostgreSQL/SQL Server).

➛ Day 3-4: Understanding databases, tables, and basic SQL syntax.

➛ Day 5-7: Working with SELECT, WHERE, and filtering data.

𝗪𝗲𝗲𝗸 𝟮: 𝗖𝗼𝗿𝗲 𝗤𝘂𝗲𝗿𝗶𝗲𝘀

➛ Day 8-10: Using JOINs – INNER, LEFT, RIGHT, FULL.

➛ Day 11-13: GROUP BY, HAVING, and aggregate functions (SUM, COUNT, AVG).

➛ Day 14: Practice session – write complex queries.

𝗪𝗲𝗲𝗸 𝟯: 𝗠𝗼𝗱𝗶𝗳𝘆𝗶𝗻𝗴 𝗗𝗮𝘁𝗮

➛ Day 15-17: INSERT, UPDATE, DELETE – altering your data.

➛ Day 18-20: Subqueries, nested queries, and derived tables.

➛ Day 21: Practice session – work on a mini-project.

𝗪𝗲𝗲𝗸 𝟰: 𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝗦𝗤𝗟 𝗧𝗼𝗽𝗶𝗰𝘀 𝗮𝗻𝗱 𝗣𝗿𝗼𝗷𝗲𝗰𝘁

➛ Day 22-24: Window functions, RANK, DENSE_RANK, ROW_NUMBER.

➛ Day 25-27: Creating and managing indexes, views, and stored procedures.

➛ Day 28-30: Capstone project – work with real-world data to design and query a database.

Here you can find SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL Number Functions 👆

SQL books won’t teach you this.

Natural Keys vs. Autoincrement IDs vs. Public IDs. (or maybe all together)


𝗡𝗮𝘁𝘂𝗿𝗮𝗹 𝗞𝗲𝘆𝘀

Natural keys carry intrinsic meaning because they are part of the domain.

They are directly related to the data, making them intuitive and easy to understand. Examples include email addresses or employee IDs.

The problem is that they are usually not good for performance, but they can also be a security risk if you expose them.


𝗔𝘂𝘁𝗼𝗶𝗻𝗰𝗿𝗲𝗺𝗲𝗻𝘁 𝗜𝗗𝘀

Autoincrement IDs automatically generate unique integers to identify rows within a table.

They are often used as primary keys.

Simple integers are fast for the database to index and query. They provide optimal performance.

However, they are vulnerable to enumeration attacks since predicting the next or previous record is easy.


𝗣𝘂𝗯𝗹𝗶𝗰 𝗜𝗗𝘀 (𝗨𝗨𝗜𝗗𝘀)

UUIDs (Universally Unique Identifiers) are 128-bit identifiers used to uniquely identify information without relying on a centralized authority.

They are difficult to guess, making them suitable for public exposure in APIs.

The problem is they are larger and more complex than integers. This can impact performance, particularly in indexing and storage.


𝗙𝗶𝗻𝗱𝗶𝗻𝗴 𝘁𝗵𝗲 𝗦𝘄𝗲𝗲𝘁 𝗦𝗽𝗼𝘁: 𝗔 𝗠𝗶𝘅𝗲𝗱 𝗔𝗽𝗽𝗿𝗼𝗮𝗰𝗵

Combining different types of keys can offer a balanced solution:

• InternalID: Used for internal operations and relationships between tables.

• PublicID: Used in API responses and endpoints to securely reference user records.

• Email (Natural Key): Used to ensure unique identification of users within the business logic.


The mixed approach keeps your system fast, secure, and easy to understand.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

SQL Query Logical Order

How a SQL query gets executed internally - Lets see step by step!

We all know SQL, but most of us do not understand the internals of it.

Let me take an example to explain this better.

Select p.plan_name, count(plan_id) as total_count
From plans p
Join subscriptions s on s.plan_id=p.plan_id
Where p.plan_name !=’premium’
Group by p.plan_name
Having count(plan_id) > 100
Order by p.plan_name
Limit 10;

Step 01: Get the table data required to run the sql query
Operations: FROM, JOIN (From plans p, Join subscriptions s)

Step 02: Filter the data rows
Operations: WHERE (where p.plan_name=’premium’)

Step 03: Group the data
Operations: GROUP (group by p.plan_name)

Step 04: Filter the grouped data
Operations: HAVING (having count(plan_id) > 100)

Step 05: Select the data columns
Operations: SELECT (select p.plan_name, count(p.plan_id)

Step 06: Order the data
Operations: ORDER BY (order by p.plan_name)

Step 07: Limit the data rows
Operations: LIMIT (limit 100)

Knowing the Internals really help.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need answer for the above question👍❤️

Hope it helps :)

SQL for Data Analysis

Most people learn SQL just enough to pull some data. But if you really understand it, you can analyze massive datasets without touching Excel or Python.

Here are 8 game-changing SQL concepts that will make you a data pro:

👇

1. Stop pulling raw data. Start pulling insights.

The biggest mistake? Running a query that gives you everything and then filtering it later.

Good analysts don’t pull raw data. They shape the data before it even reaches them.

2. “SELECT ” is a rookie move.

Pulling all columns is lazy and slow.

A pro only selects what they need.
✔️ Fewer columns = Faster queries
✔️ Less noise = Clearer insights

The more precise your query, the less time you waste cleaning data.

3. GROUP BY is your best friend.

You don’t need 100,000 rows of transactions. What you need is:
✔️ Sales per region
✔️ Average order size per customer
✔️ Number of signups per month

Grouping turns chaotic data into useful summaries.

4. Joins = Connecting the dots.

Your most important data is split across multiple tables.

Want to know how much each customer spent? You need to join:
✔️ Customer info
✔️ Order history
✔️ Payments

Joins = unlocking hidden insights.

5. Window functions will blow your mind.

They let you:
✔️ Rank customers by total purchases
✔️ Calculate rolling averages
✔️ Compare each row to the overall trend

It’s like pivot tables, but way more powerful.

6. CTEs will save you from spaghetti SQL.

Instead of writing a 50-line nested query, break it into steps.

CTEs (Common Table Expressions) make your SQL:
✔️ Easier to read
✔️ Easier to debug
✔️ Reusable

Good SQL is clean SQL.

7. Indexes = Speed.

If your queries take forever, your database is probably doing unnecessary work.

Indexes help databases find data faster.

If you work with large datasets, this is a game changer.

SQL isn’t just about pulling data. It’s about analyzing, transforming, and optimizing it.

Master these 8 concepts, and you’ll never look at SQL the same way again.

Join us on WhatsApp: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Top Tools to learn Data Engineering

Join for more data engineering resources: https://t.iss.one/sql_engineer

The way you WRITE an SQL query differs from the way SQL PROCESSES it DataAnalytics

Your query might look like this:

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

But SQL executes queries in a different order:

FROM (identifies table)
WHERE (filters rows)
GROUP BY (groups rows)
HAVING (filters grouped rows)
SELECT (chooses columns to display)
ORDER BY (sorts final output)

Why SQL is a Must-Have Skill?

If you're working with data, mastering SQL is non-negotiable! It’s the backbone of handling and making sense of vast datasets in any industry.

◆ Data at Your Fingertips
Effortlessly organize, retrieve, and manage large datasets to make informed decisions faster.

◆ Stay Organized
Use primary and foreign keys to keep your data accurate and connected across tables.

◆ Unlock Insights
Combine data from multiple sources and uncover trends using SQL's powerful query capabilities.

◆ Efficiency Matters
Optimize your databases with normalization and avoid unnecessary redundancy.

◆ Advanced Tools
From ACID transactions to optimizing with DELETE vs TRUNCATE, SQL makes sure your data is consistent and secure.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

SQL Zero to Hero 💪

SQL From Basic to Advanced level

Basic SQL is ONLY 7 commands:
- SELECT
- FROM
- WHERE (also use SQL comparison operators such as =, <=, >=, <> etc.)
- ORDER BY
- Aggregate functions such as SUM, AVERAGE, COUNT etc.
- GROUP BY
- CREATE, INSERT, DELETE, etc.
You can do all this in just one morning.

Once you know these, take the next step and learn commands like:
- LEFT JOIN
- INNER JOIN
- LIKE
- IN
- CASE WHEN
- HAVING (undertstand how it's different from GROUP BY)
- UNION ALL
This should take another day.

Once both basic and intermediate are done, start learning more advanced SQL concepts such as:
- Subqueries (when to use subqueries vs CTE?)
- CTEs (WITH AS)
- Stored Procedures
- Triggers
- Window functions (LEAD, LAG, PARTITION BY, RANK, DENSE RANK)
These can be done in a couple of days.
Learning these concepts is NOT hard at all

- what takes time is practice and knowing what command to use when. How do you master that?
- First, create a basic SQL project
- Then, work on an intermediate SQL project (search online) -

Lastly, create something advanced on SQL with many CTEs, subqueries, stored procedures and triggers etc.

This is ALL you need to become a badass in SQL, and trust me when I say this, it is not rocket science. It's just logic.

Remember that practice is the key here. It will be more clear and perfect with the continous practice

SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

5 frequently Asked SQL Interview Questions with Answers in Data Engineering interviews:
𝐃𝐢𝐟𝐟𝐢𝐜𝐮𝐥𝐭𝐲 - 𝐌𝐞𝐝𝐢𝐮𝐦

⚫️Determine the Top 5 Products with the Highest Revenue in Each Category.
Schema: Products (ProductID, Name, CategoryID), Sales (SaleID, ProductID, Amount)

WITH ProductRevenue AS (
SELECT p.ProductID,
p.Name,
p.CategoryID,
SUM(s.Amount) AS TotalRevenue,
RANK() OVER (PARTITION BY p.CategoryID ORDER BY SUM(s.Amount) DESC) AS RevenueRank
FROM Products p
JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductID, p.Name, p.CategoryID
)
SELECT ProductID, Name, CategoryID, TotalRevenue
FROM ProductRevenue
WHERE RevenueRank <= 5;

⚫️ Identify Employees with Increasing Sales for Four Consecutive Quarters.
Schema: Sales (EmployeeID, SaleDate, Amount)

WITH QuarterlySales AS (
SELECT EmployeeID,
DATE_TRUNC('quarter', SaleDate) AS Quarter,
SUM(Amount) AS QuarterlyAmount
FROM Sales
GROUP BY EmployeeID, DATE_TRUNC('quarter', SaleDate)
),
SalesTrend AS (
SELECT EmployeeID,
Quarter,
QuarterlyAmount,
LAG(QuarterlyAmount, 1) OVER (PARTITION BY EmployeeID ORDER BY Quarter) AS PrevQuarter1,
LAG(QuarterlyAmount, 2) OVER (PARTITION BY EmployeeID ORDER BY Quarter) AS PrevQuarter2,
LAG(QuarterlyAmount, 3) OVER (PARTITION BY EmployeeID ORDER BY Quarter) AS PrevQuarter3
FROM QuarterlySales
)
SELECT EmployeeID, Quarter, QuarterlyAmount
FROM SalesTrend
WHERE QuarterlyAmount > PrevQuarter1 AND PrevQuarter1 > PrevQuarter2 AND PrevQuarter2 > PrevQuarter3;

⚫️ List Customers Who Made Purchases in Each of the Last Three Years.
Schema: Orders (OrderID, CustomerID, OrderDate)

WITH YearlyOrders AS (
SELECT CustomerID,
EXTRACT(YEAR FROM OrderDate) AS OrderYear
FROM Orders
GROUP BY CustomerID, EXTRACT(YEAR FROM OrderDate)
),
RecentYears AS (
SELECT DISTINCT OrderYear
FROM Orders
WHERE OrderDate >= CURRENT_DATE - INTERVAL '3 years'
),
CustomerYearlyOrders AS (
SELECT CustomerID,
COUNT(DISTINCT OrderYear) AS YearCount
FROM YearlyOrders
WHERE OrderYear IN (SELECT OrderYear FROM RecentYears)
GROUP BY CustomerID
)
SELECT CustomerID
FROM CustomerYearlyOrders
WHERE YearCount = 3;


⚫️ Find the Third Lowest Price for Each Product Category.
Schema: Products (ProductID, Name, CategoryID, Price)

WITH RankedPrices AS (
SELECT CategoryID,
Price,
DENSE_RANK() OVER (PARTITION BY CategoryID ORDER BY Price ASC) AS PriceRank
FROM Products
)
SELECT CategoryID, Price
FROM RankedPrices
WHERE PriceRank = 3;

⚫️ Identify Products with Total Sales Exceeding a Specified Threshold Over the Last 30 Days.
Schema: Sales (SaleID, ProductID, SaleDate, Amount)

WITH RecentSales AS (
SELECT ProductID,
SUM(Amount) AS TotalSales
FROM Sales
WHERE SaleDate >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY ProductID
)
SELECT ProductID, TotalSales
FROM RecentSales
WHERE TotalSales > 200;

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

Why SQL Query Optimization Matters More Than You Think

Have you ever noticed your SQL queries dragging their feet, taking forever to return results? It’s frustrating, right? The truth is that optimizing your SQL queries is essential if you want your applications to run smoothly and efficiently.

Here’s why SQL optimization should be on your radar:

→ 𝗦𝗽𝗲𝗲𝗱 𝗠𝗮𝘁𝘁𝗲𝗿𝘀: Did you know that optimized SQL queries can run up to 10 times faster? That means getting the data you need in seconds instead of minutes. Imagine how much more you could get done with that kind of speed!

→ 𝗦𝗮𝘃𝗲 𝗼𝗻 𝗖𝗼𝘀𝘁𝘀: Every second your query runs, it’s using up resources like CPU and memory. By optimizing your queries, you can cut down on resource usage, which can save you a lot of money—especially if you’re working in the cloud where every bit of processing power is billed.

→ 𝗦𝘁𝗮𝘆 𝗦𝗰𝗮𝗹𝗮𝗯𝗹𝗲: Your data isn’t getting any smaller, and as it grows, so does the load on your database. Optimization ensures that as your datasets grow, your queries can handle the increased workload without slowing down. This is crucial if you want to keep everything running smoothly as your business scales.

→ 𝗛𝗮𝗽𝗽𝘆 𝗨𝘀𝗲𝗿𝘀: No one likes waiting, especially not your users. Faster queries mean a better experience for everyone. When your application is responsive, it keeps users engaged and happy, which is a big win.

𝗛𝗲𝗿𝗲’𝘀 𝘁𝗵𝗲 𝗶𝗺𝗽𝗮𝗰𝘁 𝗼𝗳 𝗴𝗲𝘁𝘁𝗶𝗻𝗴 𝗶𝘁 𝗿𝗶𝗴𝗵𝘁:

→ Businesses that focus on optimizing their SQL queries often see a 30-50% improvement in application performance.

→ By just tweaking the slowest 10% of your queries, you can slash your database resource usage by up to 50%.

→ Proper indexing and smart query design can turn minutes into seconds, which is a game-changer for user satisfaction and productivity.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

How to optimize SQL code:

𝟭) 𝗢𝗻𝗹𝘆 𝘀𝗲𝗹𝗲𝗰𝘁 𝘁𝗵𝗲 𝗻𝗲𝗰𝗲𝘀𝘀𝗮𝗿𝘆 𝗰𝗼𝗹𝘂𝗺𝗻𝘀

This tip is specially important when you work with large tables, running window functions, or multiple joins.

DON'T: Select all the columns from the table
DOs: Select only the columns you need!

𝟮) 𝗔𝗹𝘄𝗮𝘆𝘀 𝗳𝗶𝗹𝘁𝗲𝗿 𝗮𝘀 𝘀𝗼𝗼𝗻 𝗮𝘀 𝘆𝗼𝘂 𝗰𝗮𝗻

If you only need certain rows from a table, apply the filters as early as you can.

Please, don't wait until you do all the CTEs, then all the joins, and realize you need to apply some filters… Your company will thank you for that.

𝟯) 𝗝𝗼𝗶𝗻 𝘆𝗼𝘂𝗿 𝗹𝗮𝗿𝗴𝗲𝘀𝘁 𝘁𝗮𝗯𝗹𝗲 𝗮𝘁 𝘁𝗵𝗲 𝗲𝗻𝗱 (𝗶𝗳 𝘆𝗼𝘂 𝗰𝗮𝗻!)

We've all experienced it. But when you have your biggest table as the base, and joining it to other tables it will result in a lot of scanning.

Look at these 2 queries:

𝟭) 𝗦𝗘𝗟𝗘𝗖𝗧 * 𝗙𝗥𝗢𝗠 𝗯𝗶𝗴_𝘁𝗮𝗯𝗹𝗲 𝗜𝗡𝗡𝗘𝗥 𝗝𝗢𝗜𝗡 𝘁𝗮𝗯𝗹𝗲_𝟭 𝗢𝗡 𝘅𝘅𝘅

vs.

𝟮) 𝗦𝗘𝗟𝗘𝗖𝗧 * 𝗙𝗥𝗢𝗠 𝘁𝗮𝗯𝗹𝗲_𝟭 𝗜𝗡𝗡𝗘𝗥 𝗝𝗢𝗜𝗡 𝗯𝗶𝗴_𝘁𝗮𝗯𝗹𝗲 𝗢𝗡 𝘅𝘅𝘅

The second query will be significantly lower in computing power, so make sure to always start with the smallest table as you base (the best you can!)

𝟰) 𝗕𝗲 𝗚𝗲𝗻𝘁𝗹𝗲 𝘄𝗶𝘁𝗵 𝘄𝗶𝗻𝗱𝗼𝘄 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀

I've seen many people saying use window function for your queries. Well, I don't think it's a good idea.

Yes, they are powerful but they are also resource-intensive, so use them only if necessary.

And ask yourself how much data it needs to process!

𝟱) 𝗠𝗼𝗿𝗲 𝗖𝗧𝗘𝘀 𝗹𝗲𝘀𝘀 𝘀𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀

Think about your audience. When you are writing a huge query with sub-queries, it is (very!) hard for people to navigate.

Think of replacing them with CTE - they will thank you for that!
And it will also be easier for you to debug and optimize (if needed)

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)