-- Manpreet Singh Bansal

create table a_1 (employee_id int, employee_name varchar(20), project_id int, department varchar(10), salary int, experience int, projects varchar(20));
insert into a_1 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 a_1;

create table a_2 (performance_id int, employee_id int, project_id int, performance_rating decimal(5,1), feedback varchar(15));
insert into a_2 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, "Average"),(13, 13, 4, 8.5, "Good"),(14, 14, 4, 8, "Good"),(15, 15, Null, Null, "Worst"),(16, 16, Null, Null, "Worst");
select * from a_2;

create table a_3 (Employee_ID int, Performance_ID int, Manager varchar(20), Manager_ID int, Employee_name varchar(20), Project_ID int, Department varchar(10), Salary int, Experience int, Projects varchar(20));
insert into a_3 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,"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);
select * from a_3;


-- EASY LEVEL 

-- ANSWER 1
insert into a_1 values(22, "John", 1, "IT", 50000, 2, "Project\"A\"");
insert into a_1 values(22, "John", 1, "IT", 50000, 2, "ProjectA");

-- ANSWER 2
alter table a_1 add column manager_id int;

-- ANSWER 3
select sum(salary) from a_1;
-- 1032000

-- ANSWER 4
select department,count(employee_name) from a_1 group by department;

-- ANSWER 5
select employee_name, experience from a_1 where experience > 5;
select employee_name, experience from a_1 where experience >= 5;

-- ANSWER 6
select avg(salary) from a_1;
select * from a_1 where (select avg(salary) from a_1) < salary;

-- ANSWER 7
select * from a_1;
set sql_safe_updates = 0;
update a_1 set projects = "ProjectB" where employee_id = 25;
update a_1 set projects = "Project\"B\"" where employee_id = 25;

-- ANSWER 8
select department, avg(experience) as average_exp from a_1 group by department;

-- ANSWER 9
alter table a_1 change salary salary decimal(10,2);
describe a_1;

-- ANSWER 10
select department,max(salary) from a_1 where department = "IT";


-- MEDIUM LEVEL

-- ANSWER 1
select department, count(employee_name) as No_of_Emps, max(salary) from a_1 group by department;

-- ANSWER 2
select department, max(salary) from a_1 group by department;
select department, max(salary) from a_1 where (select max(salary) from a_1 where department = "Finance") < salary group by department;

-- ANSWER 3
alter table employee add foreign key (Project_ID) references projects(Project_ID) on update cascade on delete cascade;

-- ANSWER 4
delete from a_1 where Salary < 40000 and Department = 'IT';

-- ANSWER 5
select employee_name, department, performance_rating from a_1 left join a_2 using (employee_id);

-- ANSWER 6
select employee_name,feedback from a_1 join a_2 using (employee_id) where employee_id between 11 and 16;

-- ANSWER 7
select employee_name,salary,performance_rating,department from a_1 join a_2 using (employee_id) where salary > 50000 and performance_rating > 8 and department = "Sales";

-- ANSWER 8
select employee_id,a_1.project_id,feedback from a_1 join a_2 using (employee_id) where a_1.project_id = 3 and feedback = "Average";

-- ANSWER 9
-- There is no project_id null where feedback is "average", so to check if the syntax worker, i used "Worst"
select employee_name,a_1.project_id,feedback from a_1 join a_2 using (employee_id) where a_1.project_id is null and feedback = "Worst";

-- ANSWER 10
select performance_id from a_2 where performance_rating between 6 and 9 and feedback = "Good";


-- HARD LEVEL

-- ANSWER 1
select department, avg(performance_rating) from a_3 join a_2 using(Employee_id) group by department;

-- ANSWER 2
select employee_name, department, performance_rating from a_3 join a_2 using(employee_id) where performance_rating > 8 and a_3.project_id = 1;

-- ANSWER 3
select employee_id,employee_name, performance_rating, manager from a_3 join a_2 using (Employee_id) where performance_rating is null;

-- ANSWER 4
select employee_name, performance_rating from a_3 join a_2 using (employee_id) where performance_rating > (select avg(performance_rating) from a_2 group by department);

-- ANSWER 5
select department, avg(performance_rating) from a_3 join a_2 using (employee_id) where performance_rating > 7 group by department;

-- ANSWER 6
select manager_id, count(employee_id)  from a_3 group by manager_id;

-- ANSWER 7
select employee_name from a_3 join a_2 using (employee_id) where manager is null;

-- ANSWER 8
-- 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.
-- Haven't learned about creating temporary table yet

-- ANSWER 9
select employee_name, department from a_3 where project_id > 2;

-- ANSWER 10
select employee_name, department, projects from a_3 where manager = "kurosaki";
select employee_name, department, projects from a_3 where manager = "Aizen";

/*insert into a_1 values(17,"HEHE", 1, "Sales", 60000, 9,"Project\"A\"",null);
insert into a_2 values(17,17,4,9.0,"Excellent");
delete from a_2 where employee_id = 16;
set sql_safe_updates = 0;
rollback;
start transaction;
select * from a_3 join a_2 using (employee_id);
select * from a_1;
select * from a_2;
select * from a_3;
*/