✅ Interviewer: Count the number of employees in each department. 📊

Me: Using GROUP BY and COUNT():

SELECT d.dept_name,
       COUNT(e.id) AS employee_count
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

✔ Why it works: 
– COUNT() tallies employees per department by counting non-null IDs. 
– GROUP BY segments the results by department name for aggregated output. 
– INNER JOIN links employees to departments only where IDs match, avoiding nulls—add ORDER BY employee_count DESC to sort by largest teams first!

💬 Tap ❤️ if you're learning something new!

✅10 Most Useful SQL Interview Queries (with Examples) 💼

1️⃣ Find the second highest salary:

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

2️⃣ Count employees in each department:

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

3️⃣ Fetch duplicate emails:

SELECT email, COUNT(*)  
FROM users  
GROUP BY email  
HAVING COUNT(*) > 1;

4️⃣ Join orders with customer names:

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

5️⃣ Get top 3 highest salaries:

SELECT DISTINCT salary  
FROM employees  
ORDER BY salary DESC  
LIMIT 3;

6️⃣ Retrieve latest 5 logins:

SELECT * FROM logins  
ORDER BY login_time DESC  
LIMIT 5;

7️⃣ Employees with no manager:

SELECT name  
FROM employees  
WHERE manager_id IS NULL;

8️⃣ Search names starting with ‘S’:

SELECT * FROM employees  
WHERE name LIKE 'S%';

9️⃣ Total sales per month:

SELECT MONTH(order_date) AS month, SUM(amount)  
FROM sales  
GROUP BY MONTH(order_date);

🔟 Delete inactive users:

DELETE FROM users  
WHERE last_active < '2023-01-01';

✅ Tip: Master subqueries, joins, groupings & filters – they show up in nearly every interview!

💬 Tap ❤️ for more!

The program for the 10th AI Journey 2025 international conference has been unveiled: scientists, visionaries, and global AI practitioners will come together on one stage. Here, you will hear the voices of those who don't just believe in the future—they are creating it!

Speakers include visionaries Kai-Fu Lee and Chen Qufan, as well as dozens of global AI gurus from around the world!

On the first day of the conference, November 19, we will talk about how AI is already being used in various areas of life, helping to unlock human potential for the future and changing creative industries, and what impact it has on humans and on a sustainable future.

On November 20, we will focus on the role of AI in business and economic development and present technologies that will help businesses and developers be more effective by unlocking human potential.

On November 21, we will talk about how engineers and scientists are making scientific and technological breakthroughs and creating the future today!

Ride the wave with AI into the future!

Tune in to the AI Journey webcast on November 19-21.

✅ Advanced SQL Queries 🗄️💡

1️⃣ GROUP BY & HAVING
⦁ GROUP BY groups rows sharing a value to perform aggregate calculations.
⦁ HAVING filters groups based on conditions (like WHERE but for groups).

Example:
Find total sales per product with sales > 1000:
SELECT product_id, SUM(sales) AS total_sales
FROM sales_data
GROUP BY product_id
HAVING SUM(sales) > 1000;

2️⃣ Subqueries
⦁ A query inside another query. Useful for filtering or calculating values dynamically.

Example:
Get customers who placed orders over 500:
SELECT customer_id, order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

3️⃣ Aggregate Functions
⦁ Perform calculations on sets of rows:
⦁ COUNT() counts rows
⦁ SUM() adds numeric values
⦁ AVG() calculates average
⦁ MAX() and MIN() find extremes

Example:
Find average order amount per customer:
SELECT customer_id, AVG(amount) AS avg_order
FROM orders
GROUP BY customer_id;

4️⃣ Complex Joins with Filtering
⦁ Join tables and filter results in one query.

Example:
List customers with orders over100:
SELECT c.customer_name, o.order_id, o.amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100;

📌 SQL Roadmap: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1506

💬 Double Tap ❤️ For More!

Data Analytics isn't rocket science. It's just a different language.

Here's a beginner's guide to the world of data analytics:

1) Understand the fundamentals:
- Mathematics
- Statistics
- Technology

2) Learn the tools:
- SQL
- Python
- Excel (yes, it's still relevant!)

3) Understand the data:
- What do you want to measure?
- How are you measuring it?
- What metrics are important to you?

4) Data Visualization:
- A picture is worth a thousand words

5) Practice:
- There's no better way to learn than to do it yourself.

Data Analytics is a valuable skill that can help you make better decisions, understand your audience better, and ultimately grow your business.

