✅ SQL Aggregation & GROUP BY Explained 📊🧠

Aggregation functions are used to summarize data, especially when working with grouped values.

1️⃣ Common Aggregate Functions

⦁ COUNT() – Total number of rows
⦁ SUM() – Adds up numeric values
⦁ AVG() – Calculates the average
⦁ MIN() / MAX() – Finds smallest/largest value

Example:

SELECT department, COUNT(*)  
FROM employees  
GROUP BY department;

➡️ Returns the number of employees in each department.

2️⃣ GROUP BY

Used with aggregate functions to group rows based on column values.

SELECT city, AVG(salary)  
FROM employees  
GROUP BY city;

➡️ Shows average salary by city.

3️⃣ HAVING vs WHERE

⦁ WHERE filters rows before grouping
⦁ HAVING filters groups after aggregation

Example:

SELECT dept_id, COUNT(*)  
FROM employees  
GROUP BY dept_id  
HAVING COUNT(*) > 5;

➡️ Shows departments with more than 5 employees.

4️⃣ GROUP BY Multiple Columns

SELECT dept_id, role, COUNT(*)  
FROM employees  
GROUP BY dept_id, role;

➡️ Groups by department and role for detailed summaries.

5️⃣ Real-World Use Cases

✅ Sales by region
✅ Orders per customer
✅ Avg. rating per product
✅ Monthly revenue reports

💬 Tap ❤️ for more!

✅ SQL Window Functions 🪟📊

Window functions perform calculations across rows related to the current row without collapsing data, unlike GROUP BY.

1️⃣ ROW_NUMBER()
Assigns a unique number to each row within a partition.

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

📌 *Use case:* Rank employees by salary within each department.


2️⃣ RANK() vs DENSE_RANK()
• RANK() → Skips numbers on ties (1, 2, 2, 4)
• DENSE_RANK() → No gaps (1, 2, 2, 3)

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

3️⃣ LAG() & LEAD()
Access previous or next row values.

SELECT name, salary,
  LAG(salary)  OVER (ORDER BY id) AS prev_salary,
  LEAD(salary) OVER (ORDER BY id) AS next_salary
FROM employees;

📌 Use case: Compare current vs previous/next values
(e.g., salary change, stock price movement).

4️⃣ NTILE(n)
Divides rows into *n* equal buckets.

SELECT name,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

📌 Use case: Quartiles & percentile-based analysis.


5️⃣ Aggregates with OVER()
Running totals & partition-wise calculations.

SELECT name, dept_id, salary,
  SUM(salary) OVER (PARTITION BY dept_id) AS dept_total
FROM employees;

🧠 Interview Q&A

Q1️⃣ GROUP BY vs OVER()?
• GROUP BY → Collapses rows (one row per group)
• OVER() → Keeps all rows and adds calculated columns

Q2️⃣ When to use LAG()?
To compare current row with previous data
(e.g., daily revenue change, previous month balance).

Q3️⃣ No PARTITION BY used?
The function runs over the entire result set.

Q4️⃣ Can we use ORDER BY inside OVER()?
✅ Yes. Required for ranking, LAG/LEAD, running totals.

💬 Double tap ❤️ & share for more SQL tips! 🚀

✅ SQL Basics You Should Know 💻📊

1️⃣ What is SQL?
SQL (Structured Query Language) is a standardized language used to manage and manipulate relational databases.

2️⃣ Most Common SQL Commands:
• SELECT – fetch data
• INSERT – add data
• UPDATE – modify existing data
• DELETE – remove data
• CREATE – create a new table or database
• DROP – delete a table or database

3️⃣ Filtering Data:
To filter records based on conditions:

SELECT * FROM employees WHERE salary > 50000;

4️⃣ Sorting Data:
To sort results in ascending or descending order:

SELECT name FROM students ORDER BY marks DESC;

5️⃣ Using Functions:
Common aggregate functions:
• COUNT() – counts the number of records
• AVG() – calculates the average value
• MAX() / MIN() – returns the highest/lowest value
• SUM() – computes the total sum

6️⃣ Grouping Data:
To group records and perform calculations:

SELECT department, COUNT(*) FROM employees GROUP BY department;

