Pl/Sql Block For Employee Salary.

(a) Create the table EmpDetails and insert 5 records:

— Create the EmpDetails table
CREATE TABLE EmpDetails (
EmpId NUMBER PRIMARY KEY,
EmpName VARCHAR2(100),
BasicSalary NUMBER,
DA NUMBER,
NetPAY NUMBER
);

— Insert 5 records into the EmpDetails table
INSERT INTO EmpDetails (EmpId, EmpName, BasicSalary, DA, NetPAY) VALUES (1, ‘Alpha Beta’, 45000, 0, 0);
INSERT INTO EmpDetails (EmpId, EmpName, BasicSalary, DA, NetPAY) VALUES (2, ‘Alps Connect’, 55000, 0, 0);
INSERT INTO EmpDetails (EmpId, EmpName, BasicSalary, DA, NetPAY) VALUES (3, ‘Alpesh Purohit’, 60000, 0, 0);
INSERT INTO EmpDetails (EmpId, EmpName, BasicSalary, DA, NetPAY) VALUES (4, ‘IT Prashnavali’, 40000, 0, 0);
INSERT INTO EmpDetails (EmpId, EmpName, BasicSalary, DA, NetPAY) VALUES (5, ‘Code with Alpesh’, 70000, 0, 0);

— Commit the changes
COMMIT;

(b) Write a PL/SQL block to calculate DA (125% of BasicSalary) and NetPay:

BEGIN
FOR emp IN (SELECT EmpId, BasicSalary FROM EmpDetails) LOOP
— Calculate DA as 125% of BasicSalary
UPDATE EmpDetails
SET DA = emp.BasicSalary * 1.25,
NetPAY = emp.BasicSalary + (emp.BasicSalary * 1.25)
WHERE EmpId = emp.EmpId;
END LOOP;

COMMIT;

END;

(c) Write a PL/SQL block that accepts EmpId from the user and fetches that employee’s salary from the table, and checks if the salary is more than 50,000:

DECLARE
v_EmpId NUMBER;
v_BasicSalary NUMBER;
BEGIN
— Accept EmpId from user
v_EmpId := &EmpId; — This will prompt the user to enter the EmpId

-- Fetch BasicSalary for the given EmpId
SELECT BasicSalary INTO v_BasicSalary
FROM EmpDetails
WHERE EmpId = v_EmpId;

-- Check if BasicSalary is more than 50,000
IF v_BasicSalary > 50000 THEN
    DBMS_OUTPUT.PUT_LINE('Salary is more than 50,000');
ELSE
    DBMS_OUTPUT.PUT_LINE('Salary is less than or equal to 50,000');
END IF;

END;