Murach’s SQL for SQL Server – Exercise 12 – Guaranteed 100% Score
1. Write a CREATE VIEW statement that defines a view named InvoiceBasic that
returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write
a SELECT statement that returns all of the columns in the view, sorted by
VendorName, where the first letter of the vendor name is N, O, or P.
2. Create a view named Top10PaidInvoices that returns three columns for each vendor:
VendorName, LastInvoice (the most recent invoice date), and SumOfInvoices (the
sum of the InvoiceTotal column). Return only the 10 vendors with the largest
SumOfInvoices and include only paid invoices.
3. Create an updatable view named VendorAddress that returns the VendorID, both
address columns, and the city, state, and zip code columns for each vendor. Then,
write a SELECT query to examine the result set where VendorID=4. Next, write an
UPDATE statement that changes the address so that the suite number (Ste 260) is
stored in VendorAddress2 rather than in VendorAddress1. To verify the change,
rerun your SELECT query.
STATUS
4. (If you’ve done exercise 10-5) Query each of the four information schema views
listed in figure 12-9 of the text that return information about constraints. Which of
these four views returns the code for a check constraint clause? What is the name of
the column in which the code is returned?
5. Using the Enterprise Manager, create a view named AccountByVendor that returns
the sum of InvoiceLineItemAmounts in the InvoiceLineItems table, grouped by
VendorName and AccountDescription. Check the joins that are automatically coded
in the FROM clause to be sure they’re correct.
6. Using the Enterprise Manager, modify the InvoiceBasic view created in exercise 1 to
sort the result set by VendorName. What clause does the system automatically code
to allow the use of an ORDER BY clause in the view?
