onLineShoppingMiniQuizSol

onLineShoppingMiniQuizSol - this view. create view...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
Using the Eclectic Online Shopping Example, create solutions for the following: a) A view showing the loginName, firstName, lastName, orderCartId, and totalPrice of each order. create view orderTotal as select L.orderCartId, sum(L.orderPrice * L.qtyOrdered) as totalPrice from lineItems L group by L.orderCartId; create view orderInfo as select C.loginName, C.firstName, C.lastName, O.orderCartId, O.totalPrice from customerInfo C, orderCartInfo OC, orderTotal O where O.orderCartId = OC.orderCartId and C.loginName = OC.customerId and OC.type = `P’; b) A view showing the loginName, firstName, lastName, and total of all orders placed by each customer. Hint: You can use the view created in the previous problem to express
Background image of page 1
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: this view. create view customerOrderInfo as select loginName, firstName, lastName, sum(totalPrice) as totalPrice from orderInfo group by loginName, firstName, lastName; c) An assertion expressing the fact that even though a customer can be related to many instances of orderCartInfo , a customer can have at most one shopping cart. create assertion customerOneCart check( not exists (select count(orderCartId) as numberOfCart from orderCartInfo where type = S group by customerId having count(orderCartId)> 1)); d) A DDL statement to delete all inventoryItem s that have never appeared in an order. delete from inventoryItem where (itemNum, code) not in (select itemNum, code from lineItems);...
View Full Document

This note was uploaded on 02/27/2011 for the course CSE 412 taught by Professor Davulcu,h during the Spring '08 term at ASU.

Ask a homework question - tutors are online