payroll_system.sql

create database payroll_system;
show databases;
use payroll_system;

CREATE TABLE departmentp(
did INT primary key,
dname VARCHAR(50),
loc VARCHAR(50)
);

INSERT INTO departmentp VALUES
(10,'HR','Mumbai'),
(20,'Finance','Delhi'),
(30,'IT','Bangalore'),
(40,'Marketing','Chennai');

create table employeep(
eid int primary key,
ename varchar(50),
gender varchar(50),
did int,
foreign key (did) references departmentp(did),
hiredate date
);

insert into employeep(eid,ename,gender,did,hiredate)
values(101,'Arpita','Female',10,'2022-01-10'),
(102,'Rahul','Male',20,'2021-03-15'),
(103,'Sneha','Female',30,'2020-07-21'),
(104,'Amit','Male',40,'2019-11-05'),
(105,'Pooja','Female',10,'2023-02-18'),
(106,'Rohit','Male',20,'2022-06-12'),
(107,'Neha','Female',30,'2021-09-25'),
(108,'Karan','Male',40,'2020-12-30'),
(109,'Anjali','Female',10,'2023-01-08'),
(110,'Vikas','Male',20,'2019-04-19'),
(111,'Megha','Female',30,'2021-08-11'),
(112,'Suresh','Male',40,'2022-10-05'),
(113,'Priya','Female',10,'2020-05-14'),
(114,'Nikhil','Male',20,'2023-03-22'),
(115,'Kavya','Female',30,'2021-12-01');

create table salaryp(
sid int,
sal decimal(9,2),
bonus int ,
deduction int ,
eid int primary key,
foreign key (eid) references employeep (eid));

INSERT INTO salaryp VALUES
(1,35000.00,2000,500,101),
(2,42000.00,2500,700,102),
(3,38000.00,1800,600,103),
(4,45000.00,3000,800,104),
(5,32000.00,1500,400,105),
(6,40000.00,2200,650,106),
(7,37000.00,2000,550,107),
(8,46000.00,3200,900,108),
(9,34000.00,1700,450,109),
(10,41000.00,2400,700,110),
(11,39000.00,2100,600,111),
(12,47000.00,3300,950,112),
(13,36000.00,1900,500,113),
(14,43000.00,2600,750,114),
(15,44000.00,2700,800,115);

create table attendencep(
at_id int ,
work_days int, 
leavez int,
eid int primary key,
foreign key (eid) references employeep (eid));

INSERT INTO attendencep VALUES
(1,22,2,101),
(2,24,1,102),
(3,21,3,103),
(4,23,2,104),
(5,20,4,105),
(6,25,1,106),
(7,22,2,107),
(8,24,1,108),
(9,21,3,109),
(10,23,2,110),
(11,22,2,111),
(12,24,1,112),
(13,20,4,113),
(14,23,2,114),
(15,25,1,115);

select * from employeep;
select * from departmentp;
select * from salaryp;
select * from attendencep;

-- Show which employee works in which department.
select e.ename, d.dname
from employeep e
join departmentp d
on e.did=d.did;

-- Employee Salary Details.
select e.ename,s.sal
from employeep e 
join salaryp s
on e.eid=s.eid;

-- Employees gender,sal along with bonus and deduction.
select e.ename,e.gender,s.sal,s.bonus,s.deduction
from employeep e
join salaryp s 
on e.eid=s.eid;

-- Total Salary of Each Employee.
select eid,(sal+bonus)-deduction as total_sal
from salaryp;

-- Complete Payroll Report.
select e.eid,a.at_id,d.did,s.sal,s.bonus,s.deduction,(s.sal+s.bonus-s.deduction) as total_sal
from employeep e 
join departmentp d on d.did=e.did
join attendencep a on e.eid=a.eid
JOIN salaryp s ON e.eid = s.eid; 

-- Employee Attendance Report
select a.eid,e.ename,a.at_id,a.work_days,a.leavez
from attendencep a 
join employeep e 
on e.eid=a.eid;

--  Average Salary
select avg(sal) from salaryp;

-- Highest Salary
select max(sal) from salaryp;

-- Average Salary by Department
select d.dname, avg(s.sal) as avg_sal
from employeep e  
join departmentp d on e.did=d.did
join salaryp s on e.eid=s.eid
group by d.dname;

-- Employees With More Than 2 Leaves
select eid,leavez from attendencep
where leavez>2;

-- Count Employees in Each Department
select count(e.ename) as count_emp,dname
from employeep e
join departmentp d on d.did=e.did
group by d.dname;

-- Payroll View
create view  payroll_report as
select e.ename,d.dname,s.sal,s.bonus,s.deduction,
(s.sal + s.bonus - s.deduction) as total_salary
from employeep e
join departmentp d on e.did = d.did
join salaryp s on e.eid = s.eid;

select * from payroll_report;