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.
USE AP;
SELECT VendorName, First InvoiceDate, InvoiceTota1
FROM Invoices JOIN
(SELECT vendor1D, MIN( InvoiceDate) AS FirstInvoiceDate
FROM Invoices
GROUP BY vendor1D) AS Firstlnvoice
ON (Invoices AND
Invoices.InvoiceDate
FirstInvoice.FirstInvoiceDate)
JOIN vendors
ON Invoices.VendorID = Vendors.VendorID
ORDER BY VendorName, FirstInvoiceDate;
4. 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.
1. Create a stored procedure named spBalanceRange that accepts three optional parameters. The procedure should return 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, as shown in figure 15-5. @BalanceMin and @BalanceMax are parameters used to specify the requested range of balances due. If called with no parameters or with a maximum value of 0, the procedure should return all invoices with a balance due.
8. Create a trigger for the Invoices table that automatically inserts the vendor name and address for a paid invoice into a table named ShippingLabels. The trigger should fire any time the PaymentTotal column of the Invoices table is updated. The structure of the ShippingLabels table is as follows:
CREATE TABLE ShippingLabels
(VendorName
varchar (50),
VendorAddress1 varchar (50),
VendorAddress2 varchar (50),
VendorCity varchar (50),
VendorState char (2),
VendorZipCode varchar (20));
Use this UPDATE statement to test the trigger:
UPDATE Invoices
SET paymentotal = 67.92, paymentDate = '2016-04-23'
WHERE Invoice1D = 100;