create table EmpData(EmpId int primary key, FirstName varchar(10), LastName varchar(10),
HireDate date, Spouse varchar(10), WHAllowance int, BenPlanId int, foreign key (BenPlanId)
references Benifits(BenPlanId) );
create table Benifits (BenPlanId int primary key, PlanName varchar(10), BaseCost int, SpouseCost
int);
create table Department (DeptId int primary key, Dept varchar(10), Title varchar(10), Grade char);
create table Work (WorkId int primary key, PayRate int, Salaried varchar(3), EmpId int, DeptId int ,
foreign key (EmpId) references EmpData(EmpId), foreign key (DeptId) references
Department(DeptId) );
-- ++++++++++++++++++++++++++++++++
insert into Benifits values ( 101, "Health", 200, 150);
insert into Benifits values ( 102, "Life", 300, 250);
insert into Benifits values ( 103, "Car", 150, 100);
insert into Benifits values ( 104, "Home", 200, 200);
insert into Benifits values ( 105, "Pearsonal", 300, 280);
-- ===================================
insert into EmpData values (202, "Ish", "Kale", "2022-11-18", "-", 1, 102);
insert into EmpData values (203, "Shubham", "Jadhav", "2022-12-19", "-", 2, 103);
insert into EmpData values (204, "Suraj", "Jedhe", "2022-10-14", "-", 3, 101);
insert into EmpData values (205, "Saurav", "Mane", "2022-12-8", "-", 1, 102);
insert into EmpData values (206, "Megha", "Giri", "2022-12-8", "Someone", 1, 104);
-- ===================================
insert into Department values (1, "CS", "Tutor", "A");
insert into Department values (2, "EE", "Tutor", "B");
insert into Department values (3, "Mech", "Tutor", "C");
-- ==============================
insert into Work values (301, 500, "YES", 201, 3);
insert into Work values (302, 200, "YES", 203, 2 );
insert into Work values (303, 450, "YES", 202, 1 );
insert into Work values (304, 550, "YES", 204, 2 );
insert into Work values (305, 50, "YES", 204, 3);
insert into Work values (306, 5000, "YES", 206, 3);
-- ==================================
use upthink;
-- ==================================
select * from EmpData;
Select * from Benifits;
select * from Work;
Select * from Department;
select * from Employee_details;
-- =================================
(select Empdata.EmpId, PlanName, Benifits.BaseCost, Benifits.SpouseCost, EmpData.Spouse,
EmpData.WHAllowance,
if((WHAllowance = 1), 0.33, if((WhAllowance = 2), 0.25, if((WhAllowance = 3), 0.17, if((WhAllowance
= 4), 0.11, 0.08)))) As TaxRate, PayRate
From EmpData
right join Benifits
On EmpData.BenPlanId = Benifits.BenPlanId
right join
Work
on
EmpData.EmpId = Work.EmpId
union
select Empdata.EmpId, PlanName, Benifits.BaseCost, Benifits.SpouseCost, EmpData.Spouse,
EmpData.WHAllowance,
if((WHAllowance = 1), 0.33, if((WhAllowance = 2), 0.25, if((WhAllowance = 3), 0.17, if((WhAllowance
= 4), 0.11, 0.08)))) As TaxRate, PayRate
From EmpData
left join Benifits
On EmpData.BenPlanId = Benifits.BenPlanId
left join
Work
on
EmpData.EmpId = Work.EmpId
);
-- =====================================
create view Employee_details AS (select Empdata.EmpId, PlanName, Benifits.BaseCost,
Benifits.SpouseCost, EmpData.Spouse, EmpData.WHAllowance,
if((WHAllowance = 1), 0.33, if((WhAllowance = 2), 0.25, if((WhAllowance = 3), 0.17, if((WhAllowance
= 4), 0.11, 0.08)))) As TaxRate, PayRate
From EmpData
right join Benifits
On EmpData.BenPlanId = Benifits.BenPlanId
right join
Work
on
EmpData.EmpId = Work.EmpId
union
select Empdata.EmpId, PlanName, Benifits.BaseCost, Benifits.SpouseCost, EmpData.Spouse,
EmpData.WHAllowance,
if((WHAllowance = 1), 0.33, if((WhAllowance = 2), 0.25, if((WhAllowance = 3), 0.17, if((WhAllowance
= 4), 0.11, 0.08)))) As TaxRate, PayRate
From EmpData
left join Benifits
On EmpData.BenPlanId = Benifits.BenPlanId
left join
Work
on
EmpData.EmpId = Work.EmpId
);