7️⃣ Joins:
Combining rows from two or more tables based on related columns:
• INNER JOIN: Returns matching records in both tables
• LEFT JOIN: Returns all records from the left table and matching records from the right table
• RIGHT JOIN: Returns all records from the right table and matching records from the left table
• FULL JOIN: Returns all records when there is a match in either left or right table

8️⃣ Aliases:
To rename a column or table for readability:

SELECT name AS employee_name FROM employees;

9️⃣ Subqueries:
Using a query within another query:

SELECT name FROM students WHERE marks > (SELECT AVG(marks) FROM students);

🔟 Real Use Cases:
• Managing employee records
• Generating sales reports
• Tracking inventory levels
• Analyzing customer insights

💬 Tap ❤️ for more SQL tips and tricks!

✅ Useful SQL Concepts You Should Know 🚀📈

1️⃣ Constraints in SQL:

- PRIMARY KEY – Uniquely identifies each row
- FOREIGN KEY – Links to another table
- UNIQUE – Ensures all values are different
- NOT NULL – Column must have a value
- CHECK – Validates data before insert/update

2️⃣ SQL Views:

Virtual tables based on result of a query

CREATE VIEW top_students AS
SELECT name, marks FROM students WHERE marks > 90;

3️⃣ Indexing:

Improves query performance

CREATE INDEX idx_name ON employees(name);

4️⃣ SQL Transactions:

Ensure data integrity

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

5️⃣ Triggers:

Automatic actions when events occur

CREATE TRIGGER log_update
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO logs(action) VALUES ('Employee updated');

6️⃣ Stored Procedures:

Reusable blocks of SQL logic

CREATE PROCEDURE getTopStudents()
BEGIN
  SELECT * FROM students WHERE marks > 90;
END;

7️⃣ Common Table Expressions (CTEs):

Temporary named result sets

WITH dept_count AS (
  SELECT department, COUNT(*) AS total FROM employees GROUP BY department
)
SELECT * FROM dept_count;

💬 Double Tap ❤️ For More!

✅ SQL Coding Questions with Answers: Part-1 📊💻

1️⃣ Get the Second Highest Salary
Table: Employees
| id | name | salary |
|----|---------|--------|
| 1 | Alice | 5000 |
| 2 | Bob | 7000 |
| 3 | Charlie | 7000 |
| 4 | David | 6000 |

Query:

SELECT MAX(salary) AS Second_Highest
FROM Employees
WHERE salary < (
  SELECT MAX(salary) FROM Employees
);

This returns the highest salary less than the maximum—i.e., the second highest.

2️⃣ Count Employees Per Department
Table: Employees
| id | name | dept |
|----|--------|--------|
| 1 | Alice | HR |
| 2 | Bob | IT |
| 3 | Clara | IT |
| 4 | Dan | Sales |

Query:

SELECT dept, COUNT(*) AS total_employees  
FROM Employees  
GROUP BY dept;

This groups employees by department and counts how many are in each.

3️⃣ Find Duplicate Emails
Table: Users
| id | email |
|----|------------------|
| 1 | a@gmail.com |
| 2 | b@gmail.com |
| 3 | a@gmail.com |

Query:

SELECT email, COUNT(*) AS count  
FROM Users  
GROUP BY email  
HAVING COUNT(*) > 1;

Returns all emails that appear more than once.

4️⃣ Get Top 2 Salaries Per Department
Table: Employees
| id | name | dept | salary |
|----|--------|-------|--------|
| 1 | Alice | IT | 7000 |
| 2 | Bob | IT | 6500 |
| 3 | Clara | HR | 6000 |
| 4 | Dan | HR | 5900 |

Query:

SELECT * FROM (
  SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk  
  FROM Employees  
) AS ranked  
WHERE rnk <= 2;

Ranks salaries within each department and returns top 2 per group.

5️⃣ Employees With No Manager Assigned
Table: Employees
| id | name | manager_id |
|----|-------|------------|
| 1 | John | NULL |
| 2 | Sarah | 1 |
| 3 | Alex | 2 |

Query:

SELECT * FROM Employees  
WHERE manager_id IS NULL;

Returns employees without any assigned manager.

💬 Double Tap ❤️ for Part-2!

✅ SQL Coding Interview Questions with Answers – Part 2 📚💻