It's never too late to start learning!

Tune in to the 10th AI Journey 2025 international conference: scientists, visionaries, and global AI practitioners will come together on one stage. Here, you will hear the voices of those who don't just believe in the future—they are creating it!

Speakers include visionaries Kai-Fu Lee and Chen Qufan, as well as dozens of global AI gurus! Do you agree with their predictions about AI?

On the first day of the conference, November 19, we will talk about how AI is already being used in various areas of life, helping to unlock human potential for the future and changing creative industries, and what impact it has on humans and on a sustainable future.

On November 20, we will focus on the role of AI in business and economic development and present technologies that will help businesses and developers be more effective by unlocking human potential.

On November 21, we will talk about how engineers and scientists are making scientific and technological breakthroughs and creating the future today! The day's program includes presentations by scientists from around the world:
- Ajit Abraham (Sai University, India) will present on “Generative AI in Healthcare”
- Nebojša Bačanin Džakula (Singidunum University, Serbia) will talk about the latest advances in bio-inspired metaheuristics
- AIexandre Ferreira Ramos (University of São Paulo, Brazil) will present his work on using thermodynamic models to study the regulatory logic of trannoscriptional control at the DNA level
- Anderson Rocha (University of Campinas, Brazil) will give a presentation ennoscriptd “AI in the New Era: From Basics to Trends, Opportunities, and Global Cooperation”.

And in the special AIJ Junior track, we will talk about how AI helps us learn, create and ride the wave with AI.

The day will conclude with an award ceremony for the winners of the AI Challenge for aspiring data scientists and the AIJ Contest for experienced AI specialists. The results of an open selection of AIJ Science research papers will be announced.

Ride the wave with AI into the future!

Tune in to the AI Journey webcast on November 19-21.

✅ SQL Practice Questions with Answers 🧠🗃️

🔍 Q1. How to find the 2nd highest salary from a table?
✅ Answer:

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

🔍 Q2. How to find duplicate values in a column?
✅ Answer:

SELECT name, COUNT(*) FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

🔍 Q3. How to select records that exist in one table but not in another?
✅ Answer:

SELECT * FROM employees
WHERE id NOT IN (SELECT employee_id FROM payroll);

🔍 Q4. How to get the top 3 highest salaries? (MySQL)
✅ Answer:

SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 3;

🔍 Q5. How to fetch employees with the same salary?
✅ Answer:

SELECT * FROM employees e1
WHERE EXISTS (
  SELECT 1 FROM employees e2
  WHERE e1.salary = e2.salary AND e1.id <> e2.id
);

🔍 Q6. How to get the department-wise highest salary?
✅ Answer:

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department;

💬 Tap ❤️ for more!

✅ SQL Practice Questions with Answers: Part-2 🧠🗃️

🔍 Q7. Find employees who never received a bonus
👤 Table: employees
id | name
1 | Arjun
2 | Riya
3 | Meena

💰 Table: bonus
employee_id | bonus_amount
1 | 3000
3 | 5000

📄 Query:

SELECT e.id, e.name  
FROM employees e  
LEFT JOIN bonus b ON e.id = b.employee_id  
WHERE b.employee_id IS NULL;

📌 Result: Riya

🔍 Q8. Get highest salary employee from each department
🧾 Table: employees
id | name | dept | salary
1 | Arjun | HR | 40000
2 | Riya | IT | 55000
3 | Meena | IT | 62000
4 | Kabir | HR | 45000

📄 Query:

SELECT e.*  
FROM employees e  
JOIN (  
  SELECT department, MAX(salary) AS max_salary  
  FROM employees  
  GROUP BY department  
) t  
ON e.department = t.department  
AND e.salary = t.max_salary;

📌 Result: Kabir (HR), Meena (IT)

🔍 Q9. Count number of employees who joined each year
🗓 Table: employees
id | name | join_date
1 | Arjun | 2021-03-10
2 | Riya | 2022-05-12
3 | Meena | 2021-11-03
4 | Kabir | 2023-01-09

📄 Query:

SELECT YEAR(join_date) AS join_year, COUNT(*) AS total  
FROM employees  
GROUP BY YEAR(join_date)  
ORDER BY join_year;

📌 Result:
2021 → 2
2022 → 1
2023 → 1

🔍 Q10. Find employees earning more than department average
🧾 Table: employees
id | name | dept | salary
1 | Arjun | HR | 40000
2 | Riya | IT | 55000
3 | Meena | IT | 62000
4 | Kabir | HR | 45000

