Answer to – SQL Assignment-3

The answer to assignment-3 is provided below.

If you haven’t visited the post, please do so by clicking here.

Solution:1

;WITH CTE AS (SELECT DName, EName, EmpNo,
Sal = CASE
WHEN DName = ‘SALES’ THEN (Sal + Sal * 0.10)
WHEN DName = ‘RESEARCH’ THEN (Sal + Sal * 0.08)
WHEN DName = ‘ACCOUNTING’ THEN (Sal + Sal * 0.05)
ELSE (Sal + Sal * 0.02)
END
FROM Emp E
JOIN Dept D ON E.DeptNo = D.DeptNo)

UPDATE e SET e.Sal = c.Sal FROM Emp e
JOIN CTE c ON e.EmpNo = c.EmpNo

SELECT * FROM Emp;

Solution:2

UPDATE emp SET sal = sal + sal*0.10 FROM emp
JOIN Dept ON Dept.DeptNo = emp.DeptNo WHERE DeptNo = ‘SALES’;

UPDATE emp SET sal = sal + sal*0.08 FROM emp
JOIN Dept ON Dept.DeptNo = emp.DeptNo WHERE DeptNo = ‘RESEARCH’;

UPDATE emp SET sal = sal + sal*0.05 FROM emp
JOIN Dept ON Dept.DeptNo = emp.DeptNo WHERE DeptNo = ‘ACCOUNTING’;

UPDATE emp SET sal = sal + sal*0.02 FROM emp
JOIN Dept ON Dept.DeptNo = emp.DeptNo WHERE DeptNo = ‘OPERATIONS’;