1️⃣ Find Employees Who Earn More Than Their Manager
Table: Employees
| id | name | salary | manager_id |
|----|--------|--------|------------|
| 1 | Alice | 8000 | NULL |
| 2 | Bob | 6000 | 1 |
| 3 | Clara | 9000 | 1 |
| 4 | Dan | 5000 | 2 |

Query:

SELECT e.name  
FROM Employees e  
JOIN Employees m ON e.manager_id = m.id  
WHERE e.salary > m.salary;

*Finds employees whose salary is greater than their manager’s.*

2️⃣ Find Departments With More Than 3 Employees
Table: Employees
| id | name | dept |
|----|-------|-------|
| 1 | Alice | IT |
| 2 | Bob | IT |
| 3 | Clara | HR |
| 4 | Dan | IT |
| 5 | Eva | IT |

Query:

SELECT dept  
FROM Employees  
GROUP BY dept  
HAVING COUNT(*) > 3;

*Lists departments that have more than 3 people.*

3️⃣ Find Employees Who Joined in Last 30 Days
Table: Employees
| id | name | join_date |
|----|-------|------------|
| 1 | Alice | 2023-11-10 |
| 2 | Bob | 2023-12-15 |
| 3 | Clara | 2023-12-25 |

Query:

SELECT * FROM Employees  
WHERE join_date >= CURRENT_DATE - INTERVAL 30 DAY;

*Shows all recent joiners.*

4️⃣ Find Common Records in Two Tables
Tables: A B
| A.id |
|------|
| 1 |
| 2 |
| 3 |

| B.id |
|------|
| 2 |
| 3 |
| 4 |

Query:

SELECT A.id  
FROM A  
INNER JOIN B ON A.id = B.id;

*Returns IDs that are present in both tables.*

5️⃣ List Employees with Same Salary
Table: Employees
| id | name | salary |
|----|-------|--------|
| 1 | Alice | 5000 |
| 2 | Bob | 6000 |
| 3 | Dan | 5000 |

Query:

SELECT salary  
FROM Employees  
GROUP BY salary  
HAVING COUNT(*) > 1;

*Then join it back if you want full details:*

SELECT * FROM Employees  
WHERE salary IN (  
  SELECT salary  
  FROM Employees  
  GROUP BY salary  
  HAVING COUNT(*) > 1
);

💬 Tap ❤️ for Part-3!

✅ SQL Coding Interview Questions with Answers: Part 3 📊💻

1️⃣ Get Highest Salary Per Department
Table: Employees
Columns: id, name, dept, salary

SELECT dept, MAX(salary) AS highest_salary  
FROM Employees  
GROUP BY dept;

Use case: Department-wise pay analysis.

2️⃣ Find Employees Without Matching Department
Tables: Employees, Departments

SELECT e.*  
FROM Employees e  
LEFT JOIN Departments d  
ON e.dept_id = d.id  
WHERE d.id IS NULL;

Use case: Data quality checks after joins.

3️⃣ Delete Duplicate Records but Keep One
Table: Users
Column: email

DELETE FROM Users  
WHERE id NOT IN (  
  SELECT MIN(id)  
  FROM Users  
  GROUP BY email  
);

Use case: Cleanup before analytics.

4️⃣ Find Nth Highest Salary (Example: 3rd)
Table: Employees

SELECT salary  
FROM (  
  SELECT salary,  
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk  
  FROM Employees  
) t  
WHERE rnk = 3;

Use case: Works even with duplicate salaries.

5️⃣ Swap Gender Values
Table: Employees
Column: gender (M, F)

UPDATE Employees  
SET gender =  
CASE  
  WHEN gender = 'M' THEN 'F'  
  WHEN gender = 'F' THEN 'M'  
END;

Use case: Data correction tasks.

6️⃣ Find Employees with Odd IDs
Table: Employees

SELECT *  
FROM Employees  
WHERE id % 2 = 1;

Use case: Common filter logic question.

7️⃣ Get Running Total of Salary
Table: Employees
Column: salary

SELECT id, salary,  
SUM(salary) OVER (ORDER BY id) AS running_total  
FROM Employees;

Use case: Used in financial and growth reports.

💬 Tap ❤️ for Part 4!

✅ SQL Coding Interview Questions with Answers: Part 4 📘💻

1️⃣ Retrieve Employees with the Highest Salary in Each Department (Full Details)
Table: Employees
Columns: id, name, dept, salary

