SQL Interview Series — Part 5

📌 Question 5: Find Employees Who Earn More Than Their Manager

Suppose you have an Employee table:

Employee

• employee_id

• employee_name

• manager_id

• salary

Example:

employee_id | employee_name | manager_id | salary
------------|---------------|------------|-------
1           | Amit          | NULL       | 100000
2           | Rahul         | 1          | 120000
3           | Priya         | 1          | 90000
4           | Neha          | 2          | 130000
5           | Raj           | 2          | 110000

❓ Find employees whose salary is greater than their manager's salary.

━━━━━━━━━━━━━━━━━━

💡 Approach

The Employee table contains both employees and managers.

So, we need to join the table with itself.

This is called a Self Join.

📌 SQL Solution

SELECT
    e.employee_name AS employee,
    e.salary AS employee_salary,
    m.employee_name AS manager,
    m.salary AS manager_salary
FROM Employee e
JOIN Employee m
    ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

━━━━━━━━━━━━━━━━━━

🔎 How does it work?

We use the Employee table twice:

"e" → represents the employee

"m" → represents the manager

The join condition:

e.manager_id = m.employee_id

connects each employee with their manager.

Then:

WHERE e.salary > m.salary

keeps only employees whose salary is higher than their manager's.

Expected result:

employee | employee_salary | manager | manager_salary
---------|-----------------|---------|---------------
Rahul    | 120000          | Amit    | 100000
Neha     | 130000          | Rahul   | 120000

━━━━━━━━━━━━━━━━━━

🎯 Interview Tip

Whenever the same table contains a relationship such as:

• Employee → Manager

• Employee → Mentor

• Employee → Supervisor

• Employee → Parent

think about using a Self Join.

A very common interview pattern is:

FROM Employee e
JOIN Employee m
    ON e.manager_id = m.employee_id

The key is to understand what each table alias represents before writing the query.

Double Tap ❤️ For More

🚀 𝗪𝗵𝗶𝗰𝗵 𝗧𝗲𝗰𝗵 𝗦𝗸𝗶𝗹𝗹 𝗪𝗶𝗹𝗹 𝗬𝗼𝘂 𝗠𝗮𝘀𝘁𝗲𝗿 𝗶𝗻 𝟮𝟬𝟮𝟲?

Want to build apps, uncover insights, or create AI solutions? Explore certification courses that match your career goals! 🎯

💻 𝗙𝘂𝗹𝗹 𝗦𝘁𝗮𝗰𝗸 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁
👉 https://pdlink.in/3SuUeuD

📊 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀
👉 https://pdlink.in/45vk5ph

💫𝗔𝗜 𝗘𝗻𝗴𝗶𝗻𝗲𝗲𝗿𝗶𝗻𝗴
👉 https://pdlink.in/4fWJVID

🔥 Choose your path, sharpen your skills, and take the next step towards your tech career!

📌 Save this post & share it with a friend planning to upskill.

SQL Interview Series — Part 6

📌 Question 6: Find the Top 2 Highest-Paid Employees in Each Department

Suppose you have:

Employee

employee_id employee_name department salary

1 Amit IT 60000

2 Rahul IT 90000

3 Priya IT 80000

4 Neha HR 70000

5 Raj HR 50000

6 Karan HR 90000

❓ Find the top 2 highest-paid employees from each department.

💡 Approach

This is a classic window

-function interview question.

We need to:

1️⃣ Divide employees by department.

2️⃣ Rank employees by salary within each department.

3️⃣ Keep only employees with rank ≤ 2.

SQL Solution

SELECT
    employee_id,
    employee_name,
    department,
    salary
FROM (
    SELECT
        employee_id,
        employee_name,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rn
    FROM Employee
) e
WHERE rn <= 2;

🔎 How does it work?

PARTITION BY department

Creates a separate ranking for each department.

ORDER BY salary DESC

Places the highest salary first.

ROW_NUMBER()

Assigns 1, 2, 3... within each department.

Finally:

WHERE rn <= 2

keeps the top two employees from each department.

Expected Result

employee_name department salary

Rahul IT 90000

Priya IT 80000

Karan HR 90000

