SQLJoinsAssignment.sql

---Employee(EID,Ename.MID,DID)
 ---e101,'Suresh',10,'1'
---Manager(MID,Mname)
 ---10,'Alice'
---Project (PID,Pname,EID)
  ---P1,'Data Migration',E101
---Department(DID,Dname)
 ---1,'IT'
--- store atleast 6-7 values for each 

create database anju

use anju

--- EMPLOYEE TABLE
CREATE TABLE Employee
(
    EID VARCHAR(10),
    Ename VARCHAR(50),
    MID INT,
    DID INT
);

--- MANAGER TABLE
CREATE TABLE Manager
(
    MID INT,
    Mname VARCHAR(50)
);

--- PROJECT TABLE
CREATE TABLE Project
(
    PID VARCHAR(10),
    Pname VARCHAR(100),
    EID VARCHAR(10)
);

--- DEPARTMENT TABLE
CREATE TABLE Department
(
    DID INT,
    Dname VARCHAR(50)
);

---inserting data for employees 
INSERT INTO Employee VALUES
('E101', 'Suresh', 10, 1),
('E102', 'Ramesh', 20, 2),
('E103', 'Anita', 10, 1),
('E104', 'Priya', 30, 3),
('E105', 'Rahul', 40, 4),
('E106', 'Sneha', 50, 5),
('E107', 'Karan', 60, 6);

---inserting data for manager 
INSERT INTO Manager VALUES
(10, 'Alice'),
(20, 'Bob'),
(30, 'Charlie'),
(40, 'Diana'),
(50, 'Edward'),
(60, 'Fiona');

---inserting data for departments 
INSERT INTO Department VALUES
(1, 'IT'),
(2, 'HR'),
(3, 'Finance'),
(4, 'Sales'),
(5, 'Marketing'),
(6, 'Operations');

---inserting data for project 
INSERT INTO Project VALUES
('P1', 'Data Migration', 'E101'),
('P2', 'Payroll System', 'E102'),
('P3', 'Website Revamp', 'E103'),
('P4', 'Budget Analysis', 'E104'),
('P5', 'CRM Implementation', 'E105'),
('P6', 'Market Research', 'E106'),
('P7', 'Supply Chain Optimization', 'E107');

SELECT * FROM Employee;
SELECT * FROM Manager;
SELECT * FROM Project;
SELECT * FROM Department;

---SQL Joins Assignment

---1. Display each employee's name and their corresponding department name.
---we can join this one by using ineer join .use syntax for inner join 
select E.ename,D.dname from employee E
Join department D 
ON E.DID = D.DID 

---2. List all projects along with the name of the employee assigned to them.
SELECT P.pname,E.ename from project P
Join Employee E 
ON P.EID = E.EID

---3. Show the names of employees and the names of their managers.
SELECT E.ename,M.mname from employee E
join manager M 
ON E.MID = M.MID 
---Select 
---From 
---Join

---4. Display the Project ID and the Department ID for every project.
Select P.PID, D.DID from Project P
Join employee E 
ON P.EID = E.EID
Join Department D
ON E.DID = D.DID

---5. List employees who work in the 'IT' department.
SELECT E.ename,D.dname from employee E 
Join Department D 
ON E.DID = D.DID 
WHERE D.DNAME = 'IT'

---6. Display employee names and their manager names for all employees in department 10.SELECT E.ename,D.dname from employee E 
SELECT E.ename,M.mname from employee E 
Join Manager M 
ON E.MID = M.MID 
WHERE E.DID = '10'

---7. Show all projects handled by ‘Suresh’.
Select P.pname,E.ename from project P
Join employee e 
on P.eid = E.eid 
where E.ename = 'suresh'

---8. Find the department name associated with Project ID 101.
select D.dname from Department D 
JOIN employee E 
ON D.DID = E.DID 
JOIN Project P 
ON E.EID = P.EID 
Where P.pid = E101 

---9. List all employees whose manager's name is 'Suresh'.
select e.ename from employee e 
join manager m 
on e.mid = m.mid 
where m.mname = 'suresh'

---10.Display the count of employees in each department name.
select d.dname, count(e.eid) from department D 
left join employee e 
on d.did = e.did
group by d.dname 

---11. List all departments and the employees working in them (including departments with no employees).
select d.dname,E.ename from department D 
left join employee E 
on D.did = E.did 

---12.Display all employees and the projects they are assigned to (including those with no projects).
select e.ename , p.pname from employee E 
left join project P 
ON E.EID = P.EID 

---13. Show the names of employees, their department names, and their manager names in one result.
select E.ename, D.dname,M.mname from Employee E
JOIN Department D 
ON E.DID = D.DID 
JOIN Manager M 
ON E.MID = M.MID 

---14. Find all projects and the department name they belong to.
SELECT P.PNAME,D.DNAME FROM PROJECT P
JOIN EMPLOYEE E 
ON P.EID = E.EID 
JOIN DEPARTMENT D 
ON E.DID = D.DID 

---15. List all managers and the names of employees reporting to them (including managers with no reporters).
SELECT P.PNAME,E.ENAME FROM PROJECT P 
LEFT JOIN EMPLOYEE E 
ON P.EID = E.EID 
WHERE P.EID IS NULL

---16. Find employees who are NOT assigned to any project.
SELECT P.PNAME,E.ENAME FROM PROJECT P 
LEFT JOIN EMPLOYEE E 
ON P.EID = E.EID 
WHERE P.EID IS NULL

---17.Display the names of all employees and the names of projects, but only for those in the ‘IT’ department.
SELECT E.Ename, P.Pname
FROM Employee E
JOIN Department D
    ON E.DID = D.DID
JOIN Project P
    ON E.EID = P.EID
WHERE D.Dname = 'IT';

---18. Show the names of managers who are managing employees in the 'Finance' department.
SELECT DISTINCT M.Mname
FROM Manager M
JOIN Employee E
    ON M.MID = E.MID
JOIN Department D
    ON E.DID = D.DID
WHERE D.Dname = 'Finance';

--- 19.Display Department Name, Employee Name, and Project Name for all matches.
SELECT D.Dname, E.Ename, P.Pname
FROM Department D
JOIN Employee E
    ON D.DID = E.DID
JOIN Project P
    ON E.EID = P.EID;

---20. List all projects and the manager's name responsible for the employee assigned to that project.
SELECT P.Pname, M.Mname
FROM Project P
JOIN Employee E
    ON P.EID = E.EID
JOIN Manager M
    ON E.MID = M.MID;