Welcome to Database Laboratory
Welcome to Database Laboratory
Detailed explanation of various SQL JOIN operations using the Employee and Department tables, including examples and expected outputs in tabular format.
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.