SQL Interview Series — Part 2

📌 Question 2: Find Duplicate Records

Suppose you have an Employee table:

Employee

• employee_id

• employee_name

• department

• email

❓ Find all email addresses that appear more than once in the Employee table.

Example:

employee_id | employee_name | email
------------|---------------|-------------------
1           | Amit          | amit@gmail.com
2           | Rahul         | rahul@gmail.com
3           | Priya         | amit@gmail.com
4           | Neha          | neha@gmail.com
5           | Raj           | rahul@gmail.com

Expected result:

email

amit@gmail.com
rahul@gmail.com

💡 Approach

We need to:

1️⃣ Group records by email.

2️⃣ Count how many times each email appears.

3️⃣ Keep only the emails whose count is greater than 1. 

📌 SQL Solution

SELECT email, COUNT(*) AS occurrence_count
FROM Employee
GROUP BY email
HAVING COUNT(*) > 1;

🔎 Why use HAVING instead of WHERE?

"WHERE" filters individual rows before grouping.

"HAVING" filters groups after "GROUP BY".

Since we want to filter based on "COUNT(*)", we use "HAVING".

GROUP BY email
HAVING COUNT(*) > 1

This means:

"Group employees by email and return only those groups containing more than one record."

🎯 Double Tap ❤️ For Part-3

𝗠𝗮𝘀𝘁𝗲𝗿 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘! 🔥

Learn Power BI through these FREE learning resources
​
✨ What You'll Learn:
📊 Interactive Dashboards
📈 Data Visualization
🧹 Data Transformation
💼 Real-World Reporting Skills
🎯 Beginner-Friendly — No Coding Required

𝗦𝘁𝗮𝗿𝘁 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘
​
​https://pdlink.in/4hznwlu

💫Perfect for Students • Freshers • Data Analyst Aspirants • Working Professionals

SQL Interview Series — Part 4

📌 Question 4: Find the Highest Salary in Each Department

Suppose you have an Employee table:

Employee

• employee_id

• employee_name

• department

• salary

Example:

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

❓ Find the employee(s) with the highest salary in each department.

💡 Approach

We need to find the maximum salary separately for every department.

A common approach is to use a window function.

"RANK()" assigns a ranking within each department.

SELECT
    employee_id,
    employee_name,
    department,
    salary
FROM (
    SELECT
        employee_id,
        employee_name,
        department,
        salary,
        RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rnk
    FROM Employee
) e
WHERE rnk = 1;

🔎 How does it work?

"PARTITION BY department"

→ Creates a separate ranking for each department.

"ORDER BY salary DESC"

→ Places the highest salary first.

"RANK()"

→ Gives the highest salary a rank of 1.

Finally:

WHERE rnk = 1

→ Keeps only the highest-paid employee(s).

Expected result:

employee_name | department | salary
--------------|------------|-------
Rahul         | IT         | 90000
Neha          | HR         | 70000
Karan         | Finance    | 95000

⚠️ Why use "RANK()"?

If two employees have the same highest salary, both will receive rank 1 and both will be returned.

For example:

IT

Rahul 90000

Amit 90000

Raj 80000

Both Rahul and Amit will be returned.

Double Tap ❤️ For More

🚀𝗣𝗮𝘆 𝗔𝗳𝘁𝗲𝗿 𝗣𝗹𝗮𝗰𝗲𝗺𝗲𝗻𝘁 𝗧𝗿𝗮𝗶𝗻𝗶𝗻𝗴 | 𝗕𝗲𝗰𝗼𝗺𝗲 𝗮 𝗙𝘂𝗹𝗹𝘀𝘁𝗮𝗰𝗸 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗲𝗿 𝗪𝗜𝘁𝗵 𝗚𝗲𝗻𝗔𝗜

Start a high-paying tech career—even without prior coding experience

🏆 𝗣𝗹𝗮𝗰𝗲𝗺𝗲𝗻𝘁 𝗛𝗶𝗴𝗵𝗹𝗶𝗴𝗵𝘁𝘀:
💰 ₹41 LPA highest salary
📈 ₹7.4 LPA average salary
🎓 2,000+ students placed
🏢 500+ hiring partners
✅ 100% job assistance
📜 Skill India–authenticated certificate

🔗 𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄👇:-

https://pdlink.in/3SuUeuD

🎯 HurryUp.....Limited Seats Available

🎓 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘄𝗶𝘁𝗵 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗲𝘀! 🚀🔥

Upgrade your resume with Microsoft learning opportunities. Explore courses covering some of today's most in-demand tech skills!

🎯 Perfect for Students • Freshers • Job Seekers • Working Professionals

✅ Learn at your own pace ✅ Build career-relevant skills ✅ Explore available certificates and credentials

🔗 𝗔𝗰𝗰𝗲𝘀𝘀 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗛𝗲𝗿𝗲 👇

https://pdlink.in/3Uf5trR

🔥 Share with friends looking to upskill in 2026!

🇮🇳 𝗚𝗢𝗩𝗘𝗥𝗡𝗠𝗘𝗡𝗧 𝗢𝗙 𝗜𝗡𝗗𝗜𝗔 — 𝗔𝗜𝗖𝗧𝗘 𝗜𝗡𝗧𝗘𝗥𝗡𝗦𝗛𝗜𝗣𝗦 𝟮𝟬𝟮𝟲 🚀

Students and freshers can explore internship opportunities through the AICTE Internship Portal across multiple domains:

💻 AI, Data Science & Web Development
📈 Digital Marketing & Business Development
💰 Finance & Accounting
✍️ Content Writing & Graphic Design
⚙️ Mechanical, Civil & Electrical Engineering

🔗 𝗖𝗵𝗲𝗰𝗸 𝗢𝗽𝗽𝗼𝗿𝘁𝘂𝗻𝗶𝘁𝗶𝗲𝘀 & 𝗔𝗽𝗽𝗹𝘆 👇

https://pdlink.in/4y3vcBl

📢 Share this opportunity with your friends and classmates!

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