✅ SQL Mistakes Beginners Should Avoid 🧠💻

1️⃣ Using SELECT *
• Pulls unused columns
• Slows queries
• Breaks when schema changes
• Use only required columns

2️⃣ Ignoring NULL Values
• NULL breaks calculations
• COUNT(column) skips NULL
• Use COALESCE or IS NULL checks

3️⃣ Wrong JOIN Type
• INNER instead of LEFT
• Data silently disappears
• Always ask: Do you need unmatched rows?

4️⃣ Missing JOIN Conditions
• Creates cartesian product
• Rows explode
• Always join on keys

5️⃣ Filtering After JOIN Instead of Before
• Processes more rows than needed
• Slower performance
• Filter early using WHERE or subqueries

6️⃣ Using WHERE Instead of HAVING
• WHERE filters rows
• HAVING filters groups
• Aggregates fail without HAVING

7️⃣ Not Using Indexes
• Full table scans
• Slow dashboards
• Index columns used in JOIN, WHERE, ORDER BY

8️⃣ Relying on ORDER BY in Subqueries
• Order not guaranteed
• Results change
• Use ORDER BY only in final query

9️⃣ Mixing Data Types
• Implicit conversions
• Index not used
• Match column data types

🔟 No Query Validation
• Results look right but are wrong
• Always cross-check counts and totals

🧠 Practice Task
• Rewrite one query
• Remove SELECT *
• Add proper JOIN
• Handle NULLs
• Compare result count

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

❤️ Double Tap For More

✅ 🔤 A–Z of SQL Commands 🗄️💻⚡

A – ALTER
Modify an existing table structure (add/modify/drop columns).

B – BEGIN
Start a transaction block.

C – CREATE
Create database objects like tables, views, indexes.

D – DELETE
Remove records from a table.

E – EXISTS
Check if a subquery returns any rows.

F – FETCH
Retrieve rows from a cursor.

G – GRANT
Give privileges to users.

H – HAVING
Filter aggregated results (used with GROUP BY).

I – INSERT
Add new records into a table.

J – JOIN
Combine rows from two or more tables.

K – KEY (PRIMARY KEY / FOREIGN KEY)
Define constraints for uniqueness and relationships.

L – LIMIT
Restrict number of rows returned (MySQL/PostgreSQL).

M – MERGE
Insert/update data conditionally (mainly in SQL Server/Oracle).

N – NULL
Represents missing or unknown data.

O – ORDER BY
Sort query results.

P – PROCEDURE
Stored program in the database.

Q – QUERY
Request for data (general SQL statement).

R – ROLLBACK
Undo changes in a transaction.

S – SELECT
Retrieve data from tables.

T – TRUNCATE
Remove all records from a table quickly.

U – UPDATE
Modify existing records.

V – VIEW
Virtual table based on a query.

W – WHERE
Filter records based on conditions.

X – XML PATH
Generate XML output (mainly SQL Server).

Y – YEAR()
Extract year from a date.

Z – ZONE (AT TIME ZONE)
Convert datetime to specific time zone.

❤️ Double Tap for More

✅ Complete Roadmap to Learn SQL in 2026 🚀

💎 SQL powers 80% of data analytics jobs.

📚 🔹 SQL FOUNDATIONS

🎯 1️⃣ SELECT Basics (Week 1)
- SELECT \*, specific columns
- FROM tables
- WHERE filters
- ORDER BY, LIMIT

🟢 Practice: Query your first dataset today

🔍 2️⃣ Filtering Mastery
- Comparison operators (=, >, BETWEEN)
- Logical: AND, OR, IN
- Pattern matching: LIKE, %
- NULL handling

📊 3️⃣ Aggregate Power
- COUNT(\*), SUM, AVG, MIN/MAX
- GROUP BY essentials
- HAVING vs WHERE
- DISTINCT counts

🎓 🔥 SQL CORE SKILLS

🔗 4️⃣ JOINS (Most Important ⭐)
- INNER JOIN (must-know)
- LEFT, RIGHT, FULL JOIN
- Multi-table joins
- Self-joins

