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

Answer:

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' )

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.