Create a view named ITEMORDER It consists of the item number

Create a view named ITEM_ORDER. It consists of the item number, description, price, order number, order date, number ordered, and quoted price for all order lines currently on file.

a. Write and execute the CREATE VIEW command to create the ITEM_ORDER view.

b. Write and execute the command to retrieve the item number, description, order number, and quoted price for all orders in the ITEM_ORDER view for items with quoted prices that exceed $100.

c. Write and execute the query that the DBMS actually executes.

d. Does updating the database through this view create any problems? If so, what are

they? If not, why not?

Pratt, Philip J., and Mary Z. Last. A Guide to SQL. Boston, MA: Cengage Learning, 2016. Print.

ORDER LINE ORDERS ORDER NUM ORDER DATE CUSTOMER NUM ORDER NUM TEM NUM NUM ORDERED QUOTED PRICE 5 $86.99 10/12/2015 126 51608 51608 CD33 10/12/2015 334 51610 KL78 25 $10.95 51610 10 51610 $13.99 10/13/2015 386 TR40 51613 $104.95 DL51 51613 10/13/2015 260 51614 $124.95 10/15/2015 586 51614 FD11 51617 $115.99 10/15/2015 126 51617 51619 NL89 3 10/15/2015 586 $116.95 51617 TW35 51623 51619 FD11 2 $121.95 10/16/2015 796 51625 5 $29.95 51623 DR67 12 $36.95 FH24 51623 10 $13.10 KD34 51623 51625 MTO3 8 $45.79

Solution

a.

Create or Replace view ITEM_ORDER as Select OL.ITEM_NUM,I.DESCRIPTION,I.PRICE,O.ORDER_NUM O.ORDER_DATE,OL.NUM_ORDERED,OL.QUOTED_PRICE from ORDERS O inner join ORDER_LINE OL ON O.ORDER_NUM = OL.ORDER_NUM inner join ITEM I on OL.ITEM_NUM = I.ITEM_NUM;

b.

Select ITEM_NUM,DESCRIPTION,ORDER_NUM,QUOTED_PRICE from ITEM_ORDER where QUOTED_PRICE > 100;

c.

Updating database through this view is not possible because the view should be updatable ie its fields should be updatable if we want to update it.