📄 Query:

SELECT e.*  
FROM employees e  
JOIN (  
  SELECT department, AVG(salary) AS avg_salary  
  FROM employees  
  GROUP BY department  
) t  
ON e.department = t.department  
WHERE e.salary > t.avg_salary;

📌 Result: Kabir (HR), Meena (IT)

🔍 Q11. Fetch the 5th highest salary from employee table
🧾 Table: employees
Salaries: 90000, 85000, 78000, 76000, 72000, 70000

📄 Query:

SELECT DISTINCT salary  
FROM employees  
ORDER BY salary DESC  
LIMIT 1 OFFSET 4;

📌 Result: 72000

🔍 Q12. Find employees working on more than one project
📂 Table: project_assignments
employee_id | project_id
1 | 101
1 | 102
2 | 103
3 | 104
3 | 105
3 | 106

📄 Query:

SELECT employee_id, COUNT(*) AS project_count  
FROM project_assignments  
GROUP BY employee_id  
HAVING COUNT(*) > 1;

📌 Result:
1 → 2 projects
3 → 3 projects

💬 Tap ❤️ for more!

✅ SQL Practice Questions with Answers: Part-3 🧠🗃️

🔍 Q13. Find employees whose salary is above the company average
🧾 Table: employees
id | name | salary
1 | Arjun | 40000
2 | Riya | 55000
3 | Meena | 62000
4 | Kabir | 45000

📄 Query:

SELECT *  
FROM employees  
WHERE salary > (SELECT AVG(salary) FROM employees);

📌 Result: Riya, Meena

🔍 Q14. Get the 3 most recent joined employees
🗓 Table: employees
id | name | join_date
1 | Arjun | 2021-03-10
2 | Riya | 2022-05-12
3 | Meena | 2023-02-01
4 | Kabir | 2023-11-09

📄 Query:

SELECT *  
FROM employees  
ORDER BY join_date DESC  
LIMIT 3;

📌 Result: Kabir, Meena, Riya

🔍 Q15. Retrieve employees who don’t have a manager assigned
🧾 Table: employees
id | name | manager_id
1 | Arjun | NULL
2 | Riya | 1
3 | Meena | NULL
4 | Kabir | 2

📄 Query:

SELECT id, name  
FROM employees  
WHERE manager_id IS NULL;

📌 Result: Arjun, Meena

🔍 Q16. Find departments where more than 2 employees work
🧾 Table: employees
id | name | department
1 | Arjun | HR
2 | Riya | IT
3 | Meena | IT
4 | Kabir | HR
5 | John | IT

📄 Query:

SELECT department, COUNT(*) AS total  
FROM employees  
GROUP BY department  
HAVING COUNT(*) > 2;

📌 Result: IT → 3 employees

🔍 Q17. Select employees whose salary equals department average
🧾 Table: employees
id | name | dept | salary
1 | Arjun | HR | 40000
2 | Riya | IT | 55000
3 | Meena | IT | 62000
4 | Kabir | HR | 45000

📄 Query:

SELECT e.*  
FROM employees e  
JOIN (  
  SELECT dept, AVG(salary) AS avg_salary  
  FROM employees  
  GROUP BY dept  
) t ON e.dept = t.dept  
WHERE e.salary = t.avg_salary;

📌 Result: None (but logic works if matches exist)

🔍 Q18. Get employees who have at least one matching project
📂 Table: project_assignments
employee_id | project_id
1 | 101
2 | 101
2 | 102
3 | 103
4 | 101

📄 Query:

SELECT p1.employee_id, p2.employee_id AS colleague  
FROM project_assignments p1  
JOIN project_assignments p2  
ON p1.project_id = p2.project_id  
AND p1.employee_id <> p2.employee_id;

📌 Result:
Employees 1, 2, 4 share project 101

💬 Tap ❤️ for more!

✅ Free Resources to Learn SQL in 2025 🧠📚

1. YouTube Channels
• freeCodeCamp – Comprehensive SQL courses
• Simplilearn – SQL basics and advanced topics
• CodeWithMosh – SQL tutorial for beginners
• Alex The Analyst – Practical SQL for data analysis

2. Websites
• W3Schools SQL Tutorial – Easy-to-understand basics
• SQLZoo – Interactive SQL tutorials with exercises
• GeeksforGeeks SQL – Concepts, interview questions, and examples
• LearnSQL – Free courses and interactive editor