⚡ 5️⃣ Subqueries & CTEs
- Subqueries in WHERE/FROM
- WITH clause (CTEs)
- Multiple CTE chains
- EXISTS/NOT EXISTS

📈 6️⃣ Window Functions (Game-Changer ⭐)
- ROW_NUMBER(), RANK()
- PARTITION BY magic
- LAG/LEAD (trends)
- Running totals

🎨 🚀 ADVANCED SQL MASTERY

⏰ 7️⃣ Date & Time
- DATEADD, DATEDIFF
- DATE_TRUNC, EXTRACT
- Date filtering patterns
- Cohort analysis

🔤 8️⃣ String Functions
- CONCAT, SUBSTRING
- TRIM, UPPER/LOWER
- LENGTH, REPLACE

🤖 9️⃣ CASE Statements
- Simple vs searched CASE
- Nested logic
- Policy calculations

⚙️ 🔧 PERFORMANCE & JOBS

🚀 1️⃣0️⃣ Indexing Basics
- CREATE INDEX strategies
- EXPLAIN query plans
- Composite indexes

💻 1️⃣1️⃣ Practice Platforms
- LeetCode SQL (50 problems)
- HackerRank SQL
- StrataScratch (real cases)
- DDIA datasets

📱 1️⃣2️⃣ Modern SQL Tools
- pgAdmin (PostgreSQL)
- DBeaver (universal)
- BigQuery Sandbox (free)
- dbt + SQL

💼 ⚡ INTERVIEW READY

🎯 1️⃣3️⃣ Top Interview Questions
- Find 2nd highest salary
- Nth highest records
- Duplicate detection
- Window ranking

📊 1️⃣4️⃣ Real Projects
- Sales dashboard queries
- Customer segmentation
- Inventory optimization
- Build GitHub portfolio

🎨 ⭐ ESSENTIAL SQL TOOLS 2026
- PostgreSQL (free, powerful)
- MySQL Workbench
- BigQuery (cloud-native)
- Snowflake (trial)

1️⃣5️⃣ FREE RESOURCES
🌐 SQLBolt (interactive)
📚 Mode Analytics Tutorial
⚡ LeetCode SQL 50
🎥 DataCamp SQL (free tier)
🐙 W3schools

Double Tap ♥️ For Detailed Explanation

If I had to start learning data analyst all over again, I'd follow this:

1- Learn SQL:

---- Joins (Inner, Left, Full outer and Self)
---- Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)
---- Group by and Having clause
---- CTE and Subquery
---- Windows Function (Rank, Dense Rank, Row number, Lead, Lag etc)

2- Learn Excel:

---- Mathematical (COUNT, SUM, AVG, MIN, MAX, etc)
---- Logical Functions (IF, AND, OR, NOT)
---- Lookup and Reference (VLookup, INDEX, MATCH etc)
---- Pivot Table, Filters, Slicers

3- Learn BI Tools:

---- Data Integration and ETL (Extract, Transform, Load)
---- Report Generation
---- Data Exploration and Ad-hoc Analysis
---- Dashboard Creation

4- Learn Python (Pandas) Optional:

---- Data Structures, Data Cleaning and Preparation
---- Data Manipulation
---- Merging and Joining Data (Merging and joining DataFrames -similar to SQL joins)
---- Data Visualization (Basic plotting using Matplotlib and Seaborn)

Hope this helps you 😊

🎯 SQL Fundamentals Part-1: SELECT Basics

SELECT is the most used SQL command, used to retrieve data from a database.

Think of SQL like asking questions to a database. SELECT = asking what data you want.

✅ What is SELECT in SQL?
SELECT statement retrieves data from one or more tables in a database.

👉 Basic Syntax

SELECT column_name 
FROM table_name;

How SQL executes:
1. Finds table (FROM)
2. Applies filter (WHERE)
3. Returns selected columns (SELECT)
4. Sorts results (ORDER BY)
5. Limits rows (LIMIT)

🔹 1. SELECT All Columns (SELECT *)
Used to retrieve every column from a table.