SELECT *
FROM Employees e
WHERE salary = (
  SELECT MAX(salary)
  FROM Employees
  WHERE dept = e.dept
);

🧠 _Use case:_ Get full employee details, not just the salary, for top earners per department.

2️⃣ Find Departments Without Employees
Tables: Departments (id, name), Employees (id, name, dept_id)

SELECT d.*
FROM Departments d
LEFT JOIN Employees e ON d.id = e.dept_id
WHERE e.id IS NULL;

🧠 _Use case:_ Identify departments that haven’t been staffed yet.

3️⃣ Rank Employees by Salary Within Department (With Ties)
Table: Employees

SELECT id, name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
FROM Employees;

🧠 _Use case:_ Useful for performance reviews or compensation analysis.

4️⃣ Find Consecutive Login Days Per User
Table: Logins (user_id, login_date)

SELECT user_id, login_date,
       DATEDIFF(login_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp
FROM Logins;

🧠 _Use case:_ Group by user_id and grp to find streaks of consecutive logins.

5️⃣ Get Employees with the Minimum Salary in the Company
Table: Employees

SELECT *
FROM Employees
WHERE salary = (SELECT MIN(salary) FROM Employees);

🧠 _Use case:_ Identify underpaid or entry-level employees.

6️⃣ Find Managers Who Don’t Have Any Direct Reports
Table: Employees (id, name, manager_id)

SELECT *
FROM Employees
WHERE id NOT IN (
  SELECT DISTINCT manager_id
  FROM Employees
  WHERE manager_id IS NOT NULL
);

🧠 Use case: Spot inactive or placeholder managers.

💬 Double Tap ❤️ For More

Most Asked SQL Interview Questions at MAANG Companies🔥🔥

Preparing for an SQL Interview at MAANG Companies? Here are some crucial SQL Questions you should be ready to tackle:

1. How do you retrieve all columns from a table?

SELECT * FROM table_name;

2. What SQL statement is used to filter records?

SELECT * FROM table_name
WHERE condition;

The WHERE clause is used to filter records based on a specified condition.

3. How can you join multiple tables? Describe different types of JOINs.

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

Types of JOINs:

1. INNER JOIN: Returns records with matching values in both tables

SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;

2. LEFT JOIN: Returns all records from the left table & matched records from the right table. Unmatched records will have NULL values.

SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

3. RIGHT JOIN: Returns all records from the right table & matched records from the left table. Unmatched records will have NULL values.

SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

4. FULL JOIN: Returns records when there is a match in either left or right table. Unmatched records will have NULL values.

SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column;

4. What is the difference between WHERE & HAVING clauses?

WHERE: Filters records before any groupings are made.

SELECT * FROM table_name
WHERE condition;

HAVING: Filters records after groupings are made.

SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > value;

5. How do you calculate average, sum, minimum & maximum values in a column?

Average: SELECT AVG(column_name) FROM table_name;

Sum: SELECT SUM(column_name) FROM table_name;

Minimum: SELECT MIN(column_name) FROM table_name;

Maximum: SELECT MAX(column_name) FROM table_name;

Here you can find essential SQL Interview Resources👇
https://news.1rj.ru/str/mysqldata

Like this post if you need more 👍❤️

Hope it helps :)

✅ Top SQL Queries: Part-1 🧠💻

1️⃣ SELECT – Retrieve Data
🔹 Use case: Show all employees
SELECT * FROM employees;

2️⃣ WHERE – Filter Data
🔹 Use case: Get employees from ‘Sales’ department
SELECT name FROM employees WHERE department = 'Sales';

3️⃣ ORDER BY – Sort Results
🔹 Use case: List products by price (low to high)
SELECT product_name, price FROM products ORDER BY price ASC;

4️⃣ GROUP BY – Aggregate Data
🔹 Use case: Count employees in each department
SELECT department, COUNT(*) FROM employees GROUP BY department;

5️⃣ JOIN – Combine Tables
🔹 Use case: Show orders with customer names
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

6️⃣ INSERT – Add New Records
🔹 Use case: Add a new product
INSERT INTO products (name, price, category)
VALUES ('Headphones', 1500, 'Electronics');

7️⃣ UPDATE – Modify Existing Records
🔹 Use case: Change price of 'Headphones'
UPDATE products SET price = 1700 WHERE name = 'Headphones';

