-- create database 
create database if not exists Assignment;
use Assignment;

create table Employees (Employee_Id int,Employee_Name varchar(30),Project_Id int,Department Varchar(30),
Salary int,Experience int,Projects Varchar(30));

Insert into Employees values (1,'Mohit',2,'IT',50000,2,'Project"A"'),(2,'Reema',3,'Sales',30000,1,'Project"B"'),(3,'Harish',2,'IT',50000,2,'Project"A"'),
(4,'Paras',2,'IT',95000,4,'Project"A"'),(5,'Shivam',1,'HR',77000,4,'Project"C"'),(6,'Harsh',3,'Sales',30000,1,'Project"B"'),(7,'Dorthy',1,'HR',65000,3,'Project"C"'),
(8,'Archana',3,'Sales',18000,NuLL,'Project"B"'),(9,'Vishal',3,'Sales',18000,Null,'Project"B"'),(10,'Shubendu',1,'HR',150000,8,'Project"C"'),
(11,'Drigga',4,'Finance',48000,3,'Project"D"'),(12,'Maninder',2,'IT',145000,7,'Project"A"'),(13,'Nithish',4,'Finance',115000,6,'Project"D"'),
(14,'Shubham',4,'Finance',74000,5,'Project"D"'),(15,Null,null,'Finance',17000,Null,Null),(16,'Prince',Null,Null,Null,Null,null);

Create table Employees_Performances (Performance_Id int,Employee_Id int, Project_Id int,Performance_Rating float,Feedback Varchar(20));
insert into Employees_Performances values(1,1,2,9,'Excellent'),(2,2,3,8,'Good'),(3,3,2,9,'Excellent'),(4,4,2,9.3,'Excellent'),(5,5,1,9,'Excellent'),
(6,6,3,8.5,'Good'),(7,7,1,8,'Good'),(8,8,3,5,'Average'),(9,9,3,6,'Average'),(10,10,1,9.4,'Excellent'),(11,11,4,7,'Average'),(12,12,2,9.5,'Excellent'),
(13,13,4,8.5,'Good'),(14,14,4,8,'Good'),(15,15,null,null,'Worst'),(16,16,null,null,'Worst');

select * from employees;
select * from Employees_Performances;

-- EASY LEVEL QUESTIONS :-

-- Q1) Insert a new employee with the following details : Employee_Id=22,Employee_Name='John',
-- Project_Id=1,Department='IT',Salary=50000,Experience=2,Projects=ProjectA ?
insert into Employees (Employee_Id,Employee_Name,Project_Id,Department,Salary,Experience,Projects)
values (22,'John',1,'IT',50000,2,'Project"A"');
select * from employees;

-- Q2 -> How would you add a new column Called "Manager_Id" to the Employees Table ?
Alter Table Employees Add column Manager_Id int;
select * from employees;

-- Reset the table after inserting values given in questions and droping last column we add in Question
Delete from Employees order by  employee_id desc limit 1 ;
Alter table employees drop column Manager_id; 
select * from employees;

-- Q3 -> What is the total salary expenditure of the company ?
select sum(Salary) from Employees ;

-- Q4 -> How many Employees are there in Each Department?
select Department,Count(*) as Total_Employees from Employees Group by Department;

-- Q5 -> Are there any employees with an Experience of more than 5 Years ?
select Employee_Id,Employee_Name from Employees where Experience >=5 ;

-- Q6 -> Retrieve the details of employees whose salary is greater than the average salary of all employees ?
select * from employees where salary > all( select avg(salary) from employees);

-- Q7 -> Add a new 'Project"B"' for the  employees with Employee_id=25 ?
insert into Employees (Employee_Id,Projects) values (25,'Project"B"'); 
select * from employees;

# Reseting the table 
Delete from employees order by employee_id desc limit 1;
select* from employees;

-- Q8 -> What is the average experience of employees in each department ?
select Department,avg(Experience) as Average_Experience from employees group by Department;

-- Q9 -> Modify the data type of the salary column from Int to Decimal(10,2) ?
Alter table employees modify column salary decimal(10,2);
describe employees;

-- Q10 -> What is the maximum salary among all employees in "IT" department ?
Select Department,max(salary) from employees Where Department='IT';


