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

EmployeeID
1

LastName FirstName Phone Email

Stuart Anne 206-527-0010 [e-mail]

2 Stuart George 206-527-0011 [e-mail]

3 Stuart Mary 206-527-0012 [e-mail]

4 Orange William 206-527-0013 [e-mail]

5 Griffith John 206-527-0014 [e-mail]

Figure 7-44
Sample Data for the QaCS
EMOLYEE table

Using the QACS database, create an SQL script named QACS-Create-Views-and-
Functions.sql to answer questions L through Q.

L. Write an SQL statement to create a view called SaleSummaryView that con-
tains SALE.SaleID, SALE.SaleDate, SALE_ITEM.SaleItemID, SALE_ITEM.ItemID,
ITEM.ItemDescription, and ITEM.ItemPrice. Run the statement to create the view,
and then test the view with an appropriate SQL SELECT statement.

M. 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).

N. Write an SQL statement to create a view called CustomerSaleSummaryView that contains
SALE.SaleID, SALE.SaleDate, CUSTOMER.LastName, CUSTOMER,FirstName, SALE_
ITEM.SaleItemID, SALE_ITEM.ItemID, ITEM.ItemDescription, and ITEM.ItemPrice.
Run the statement to create the view, and then test the view with an appropriate SQL
SELECT statement.

O. Write an SQL statement to create a view called CustomerLastNameFirstSaleSummaryView
that contains SALE.SaleID, SALE.SaleDate, the concatenated customer name using
the LastNameFirst function, SALE_ITEM.SaleItemID, SALE_ITEM.ItemID, ITEM
.ItemDescription, and ITEM.ItemPrice. Run the statement to create the view, and then
test the view with an appropriate SQL SELECT statement.

P. Write an SQL statement to create a view called CustomerSaleHistoryView that (1) in-
cludes all columns of CustomerOrderSummaryView except SALE_ITEM.ItemNumber
and SALE_ITEM.ItemDescription; (2) groups orders by CUSTOMER.LastName,
CUSTOMER.FirstName, and SALE.SaleID, in that order; and (3) sums and averages
SALE_ITEM.ItemPrice for each order for each customer. Run the statement to create
the view, and then test the view with an appropriate SQL SELECT statement.

Q. Write an SQL statement to create a view called CustomerSaleCheckView that uses
CustomerSaleHistoryView and that shows that any customers for whom the sum of
SALE_ITEM.ExtendedPrice is not equal to SALE.SubTotal. Run the statement to cre-
ate the view, and then test the view with an appropriate SQL SELECT statement.

R. Explain, in general terms, how you will use triggers to enforce minimum cardinality
actions as required by your design. You need not write the triggers, just specify which
triggers you need and describe, in general terms, their logic.