8️⃣ DELETE – Remove Data
🔹 Use case: Delete users inactive for 1 year
DELETE FROM users WHERE last_login < '2024-01-01';

9️⃣ LIKE – Pattern Matching
🔹 Use case: Find customers whose names start with 'A'
SELECT * FROM customers WHERE name LIKE 'A%';

🔟 LIMIT – Restrict Output
🔹 Use case: Show top 3 most expensive items
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

💬 Tap ❤️ for Part 2!

🚀 Roadmap to Master SQL in 30 Days! 🗃️🧠

📅 Week 1: SQL Basics
🔹 Day 1–2: What is SQL? DBMS vs RDBMS
🔹 Day 3–5: SELECT, WHERE, ORDER BY, LIMIT
🔹 Day 6–7: Filtering with AND, OR, IN, NOT, BETWEEN

📅 Week 2: Intermediate SQL
🔹 Day 8–9: Functions (COUNT, SUM, AVG, MIN, MAX)
🔹 Day 10–11: GROUP BY, HAVING
🔹 Day 12–14: JOINS (INNER, LEFT, RIGHT, FULL)

📅 Week 3: Advanced SQL
🔹 Day 15–17: Subqueries Nested Queries
🔹 Day 18–20: CASE statements, COALESCE, NULL handling
🔹 Day 21–22: Window Functions (ROW_NUMBER, RANK, PARTITION BY)

📅 Week 4: Practical Use Projects
🔹 Day 23–25: Views, Indexes, Stored Procedures (basic)
🔹 Day 26–28: Real-world project (e.g., Sales dashboard with queries)
🔹 Day 29–30: Practice on platforms like LeetCode, HackerRank, Mode

💡 Bonus Tools:
• MySQL / PostgreSQL / SQLite
• DB Fiddle / SQLZoo / W3Schools
• Power BI / Excel for data connection

💬 Tap ❤️ for more!

Today, let's start with the complete SQL series starting with the basics:

✅ SQL Basics: Part-1 🧠💾

SQL (Structured Query Language) is the standard language used to communicate with databases.
You use it to store, retrieve, update, and delete data in a structured format.

🛠️ Why Learn SQL?
• It’s used in data analytics, development, and business intelligence.
• Works with tools like Power BI, Excel, Python, Tableau, etc.
• Helps in querying and analyzing large datasets efficiently.

📚 Key Concepts:

1️⃣ DBMS (Database Management System)
• A software to manage databases.
• Stores data in files or documents.
• Examples: Microsoft Access, MongoDB (non-relational).
• No strict structure or rules.

2️⃣ RDBMS (Relational Database Management System)
• Stores data in tables with rows and columns.
• Ensures data consistency using relationships.
• Follows ACID properties (Atomicity, Consistency, Isolation, Durability).
• Examples: MySQL, PostgreSQL, Oracle, SQL Server.

🗂️ Simple Table Example (in RDBMS):

Customers Table

You can use SQL to:
SELECT * FROM Customers;
➡️ This returns all records from the table. (Note: The * here is a wildcard meaning "all columns").

🔁 Real-life Analogy:
Think of RDBMS as Excel — rows are records, columns are fields.
SQL is the language to ask questions like:
- Who are my customers from Delhi?
- What is the total number of orders last month?

🎯 Task for You Today:
✅ Install MySQL or use an online SQL editor (like SQLFiddle)
✅ Learn basic syntax: SELECT, FROM
✅ Try creating a sample table and selecting data

💬 Tap ❤️ for Part-2

✅ SQL Basics: Part-2 (SQL Commands) 🧠💾

1️⃣ SELECT – Pull data from a table
_Syntax:_

SELECT column1, column2 FROM table_name;

_Example:_

SELECT name, city FROM customers;

To get _everything_ use:

SELECT * FROM customers;

2️⃣ WHERE – Filter specific rows
_Syntax:_

SELECT columns FROM table_name WHERE condition;

_Example:_

SELECT name FROM customers WHERE city = 'Delhi';

_Operators you can use:_
• =, !=, >, <, >=, <=
• LIKE (pattern match)
• BETWEEN, IN, IS NULL

3️⃣ ORDER BY – Sort results
_Syntax:_

SELECT columns FROM table_name ORDER BY column ASC|DESC;

_Example:_

SELECT name, age FROM employees ORDER BY age DESC;