SELECT * 
FROM employees;

👉 Returns complete table data.

📌 When to use:
✔ Exploring new dataset
✔ Checking table structure
✔ Quick testing

⚠️ Avoid in production: Slow on large tables, fetches unnecessary data.

🔹 2. SELECT Specific Columns
Best practice — retrieve only required data.

SELECT name, salary 
FROM employees;

👉 Returns only selected columns.
💡 Why important:
✅ Faster queries
✅ Better performance
✅ Cleaner results

🔹 3. FROM Clause (Data Source)
Specifies where data comes from.

SELECT name 
FROM customers;

👉 SQL reads data from customers table.

🔹 4. WHERE Clause (Filtering Data)
Used to filter rows based on conditions.

SELECT column 
FROM table 
WHERE condition;

Examples:
- Filter by value: SELECT * FROM employees WHERE salary > 50000;
- Filter by text: SELECT * FROM employees WHERE city = 'Mumbai';

🔹 5. ORDER BY (Sorting Results)
Sorts query results.

SELECT column 
FROM table 
ORDER BY column ASC | DESC;

Examples:
- Ascending: SELECT name, salary FROM employees ORDER BY salary ASC;
- Descending: SELECT name, salary FROM employees ORDER BY salary DESC;

🔹 6. LIMIT (Control Output Rows)
Restricts number of returned rows.

SELECT * 
FROM employees 
LIMIT 5;

👉 Returns first 5 records.

⭐ SQL Query Execution Order
1. FROM
2. WHERE
3. SELECT
4. ORDER BY
5. LIMIT

🧠 Real-World Example
Business question: "Show top 10 highest paid employees."

SELECT name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 10;

🚀 Mini Practice Tasks
✅ Task 1: Get all records from customers.
✅ Task 2: Show only customer name and city.
✅ Task 3: Find employees with salary > 40000.
✅ Task 4: Show top 3 highest priced products.

Double Tap ♥️ For Part-2

🔍 SQL Fundamentals Part-2: Filtering

After learning SELECT basics, the next step is learning how to filter data.

👉 In real-world data analysis, you rarely need full data — you filter specific rows.

Filtering = extracting only relevant data from a table.

✅ What is Filtering in SQL?
Filtering is done using the WHERE clause.

It allows you to:
✔ Get specific records
✔ Apply conditions
✔ Clean data
✔ Extract business insights

🔹 1. Comparison Operators
Used to compare values.
Operator Meaning
• = Equal
• > Greater than
• < Less than
• >= Greater than or equal
• <= Less than or equal
• != or <> Not equal

✅ Examples

• Equal to
SELECT * FROM employees WHERE city = 'Pune';

• Greater than
SELECT * FROM employees WHERE salary > 50000;

• Not equal
SELECT * FROM employees WHERE department != 'HR';

💡 Most commonly used in dashboards reporting.

🔹 2. Logical Operators (AND, OR, NOT)

Used to combine multiple conditions.

✅ AND — Both conditions must be true

SELECT * FROM employees WHERE salary > 50000 AND city = 'Mumbai';

👉 Returns employees with: salary > 50000 AND located in Mumbai

✅ OR — Any condition can be true

SELECT * FROM employees WHERE city = 'Delhi' OR city = 'Pune';

👉 Returns employees from either city.

✅ NOT — Reverse condition

SELECT * FROM employees WHERE NOT department = 'Sales';

👉 Excludes Sales department.

🔹 3. BETWEEN (Range Filtering)

Used to filter values within a range.

Syntax
SELECT * FROM table WHERE column BETWEEN value1 AND value2;

✅ Example
SELECT * FROM employees WHERE salary BETWEEN 30000 AND 70000;

👉 Includes boundary values.

🔹 4. IN Operator (Multiple Values Shortcut)

Better alternative to multiple OR conditions.

❌ Without IN
WHERE city = 'Pune' OR city = 'Delhi' OR city = 'Mumbai'

✅ With IN
SELECT * FROM employees WHERE city IN ('Pune','Delhi','Mumbai');

