Murach’s SQL for SQL Server – Exercise 14 – Guaranteed 100% score
1. Create a stored procedure in the AP database named spWhichTable that accepts a
column name and returns the name of the table or tables that have a column by that
name. Code a statement that calls the procedure.
2. 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, as shown in figure 14-5 of the text. @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
3. Code three calls to the procedure in exercise 2:
(1) passed by position with @VendorVar=’Z%’ and no balance range
(2) passed by name with @VendorVar omitted and a balance range from $200 to
(3) passed by position with a balance due that’s less than $200 filtering for vendors
whose names begin with C or F
4. 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, the procedure should return an error message
describing the 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.
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 VendorName, InvoiceNumber, InvoiceDueDate,
InvoiceTotal – CreditTotal – PaymentTotal AS Balance
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceID = dbo.fnUnpaidInvoiceID()
6. Create a table-valued function named fnDateRange, similar to the stored procedure
of exercise 4. 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, 2002.
7. Use the function you created in exercise 6 in a SELECT statement that returns five
columns: VendorName and the four columns returned by the function.
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
9. A column that accepts null values but has a unique constraint can only have a single
row with a null value. Write a trigger that prohibits duplicates, except for nulls. Use
the following table. If an INSERT or UPDATE statement creates a duplicate value in
the NoDupName column, roll back the statement and return an error message.
CREATE TABLE TestUniqueNulls
(RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL)
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at firstname.lastname@example.org.
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at email@example.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at firstname.lastname@example.org.
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************