create database WebContent;
use WebContent;

#Run below command to create a sample table structure:
CREATE TABLE Emp (
  Emp_ID varchar(30) NOT NULL PRIMARY KEY,
  Emp_Name varchar(30),
  Department varchar(30),
  Location varchar(30),
  commission float(30),
  phone_no varchar(30),
  country varchar(30)
  );

#Insert sample data into the table:
  INSERT INTO Emp VALUES ('1', 'Ramasundar', 'Collection', 'Bangalore', '1500', '077-25814763', 'India'),
  ('2', 'Alex', 'Collection','London', '1100', '075-12458969', 'UK'),
  ('3', 'Alford', 'Collection','New York', '1000', '044-25874365', 'USA'),
  ('4', 'Ravi Kumar', 'Collection','Bangalore', '1500', '077-45625874', 'India'),
  ('5', 'Santakumar', 'Collection', 'Chennai', '1000', '007-22388644', 'India'),
  ('6', 'Lucida', 'Collection','San Jose', '800', '044-52981425', 'USA'),
  ('7', 'Anderson', 'Collection','Brisban', '1800', '045-21447739', 'New Zealand'),
  ('8', 'Subbarao','Collection', 'Bangalore', '1400', '077-12346674', 'India'),
  ('9', 'Mukesh', 'Collection','Mumbai', '1600', '029-12358964', 'India'),
  ('10', 'McDen', 'Collection','London', '1700', '078-22255588', 'UK'),
  ('11', 'Ivan', 'Collection','Toronto', '500', '008-22544166', 'Canada'),
  ('12', 'Benjamin', 'Collection','Hampshair', '1500', '008-22536178', 'Canada'),
  ('13', 'Ramasundar', 'Collection','Bangalore', '1900', '077-25814763', 'India'),
  ('14', 'Ramesh', 'Sales', 'Bangalore', '2000', '077-25804760', 'India'),
  ('15', 'William', 'Sales','London', '1500', '075-1245800', 'UK'),
  ('16', 'Samantha', 'Sales','New York', '1800', '044-20074305', 'USA'),
  ('17', 'Suman', 'Sales','Bangalore', '2000', '077-40025874', 'India'),
  ('18', 'Padmaraj', 'Sales', 'Chennai', '1700', '007-22396644', 'India'),
  ('19', 'Bill', 'Sales','San Jose', '800', '044-52481425', 'USA'),
  ('20', 'Jackson', 'Sales','Brisban', '2400', '045-21447739', 'New Zealand'),
  ('21', 'Ratika','Sales', 'Bangalore', '1200', '077-13346674', 'India'),
  ('22', 'Anjan', 'Sales','Mumbai', '1600', '029-12357864', 'India'),
  ('23', 'Michael', 'Sales','London', '1700', '078-22250088', 'UK'),
  ('24', 'Ivy', 'Sales','Toronto', '500', '008-22544100', 'Canada'),
  ('25', 'Ben', 'Sales','Hampshair', '1900', '008-22446178', 'Canada'),
  ('26', 'Brijesh', 'Sales','Bangalore', '2000', '077-25784763', 'India'),
  ('27', 'Brijesh', 'Collection','Haryana', '100', '011-65482262', 'Canada');


#View Emp Data contents in MySQL:
select * from Emp;

#Employee count:
select COUNT(Emp_ID) as Emp_Count from Emp;

select Department, COUNT(Emp_ID) as Emp_Count from Emp group by Department;


#Total Commission for each department:
select Department, SUM(commission) as Emp_Commission from Emp group by Department;


#Average Commission for each department:
select Department, AVG(commission) as Emp_Commission from Emp group by Department;


#Top 5 employees with highest commission:
SELECT * from Emp 
where commission > (SELECT AVG(commission) from Emp) 
order by commission DESC limit 5;


#Top 5 employees with lowest commission:
SELECT * from Emp 
where commission < (SELECT AVG(commission) from Emp) 
order by commission ASC limit 5;