SQL: Employee Salary

/* Exercise NO 21 Generate the employee details and compute the salary based on the department.*/ -- creating database employee and opening it for use. create database employee; use employee; -- create employee table in employee database create table emp_table( emp_id int(4), Dpt_id int(4), emp_name varchar(20), salary decimal(7,2) ); -- create department table create table dpt_table( dpt_id int(5), dpt_name varchar(20), supervisor varchar(20) ); -- Insert record into the employee table. INSERT INTO EMP_table (emp_id,Dpt_id,emp_name,salary)VALUES (101, 01, 'ARUN', 15000); INSERT INTO EMP_table (emp_id,Dpt_id,emp_name,salary)VALUES (102, 02, 'ARUNachalam', 25000); INSERT INTO EMP_table (emp_id,Dpt_id,emp_name,salary)VALUES (103, 03, 'Blesson', 15000); INSERT INTO EMP_table (emp_id,Dpt_id,emp_name,salary)VALUES (104, 04, 'Charan', 45000); INSERT INTO EMP_table (emp_id,Dpt_id,emp_name,salary)VALUES (105, 05, 'Lavanya', 50000); INSERT INTO EMP_table (emp_id,Dpt_id,emp_name,salary)VALUES (106, 06, 'ketan', 75000); INSERT INTO EMP_table (emp_id,Dpt_id,emp_name,salary)VALUES (107, 07, 'Keerthana', 29000); INSERT INTO EMP_table (emp_id,Dpt_id,emp_name,salary)VALUES (108, 08, 'Jyothi', 15000); INSERT INTO EMP_table (emp_id,Dpt_id,emp_name,salary)VALUES (109, 09, 'pavan', 25000); INSERT INTO EMP_table (emp_id,Dpt_id,emp_name,salary)VALUES (110, 10, 'ARUNRAJ', 20000); -- insert records in the department table insert into dpt_table(dpt_id ,dpt_name , supervisor ) values(05,'purchase','Raghav'); insert into dpt_table(dpt_id,dpt_name,supervisor)values(01,'sales','Shyam'); insert into dpt_table(dpt_id,dpt_name,supervisor)values(09,'purchase','Sundar'); insert into dpt_table(dpt_id,dpt_name,supervisor)values(09,'Accounts','Sundar'); -- 1. Find the names of all employees who work for the Accounts department. select * from emp_table where dpt_id=(select dpt_id from dpt_table where dpt_name='accounts'); -- 2. How many employees work for Accounts department? select count(*) from emp_table where dpt_id=(select dpt_id from dpt_table where dpt_name='accounts'); -- 3. What are the Minimum, Maximum and Average salary of employees working for Accounts department? select min(salary),max(salary),avg(salary) from emp_table where dpt_id=(select dpt_id from dpt_table where dpt_name='accounts'); -- 4. List the employees working for a particular supervisor. select * from emp_table where dpt_id=(select dpt_id from dpt_table where supervisor='pandu'); -- 5. Retrieve the department names for each department where only one employee works. select dpt_name from dpt_table where dpt_id in( select dpt_id from emp_table group by dpt_id having count(*)=1); -- 6. Increase the salary of all employees in the sales department by 15%. update emp_table set salary=salary+salary*15/100 where dpt_id=(select dpt_id from dpt_table where dpt_name='sales'); -- 7. Add a new Colum to the table EMPLOYEE called BONUS and compute 5% of the salary to the said field. alter table emp_table add(bonus Decimal(7,2)); update emp_table set bonus=salary5*/100; -- Display the final record set in employee table. select * from emp_table;