Detailed explanation of various SQL JOIN operations using the Employee and Department tables, including examples and expected outputs in tabular format.

Tables Overview

1. Join Query to Retrieve Employee and Department Information 

This query joins the Employee and Department tables based on the foreign key relationship to display each employee's name, designation, and department name. 

SELECT E.Emp_Name, E.Designation, D.Dept_Name

FROM Employee E

JOIN Department D ON E.Designation_NO = D.Dept_ID;

Explanation:

• JOIN Clause: The query joins the Employee table (E) with the Department table (D) using the foreign key Designation_NO from Employee and the primary key Dept_ID from Department.

2. Query to Find Employees in a Specific Department 

You can filter the results to find all employees working in a specific department (e.g., "IT"). 

SELECT E.Emp_Name, E.Designation

FROM Employee E

JOIN Department D ON E.Designation_NO = D.Dept_ID

WHERE D.Dept_Name = 'IT';

Explanation:

• WHERE Clause: Filters the results to only include employees working in the "IT" department.

3. Count Employees in Each Department 

This query counts the number of employees in each department.

SELECT D.Dept_Name, COUNT(E.Emp_No) AS NumberOfEmployees

FROM Employee E

JOIN Department D ON E.Designation_NO = D.Dept_ID

GROUP BY D.Dept_Name;

Explanation:

• COUNT Function: Counts the number of employees (Emp_No) in each department.

• GROUP BY Clause: Groups the results by department name to provide a count for each department.

• Result: This will return the number of employees in each department.

4. Find Departments with No Employees 

This query finds departments that do not have any employees. 

SELECT D.Dept_Name

FROM Department D

LEFT JOIN Employee E ON D.Dept_ID = E.Designation_NO

WHERE E.Emp_No IS NULL;

Explanation:

• LEFT JOIN: Returns all departments and matches them with employees. If there’s no match, the employee columns will contain NULL.

• WHERE Clause: Filters to find rows where Emp_No is NULL, indicating no employees are associated with that department.

• Result: This will return the names of departments that currently have no employees.

5. Query to Update Employee's Department 

This query updates the department of a specific employee by changing the foreign key value.

UPDATE Employee

SET Designation_NO = (SELECT Dept_ID FROM Department WHERE Dept_Name = 'Finance')

WHERE Emp_Name = 'John Doe';

Explanation:

• Subquery: Finds the Dept_ID for the "Finance" department.

• UPDATE Statement: Updates the Designation_NO for the employee named "John Doe" to the Dept_ID of the "Finance" department.

• Result: This will change John Doe's department to "Finance".

6. Delete Employees in a Specific Department 

This query deletes all employees working in a specific department (e.g., "Sales").

DELETE FROM Employee

WHERE Designation_NO = (SELECT Dept_ID FROM Department WHERE Dept_Name = 'Sales');

Explanation:

• Subquery: Finds the Dept_ID for the "Sales" department.

• DELETE Statement: Deletes all employees who belong to the "Sales" department by matching Designation_NO with the Dept_ID of "Sales".

• Result: This will remove all employees working in the Sales department from the Employee table.