-- MEDIUM LEVEL QUESTIONS :-
-- Q1 -> How many employees are there in each department and what is the maximum salary in each department ?
Select Department,Count(*) as Total_employees,max(salary) from employees group by Department;

-- Q2 -> Retrieve the details of employees whose salary is greater than the highest salary in any other department ? 
Select * from employees where salary >(select max(salary) from employees where employees.department group by Department);

-- Q3 -> Add a foreign key constraint on the Project_Id column in the employee table referencing the Project_Id column in the Projects table ?
Alter Table employees Add constraint primary key(Project_Id);
ALTER TABLE employees ADD CONSTRAINT Project_Id FOREIGN KEY (Project_Id) REFERENCES Employees_Performances(Project_Id);

-- Q4 -> Delete all employees with a salary less than 40000 and Department is "IT" ?
delete from employees where salary < 40000 and Department='IT' ;
select * from employees;

-- Q5 -> Retrieve the Employee_Name,Department and Performance_Rating of all employees along with their respective Departments ? 
Select Employee_Name,Department,Performance_rating from employees join employees_performances on employees.Employee_Id=
employees_performances.Employee_Id;

-- Q6 -> Retrieve the Employees_Name and feedback for employees with Performance_Id between 11 and 16 ?
select Employee_Name,Feedback from employees left join employees_performances on employees.Employee_Id=
employees_performances.Employee_Id where Performance_Id between 11 and 16 ; 

-- Q7 -> Retrieve Employee_Name who have salary more than 50000,Performance_Rating greater than 8 from "Sales" Department ? 
select Employee_Name from employees left join employees_performances on employees.Employee_Id=
employees_performances.Employee_Id where Salary > 50000 and Performance_Rating>8 and Department='Sales';

-- Q8 -> Which employee_id have 'Average' feedback with Project_Id "3" ?
select Employee_Id from employees_performances where Feedback='Average' and Project_Id='3';

-- Q9 -> Find the Employee_Name who do not have any project with Feedback 'Average' ?
select Employee_Name from employees left join employees_performances on employees.Employee_Id=
employees_performances.Employee_Id where Feedback !='Average'; 

-- Q10 -> Retrieve Performance_Id with the Performance_Rating above "6" and below "9" with feedback "Good" ? 
select Performance_Id from employees_performances where Performance_Rating between 6 and 9 and Feedback='Good' ;


###  CREATING NEW TABLES FOR HARD QUESTIONS 

create table Department (Employee_ID int,Performance_ID int,Manager varchar(20),Manager_ID int,Employee_Name varchar(30),
Project_ID int,Departments varchar(20),Salary int,Experience int,Projects Varchar(30));

Insert into Department values (1,1,'Aizen',1,'Mohit',2,'IT',50000,2,'Project"A"'),(2,2,'Ichigo',2,'Reema',3,'Sales',30000,1,'Project"B"'),
(3,3,'Aizen',1,'Harish',2,'IT',50000,2,'Project"A"'),(4,4,'Aizen',1,'Paras',2,'IT',95000,4,'Project"A"'),(5,5,'Shunsui',3,'Shivam',1,'HR',77000,4,'Project"C"'),
(6,6,'Ichigo',1,'Harsh',3,'Sales',30000,1,'Project"B"'),(7,7,'Shunsui',3,'Dorthy',1,'HR',65000,3,'Project"C"'),(8,8,'Ichigo',2,'Archana',3,'Sales',18000,null,'Project"B"'),
(9,9,'Ichigo',2,'Vishal',3,'Sales',18000,null,'Project"B"'),(10,10,'Shunsui',3,'Shubendu',1,'HR',150000,8,'Project"C"'),(11,11,'Rukia',4,'Drigga',4,'Finance',
48000,3,'Project"D"'),(12,12,'Aizen',1,'Maninder',2,'IT',145000,7,'Project"A"'),(13,13,'Rukia',4,'Nithish',4,'Finance',115000,6,'Projrct"D"'),(14,14,'Rukia',4,'Shubham',
4,'Finance',74000,5,'Project"D"'),(15,15,null,null,null,null,'Finance',17000,Null,Null),(16,16,Null,Null,'Prince',Null,null,null,null,null);


