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

N. Create and test a user-defined function named StoreContactAndPhone that combines
two parameters named StoreContact and ContactPhone into a concatenated data field
formatted StoreContact: ContactPhone (including the colon and space).

O. Write an SQL statement to create a view called StorePurchaseHistoryView that shows
STORE.StoreName, STORE.Phone, STORE.Contact, ITEM.ItemID, ITEM.PurchaseDate,
ITEM.ItemDescription, and ITEM.PriceUSD. 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 StoreContactPurchaseHistoryView
that shows STORE.StoreName, the contacted result of STORE.Phone and
STORE.Contact from the StoreContactAndPhone function, ITEM.ItemID, ITEM.
PurchaseDate, ITEM.ItemDescription, and ITEM.PriceUSD. 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 StoreHistoryView that sums the
PriceUSD column of StorePurchaseHistoryView for each store into a column named
TotalPurchases. Run the statement to create the view, and then test the view with an
appropriate SQL SELECT statement.

R. Write an SQL statement to create a view called MajorSources that uses
StoreHistoryView and selects only those stores that have TotalPurchases greater than
100000. Run the statement to create the view, and then test the view with an appropri-
ate SQL SELECT statement.

S. 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.

Figure 7-49
Sample Data for the
MI EMPLOYEE table

EmployeeID LastName FirstName Department Phone Fax EmailAddress

101 Morgan James Executive 310-208-1401 310-208-1499 [e-mail]

102 Morgan Jessica Executive 310-208-1402 310-208-1499 [e-mail]

103 Williams David Purchasing 310-208-1434 310-208-1498 [e-mail]

104 Gilbertson Teri Purchasing 310-208-1435 310-208-1498 [e-mail]

105 Wright James Receiving 310-208-1456 310-208-1497 [e-mail]

106 Douglas Tom Receiving 310-208-1457 310-208-1497 [e-mail]