✅ SQL Scenario-Based Question & Answer 💻📊

Scenario:
You have two tables:

Employees

| emp_id | name  | dept_id | salary |
| ------ | ----- | ------- | ------ |
| 1      | John  | 10      | 5000   |
| 2      | Alice | 20      | 6000   |
| 3      | Bob   | 10      | 4500   |
| 4      | Mary  | NULL    | 7000   |

Departments

| dept_id | dept_name |
| ------- | --------- |
| 10      | Sales     |
| 20      | Marketing |
| 30      | HR        |

Question:
Write a query to display all employees with their department name. If an employee doesn't belong to any department, show "Not Assigned" instead.

Answer:

SELECT e.name AS employee_name,
       COALESCE(d.dept_name, 'Not Assigned') AS department_name
FROM Employees e
LEFT JOIN Departments d
ON e.dept_id = d.dept_id;

Explanation:
⦁ LEFT JOIN ensures all employees are included, even those without a matching dept_id (like Mary).
⦁ COALESCE picks the first non-NULL value: dept_name if available, otherwise "Not Assigned".
⦁ This handles NULLs gracefully while keeping the full employee list intact.

Result:

| employee_name | department_name |
| ------------- | --------------- |
| John          | Sales           |
| Alice         | Marketing       |
| Bob           | Sales           |
| Mary          | Not Assigned    |

💬 Double Tap ❤️ if this helped you!