o. Write an SQL statement to create a view called StorePurchaseHistoryView that

Question

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 state- ment to create the view, and then test the view with an appropriate SQL SELECT statement. Write an SQL statement to create a view called StoreContactPurchaseHistoryView that shows STORE.StoreName, the concatenated result of STORE.Phone and STORE.Contact from the StoreContactAndPhone function, ITEM.ItemID, ITEM.PurchaseDate, ITEM.ItemDescription, and ITEM.PriceUSD. Run the state ment to create the view, and then test the view with an appropriate SQL SELECT statement. P.

Explanation / Answer

For creating the view 3 tables are considered namely STORE, ITEM and STORE_ITEM. All the 3 tables are joined to get the relevant field in the View.

Note- Table STORE_ITEM is assumed to keep the record of the Store and their associated items. It has two fileds StoreID and ItemID which references to STORE and ITEM table respectively. You can change it with the appropriate table names.

O) Create view statement is used to create the view.

CREATE VIEW StorePurchaseHistoryView AS
SELECT STORE.StoreName, STORE.Phone, STORE.Contact, ITEM.ItemID, ITEM.PurchaseDate, ITEM.ItemDescription, ITEM.PriceUSD
FROM STORE
INNER JOIN ITEM_STORE
ON STORE.StoreID = ITEM_STORE.StoreID
INNER JOIN ITEM
ON ITEM.ItemID = ITEM_STORE.ItemID;

Executing the view
SELECT * FROM StorePurchaseHistoryView

P)
CREATE VIEW StoreContactPurchaseHistoryView AS
SELECT STORE.StoreName, CONCAT(STORE.Phone, STORE.Contact), ITEM.ItemID, ITEM.PurchaseDate, ITEM.ItemDescription, ITEM.PriceUSD
FROM STORE
INNER JOIN ITEM_STORE
ON STORE.StoreID = ITEM_STORE.StoreID
INNER JOIN ITEM
ON ITEM.ItemID = ITEM_STORE.ItemID;

Executing the View-
SELECT * FROM StoreContactPurchaseHistoryView

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.