Question is as follow:
Database Schema:
/* Murach Chapter 13 Figure 13-4 A SQL Script that Uses Variables*/ DECLARE max invoice total NUMBER(9,2); -Variable of the same type as invoice_total column values min-invoice-total invoices . invoice-total%TYPE;?Variable type is grabbed from the column percent_difference NUMBER(4,2); count invoice id NUMBER(8); -- A count is an integer vendor_id var NUMBER95; Declare a variable and assign it a value BEGIN BESELECT HAX( nvotce. total), HIN( tavotce. total), coUNT(tnvotce id) INTO max invoice_total, min invoice total, count invoice id FROM ap.invoices- HERE vendor_1d = vendor-id-var; percent difference : (nax_tnvotce total - mtn tnvotce total) min_invoice_total 100; send the DBMS-OUTPUT . PUT-LINE ("Maximum invoice: $' 11 max-invoice-total); DBMS OUTPUT.PUT_ LINE( 'Minimum invoice: II min_invoice_total); DBMS_OUTPUT . PUT-LINE("Percent difference: %' ll output to DBHS-OUTPUT -- ROUND (percent_difference, 2)); DBMS OUTPUT.PUT_LINE Number of invoices: '11 count_invoice_id) END * Task 1. From the tool library in the jdoe22 schema, write a script to calculate and output the average tine tools stay on hold and the average time tools stay on loan. The tine a tool stays on hold is the number of days between hold status_change date and hold_date for all holds with a hold_active status indicating the hold is not active. The time a tool stays on loan is the number of days between loan status_change date and tool_out_date. Also calculate and output the percent difference between the average loan tine and the average hold time.* /* Murach Chapter 13 Figure 13-4 A SQL Script that Uses Variables*/ DECLARE max invoice total NUMBER(9,2); -Variable of the same type as invoice_total column values min-invoice-total invoices . invoice-total%TYPE;?Variable type is grabbed from the column percent_difference NUMBER(4,2); count invoice id NUMBER(8); -- A count is an integer vendor_id var NUMBER95; Declare a variable and assign it a value BEGIN BESELECT HAX( nvotce. total), HIN( tavotce. total), coUNT(tnvotce id) INTO max invoice_total, min invoice total, count invoice id FROM ap.invoices- HERE vendor_1d = vendor-id-var; percent difference : (nax_tnvotce total - mtn tnvotce total) min_invoice_total 100; send the DBMS-OUTPUT . PUT-LINE ("Maximum invoice: $' 11 max-invoice-total); DBMS OUTPUT.PUT_ LINE( 'Minimum invoice: II min_invoice_total); DBMS_OUTPUT . PUT-LINE("Percent difference: %' ll output to DBHS-OUTPUT -- ROUND (percent_difference, 2)); DBMS OUTPUT.PUT_LINE Number of invoices: '11 count_invoice_id) END * Task 1. From the tool library in the jdoe22 schema, write a script to calculate and output the average tine tools stay on hold and the average time tools stay on loan. The tine a tool stays on hold is the number of days between hold status_change date and hold_date for all holds with a hold_active status indicating the hold is not active. The time a tool stays on loan is the number of days between loan status_change date and tool_out_date. Also calculate and output the percent difference between the average loan tine and the average hold time.*
