create database  if not exists theta_academy;
use theta_academy;

create table if not exists employes_table (employes_id int,employes_name varchar(20),project_Id int,department Varchar(20),salary int,experience int,projects Varchar(20));

Insert into  employes_table 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);


SELECT * FROM theta_academy.employes_table;

-- --Easy Level :-

--- -Q 1. Insert a new employee with the following details: Employee_ID = 22, Employee_name = 'John', Project_ID = 1, Department = 'IT', Salary = 50000, Experience = 2 years, Projects = 'ProjectA'.
insert into employes_table(employes_id,employes_name,project_id,department,salary,experience,projects) values(22,'John',1,'IT',50000,2,'project"A"');

-- --Q 2. How would you add a new column called "Manager_ID" to the employee table?
alter table employes_table add column Manager_ID int;

-- Q 3. What is the total salary expenditure of the company?
select sum(salary) from employes_table;

-- Q 4. How many employees are there in each department?
select count(employes_id),department from employes_table  group by department;

-- Q 5. Are there any employees with an Experience of more than 5 years?
select employes_name from employes_table where experience >5;

-- Q 6. Retrieve the details of employees whose Salary is greater than the average salary of all employees.
select employes_name,employes_id,department,experience from employes_table where salary>=(select avg(salary) from employes_table);

-- Q 7. Add a new project 'ProjectB' for the employee with Employee_ID = 25.
insert into employes_table(employes_id,employes_name,project_id,department,salary,experience,projects)values(24,null,null,null,null,null,'projects"B');
select * from employes_table;

-- Q 8. What is the average experience of employees in each department?
select avg(experience) from  employes_table group by department;

-- Q 9. Modify the data type of the Salary column from INT to DECIMAL(10, 2).
alter table employes_table modify column salary decimal;
describe employes_table;

-- Q 10. What is the maximum salary among all employees in “IT” department?
select max(salary) from employes_table where department='IT';



Create table  if not exists employes_performance (Performance_ID int,Employee_Id int, Project_ID int,Performance_rating float,Feedback Varchar(20));
insert into Employes_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 theta_academy.employes_performance;


-- -Medium Level :-

-- Q 1. How many employees are there in each department, and what is the maximum salary in each department?
select count(employes_name),max(salary) from employes_table group by department;

-- Q 2. Retrieve the details of employees whose Salary is greater than the highest salary in any other department.
select employes_name from employes_table where salary>all(select salary from employes_table group by department);

-- Q 3. Add a foreign key constraint on the Project_ID column in the employee table referencing the Project_ID column in the projects table.

-- Q 4. Delete all employees with a salary less than 40000 and Department is “IT”?
delete from employes_table where salary<40000 and department='IT';

-- Q 5.  Retrieve the Employee_name, Department, and Performance_rating of all employees along with their respective departments.
select employes_name,department,performance_rating from employes_table inner join employes_performance on employes_table.employes_id=employes_performance.Employee_ID group by department;

-- Q 6. Retrieve the Employee_name and Feedback for employees with Employee_ID between 11 and 16.
select employes_name,feedback from employes_table inner join employes_performance on employes_table. employes_id= employes_performance.Employee_ID  where employes_ID between 11 and 16;

-- Q 7. Retrieve Employee_name who have Salary more than “50,000”, performance_rating greater than “8” from “Sales” Department?
select employes_name from employes_table inner join employes_performance on employes_id=employes_ID  where (salary>=50000 and performance_rating>=8 )and (department='sale') ;

-- Q 8. Which employee_ID have “Average” Feedback with Project_ID “3”.
select Employee_ID from  employes_performance where(Feedback='average')and (Project_ID=3);

-- Q 9. Find a employee_name who have who do not have any project with Feedback “Average”?
select Employee_ID,employes_name from employes_table inner join employes_performance on employes_table.employes_id= employes_performance.Employee_ID where( Feedback='average')and (projects=null);

-- Q 10. Retrieve performance_id with the performance_rating above “6” and below “9” With feedback “Good”.
select performance_id, performance_rating from employes_performance where (performance_rating>6 and performance_rating<9 )and( Feedback='good');


create table if not exists Manager_table(employes_id int,performance_id int,manager varchar(20),manager_id int,employes_name varchar(20),project_Id int,department Varchar(20),salary int,experience int,projects Varchar(20));
select * from Manager_table;
Insert into manager_table 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,8,'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,'Project"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);


-- ADVANCE QUESTION 



-- Q 1. Retrieve the Department along with the average Performance_rating for each department.
select department,avg(Performance_rating) from manager_table inner join employes_performance on employes_id=Employee_ID group by department;

-- Q 2. Retrieve the Employee_name, Department, and Performance_rating of employees whose Performance_rating is above 8 and are assigned to project ID 1.
select employes_name,department,performance_rating from  manager_table inner join employes_performance on manager_table.employes_id=employes_performance.Employes_ID where (Performance_rating>8) and (Project_ID=1);

-- Q 3. Retrieve all employees along with their performance ratings. If an employee has no performance rating recorded, and mention manager name 

-- Q 4. Retrieve the Employee_name and Performance_rating for employees whose Performance_rating is higher than the average Performance_rating for their respective departments.
select employes_name ,performance_rating from manager_table inner join employes_performance on employes_id=Employee_ID where Performance_rating>(select avg(Performance_rating) from employes_performance group by department);


-- Q 5. Retrieve the Department along with the average Performance_rating for departments having an average Performance_rating greater than 7.
select department from manager_table inner join employes_performance on employes_id=Employee_ID where (Performance_rating>7)and(select avg(Performance_rating) from employes_performance);


-- Q 6. Retrieve the Employee_name and the number of employees who have the same Manager_ID.
select employes_name from manager_table where manager_id=employes_id;


-- Q 7. Retrieve the Employee_name for employees who have a corresponding record in the employee_performance table and do not have a manager.
select employes_name,Performance_ID,employes_id,Performance_rating,Feedback from employes_table inner join employes_performance on employes_id=Employee_ID;


-- Q 8. 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 employee table and the temporary table.
select * from tempp inner join employes_table where employes_table.employes_id=tempp.employe_id;

-- Q 9. Retrieve the Employee_name and Department for employees who are assigned to a project with a Project_ID greater than 2.
select  employes_name,department from employes_table where project_Id>2;

-- Q 10. Retrieve the Employee_name, Department, and Project_name for employees assigned to projects with manager name “Kurosaki”.
select employes_name,department,projects from manager_table where manager='aizen';







