This the example he gave me from the written assignment from module 8:

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; /

I am using SQLPLUS

I am lose with this one can someone please help me please.

For this project, we will build on the Module 8 written assignment. In the written assignment, we returned a simple formatted list of average guests per room. For this project we will take what we've accomplished in previous projects with the head of forms, form breaks and end of forms to provide a detailed printed report.

Using the same principle as the last couple weeks' projects update your module 8 written assignment, to produce a report with title, grouped by room number, ordered by date, and instead of a subtotal, it should be the average guests per night for that room (the number from the written assignment). In the header of each group, by the room number, you should also list how many beds the room has. Only include the following columns: Date, Last Name, Num adults, num children. At the bottom of the report, instead of grand total, include the overall average number of guests per night, the total number of adults and the total number of children.

Make sure you have comments, good formatting, at least one FUNCTION, and no additional data beyond what is described above.