Neha HR 70000

⚠️ Interview Trap

What if two employees have the same salary?

For example:

Rahul 90000

Priya 90000

Amit 80000

ROW_NUMBER() will still assign unique numbers:

Rahul 1

Priya 2

Amit 3

Double Tap ❤️ For More

𝗧𝗢𝗣 𝗙𝗥𝗘𝗘 𝗖𝗢𝗨𝗥𝗦𝗘𝗦 𝗧𝗢 𝗕𝗢𝗢𝗦𝗧 𝗬𝗢𝗨𝗥 𝗦𝗞𝗜𝗟𝗟𝗦 𝗜𝗡 𝟮𝟬𝟮𝟲!

Explore FREE online learning resources to develop in-demand skills and improve your career opportunities!

🎓 Why Explore These Courses?
✅ Learn valuable professional skills
✅ Improve your resume
✅ Build practical knowledge
✅ Learn at your own pace

𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗛𝗲𝗿𝗲👇

https://pdlink.in/3VBdt6V

💡 Learn New Skills → Build Confidence → Unlock Career Opportunities!

SQL Interview Series — Part 8

📌 Question 8: Find Customers Who Have Never Placed an Order

Suppose you have two tables:

Customers

customer_id | customer_name
------------|--------------
1           | Amit
2           | Rahul
3           | Priya
4           | Neha
5           | Raj

Orders

order_id | customer_id
---------|------------
101      | 1
102      | 3
103      | 1
104      | 5

❓ Find all customers who have never placed an order.

💡 Approach

We need to find customers from the "Customers" table who do not have a matching record in the "Orders" table.

A "LEFT JOIN" is a common and interview-friendly approach.

📌 SQL Solution

SELECT
    c.customer_id,
    c.customer_name
FROM Customers c
LEFT JOIN Orders o
    ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

🔎 How does it work?

"LEFT JOIN" keeps every customer, even if they don't have an order.

For customers without an order, the columns from "Orders" become "NULL".

Then:

WHERE o.customer_id IS NULL

keeps only those customers.

📌 Expected Result:

customer_id | customer_name
------------|--------------
2           | Rahul
4           | Neha

⚠️ Interview Trap

You can also solve this using "NOT EXISTS":

SELECT
    c.customer_id,
    c.customer_name
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

"NOT EXISTS" is often preferred when you specifically want to test whether a matching row exists.

Be careful with "NOT IN" when NULL values can exist in the subquery, because NULL can lead to unexpected results.

🎯 Double Tap ❤️ For More

SQL Interview Series — Part 11

📌 Question 11: Calculate a Running Total of Sales

Suppose you have a Sales table:

sale_id | sale_date  | amount
--------|------------|-------
1       | 2026-01-01 | 1000
2       | 2026-01-02 | 1500
3       | 2026-01-03 | 800
4       | 2026-01-04 | 1200

❓ Calculate the cumulative (running) sales amount for each day.

💡 Approach

A running total means:

Current day's sales + all previous sales

For example:

1000

1000 + 1500 = 2500

2500 + 800 = 3300

3300 + 1200 = 4500

The easiest approach is to use the "SUM()" window function.

📌 SQL Solution

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM Sales
ORDER BY sale_date;

🔎 How does it work?

1️⃣ "ORDER BY sale_date"

Determines the order in which the running total is calculated.

2️⃣ "SUM(amount) OVER(...)"

Calculates the sum without collapsing the rows like a normal "GROUP BY".

3️⃣ "UNBOUNDED PRECEDING"

Means:

Start from the first row.

4️⃣ "CURRENT ROW"

Means:

Include the current row in the total.

So the calculation becomes:

Date        Amount    Running Total
----------  --------  -------------
Jan 01       1000        1000
Jan 02       1500        2500
Jan 03        800        3300
Jan 04       1200        4500

⚠️ Interview Trap

Don't use:

GROUP BY sale_date

for a running total.

"GROUP BY" collapses rows into groups.

A window function allows us to calculate an aggregate while keeping individual rows.

Also, if multiple rows can have the same "sale_date", be careful about the ordering. A unique column such as "sale_id" may need to be included:

