View the step-by-step solution to: 1. Write a script that declares and sets a variable that’s

1. Write a script that declares and sets a variable that’s equal to the total outstanding balance due. If that balance due is greater than $10,000.00, the script should return a result set consisting of VendorName, InvoiceNumber, InvoiceDueDate, and Balance for each invoice with a balance due, sorted with the oldest due date first. If the total outstanding balance due is less than $10,000.00, return the message “Balance due is less than $10,000.00.” 2. The following script uses a derived table to return the date and invoice total of the earliest invoice issued by each vendor. Write a script that generates the same result set but uses a temporary table in place of the derived table. Make sure your script tests for the existence of any objects it creates. SELECT VendorName, FirstInvoiceDate, InvoiceTotal FROM Invoices JOIN (SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate FROM Invoices GROUP BY VendorID) AS FirstInvoice ON (Invoices.VendorID = FirstInvoice.VendorID AND Invoices.InvoiceDate = FirstInvoice.FirstInvoiceDate) JOIN Vendors ON Invoices.VendorID = Vendors.VendorID ORDER BY VendorName, FirstInvoiceDate 3. Create a stored procedure named spBalanceRange that accepts three optional parameters. The procedure returns a result set consisting of VendorName, InvoiceNumber, and Balance for each invoice with a balance due, sorted with largest balance due first. The parameter @VendorVar is a mask that’s used with a LIKE operator to filter by vendor name. @BalanceMin and @BalanceMax are parameters used to specify the requested range of balances due. If called with no parameters, the procedure should return all invoices with a balance due. 4. Code three calls to the procedure created in problem 3 as follows: (a) pass the parameters by position with @VendorVar='Z%' and no balance range (b) pass the parameters by name with @VendorVar omitted and a balance range from $200 to $1000 (c) pass the parameters by position with a balance due that’s less than $200 filtering for vendors whose names begin with C or F. 5. Create a scalar-valued function named fnUnpaidInvoiceID that returns the InvoiceID of the earliest invoice with an unpaid balance. Test the function in the following SELECT statement: SELECT VendorName, InvoiceNumber, InvoiceDueDate, InvoiceTotal - CreditTotal - PaymentTotal AS Balance FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceID = dbo.fnUnpaidInvoiceID() Extra Credit (5 points): 1. Write a script that generates the same result set as the code shown in problem 2, but uses a view instead of a derived table. Also write the script that creates the view. Make sure that your script tests for the existence of the view. The view doesn’t need to be redefined each time the script is executed. 2. Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. Name the column CountOfTable, where Table is the chosen table name. Hint: Use the sys.tables catalog view. 3. Create a stored procedure named spDateRange that accepts two parameters, @DateMin and @DateMax, with data type varchar and default value null. If called with no parameters or with null values, raise an error that describes the invalid syntax. If called with non-null values, validate the parameters. Test that the literal strings are valid dates and test that @DateMin is earlier than @DateMax. If the parameters are valid, return a result set that includes the InvoiceNumber, InvoiceDate, InvoiceTotal, and Balance for each invoice for which the InvoiceDate is within the date range, sorted with earliest invoice first. 4. Code a call to the stored procedure created in exercise 3 that returns invoices with an InvoiceDate between April 10 and April 20, 2008. This call should also catch any errors that are raised by the procedure and print the error number and description. 5. Create a table-valued function named fnDateRange, similar to the stored procedure of exercise 3. The function requires two parameters of data type smalldatetime. Don’t validate the parameters. Return a result set that includes the InvoiceNumber, InvoiceDate, InvoiceTotal, and Balance for each invoice for which the InvoiceDate is within the date range. Invoke the function from within a SELECT statement to return those invoices with InvoiceDate between April 10 and April 20, 2008. im using sql servers

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors and customizable flashcards—available anywhere, anytime.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access or to earn money with our Marketplace.

    Browse Documents
  • 890,990,898

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
  • 890,990,898

    Flashcards

    Browse existing sets or create your own using our digital flashcard system. A simple yet effective studying tool to help you earn the grade that you want!

    Browse Flashcards