👉 Cleaner and faster.

🔹 5. LIKE — Pattern Matching
Used for searching text patterns.

⭐ Wildcards
Symbol Meaning
• % Any number of characters
• _ Single character

✅ Starts with "A"
SELECT * FROM customers WHERE name LIKE 'A%';

✅ Ends with "n"
WHERE name LIKE '%n';

✅ Contains "an"
WHERE name LIKE '%an%';

Used heavily in search features.

🔹 6. NULL Handling (Very Important ⭐)

NULL means:
👉 Missing / unknown value
👉 Not zero
👉 Not empty

❌ Wrong
WHERE salary = NULL

✅ Correct
SELECT * FROM employees WHERE salary IS NULL;

Check non-null values
WHERE salary IS NOT NULL;

💡 Very common interview question.

⭐ Order of Filtering Execution
SQL processes filtering after reading table:

FROM → WHERE → SELECT → ORDER BY → LIMIT

🧠 Real-World Data Analyst Examples

Q. Find customers from Pune
SELECT * FROM customers WHERE city = 'Pune';

Q. Find high-paying jobs in IT department
SELECT * FROM employees WHERE salary > 80000 AND department = 'IT';

Q. Find names starting with "R"
SELECT * FROM employees WHERE name LIKE 'R%';

Used daily in business analytics.

🚀 Mini Practice Tasks
✅ Q1
Find employees whose salary is greater than 60000.
✅ Q2
Find customers from Pune or Mumbai.
✅ Q3
Find products priced between 100 and 500.
✅ Q4
Find employees whose name starts with "S".
✅ Q5
Find records where email is missing (NULL).

✅ Double Tap ♥️ For More

SQL is easy to learn, but difficult to master.

Here are 5 hacks to level up your SQL 👇

1. Know complex joins
2. Master Window functions
3. Explore alternative solutions
4. Master query optimization
5. Get familiar with ETL

———

𝘉𝘵𝘸, 𝘵𝘩𝘦𝘳𝘦 𝘢𝘳𝘦 𝘱𝘳𝘢𝘤𝘵𝘪𝘤𝘦 𝘱𝘳𝘰𝘣𝘭𝘦𝘮𝘴 𝘪𝘯 𝘵𝘩𝘦 𝘤𝘢𝘳𝘰𝘶𝘴𝘦𝘭.

𝟭/ 𝗞𝗻𝗼𝘄 𝗰𝗼𝗺𝗽𝗹𝗲𝘅 𝗷𝗼𝗶𝗻𝘀

LEFT JOIN, RIGHT JOIN, INNER JOIN, OUTER JOIN — these are easy.

But SQL gets really powerful, when you know
↳ Anti Joins
↳ Self Joins
↳ Cartesian Joins
↳ Multi-Table Joins

𝟮/ 𝗠𝗮𝘀𝘁𝗲𝗿 𝗪𝗶𝗻𝗱𝗼𝘄 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀

Window functions = flexible, effective, and essential.

They give you Python-like versatility in SQL. 𝘚𝘶𝘱𝘦𝘳 𝘤𝘰𝘰𝘭.

𝟯/ 𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝗮𝗹𝘁𝗲𝗿𝗻𝗮𝘁𝗶𝘃𝗲 𝘀𝗼𝗹𝘂𝘁𝗶𝗼𝗻𝘀

In SQL, there’s rarely one “right” way to solve a problem.

By exploring alternative approaches, you develop flexibility in thinking AND learn about trade-offs.

𝟰/ 𝗠𝗮𝘀𝘁𝗲𝗿 𝗾𝘂𝗲𝗿𝘆 𝗼𝗽𝘁𝗶𝗺𝗶𝘇𝗮𝘁𝗶𝗼𝗻

Inefficient queries overload systems, cost money and waste time.

3 (super quick) tips on optimizing queries:
1. Use indexes effectively
2. Analyze execution plans
3. Reduce unnecessary operations

𝟱/ 𝗚𝗲𝘁 𝗳𝗮𝗺𝗶𝗹𝗶𝗮𝗿 𝘄𝗶𝘁𝗵 𝗘𝗧𝗟

