(Solved) : Example Gave Written Assignment Module 8 Set Echo Onset Serverout Ondeclare Emprec Hremplo Q31894069 . . .
This the example hegave me from the written assignment from module 8:
SET ECHO ONSET SERVEROUT ONDECLARE 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 thisone can someone please help me please.
For this project, wewill build on the Module 8 written assignment. In the writtenassignment, we returned a simple formatted list of average guestsper room. For this project we will take what we’ve accomplished inprevious projects with the head of forms, form breaks and end offorms to provide a detailed printed report.
Using the sameprinciple as the last couple weeks’ projects update your module 8written assignment, to produce a report with title, grouped by roomnumber, ordered by date, and instead of a subtotal, it should bethe average guests per night for that room (the number from thewritten assignment). In the header of each group, by the roomnumber, you should also list how many beds the room has. Onlyinclude the following columns: Date, Last Name, Num adults, numchildren. At the bottom of the report, instead of grand total,include the overall average number of guests per night, the totalnumber of adults and the total number of children.
Make sure you havecomments, good formatting, at least one FUNCTION, and no additionaldata beyond what is described above.
Expert Answer
Answer to Example Gave Written Assignment Module 8 Set Echo Onset Serverout Ondeclare Emprec Hremplo Q31894069 . . .
OR