4️⃣ LIMIT – Restrict number of results
_Syntax:_

SELECT columns FROM table_name LIMIT number;

_Example:_

SELECT * FROM products LIMIT 5;

🔥 _Quick Practice Task:_
Write a query to:
• Get top 10 highest-paid employees in 'Marketing'
• Show name, salary, and department
• Sort salary high to low:

SELECT name, salary, department
FROM employees
WHERE department = 'Marketing'
ORDER BY salary DESC
LIMIT 10;

✅ SQL Interview QA 💼🧠

Q1. What does the SELECT statement do in SQL?
_Answer:_
It retrieves data from one or more columns in a table.

SELECT name, city FROM customers;

Q2. How would you fetch all the columns from a table?
_Answer:_
Use SELECT * to get every column.

SELECT * FROM orders;

Q3. What’s the difference between WHERE and HAVING?
_Answer:_
• WHERE filters rows _before_ grouping
• HAVING filters _after_ GROUP BY
You use WHERE with raw data, HAVING with aggregated data.

Q4. Write a query to find all products with price > 500.
_Answer:_

SELECT * FROM products WHERE price > 500;

Q5. How do you sort data by two columns?
_Answer:_
Use ORDER BY col1, col2.

SELECT name, department FROM employees ORDER BY department ASC, name ASC;

Q6. What does LIMIT 1 do in a query?
_Answer:_
It returns only the _first row_ of the result.

SELECT * FROM customers ORDER BY created_at DESC LIMIT 1;

Q7. Write a query to get names of top 5 students by marks.
_Answer:_

SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 5;

Q8. Can you use ORDER BY without WHERE?
_Answer:_
Yes. ORDER BY works independently. It sorts all data unless filtered with WHERE.

💡 _Pro Tip:_
In interviews, they may ask you to _write queries live_ or explain the _output_ of a query. Stay calm, read the structure carefully, and _think in steps_.

DOUBLE TAP ❤️ FOR MORE

✅ SQL Basics: Part-3: Filtering with SQL Operators

Filtering helps you narrow down results based on specific conditions.

Let’s explore some powerful SQL operators:

1️⃣ IN – Match multiple values
Syntax:

SELECT columns FROM table_name WHERE column IN (value1, value2,...);

Example:
Get customers from specific cities:

SELECT name, city FROM customers
WHERE city IN ('Delhi', 'Mumbai', 'Chennai');

2️⃣ OR – Match any of multiple conditions
Syntax:

SELECT columns FROM table_name WHERE condition1 OR condition2;

Example:
Get employees from HR or Finance:

SELECT name FROM employees
WHERE department = 'HR' OR department = 'Finance';

3️⃣ AND – Match all conditions
Syntax:

SELECT columns FROM table_name WHERE condition1 AND condition2;

Example:
Get Sales employees earning more than 60,000:

SELECT name FROM employees
WHERE department = 'Sales' AND salary > 60000;

4️⃣ NOT – Exclude specific values or conditions
Syntax:

SELECT columns FROM table_name WHERE NOT condition;

Example:
Get all products except Electronics:

SELECT * FROM products
WHERE NOT category = 'Electronics';

5️⃣ BETWEEN – Match a range of values (inclusive)
Syntax:

SELECT columns FROM table_name WHERE column BETWEEN value1 AND value2;

Example:
Get employees with salary between 50,000 and 100,000:

SELECT name, salary FROM employees
WHERE salary BETWEEN 50000 AND 100000;

🔥 Quick Practice Task:
Write a query to:
• Get all employees in 'IT' or 'HR'
• Who earn more than 50,000
• Show name, department, and salary:

SELECT name, department, salary
FROM employees
WHERE department IN ('IT', 'HR')
AND salary > 50000;

✅ SQL Filtering Interview QA 💼🧠

Q1. What’s the difference between AND and OR?
A:
• AND requires all conditions to be true
• OR requires at least one condition to be true

Q2. Can you combine AND and OR in one query?
A: Yes, but use parentheses to control logic:

SELECT * FROM employees
WHERE (department = 'Sales' OR department = 'Marketing')
AND salary > 60000;

Q3. What does NOT IN do?
A: Excludes rows with values in the list:

SELECT * FROM customers
WHERE city NOT IN ('Delhi', 'Mumbai');

