Pl\/Sql Block For Employee Salary.<\/p>\n\n\n\n
(a) Create the table — Create the EmpDetails table — Insert 5 records into the EmpDetails table — Commit the changes (b) Write a PL\/SQL block to calculate DA (125% of BasicSalary) and NetPay:<\/strong><\/p>\n\n\n\n BEGIN END;<\/p>\n\n\n\n (c) Write a PL\/SQL block that accepts DECLARE END;<\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" Pl\/Sql Block For Employee Salary. (a) Create the table EmpDetails and insert 5 records: — Create the EmpDetails tableCREATE TABLE EmpDetails (EmpId NUMBER PRIMARY KEY,EmpName VARCHAR2(100),BasicSalary NUMBER,DA NUMBER,NetPAY NUMBER); — Insert 5 records into the EmpDetails tableINSERT INTO EmpDetails (EmpId, EmpName, BasicSalary, DA, NetPAY) VALUES (1, ‘Alpha Beta’, 45000, 0, 0);INSERT INTO EmpDetails (EmpId, EmpName, […]<\/p>\n","protected":false},"author":1,"featured_media":101,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,42],"tags":[25,26,43],"class_list":["post-118","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bca","category-concept-of-rdbms","tag-bca","tag-bca-paper-solution","tag-concept-of-rdbms"],"_links":{"self":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts\/118","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/comments?post=118"}],"version-history":[{"count":1,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts\/118\/revisions"}],"predecessor-version":[{"id":119,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts\/118\/revisions\/119"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/media\/101"}],"wp:attachment":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/media?parent=118"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/categories?post=118"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/tags?post=118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}EmpDetails<\/code> and insert 5 records:<\/strong><\/p>\n\n\n\n
CREATE TABLE EmpDetails (
EmpId NUMBER PRIMARY KEY,
EmpName VARCHAR2(100),
BasicSalary NUMBER,
DA NUMBER,
NetPAY NUMBER
);<\/p>\n\n\n\n
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);<\/p>\n\n\n\n
COMMIT;<\/p>\n\n\n\n
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;<\/p>\n\n\n\nCOMMIT;<\/code><\/pre>\n\n\n\nEmpId<\/code> from the user and fetches that employee’s salary from the table, and checks if the salary is more than 50,000:<\/strong><\/p>\n\n\n\n
v_EmpId NUMBER;
v_BasicSalary NUMBER;
BEGIN
— Accept EmpId from user
v_EmpId := &EmpId; — This will prompt the user to enter the EmpId<\/p>\n\n\n\n-- Fetch BasicSalary for the given EmpId\nSELECT BasicSalary INTO v_BasicSalary\nFROM EmpDetails\nWHERE EmpId = v_EmpId;\n\n-- Check if BasicSalary is more than 50,000\nIF v_BasicSalary > 50000 THEN\n DBMS_OUTPUT.PUT_LINE('Salary is more than 50,000');\nELSE\n DBMS_OUTPUT.PUT_LINE('Salary is less than or equal to 50,000');\nEND IF;<\/code><\/pre>\n\n\n\n