1. Write an anonymous PL/SQL script that declares and sets a variable that’s equ

Question

1. Write an anonymous PL/SQL script that declares and sets a variable that’s equal to the count of all rows in the Invoices table that have a balance due that’s greater than or equal to $5,000. Then, the script should display a message that looks like this: This solution uses a COUNT(*) function. 3 invoices exceed $5,000. 2. Write an anonymous PL/SQL script that uses variables to get (1) the count of all of the invoices in the Invoices table that have a balance due and (2) the sum of the balances due for all of those invoices. If that total balance due is greater than or equal to $50,000, the script should display a message like this: Uses an IF/THEN/ELSE after you determine the number of invoices that have a balance due. Number of unpaid invoices is 40. Total balance due is $66,796.24. Otherwise, the script should display this message: Total balance due is less than $50,000. 3. Write an anonymous PL/SQL script that creates a cursor consisting of vendor_name, invoice_number, and balance_due for each invoice with a balance due that’s greater than or equal to $5,000. The rows in this cursor should be sorted in descending sequence by balance due. Then, for each invoice, display the balance due, invoice number, and vendor name so it looks something like this: Use a CURSOR $19,351.18 P-0608 Malloy Lithographing Inc 4. Enhance your solution to exercise 3 so it shows the invoice data in three groups based on the balance due amounts with these headings: You will use the CURSOR three different times to loop through using the different criteria as defined below with the headings. $20,000 or More $10,000 to $20,000 $5,000 to $10,000 Each group should have a heading followed by the data for the invoices that fall into that group. Also, the groups should be separated by one blank line. 5. Enhance your solution to exercise 3 so it uses a substitution variable to set a bind variable that you use to determine what the minimum balance due should be for the invoices that the SELECT statement is going to retrieve. You should also use this bind variable to display a heading like this before the list of invoices: You are using the bind variable to set the balance due minimum. In this case 2000 is not a hard coded value but is set using a bind variable. Invoice amounts greater than or equal to $2,000 ================================================== where 2,000 is the value of the bind variable.

Explanation / Answer

1)

PL/SQL script:

DECLARE @balance int

SELECT @balance = COUNT(*)
FROM INVOICES

IF (@balance >= 5000)
BEGIN
    PRINT N 'invoices exceed $5,000';
END
ELSE
BEGIN
    PRINT N 'invoices not exceed $5,000';
END

2)

PL/SQL script:

DECLARE @BalanceDue INT;
DECLARE @SumBalance MONEY;
SET @BalanceDue= (SELECT COUNT(InvoiceID) FROM INVOICES);
SET @SumBalance = (SELECT AVG (ListCost) FROM INVOICES);
IF @BalanceDue > 50000
    BEIGN
        PRINT 'The number of invoices that have a balance due. ';
        PRINT 'The sum of the balances due for all of those invoices $' + CONVERT (varchar,@SumBalance,1);
    END;
ELSE
    PRINT 'The number of products is less than 50000';

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.