ETL is the backbone of moving and preparing data.

↳ Extract: Pull data from various sources
↳ Transform: Clean, filter, and reformat the data
↳ Load: Store the cleaned data in a data warehouse

Here you can find essential SQL Interview Resources👇
https://t.iss.one/mysqldata

Like this post if you need more 👍❤️

Hope it helps :)

Here are some essential SQL tips for beginners 👇👇

◆ Primary Key = Unique Key + Not Null constraint
◆ To perform case insensitive search use UPPER() function ex. UPPER(customer_name) LIKE ‘A%A’
◆ LIKE operator is for string data type
◆ COUNT(*), COUNT(1), COUNT(0) all are same
◆ All aggregate functions ignore the NULL values
◆ Aggregate functions MIN, MAX, SUM, AVG, COUNT are for int data type whereas STRING_AGG is for string data type
◆ For row level filtration use WHERE and aggregate level filtration use HAVING
◆ UNION ALL will include duplicates where as UNION excludes duplicates 
◆ If the results will not have any duplicates, use UNION ALL instead of UNION
◆ We have to alias the subquery if we are using the columns in the outer select query
◆ Subqueries can be used as output with NOT IN condition.
◆ CTEs look better than subqueries. Performance wise both are same.
◆ When joining two tables , if one table has only one value then we can use 1=1 as a condition to join the tables. This will be considered as CROSS JOIN.
◆ Window functions work at ROW level.
◆ The difference between RANK() and DENSE_RANK() is that RANK() skips the rank if the values are the same.
◆ EXISTS works on true/false conditions. If the query returns at least one value, the condition is TRUE. All the records corresponding to the conditions are returned.

Like for more 😄😄

🔗 SQL Fundamentals Part-4: JOINS

In real databases, data is stored in multiple tables, not one big table. JOINS allow you to combine data from different tables.

Example:
Customers Table
customer_id | name
1 | Rahul
2 | Priya

Orders Table
order_id | customer_id | amount
101 | 1 | 500
102 | 2 | 300

To see customer name + order amount, we must use JOIN.

Basic JOIN Syntax

SELECT columns
FROM table1
JOIN table2
ON table1.column = table2.column;

ON defines the relationship between tables.

1️⃣ INNER JOIN
Returns only matching records from both tables.

SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Result:
name | amount
Rahul | 500
Priya | 300

👉 If a customer has no order, they will not appear.

2️⃣ LEFT JOIN (Very Common ⭐)

Returns: All rows from left table, Matching rows from right table, If no match → NULL

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Result:
name | amount
Rahul | 500
Priya | 300
Amit | NULL

👉 Amit has no order.

3️⃣ RIGHT JOIN

Opposite of LEFT JOIN. Returns: All rows from right table, Matching rows from left table

SELECT customers.name, orders.amount
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

Used less frequently in analytics.

4️⃣ FULL JOIN
Returns: All records from both tables, If no match → NULL

SELECT customers.name, orders.amount
FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id;

5️⃣ SELF JOIN

A table joins with itself. Used when rows relate to other rows in the same table.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

JOIN Visual Understanding

• INNER JOIN: Only matching rows
• LEFT JOIN: All left + matching right
• RIGHT JOIN: All right + matching left
• FULL JOIN: All rows from both
• SELF JOIN: Table joined with itself

Real Data Analyst Examples
-- Customer order report
SELECT c.name, o.amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;

-- Products with category
SELECT p.product_name, c.category
FROM products p
JOIN categories c
ON p.category_id = c.category_id;

-- Sales by region
SELECT r.region_name, SUM(s.amount)
FROM sales s
JOIN regions r
ON s.region_id = r.region_id
GROUP BY r.region_name;

Used daily in Power BI dashboards, analytics queries, and reports.

Mini Practice Tasks
1. Show customer names with their order amount.
2. Show all customers even if they have no orders.
3. Show employees with their manager names.
4. Show products with their category name.