Q4. Can BETWEEN be used with dates?
A: Absolutely!

SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';

Q5. What’s the difference between IN and multiple ORs?
A: IN is cleaner and more concise:
-- Instead of:

WHERE city = 'A' OR city = 'B' OR city = 'C';

-- Use:

WHERE city IN ('A', 'B', 'C');

💡 Pro Tip:
When combining multiple filters, always use parentheses to avoid unexpected results due to operator precedence.

SQL Roadmap

DOUBLE TAP ❤️ FOR MORE

𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 𝗕𝘆 𝗜𝗻𝗱𝘂𝘀𝘁𝗿𝘆 𝗘𝘅𝗽𝗲𝗿𝘁𝘀 😍

Roadmap to land your dream job in top product-based companies

𝗛𝗶𝗴𝗵𝗹𝗶𝗴𝗵𝘁𝗲𝘀:-
- 90-Day Placement Plan
- Tech & Non-Tech Career Path
- Interview Preparation Tips
- Live Q&A

𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/3Ltb3CE

Date & Time:- 06th January 2026 , 7PM

✅ SQL Functions 📊🧠

SQL functions are built-in operations used to manipulate, calculate, and transform data. They help in summarizing results, formatting values, and applying logic in queries.

1️⃣ Aggregate Functions
These return a single result from a group of rows.

• COUNT() – Counts rows
SELECT COUNT(*) FROM employees;

• SUM() – Adds values
SELECT SUM(salary) FROM employees WHERE department = 'IT';

• AVG() – Returns average
SELECT AVG(age) FROM customers;

• MAX() / MIN() – Highest or lowest value
SELECT MAX(salary), MIN(salary) FROM employees;

2️⃣ String Functions

• UPPER() / LOWER() – Change case
SELECT UPPER(name), LOWER(city) FROM customers;

• CONCAT() – Join strings
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

• SUBSTRING() – Extract part of a string
SELECT SUBSTRING(name, 1, 3) FROM products;

• LENGTH() – Length of string
SELECT LENGTH(denoscription) FROM products;

3️⃣ Date Functions

• CURRENT_DATE / NOW() – Current date/time
SELECT CURRENT_DATE, NOW();

• DATE_ADD() / DATE_SUB() – Add or subtract days
SELECT DATE_ADD(hire_date, INTERVAL 30 DAY) FROM employees;

• DATEDIFF() – Difference between dates
SELECT DATEDIFF(end_date, start_date) FROM projects;

• YEAR() / MONTH() / DAY() – Extract parts
SELECT YEAR(order_date), MONTH(order_date) FROM orders;

4️⃣ Mathematical Functions

• ROUND() – Round decimals
SELECT ROUND(price, 2) FROM products;

• CEIL() / FLOOR() – Round up/down
SELECT CEIL(4.2), FLOOR(4.8);

• ABS() – Absolute value
SELECT ABS(balance) FROM accounts;

5️⃣ Conditional Function

• COALESCE() – Returns first non-null value
SELECT COALESCE(phone, 'Not Provided') FROM customers;

• CASE – If/else logic in SQL

  SELECT name,  
    CASE  
      WHEN salary > 50000 THEN 'High'  
      WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'  
      ELSE 'Low'  
    END AS salary_band  
  FROM employees;
  

🎯 Use These Functions To:
• Summarize data
• Clean and format strings
• Handle nulls
• Calculate time differences
• Add logic into queries

💬 Tap ❤️ for more!

✅ SQL GROUP BY HAVING 📊

What is GROUP BY?
GROUP BY is used to group rows that have the same values in one or more columns. It’s mostly used with aggregate functions like SUM(), COUNT(), AVG() to get summarized results.

What is HAVING?
HAVING is like WHERE, but it works after grouping. It filters the grouped results. You can’t use aggregate functions in WHERE, so we use HAVING instead.

📌 Problem 1:
You want to find total sales made in each city.

SELECT city, SUM(sales) AS total_sales  
FROM customers  
GROUP BY city;

✅ This groups the sales by city and shows total per group.

📌 Problem 2:
Now, show only those cities where total sales are above ₹50,000.

SELECT city, SUM(sales) AS total_sales  
FROM customers  
GROUP BY city  
HAVING total_sales > 50000;