create table department_details (Performance_Id int,Employee_Id int,Manager_Id int,Project_id int,Performance_Rating decimal(10,2),Feedback varchar(20));
insert into department_details values(1,1,1,2,9,'Excellent'),(2,2,2,3,8,'Good'),(3,3,1,2,9,'Excellent'),(4,4,1,2,9.3,'Excellent'),(5,5,3,1,9,'Excellent'),
(6,6,1,3,8.5,'Good'),(7,7,3,1,8,'Good'),(8,8,2,3,5,'Average'),(9,9,2,3,6,'Average'),(10,10,3,1,9.4,'Excellent'),(11,11,4,4,7,'Average'),(12,12,1,2,9.5,'Excellent'),
(13,13,4,4,8.5,'Good'),(14,14,4,4,8,'Good'),(15,15,null,null,null,'Worst'),(16,16,null,null,null,'Worst');

select * from department;
select * from department_details;


-- HARD LEVEL QUESTIONS :--

-- Q1 -> Retrieve the Department along with the average Performance_rating for each department ?
select Departments,avg(Performance_Rating) from department join department_details on department.Employee_ID=department_details.Employee_ID
group by Departments;

-- Q2 -> Retrieve the employee_name,Department and Performance_Rating  of employees whose performance_rating is above 8 and are assigned to Project_Id 1 ?
select d.Employee_name,d.Departments,dd.Performance_Rating from department d inner join department_details dd on d.Employee_ID=dd.Employee_Id 
where dd.Performance_Rating >8 and d.Project_ID=1;

-- Q3 -> Retrieve all employees along with their Performance_Rating,if an employee has no performance rating recorded,and mention manager name ?
select d.Employee_Name,dd.Performance_Rating,If(Performance_rating is null,dd.Performance_Rating,d.manager) as Manager_Name from department d
 join department_details dd on d.Employee_ID=dd.Employee_ID;  

-- Q4 -> Retrieve the employee_name and Performance_rating  for employees whose performance_rating is higher than the average performance_rating 
-- for their respective department ?
select employee_name,Performance_Rating from department join department_details on department.Employee_ID=department_details.Employee_Id where
Performance_Rating >any (select avg(performance_rating) from department_details);

-- Q5 -> Retrieve the department along with the average Performance_Rating for departments having an average Performance _rating greater than 7 ?
select departments,avg(Performance_Rating) from department join department_details on department.Employee_ID=department_details.Employee_Id 
group by departments having avg(Performance_Rating) > 7;

-- Q6 -> Retrieve the employee_name and the number of employees who have the same Manager_Id ?
select d.Employee_Name,Count(dd.Employee_Id) as NUMBER_OF_EMPLOYEES from department d inner join department_details dd on 
d.Manager_ID=dd.Manager_Id group by d.Employee_Name;

-- Q7 -> Retrieve the Employee_name for employees who have a corresponding record in the department_details table and do not have a manager ?
select d.Employee_Name from department d left join department_details dd on d.Employee_ID=dd.Employee_Id
where dd.Manager_Id is null;

-- Q8 -> Create a temporary table containing the employee_name and performance_rating of employees.Then retrieve the details of employees 
-- along with their department names from both the tables and the temporary table ?

# creating temporary table containg employee_name and performance_rating
Create temporary table TempPerformances As select Employee_Name,Performance_Rating from department d left join department_details dd on 
d.Employee_ID=dd.Employee_ID;

-- retrieve the details of employees along with their department names from both the tables and the temporary table 
Select d.Employee_Name,d.Departments,tp.Performance_Rating from department d left join TempPerformances tp on d.Employee_Name=tp.Employee_Name;


-- Q9 -> Retreive the Employee_name and department for employees who are assigned to a project with a Project_Id greater than 2 ?
select Employee_ID,Employee_Name,Departments from department where Project_ID>2;

-- Q10 -> Retrieve the Employee_name,Department and Project_name for employees assigned to projects with manager name "Shunsui" ?
select Employee_Name,Departments,Projects from department where Manager= 'Shunsui' ;

