Question

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.

Answer 1

CREATE VIEW InvoiceBasic AS

SELECT VendorName, InvoiceNumber, InvoiceTotal

FROM Invoices JOIN Vendors ON Invoices.InvoiceID = Vendors.VendorID

WHERE left(VendorName,1) IN ('N' , 'O ' , 'P' )

Step-by-step explanation:

CREATE VIEW creates a view named InvoiceBasic

SELECT statements selects columns VendorName, InvoiceNumber and InvoiceTotal from Invoices table

JOIN is used to combine rows from Invoices and Vendors table, based on a InvoiceID and VendorsID columns.

WHERE clause specified a condition that the first letter of the vendor name is N, O, or P. Here left function is used to extract first character of text from a VendorName column.