CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    manager_id INT,
    department VARCHAR(30),
    salary INT
);

insert into Employees(emp_id,emp_name,manager_id,department,salary)
values
(1,'Alice',NULL,'HR',90000),
(2,'Bob',1,'HR',60000),
(3,'Charlie',1,'HR',55000),
(4,'David',2,'IT',70000),
(5,'Eva',2,'IT',65000),
(6,'Frank',4,'IT',50000),
(7,'Grace',4,'Finance',80000),
(8,'Henry',7,'Finance',45000),
(9,'Ivy',7,'Finance',48000),
(10,'Jack',NULL,'Sales',95000);

-- Show every employee along with their manager's name.
-- Expected columns
-- | Employee | Manager |

select E1.emp_name as Employee,E2.emp_name as Manager
from Employees as E1
left join Employees as E2
on E1.manager_id = E2.emp_id;

-- List employees who do not have a manager.

select E1.emp_name as Employee,E2.emp_name as Manager
from Employees as E1
left join Employees as E2
on E1.manager_id = E2.emp_id
where E1.manager_id is null;

-- Find all managers.
-- (No duplicates.)

select distinct E2.emp_name as Manager
from Employees as E1
join Employees as E2
on E1.manager_id = E2.emp_id;

-- Show employees whose manager belongs to a different department.
-- Output
-- | Employee | Employee_Department | Manager | Manager_Department |

select E1.emp_name as Employee,E1.department as Employee_Department,E2.emp_name as Manager,
    E2.department as Manager_Department
from Employees as E1
join Employees as E2
on E1.manager_id = E2.emp_id;

-- Show employee salary and manager salary.

select E1.emp_name as Employee,E1.salary as Employee_salary,E2.emp_name as Manager,
    E2.salary as Manager_salary
from Employees as E1
join Employees as E2
on E1.manager_id = E2.emp_id;

-- Find employees earning more than their manager.

select E1.emp_name as Employee,E1.salary as Employee_salary
from Employees as E1
join Employees as E2
on E1.manager_id = E2.emp_id
where E1.salary>E2.salary;

-- Find employees earning exactly the same salary as their manager.

select E1.emp_name as Employee,E1.salary as Employee_salary
from Employees as E1
join Employees as E2
on E1.manager_id = E2.emp_id
where E1.salary=E2.salary;

-- Show every manager and the number of employees 
-- reporting directly to them.

select E2.emp_name,count(E1.emp_id) as emp_count
from Employees as E1
join Employees as E2
on E1.manager_id = E2.emp_id
group by E2.emp_id,E2.emp_name;

-- Find employees whose manager also has a manager.

select E1.emp_name as Employee,E2.emp_name
from Employees as E1
join Employees as E2
on E1.manager_id = E2.emp_id
where E2.manager_id is not null; 

-- Find managers with more than two direct reports.

select E2.emp_name as Manager,count(E1.emp_name) as direct_report
from Employees as E1
join Employees as E2
on E1.manager_id = E2.emp_id
group by E2.emp_id,E2.emp_name
having count(E1.emp_name)>2;

-- Show employees together with their grandmanager.

SELECT
    e.emp_name AS employee,
    m.emp_name AS manager,
    gm.emp_name AS grandmanager
FROM Employees e
JOIN Employees m
    ON e.manager_id = m.emp_id
JOIN Employees gm
    ON m.manager_id = gm.emp_id;

-- Find employees whose manager also has a manager.

select E.emp_name as Employee
from Employees as E
join Employees as M 
on E.manager_id = M.emp_id
    join Employees as GM
on M.manager_id = GM.emp_id;

-- Find employees who report to the same manager.		
	-- Output		
	-- | Employee1 | Employee2 | Manager |		
	-- Avoid duplicate pairs.		


select E1.emp_name as Employee1,E2.emp_name as Employee2,M.emp_name as Manager
from Employees as E1
join Employees as E2 
on E1.manager_id = E2.manager_id
and E1.emp_id<E2.emp_id
join Employees as M 
on E2.manager_id = M.emp_id;

-- Find every pair of employees working in the same department.			

select E1.emp_name as Employee1,E2.emp_name as Employee2
from Employees as E1
join Employees as E2 
on E1.department = E2.department
and E1.emp_id<E2.emp_id;

-- Find the highest-paid employee under each manager.		

select 
  case when E1.salary>E2.salary then E1.emp_name
    when E2.salary>E1.salary then E2.emp_name end as high_paid_emp, 
    M.emp_name as Manager
    from Employees as E1
join Employees as E2 
on E1.manager_id = E2.manager_id
and E1.emp_id<E2.emp_id
join Employees as M 
on E2.manager_id = M.emp_id; 

-- Find managers whose salary is 
-- lower than at least one of their direct reports.

select * from 
(select 
  case when E1.salary>M.salary or E2.salary>M.salary 
    then M.emp_name end as low_salary_manager,M.salary as Manager_salary    
    from Employees as E1
join Employees as E2 
on E1.manager_id = E2.manager_id
and E1.emp_id<E2.emp_id
join Employees as M 
on E2.manager_id = M.emp_id) as a 
where low_salary_manager is not null;



CREATE TABLE Products (	
    product_id INT,	
    product_name VARCHAR(50),	
    category VARCHAR(30),	
    price INT	
);	

insert into Products(product_id,product_name,category,price)
values
(1,'Laptop A','Laptop',70000),
(2,'Laptop B','Laptop',85000),
(3,'Laptop C','Laptop',70000),
(4,'Phone A','Phone',30000),
(5,'Phone B','Phone',25000),
(6,'Phone C','Phone',30000),
(7,'Tablet A','Tablet',40000);

-- Find products having the same price.	

select distinct P1.product_name,P2.product_name,P1.price
from Products as P1
join Products as P2 
on P1.price = P2.price
and P1.product_id<P2.product_id;



-- Find products in the same category.	

select distinct P1.product_name,P2.product_name,P1.category
from Products as P1
join Products as P2 
on P1.category = P2.category
and P1.product_id<P2.product_id;

-- Find products that have both the same category and same price.			

select P1.product_name,P1.price,
    P2.product_name,P2.price,
    P1.category,P2.category
    
from Products as P1
join Products as P2 
on P1.category = P2.category
and P1.product_id<P2.product_id
and P1.price = P2.price;