SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 353

P. Write an SQL statement to undo the UnitPrice modification in part N.

Q. Do not run your answer to the following question in your actual database! Write
the fewest number of DELETE statements possible to remove all the data in your data-
base but leave the table structures intact.

Using the hSD database, create an SQL script named hSD- Create-Views-and-
Functions.sql to answer questions R through T.

R. Write an SQL statement to create a view called InvoiceSummaryView that contains
INVOICE.InvoiceNumber, INVOICE.InvoiceDate, LINE_ITEM.LineNumber, SALE_
ITEM.ItemID, PRODUCT.Description, and LINE_ITEM.UnitPrice. Run the statement
to create the view, and then test the view with an appropriate SQL SELECT statement.

S. Create and test a user-defined function named LastNameFirst that combines two pa-
rameters named FirstName and LastName into a concatenated name field formatted
LastName, FirstName (including the comma and space).

T. Write an SQL statement to create a view called CustomerInvoiceSummaryView that
contains INVOICE.InvoiceNumber, INVOICE.InvoiceDate, the concatenated customer
name using the LastNameFirst function, CUSTOMER.EmailAddress, and INVOICE.
Total. Run the statement to create the view, and then test the view with an appropriate
SQL SELECT statement.

Assume that The Queen Anne Curiosity Shop designs a database with the following
tables:

CUSTOMER ( CustomerID , LastName, FirstName, Address, City, State, ZIP, Phone,
Email)
EMPLOYEE ( EmployeeID , LastName, FirstName, Phone, Email)
VENDOR ( VendorID , CompanyName, ContactLastName, ContactFirstName,
Address, City, State, ZIP, Phone, Fax, Email)
ITEM ( ItemID , ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID )
SALE ( SaleID , CustomerID, EmployeeID , SaleDate, SubTotal, Tax, Total)
SALE_ITEM ( SaleID , SaleItemID , ItemID , ItemPrice)

The referential integrity constraints are:

CustomerID in SALE must exist in CustomerID in CUSTOMER
VendorID in ITEM must exist in VendorID in VENDOR
CustomerID in SALE must exist in CustomerID in CUSTOMER
EmployeeID in SALE must exist in EmployeeID in EMPLOYEE
SaleID in SALE_ITEM must exist in SaleID in SALE
ItemID in SALE_ITEM must exist in ItemID in ITEM

Assume that CustomerID of CUSTOMER, EmployeeID of EMPLOyEE, ItemID of ITEM,
SaleID of SALE, and SaleItemID of SALE_ITEM are all surrogate keys with values as
follows:

CustomerID Start at 1 Increment by 1

EmployeeID Start at 1 Increment by 1

VendorID Start at 1 Increment by 1

ItemID Start at 1 Increment by 1

SaleID Start at 1 Increment by 1