Common Interview Questions
✔ Difference between INNER JOIN and LEFT JOIN
✔ When to use SELF JOIN
✔ Why LEFT JOIN is used in analytics
✔ Difference between JOIN and UNION
✔ Join execution order

Double Tap ♥️ For More

SQL Detailed Roadmap
|
| | |-- Fundamentals
| |-- Introduction to Databases
| | |-- What SQL does
| | |-- Relational model
| | |-- Tables, rows, columns
| |-- Keys and Constraints
| | |-- Primary keys
| | |-- Foreign keys
| | |-- Unique and check constraints
| |-- Normalization
| | |-- 1NF, 2NF, 3NF
| | |-- ER diagrams

| | |-- Core SQL
| |-- SQL Basics
| | |-- SELECT, WHERE, ORDER BY
| | |-- GROUP BY and HAVING
| | |-- JOINS: INNER, LEFT, RIGHT, FULL
| |-- Intermediate SQL
| | |-- Subqueries
| | |-- CTEs
| | |-- CASE statements
| | |-- Aggregations
| |-- Advanced SQL
| | |-- Window functions
| | |-- Analytical functions
| | |-- Ranking, moving averages, lag and lead
| | |-- UNION, INTERSECT, EXCEPT

| | |-- Data Management
| |-- Data Types
| | |-- Numeric, text, date, JSON
| |-- Indexes
| | |-- B tree and hash indexes
| | |-- When to create indexes
| |-- Transactions
| | |-- ACID properties
| |-- Views
| | |-- Standard views
| | |-- Materialized views

| | |-- Database Design
| |-- Schema Design
| | |-- Star schema
| | |-- Snowflake schema
| |-- Fact and Dimension Tables
| |-- Constraints for clean data

| | |-- Performance Tuning
| |-- Query Optimization
| | |-- Execution plans
| | |-- Index usage
| | |-- Reducing scans
| |-- Partitioning
| | |-- Horizontal partitioning
| | |-- Sharding basics

| | |-- SQL for Analytics
| |-- KPI calculations
| |-- Cohort analysis
| |-- Funnel analysis
| |-- Churn and retention tables
| |-- Time based aggregations
| |-- Window functions for metrics

| | |-- SQL for Data Engineering
| |-- ETL Workflows
| | |-- Staging tables
| | |-- Transformations
| | |-- Incremental loads
| |-- Data Warehousing
| | |-- Snowflake
| | |-- Redshift
| | |-- BigQuery
| |-- dbt Basics
| | |-- Models
| | |-- Tests
| | |-- Lineage

| | |-- Tools and Platforms
| |-- PostgreSQL
| |-- MySQL
| |-- SQL Server
| |-- Oracle
| |-- SQLite
| |-- Cloud SQL
| |-- BigQuery UI
| |-- Snowflake Worksheets

| | |-- Projects
| |-- Build a sales reporting system
| |-- Create a star schema from raw CSV files
| |-- Design a customer segmentation query
| |-- Build a churn dashboard dataset
| |-- Optimize slow queries in a sample DB
| |-- Create an analytics pipeline with dbt

| | |-- Soft Skills and Career Prep
| |-- SQL interview patterns
| |-- Joins practice
| |-- Window function drills
| |-- Query writing speed
| |-- Git and GitHub
| |-- Data storytelling

| | |-- Bonus Topics
| |-- NoSQL intro
| |-- Working with JSON fields
| |-- Spatial SQL
| |-- Time series tables
| |-- CDC concepts
| |-- Real time analytics

| | |-- Community and Growth
| |-- LeetCode SQL
| |-- Kaggle datasets with SQL
| |-- GitHub projects
| |-- LinkedIn posts
| |-- Open source contributions

Free Resources to learn SQL

• W3Schools SQL
https://www.w3schools.com/sql/

• SQL Programming
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

• SQL Notes
https://whatsapp.com/channel/0029Vb6hJmM9hXFCWNtQX944

• Mode Analytics SQL tutorials
https://mode.com/sql-tutorial/

• Data Analytics Resources
https://t.iss.one/sqlspecialist

