create database department;
create table information( employee_id int, employee_name varchar(20), project_id varchar(20), department varchar(20), salary varchar(20), experience varchar(20), projects varchar(20));
insert into information values(1, 'mohit', 2, 'IT', 50000, 2, 'projectA');
insert into information values(2, 'reema', 3, 'sales', 30000, 1, 'projectB'), 
(3, 'harish', 2, 'IT', 50000, 2, 'projectA'),
(4, 'paras', 2, 'IT', 95000, 4, 'projectA'),
(5, 'shivam', 1, 'HR', 77000, 4, 'projectC'),
(6, 'harsh', 3, 'sales', 30000, 1, 'projectB'),
(7, 'dorthy', 1, 'HR', 65000, 3, 'projectC'),
(8, 'archana', 3, 'sales', 18000, 'null', 'projectB'),
(9, 'vishal', 3, 'sales', 18000, 'null', 'projectB'),
(10, 'shubendu', 1, 'HR', 150000, 8, 'projectC'),
(11, 'drigga', 4, 'finance', 48000, 3, 'projectD'),
(12, 'maninder', 2, 'IT', 145000, 7, 'projectA'),
(13, 'nithish', 4, 'finance', 115000, 6, 'projectD'),
(14, 'shubham', 4, 'finance', 74000, 5, 'projectD'),
(15, 'null', 'null', 'finance', 17000, 'null', 'null'),
(16, 'prince', 'null', 'null', 'null', 'null', 'null');
insert into information values(22, 'john', 1, 'IT', 50000, 2, 'projectA'); -- ans 1st
alter table information add column manager_id varchar(20); -- ans 2nd
select sum(salary) from information; -- ans 3rd
select count(department) from information;
select department, count(employee_name) from information group by department; -- ans 4 th 
select * from information where experience > 5; -- ans 5th
select avg(salary) from information; -- 60705.882352941175
select * from information where(select avg(salary) from information) <salary; -- ans 6th 
set sql_safe_updates = 0;
insert into information values(25, 'null', 'null', 'null', 'null', 'null' , 'projectB', 'null'); -- 7ans
select department, avg(experience) from information group by department; -- 8ans
alter table information modify salary decimal(10, 1); -- 9 ans
select max(salary) from information where department = 'IT'; -- 10 ans
select * from information;

-- medium 

create table projects (performance_id int, employee_id int, project_id int, performance_rating decimal(5,1), feedback varchar(15));
insert into projects 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 projects;
select department, count(employee_id), max(salary) from information group by department; -- 1 ans
select department, max(salary) from information group by department;
select department, max(salary) from information where (select max(salary) from information where department = "Finance") < salary group by department; -- 2ans
set sql_safe_updates = 0;
delete from information where salary < '40000' and department = 'it'; -- 4ans
select employee_name, department, performance_rating from information join projects on information.project_id = projects.project_id; -- 5ans
select employee_name, feedback from information join projects using(employee_id) where employee_id between 11 and 16; -- 6ans
select employee_name, salary, performance_rating, department from information join projects using(employee_id) where salary > 50000 and performance_rating > 8 and department = 'sales'; -- 7ans
select employee_id from projects where feedback = 'average' and project_id = 3; -- 8 ans 
select employee_name, information.project_id, feedback from information join projects using(employee_id) where projects is null and feedback = 'worst'; -- 9ans 
select performance_id, performance_rating from projects where performance_rating between 6 and 9 and feedback = 'good'; -- 10ans