✅ `HAVING filters the result after grouping.

📌 Problem 3:
Find departments with more than 10 active employees.

SELECT department, COUNT(*) AS emp_count  
FROM employees  
WHERE active = 1  
GROUP BY department  
HAVING emp_count > 10;

✅ First, we filter rows using WHERE. Then group, then filter groups with HAVING.

💡 Use GROUP BY to summarize, HAVING to filter those summaries.

Double Tap ♥️ For More

✅ SQL JOINS 🔗📘

JOINS let you combine data from two or more tables based on related columns.

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

Problem: Get customers with their orders.

SELECT c.name, o.order_id  
FROM customers c  
INNER JOIN orders o ON c.id = o.customer_id;

✅ Only shows customers who have orders.

2️⃣ LEFT JOIN (or LEFT OUTER JOIN)
Returns all rows from the left table + matching rows from the right table. If no match, fills with NULL.

Problem: Show all customers, even if they didn’t order.

SELECT c.name, o.order_id  
FROM customers c  
LEFT JOIN orders o ON c.id = o.customer_id;

✅ Includes customers without orders.

3️⃣ RIGHT JOIN
Opposite of LEFT JOIN: keeps all rows from the right table.

4️⃣ FULL OUTER JOIN
Returns all rows from both tables. Where there’s no match, it shows NULL.

SELECT c.name, o.order_id  
FROM customers c  
FULL OUTER JOIN orders o ON c.id = o.customer_id;

✅ Includes customers with or without orders and orders with or without customers.

5️⃣ SELF JOIN
Table joins with itself.

Problem: Show employees and their managers.

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

✅ Links each employee to their manager using a self join.

💡 Quick Summary:
• INNER JOIN → Only matches
• LEFT JOIN → All from left + matches
• RIGHT JOIN → All from right + matches
• FULL OUTER JOIN → Everything
• SELF JOIN → Table joins itself

💬 Tap ❤️ for more!

✅ SQL Subqueries & Nested Queries 🧠🔍

Subqueries help you write powerful queries inside other queries. They're useful when you need intermediate results.

1️⃣ What is a Subquery?
A subquery is a query inside () that runs first and passes its result to the outer query.

Example: Get employees who earn above average salary.

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

✅ Subquery calculates average salary → main query finds those above it.

2️⃣ Subquery in SELECT Clause
You can use subqueries to return values in each row.

Example: Show employee names with department name.

SELECT name,  
  (SELECT dept_name FROM departments d WHERE d.id = e.dept_id) AS department  
FROM employees e;

3️⃣ Subquery in FROM Clause
Use when you want to filter or group temporary results.

Example: Get department-wise highest salary.

SELECT dept_id, MAX(salary)  
FROM (SELECT * FROM employees WHERE active = 1) AS active_emps  
GROUP BY dept_id;

4️⃣ Correlated Subquery
A subquery that uses a value from the outer query row.

Example: Get employees with highest salary in their department.

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

✅ Subquery runs for each row using outer query value.

💡 Real Use Cases:
• Filter rows based on dynamic conditions
• Compare values across groups
• Fetch related info in SELECT

🎯 Practice Tasks:
• Write a query to find 2nd highest salary
• Use subquery to get customers who placed more than 3 orders
• Create a nested query to show top-selling product per category


✅ Solution for Practice Tasks 👇

1️⃣ Find 2nd Highest Salary

SELECT MAX(salary) AS second_highest_salary  
FROM employees  
WHERE salary < (SELECT MAX(salary) FROM employees);

▶️ Finds the highest salary less than the max salary → gives the 2nd highest.


2️⃣ Customers Who Placed More Than 3 Orders

SELECT customer_id  
FROM orders  
GROUP BY customer_id  
HAVING COUNT(order_id) > 3;

▶️ Groups orders by customer and filters those with more than 3.

You can join to get customer names:

SELECT name  
FROM customers  
WHERE id IN (
  SELECT customer_id  
  FROM orders  
  GROUP BY customer_id  
  HAVING COUNT(order_id) > 3
);

3️⃣ Top-Selling Product Per Category

SELECT p.name, p.category_id, p.sales  
FROM products p  
WHERE p.sales = (
  SELECT MAX(sales)  
  FROM products  
  WHERE category_id = p.category_id
);

▶️ Correlated subquery finds the highest sales within each category.

💬 Tap ❤️ for more!