• HackerRank SQL practice
https://www.hackerrank.com/domains/sql

• LeetCode SQL problems
https://leetcode.com/problemset/database/

• Data Engineering Resources
https://whatsapp.com/channel/0029Vaovs0ZKbYMKXvKRYi3C

• Khan Academy SQL basics
https://www.khanacademy.org/computing/computer-programming/sql

• PostgreSQL official docs
https://www.postgresql.org/docs/

• MySQL official docs
https://dev.mysql.com/doc/

• NoSQL Resources
https://whatsapp.com/channel/0029VaxA2hTHgZWe5FpFjm3p

Double Tap ❤️ For More

⚡ Subqueries CTEs

After mastering JOINS, the next important concept is Subqueries and CTEs. These are used when queries become complex and you need intermediate results.

👉 Very common in data analyst interviews and real analytics queries.

🔹 1️⃣ What is a Subquery?

A subquery is a query inside another SQL query. It is executed first, and its result is used by the main query.

🎯 Basic Syntax

SELECT column
FROM table
WHERE column = (SELECT column FROM table);

🧠 Example 1 — Find Employees with Highest Salary

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Explanation:
1️⃣ Inner query finds maximum salary
2️⃣ Outer query finds employee with that salary

🔹 2️⃣ Subquery in WHERE Clause
Most common use.

Example — Employees earning more than average salary

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Used heavily in analytics queries.

🔹 3️⃣ Subquery in FROM Clause
Also called Derived Table.

SELECT department, AVG(avg_salary)
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_salary
GROUP BY department;

Used when intermediate results are required.

🔹 4️⃣ EXISTS / NOT EXISTS

Used to check if a record exists in another table.

Example — Customers who placed orders

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE c.customer_id = o.customer_id
);

👉 Returns customers with orders.

Example — Customers with no orders

SELECT name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE c.customer_id = o.customer_id
);

⭐ 5️⃣ Common Table Expressions (CTEs)

CTEs improve query readability and structure. Defined using WITH clause.

Basic Syntax

WITH cte_name AS (
    SELECT column
    FROM table
)
SELECT *
FROM cte_name;

🧠 Example — Average Salary by Department

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT *
FROM dept_avg;

🔹 6️⃣ Multiple CTEs

You can chain multiple CTEs.

WITH total_sales AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
),
top_customers AS (
    SELECT *
    FROM total_sales
    WHERE total > 1000
)
SELECT *
FROM top_customers;

Used often in complex analytics queries.

🧠 Real Analyst Examples

Customers with above average purchases

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (
    SELECT AVG(total)
    FROM (
        SELECT SUM(amount) AS total
        FROM orders
        GROUP BY customer_id
    ) AS totals
);

Complex analytics often uses subqueries or CTEs.

🚀 Mini Practice Tasks

🎯 Task 1 — Find employees earning more than average salary

✔ Solution

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

💡 Explanation: Subquery calculates average salary, Outer query filters employees above average.

🎯 Task 2 — Find customers who placed orders

✔ Solution (Using EXISTS ⭐)

SELECT name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE c.customer_id = o.customer_id
);

💡 Explanation: Checks if an order exists for the customer.

🎯 Task 3 — Find departments with salary greater than company average

✔ Solution

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

💡 Explanation: Subquery finds company average salary, HAVING filters departments above that average.

🎯 Task 4 — Use a CTE to calculate total sales per customer

✔ Solution

