Display Department name and Total Salary Expenditure of the Department

Write a PL/SQL block to display the department name and the total salary expenditure of the department from the Employee table.

Table name: EMPLOYEE

COLUMN	DATATYPE	CONS.
EMP_ID	NUMBER(5)	PK
EMP_NAME	VARCHAR2(25)	NOT NULL
SALARY	NUMBER(10,2)
DEPT	VARCHAR2(25)

EMP_ID  EMP_NAME  SALARY  DEPT
101     Tom       54000   MECH    
102     William   43000   CSE
103     John      34560   MECH
104     Smith     56000   CSE
105     Steve     23450   IT
            

Sample Output:

 
Department-wise salary expenditure:
IT department, total salary is 23450
CSE department, total salary is 99000
MECH department, total salary is 88560
            

(Hint: Data is case sensitive. Use '/' to terminate the PL/SQL block)

Here's a step-by-step explanation of the above provided PL/SQL code:

SET SERVEROUTPUT ON: This command enables the display of output in the console.

DECLARE: This keyword signals the start of the declaration section where variables and cursors are defined.

salary Employee.SALARY%type: This line declares a variable named 'salary' with the same data type as the 'SALARY' column in the 'Employee' table.

departmentname Employee.DEPT%type: Similarly, this line declares a variable named 'departmentname' with the same data type as the 'DEPT' column in the 'Employee' table.

CURSOR sal IS SELECT DEPT, SUM(SALARY) FROM Employee GROUP BY DEPT: This declares a cursor named 'sal' which retrieves department names and the total salary expenditure for each department from the 'Employee' table.

BEGIN: This keyword marks the beginning of the executable section of the PL/SQL block.

DBMS_OUTPUT.PUT_LINE('Department-wise salary expenditure:'): This line prints a header indicating that the following output will display department-wise salary expenditure.

OPEN sal: This command opens the cursor 'sal' to start fetching data from it.

LOOP: This keyword starts a loop that iterates through the cursor result set.

FETCH sal into departmentname, salary: This command fetches the next row of data from the cursor 'sal' into the variables 'departmentname' and 'salary'.

EXIT WHEN sal%NOTFOUND: This line checks if there are no more rows to fetch from the cursor 'sal' and exits the loop if true.

DBMS_OUTPUT.PUT_LINE(departmentname || ' department, total salary is ' || salary): This line prints the department name concatenated with the total salary for that department.

END LOOP: This marks the end of the loop.

END: This marks the end of the PL/SQL block.

In summary, this PL/SQL code retrieves department names and their respective total salary expenditures from the 'Employee' table and prints them out in the console, providing users with a clear overview of department-wise salary expenditure.