3. Practice Platforms
• LeetCode (SQL section) – Interview-style SQL problems
• HackerRank (SQL section) – Challenges and practice problems
• StrataScratch – Real-world SQL questions from companies
• SQL Fiddle – Online SQL sandbox for testing queries

4. Free Courses
• Khan Academy: Intro to SQL – Basic database concepts and SQL
• Codecademy: Learn SQL (Basic) – Interactive lessons
• Great Learning: SQL for Beginners – Free certification course
• Udemy (search for free courses) – Many introductory SQL courses often available for free

5. Books for Starters
• “SQL in 10 Minutes, Sams Teach Yourself” – Ben Forta
• “SQL Practice Problems: 57 Problems to Test Your SQL Skills” – Sylvia Moestl Wasserman
• “Learning SQL” – Alan Beaulieu

6. Must-Know Concepts
• SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
• JOINs (INNER, LEFT, RIGHT, FULL)
• Subqueries, CTEs (Common Table Expressions)
• Window Functions (RANK, ROW_NUMBER, LEAD, LAG)
• Basic DDL (CREATE TABLE) and DML (INSERT, UPDATE, DELETE)

💡 Practice consistently with real-world scenarios.

💬 Tap ❤️ for more!

✅ Top 5 Mistakes to Avoid When Learning SQL ❌📄

1️⃣ Ignoring Data Basics
Don't skip understanding tables, rows, primary keys, and relationships. These are the foundation of SQL.

2️⃣ Memorizing Queries Without Practice
Reading syntax isn't enough. Write real queries on sample databases to retain concepts.

3️⃣ Not Using Joins Early On
Many avoid JOINs thinking they're hard. Practice INNER, LEFT, and RIGHT JOINs with real examples to understand table relationships.

4️⃣ Skipping GROUP BY and Aggregates
GROUP BY with COUNT, SUM, AVG, etc., is core to analytics. Learn it early and use it often.

5️⃣ Not Practicing Real-World Scenarios
Writing SELECT * from a table isn't enough. Use projects like sales reports, user activity tracking, or inventory queries.

💬 Tap ❤️ for more!

📊 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: How do you use CASE in SQL?

👋 𝗠𝗲: Use CASE to add conditional logic inside SELECT, WHERE, or ORDER BY.

Example:

SELECT name,
       salary,
       CASE 
         WHEN salary >= 80000 THEN 'High'
         WHEN salary >= 50000 THEN 'Medium'
         ELSE 'Low'
       END AS salary_level
FROM employees;

🧠 Logic Breakdown:
- Works like if-else
- Evaluates conditions top to bottom
- Returns the first match
- ELSE is optional (defaults to NULL)

✅ Use Case:
- Create custom categories
- Replace values based on logic
- Conditional ordering or filtering

💬 Tap ❤️ for more!

✅ SQL Skills Every Beginner Should Learn 📊💻

1️⃣ Understanding the Basics
⦁ What is a database and table
⦁ Rows, columns, primary keys, foreign keys
⦁ Relational database concepts

2️⃣ Core SQL Queries
⦁ SELECT, FROM, WHERE – Get filtered data
⦁ ORDER BY, LIMIT – Sort and control output
⦁ DISTINCT, BETWEEN, IN, LIKE – Filter smarter

3️⃣ Joins (Combine Tables)
⦁ INNER JOIN – Matching records in both tables
⦁ LEFT JOIN, RIGHT JOIN – Include unmatched from one side
⦁ FULL OUTER JOIN – All records, matched or not

4️⃣ Aggregations
⦁ COUNT(), SUM(), AVG(), MIN(), MAX()
⦁ GROUP BY to summarize data
⦁ HAVING to filter aggregated results

5️⃣ Subqueries & CTEs
⦁ Subquery inside WHERE or SELECT
⦁ WITH clause for clean and reusable code

6️⃣ Window Functions
⦁ ROW_NUMBER(), RANK(), DENSE_RANK()
⦁ PARTITION BY, ORDER BY inside OVER()

7️⃣ Data Cleaning & Logic
⦁ Handle NULL values
⦁ Use CASE WHEN for conditional columns
⦁ Remove duplicates using DISTINCT or ROW_NUMBER()

8️⃣ Practice & Projects
⦁ Sales reports, user activity, inventory tracking
⦁ Work on public datasets
⦁ Solve SQL questions on LeetCode or HackerRank

Double Tap ♥️ For More

✅ 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