SELECT * FROM assignment.employees;
use employees;
insert into employees values(22,'John',1,'IT',50000,2,'Project"A" ');
select * from employees;

--- Ques2: How would you add a new column called Manager_ID to the employee table?
alter table employees add column Manager_ID int;

--- Ques4: How many employees are there in each department?
select department ,count(*) as employee_id from assignment.employees  group by department;

--- Ques5: Are there any employees with experience of more than 5 years?
select employee_name from assignment.employees where experience>5;

--- Ques6: Retrieve the details of employees whose salary is greater than the average salary of all employees
SELECT * FROM assignment.employees WHERE salary>(SELECT AVG (SALARY)from assignment.employees);

--- Ques8: What is the average experience of employees in each department?
select department, AVG (experience) AS averageExperience FROM assignment.employees group by department;

--- Ques10: What is the maximum salary among all employees in IT department?
select max(salary) from assignment.employees where department='IT';

--- Ques9: Modify the data type of salary column from int to decimal.
alter table assignment.employees modify column salary decimal;
describe assignment.employees;

--- MEDIUM LEVEL
--- Ques1: How many employees are there in each department,and what is the max salary in each department?
select count(employee_name),max(salary) from assignment.employees group by department;

--- Ques2: Retrieve the details of employees whose salary is greater than the highest salary in other department.
select employees_name from assignment.employees where salary> all(select salary from assignment.employees group by department);


--- Ques4: Delete all employees with a salary less than 40000 and Department is IT?
delete from assignment.employees where salary<40000 and department='IT';

--- Ques5: Retrieve the Employee_name,department and performance_rating of all employees alomg with their respective departments.
select employees_name from assignment.employees inner join assignment.employees_performances on assignment.employees.employees_id=
assignment.employees_performances.employee_id group by department;

--- Ques6: retrieve the employee_name and feedback for employees with employee_id between 11 and 16.
select employees_name,feedback from assignment.employees inner join assignment.employees_performances on assignment.employees.employee_id=
assignment.employees_performances.employee_id where employee_id between 11 and 16;

--- ques7: retrieve employee_name who have salary more than"50,000",performance_rating greater than "8" from "sales" department.
select employee_name from assignment.employees inner join assignment.employees_performances on employees_id = employees_id where(salary>=50000
and performance_rating>8) and (department='sale');

 