I really need help so can someone help me please. Can someone help me please. Here is the questions?

Review the example given. This example displays the average salary per department. It contains a function that is called inside the TO_CHAR statement during output. Even though you can use a SQL statement to accomplish the same thing, using a function gives us more flexibility in manipulating values, formatting output, and cleaner code in our PL/SQL blocks, plus it provides a simple example you can verify using the avg function in SQL with a group by clause. Run both examples below, SQL Statement on the prompt and the example script.

select department_id, avg(salary) as "Avg Salary by Dept" from hr.employees group by department_id order by department_id;

EXAMPLE FILE: Here is the example he gave

SET ECHO ON

SET SERVEROUT ON

DECLARE

emp_rec hr.employees%rowtype;

crnt_dept hr.employees.department_id%type := 0;

found_rows BOOLEAN := FALSE; --variable to test for no_data_found exception in the FOR LOOP

-- DECLARE AND DEFINE FUNCTION TO CALC AVG SALARY BY DEPT

FUNCTION avg_salary(

dept_id IN NUMBER)

RETURN NUMBER

AS

average NUMBER;

BEGIN

SELECT AVG(SALARY)

INTO average

FROM HR.EMPLOYEES

WHERE department_id = dept_id;

RETURN average;

EXCEPTION

WHEN others THEN

RETURN NULL;

END;

BEGIN

DBMS_OUTPUT.PUT_LINE('---------------------------------------');

DBMS_OUTPUT.PUT_LINE(' Average Salary by Department ');

DBMS_OUTPUT.PUT_LINE('---------------------------------------');

-- LOOP CALLS FUNCTION AVG_SALARY ON EACH CHANGE IN DEPT ID/DISPLAYS AVG SALARY

FOR emp_rec IN

(SELECT *

FROM hr.employees

ORDER BY department_id)

LOOP

found_rows := TRUE; --if data exist, sets variable to TRUE, so IF statement doesn't run

IF crnt_dept != emp_rec.department_id THEN

crnt_dept := emp_rec.department_id;

DBMS_OUTPUT.PUT_LINE('Department '||crnt_dept||': '||TO_CHAR(avg_salary(crnt_dept),'$99999.99'));

END IF;

END LOOP;

/* Used to throw exception for FOR LOOP when no rows are returned from SELECT query */

IF NOT found_rows THEN

RAISE NO_DATA_FOUND;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No data found.');

END;

/

For the assignment, create an average function similar to the example, and use that function to print to screen the average # of guests per room, ordered by room number. Your output will have three columns 1) room number, 2) avg guests per night, 3) # of nights booked. Make sure you round the average column to two places right of the decimal place, and base it on total guests, not just adults. Your output should be similar to the SQL statement below. Run this SQL statement from the PL/SQL prompt:

SELECT ROOMNUM, AVG(ADULTCNT + CHILDCNT) AS "AVG GUEST PER NIGHT", COUNT(*) AS "BOOKED NIGHTS" FROM DDI.LEDGER_VIEW GROUP BY ROOMNUM ORDER BY ROOMNUM;

You output should

1) Use a function 2) Be carefully formatted, something similar to the example, but also with column headers. 3) Code should use clear variable names. If you reuse code from other projects you shouldn't keep a variable named Salary and use it for room number without changing the name. 4) Include the exception handler call for the loop. Remember previously, if the for loop doesn't find any records it will not automatically call the no_data_found exception handler. You can test it by asking for the department_id at run time and choosing one that doesn't exist. 5) Include comments, header and code