SUM(amount) OVER (
    ORDER BY sale_date, sale_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

🎯 Double Tap ❤️ For More

𝗗𝗮𝘁𝗮 𝘀𝗰𝗶𝗲𝗻𝗰𝗲 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗪𝗶𝘁𝗵 𝗔𝗜😍

Future Ready Data Science and AI Skills You Build

->Build Python, Machine Learning, and AI Skills
->Get Mentored by Industry Experts
->Practice with Mock Interviews
->Ace Your Placements & Land Dream Tech Job

🤝 500+ Partner Companies |🎓 2000+ Students Placed

🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄 👇 :-

https://pdlink.in/47pezFz

📲 Save & Share with Friends Preparing for Data Science & AI Jobs!

𝗜𝗕𝗠 𝗙𝗥𝗘𝗘 𝗧𝗘𝗖𝗛 𝗖𝗢𝗨𝗥𝗦𝗘𝗦 𝟮𝟬𝟮𝟲! 🎓

Explore IBM Cognitive Class and build in-demand tech skills without spending money on course fees!

📚 𝗧𝗼𝗽 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘁𝗼 𝗘𝘅𝗽𝗹𝗼𝗿𝗲:-

💫 Artificial Intelligence & Machine Learning
🔥Python for Data Science
📊 Data Science Fundamentals
☁️ Cloud Computing & Big Data

🔗 𝗦𝘁𝗮𝗿𝘁 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗛𝗲𝗿𝗲 👇

https://pdlink.in/4zGswuX

📲 Save & Share with friends preparing for tech careers! 🔥

SQL Interview Series — Part 12

📌 Question 12: Find the Department with the Highest Average Salary

Suppose you have an Employee table:

employee_id | employee_name | department | salary
------------|---------------|------------|-------
1           | Amit          | IT         | 60000
2           | Rahul         | IT         | 80000
3           | Priya         | HR         | 70000
4           | Neha          | HR         | 90000
5           | Raj           | Finance    | 85000
6           | Karan         | Finance    | 95000

❓ Find the department that has the highest average salary.

💡 Approach

First, calculate the average salary for every department.

Then, find the department with the highest average.

Department averages:

IT → (60000 + 80000) / 2 = 70000

HR → (70000 + 90000) / 2 = 80000

Finance → (85000 + 95000) / 2 = 90000

Therefore:

Finance → 90000

📌 SQL Solution

SELECT
    department,
    AVG(salary) AS avg_salary
FROM Employee
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;

Expected result:

department | avg_salary
-----------|-----------
Finance    | 90000

🔎 How does it work?

1️⃣ "GROUP BY department"

Creates one group for each department.

2️⃣ "AVG(salary)"

Calculates the average salary within each department.

3️⃣ "ORDER BY avg_salary DESC"

Places the department with the highest average first.

4️⃣ "LIMIT 1"

Returns only the top department.

⚠️ Interview Trap

What if two departments have the same highest average salary?

For example:

IT → 80000

HR → 80000

Finance → 75000

"LIMIT 1" would return only one department.

If the interviewer asks:

👉 "Find all departments having the highest average salary"

then use a ranking approach:

SELECT department, avg_salary
FROM (
    SELECT
        department,
        AVG(salary) AS avg_salary,
        DENSE_RANK() OVER (
            ORDER BY AVG(salary) DESC
        ) AS rnk
    FROM Employee
    GROUP BY department
) d
WHERE rnk = 1;

Now both IT and HR would be returned.

🎯 Double Tap ❤️ For More

𝟰 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗧𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗖𝗼𝗱𝗶𝗻𝗴 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘😍

✅ 100% FREE YouTube Learning
✅ Beginner-Friendly Programming Tutorials
✅ Learn Popular & In-Demand Tech Skills
✅ Study at Your Own Pace
✅ Build a Strong Foundation for Coding Interviews

🔗 𝗔𝗰𝗰𝗲𝘀𝘀 𝗔𝗟𝗟 𝟰 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 👇:-

https://pdlink.in/3WcuMvi

⚡ Your coding journey can start today—completely FREE!

𝗧𝗔𝗧𝗔 𝗙𝗥𝗘𝗘 𝗠𝗜𝗖𝗥𝗢-𝗜𝗡𝗧𝗘𝗥𝗡𝗦𝗛𝗜𝗣𝗦 𝟮𝟬𝟮𝟲! 🎓

✅ 100% FREE Learning Experience
✅ Short Programs — Approximately 4–6 Hours
✅ Practical, Real-World Tasks
✅ Self-Paced & Online
✅ Certificate Opportunities for Your Resume & LinkedIn
✅ No Traditional Interview Process

🔗 𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝗧𝗔𝗧𝗔 𝗙𝗥𝗘𝗘 𝗠𝗶𝗰𝗿𝗼-𝗜𝗻𝘁𝗲𝗿𝗻𝘀𝗵𝗶𝗽𝘀👇:-

https://pdlink.in/4yWgaPn

⚡ Don't wait for your first job to start gaining practical experience!

SQL Interview Series — Part 13

📌 Question 13: Find Employees Earning More Than the Company Average

Suppose you have an Employee table:

employee_id | employee_name | department | salary
------------|---------------|------------|-------
1           | Amit          | IT         | 50000
2           | Rahul         | IT         | 80000
3           | Priya         | HR         | 60000
4           | Neha          | HR         | 90000
5           | Raj           | Finance    | 70000

❓ Find all employees whose salary is greater than the average salary of the entire company.

💡 Approach

First, calculate the average salary across all employees.

Then compare every employee's salary with that average.

Company average:

(50000 + 80000 + 60000 + 90000 + 70000) / 5

= 70000

Employees earning more than 70000:

Rahul → 80000

Neha → 90000

📌 SQL Solution

SELECT
    employee_id,
    employee_name,
    department,
    salary
FROM Employee
WHERE salary > (
    SELECT AVG(salary)
    FROM Employee
);

🔎 How does it work?

The subquery:

SELECT AVG(salary)
FROM Employee;

calculates the company-wide average:

70000

The outer query then checks:

WHERE salary > 70000

and returns only employees earning above that amount.

📌 Expected Result:

employee_name | department | salary
--------------|------------|-------
Rahul         | IT         | 80000
Neha          | HR         | 90000

Double Tap ❤️ For More

𝗠𝗜𝗖𝗥𝗢𝗦𝗢𝗙𝗧 𝗙𝗥𝗘𝗘 𝗗𝗔𝗧𝗔 𝗦𝗖𝗜𝗘𝗡𝗖𝗘 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦 𝟮𝟬𝟮𝟲! 🎓

Microsoft offers valuable FREE learning resources to help you develop in-demand technical skills! 💻

✅ 100% FREE Learning Resources
✅ Beginner-Friendly Tutorials
✅ Self-Paced Learning
✅ Practical Skills for Real-World Projects
✅ Useful for Data Analytics & Data Science Career Preparation

🔗 𝗔𝗰𝗰𝗲𝘀𝘀 𝗔𝗟𝗟 𝟰 𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 👇:-

https://pdlink.in/4k6ylNq

⚡ Your Data Science journey can start today — completely FREE!

𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 📊

Build a strong foundation in Data Science .Explore Machine Learning and Deep Learning

Eligibility :- Students • Freshers • Working Professionals

📅 Date:- 23 October 2026
⏰ Time:- 7:00 PM–9:00 PM IST
💻 Mode:- Online

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

https://pdlink.in/4i8MKGn

📢 Share this opportunity with your friends and classmates.

🚀𝗠𝗔𝗦𝗧𝗘𝗥 𝗘𝗫𝗖𝗘𝗟 𝗙𝗢𝗥 𝗙𝗥𝗘𝗘 𝗜𝗡 𝟮𝟬𝟮𝟲! 📊

Discover 4 FREE Websites to learn Microsoft Excel from beginner to advanced level! 🎓

🔥 FREE Learning Resources
🔥 Beginner to Advanced Tutorials
🔥 Practical Examples & Exercises
🔥 Learn Anytime, Anywhere

🔗 𝗟𝗶𝗻𝗸👇:-

https://pdlink.in/4jDfcCP

⚡ One powerful skill can open doors to multiple career opportunities!