https://www.youtube.com/watch?v=DKYg8JahHI0&list=PLavw5C92dz9Hxz0YhttDniNgKejQlPoAn&index=5

drop table if exists salary;
create table salary
(
	emp_id		int,
	emp_name	varchar(30),
	base_salary	int
);
insert into salary values(1, 'Rohan', 5000);
insert into salary values(2, 'Alex', 6000);
insert into salary values(3, 'Maryam', 7000);


drop table if exists income;
create table income
(
	id			int,
	income		varchar(20),
	percentage	int
);
insert into income values(1,'Basic', 100);
insert into income values(2,'Allowance', 4);
insert into income values(3,'Others', 6);


drop table if exists deduction;
create table deduction
(
	id			int,
	deduction	varchar(20),
	percentage	int
);
insert into deduction values(1,'Insurance', 5);
insert into deduction values(2,'Health', 6);
insert into deduction values(3,'House', 4);


drop table if exists emp_transaction;
create table emp_transaction
(
	emp_id		int,
	emp_name	varchar(50),
	trns_type	varchar(20),
	amount		numeric
);
insert into emp_transaction
select s.emp_id, s.emp_name, x.trns_type
, case when x.trns_type = 'Basic' then round(base_salary * (cast(x.percentage as decimal)/100),2)
	   when x.trns_type = 'Allowance' then round(base_salary * (cast(x.percentage as decimal)/100),2)
	   when x.trns_type = 'Others' then round(base_salary * (cast(x.percentage as decimal)/100),2)
	   when x.trns_type = 'Insurance' then round(base_salary * (cast(x.percentage as decimal)/100),2)
	   when x.trns_type = 'Health' then round(base_salary * (cast(x.percentage as decimal)/100),2)
	   when x.trns_type = 'House' then round(base_salary * (cast(x.percentage as decimal)/100),2) end as amount	   
from salary s
cross join (select income as trns_type, percentage from income
			union
			select deduction as trns_type, percentage from deduction) x;


select * from salary;
select * from income;
select * from deduction;
select * from emp_transaction;

UNION_UNION ALL
WITH

INSERT INTO emp_transaction

WITH CTE AS (
SELECT emp_id,emp_name,TRNS_TYPE,percentage FROM salary
CROSS JOIN (
SELECT income AS TRNS_TYPE,percentage FROM income
UNION ALL 
SELECT deduction,percentage FROM deduction) type
)
SELECT CTE.emp_id,CTE.emp_name,TRNS_TYPE,(CAST((Percentage/100)*base_salary AS SIGNED)) AS Amount FROM CTE JOIN Salary ON CTE.emp_id = salary.emp_id
ORDER BY TRNS_TYPE,emp_id;

CASE WHEN

WITH CTE AS (
SELECT emp_name AS Employee, 
MAX(CASE WHEN trns_type = 'BASIC' THEN Amount END) AS BASIC,
MAX(CASE WHEN trns_type = 'Allowance' THEN Amount END) AS ALLOWANCE,
MAX(CASE WHEN trns_type = 'Others' THEN Amount END) AS OTHERS,
MAX(CASE WHEN trns_type = 'Insurance' THEN Amount END) AS INSURANCE,
MAX(CASE WHEN trns_type = 'Health' THEN Amount END) AS HEALTH,
MAX(CASE WHEN trns_type = 'House' THEN Amount END) AS HOUSE
FROM emp_transaction
GROUP BY emp_name
)
SELECT EMPLOYEE,BASIC,ALLOWANCE,OTHERS,(BASIC+ALLOWANCE+OTHERS) AS GROSS,INSURANCE,HEALTH,HOUSE,(INSURANCE+HEALTH+HOUSE) AS TOTAL_DEDUCTION
FROM CTE
ORDER BY EMPLOYEE;