WITH customer_sales AS (
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM customer_sales;

💡 Explanation: CTE calculates total sales for each customer, Main query retrieves the result.

Double Tap ♥️ For More

🧠 SQL Interview Question (Moderate–Tricky & Duplicate Transaction Detection)
📌

transactions(transaction_id, user_id, transaction_date, amount)

❓ Ques :

👉 Find users who made multiple transactions with the same amount consecutively.

🧩 How Interviewers Expect You to Think

• Sort transactions chronologically for each user
• Compare the current transaction amount with the previous one
• Use a window function to detect consecutive duplicates

💡 SQL Solution

SELECT
user_id,
transaction_date,
amount
FROM (
SELECT
user_id,
transaction_date,
amount,
LAG(amount) OVER (
PARTITION BY user_id
ORDER BY transaction_date
) AS prev_amount
FROM transactions
) t
WHERE amount = prev_amount;

🔥 Why This Question Is Powerful

• Tests understanding of LAG() for row comparison
• Evaluates ability to identify patterns in sequential data
• Reflects real-world use cases like detecting suspicious or duplicate transactions

❤️ React if you want more tricky real interview-level SQL questions 🚀

🚀 Window Functions ⭐

Window functions are one of the most powerful SQL features used in data analytics, reporting, and advanced SQL interviews.

👉 They allow you to perform calculations across rows without collapsing them like GROUP BY.

In simple words:
GROUP BY → reduces rows

Window Functions → keep rows but add calculated values

🧠 Basic Syntax of Window Functions

SELECT column, window_function() 
OVER ( 
  PARTITION BY column 
  ORDER BY column 
) 
FROM table;

Components:
- OVER() → defines the window
- PARTITION BY → splits data into groups
- ORDER BY → defines calculation order

🔹 1️⃣ ROW_NUMBER()

Assigns a unique sequential number to rows.

SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rank 
FROM employees;

Result:
name | salary | rank
Rahul | 90000 | 1
Priya | 85000 | 2
Amit | 85000 | 3

👉 Even if salaries are same, numbers stay unique.

🔹 2️⃣ RANK()

Assigns rank but skips numbers when ties occur.

SELECT name, salary, RANK() OVER(ORDER BY salary DESC) AS rank 
FROM employees;

Result:
name | salary | rank
Rahul | 90000 | 1
Priya | 85000 | 2
Amit | 85000 | 2
Neha | 80000 | 4

Notice rank 3 is skipped.

🔹 3️⃣ DENSE_RANK()

Similar to RANK but does not skip numbers.

SELECT name, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS rank 
FROM employees;

Result:
name | salary | rank
Rahul | 90000 | 1
Priya | 85000 | 2
Amit | 85000 | 2
Neha | 80000 | 3

🔹 4️⃣ PARTITION BY

Used to divide rows into groups before calculation.

Example: Rank employees within each department

SELECT name, department, salary, 
RANK() OVER( 
  PARTITION BY department 
  ORDER BY salary DESC 
) AS dept_rank 
FROM employees;

👉 Each department gets its own ranking.

🔹 5️⃣ LAG()

Used to access previous row values.

Example: Compare sales with previous day.

SELECT date, sales, LAG(sales) OVER(ORDER BY date) AS previous_sales 
FROM sales;

🔹 6️⃣ LEAD()

Used to access next row values.

SELECT date, sales, LEAD(sales) OVER(ORDER BY date) AS next_sales 
FROM sales;

⭐ Real Data Analyst Examples

Top 3 highest salaries

SELECT ** 
FROM ( 
  SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn 
  FROM employees 
) t 
WHERE rn <= 3;

Running total of sales

SELECT date, sales, SUM(sales) OVER(ORDER BY date) AS running_total 
FROM sales;

Rank products by category

SELECT product_name, category, price, 
RANK() OVER(PARTITION BY category ORDER BY price DESC) AS rank 
FROM products;

🎯 Common Interview Questions
✔ Difference between ROW_NUMBER, RANK, DENSE_RANK
✔ Find Nth highest salary
✔ Running totals using window functions
✔ Compare current row with previous row
✔ Rank employees by department

🚀 Mini Practice Tasks
Task 1: Assign row numbers to employees by salary.
Task 2: Rank employees by salary.
Task 3: Find top 3 highest salaries using window functions.
Task 4: Calculate running total of sales.

💼 What You Must Master
✅ ROW_NUMBER()
✅ RANK()
✅ DENSE_RANK()
✅ PARTITION BY
✅ LAG() / LEAD()
✅ Running totals

These functions are used heavily in real analytics queries and SQL interviews.

Double Tap ♥️ For More