SQL Self Join - Day20
@LearnWithRD0529
Q1:Suppose you have an employee database with a table named "employees2" that represents a company's hierarchy. Each employee has an ID, a --name, and a reference to their manager's ID. You want to find the employees and their managers.
Employee2 table scripts -
CREATE TABLE employees2 (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
manager_id INT
);
INSERT INTO employees2 (employee_id, employee_name, manager_id) VALUES (1, 'John', NULL);
INSERT INTO employees2 (employee_id, employee_name, manager_id) VALUES (2, 'Alice', 1);
INSERT INTO employees2 (employee_id, employee_name, manager_id) VALUES (3, 'Bob', 2);
INSERT INTO employees2 (employee_id, employee_name, manager_id) VALUES (4, 'Eva', 2);
INSERT INTO employees2 (employee_id, employee_name, manager_id) VALUES (5, 'Sarah', 1);
------------------------------------------------
Q2: Let's create a table for a organization structure where employees report to other employees, and we want to find who reports to whom at various organizational levels.
Organization table scrpts -
-- Create the Organization table
CREATE TABLE Organization (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
ManagerID INT
);
-- Insert sample data
INSERT INTO Organization (EmployeeID, EmployeeName, ManagerID) VALUES(1, 'CEO', NULL);
INSERT INTO Organization (EmployeeID, EmployeeName, ManagerID) VALUES(2, 'Manager A', 1);
INSERT INTO Organization (EmployeeID, EmployeeName, ManagerID) VALUES(3, 'Employee A1', 2);
INSERT INTO Organization (EmployeeID, EmployeeName, ManagerID) VALUES(4, 'Employee A2', 2);
INSERT INTO Organization (EmployeeID, EmployeeName, ManagerID) VALUES(5, 'Manager B', 1);
INSERT INTO Organization (EmployeeID, EmployeeName, ManagerID) VALUES(6, 'Employee B1', 5);
INSERT INTO Organization (EmployeeID, EmployeeName, ManagerID) VALUES(7, 'Employee B2', 5);
INSERT INTO Organization (EmployeeID, EmployeeName, ManagerID) VALUES(8, 'Manager C', 1);
INSERT INTO Organization (EmployeeID, EmployeeName, ManagerID) VALUES(9, 'Employee C1', 8);
INSERT INTO Organization (EmployeeID, EmployeeName, ManagerID) VALUES(10, 'Employee C2', 8);