Create a PL/SQL program that handles the order placement for the application described in Project 1. When customer Newton decides to buy, all the items in his shopping cart that are not in the wish list will be included in a purchase order. The following actions take place:
1. If there are items in the shopping cart for Newton, a new row is inserted in the ORDERS table. Increment the order ID of the new row accordingly.
2. For each item in the SHOPPING_CART_ITEMS that is in Newton’s buying list (not in the wish list) do the following:
a. A corresponding item is created in the ORDER_ITEMS table. All these newly added order items refer to the order created in step 1.
b. The quantity in INVENTORY_ITEMS table is updated accordingly
3. All the items in the SHOPPING_CART_ITEMS table that were just ordered are removed
Add a few output lines using DBMS_OUTPUT.PUT_LINE function to indicate how your program progresses through the list of actions.
Submit the PL/ SQL program as a text file (.txt) following the document naming convention FirstnameLastnameHW3.txt.
reference: project 1:
Consider the multi-table third normal form (3NF) database specified in the file “Project 1 DDL.txt” attached. The database is used by an e-commerce website that sells a variety of products. Customers go to the web site, select products based on product categories and sets of product features, and fill a shopping cart with products that they want to buy. Later on, they place an order for one or more products that exist in the shopping cart. Once an order is placed, all the items that were bought are removed from the shopping cart and added to the ordered items table. The shopping cart has also the ability to hold products as part of a wish list, which the customer is interested in but hasn’t decided yet to buy.
Use the file “Project 1 DDL.txt” to study and understand the structure of this database. Use it also to create and populate the database on your personal Oracle account. Then generate SQL code to answer the following questions:
- Show the shopping cart content for the customers with word ‘David’ in their full name, sorted with the wish list at the end
- List of ALL customers and the total price of their shopping carts, excluding the wish list
- List ALL shopping carts in descending order of the number of items that they hold, excluding the wish list
- List ALL products and the number of shopping carts they are in (if any)
- List the products with a ‘Brand’ feature in ‘Outdoors’ and ‘Electronics’ categories
- Create a view of unshipped goods, with individual quantities and prices, for each customer, and select all the rows from it. An order item is unshipped if theDateShipped attribute in set to NULL
- Using the view created before, display all customers who have unshipped orders, together with the total value of the unshipped orders per customer
- List the first three most sold products of category ‘Books’ (don’t count unshipped orders)
- Display the number of ‘HP’ brand products sold during the last month (from current date)
- Use a correlated query to list the names of the customers who have more than 2 copies of the same item in their shopping cart
- List of shopping carts, together with the cheapest and the most expensive product in each.
The second file attached (“Project 1 Results.txt”) contains the expected results for each of the queries. Try to make your queries match the content provided in this second file.
Use your Oracle account to run these SQL statements and verify correctness.